Arduino + rPi and controlling sensors

I am trying to wrap my brain around how to best do this. I'm pretty new to arduino. From what I can understand, I have to have compiled code on the arduino that will send or listen for data via serial port from my rPi. I have found a tutorial that I think will let me do that.

My question is, when I have multiple sensors or relays attached to the arduino, what's the best way to start/stop data collection or turn on/off a relay?My rPi and arduino uno are connected via USB (serial). I am setting up a web-based control system on the rPi. My only thought was to have the ardunio talk to a MySQL db on the rPi. There, I could create a table that simply records a 1 or 0 if data collection or sensor is to be on. The problem I can see with this is overhead. The arduino would need to check with each iteration of a particular function to see if the sensor is listed as on, and then store the data. That's two SQL statements with each sensor per cycle. The I/O on a rPi is nothing stellar and I can see this bottlenecking eventually. What is another way to do this? I may be fundamentally off on how the two are to communicate.

From what I can understand, I have to have compiled code on the arduino that will send or listen for data via serial port from my rPi

Yes.

when I have multiple sensors or relays attached to the arduino, what's the best way to start/stop data collection

That depends on what data you are collecting, and where you are collecting it. There is no on-size-fits-all answer.

r turn on/off a relay?

There is only one way to turn on or off a relay. Turn the digital pin on (HIGH) or off (LOW).

My only thought was to have the ardunio talk to a MySQL db on the rPi.

It can't do that. It can talk to the Pi, and the Pi can talk to the MySQL database.

I may be fundamentally off on how the two are to communicate.

Pick a particular project. Implement that. The Pi shouldn't have to talk to the database every time the Arduino asks for information. The Arduino shouldn't have to ask for information all that often. In any case, how much communication happens is a result of a SPECIFIC project, not some hand-waving.

Take a look at Arduino Yun. A Linux processor (to replace the Pi) and the AVR (arduino) all in the arduino sized package with e-net and WiFi.

Mark

From what I can understand, I have to have compiled code on the arduino that will send or listen for data via serial port from my rPi.

Well no you actually compile the code on your computer and then download that code to the arduino.

what's the best way to start/stop data collection

Simply do not read them or if you do read them don't do anything with the result.

The I/O on a rPi is nothing stellar and I can see this bottlenecking eventually.

What data rate do you envisage? The problem with the Pi is that the Linux keeps wandering off and so in any real time communication you need to buffer the input. This is done automatically with the serial port and so is a good choice. On the Pi end you have about a 4K buffer.

You need to give some more detail about the project:
How many sensors are there?
How often do they need to be polled?
How many relays are there?
What sort of response is necessary for the turning off or on of the relay and how often? (i.e. is a 1, 10, 100 ms delay OK?)
How often will a sensor be enabled/disabled by the user or program?

As an aside, it sounds like a Gertboard would be ideal for this application if you have not got one already. (https://www.sparkfun.com/products/11773 or Farnell et. al.)
Without answers to the above questions I will shoot in the dark…

To avoid latency with the SQL lookups, use a “push” scenario rather than the Arduino polling frequently, reading the same data over and over. e.g.
When the units (Arduino and RPi(this is where the Gertboard would help with syncronisation)) are initially turned on, the rpi reads the DB and downloads which sensors should be enabled to the Arduino, to be written to a lookup table. e.g.

1:0
2:1
3:1
4:0

Sensor 1 is off; sensor 2 is on; sensor 3 is on; sesnor 4 is off

After that the RPi will only send changes as they are made to the status of each sensor, by UART to the Arduino (e.g. “3:0” ,not every sensor). The Arduino would poll the its UART every so many ms using a non-blocking poll (see blink without delay example sketch). The frequency would depend on the answers above but should not be critical as the Arduino UART has a 64 byte buffer so should buffer quite a few updates.

The Arduino would read the sensors according to its internal lookup table (as to which ones should be read) at a frequency determined by answers above. these would be sent by serial to the Pi in a similar fashion. e.g.

2:2653
3:142

Th Pi would be polling its serial port and write these to the DB.

To minimise communication between the Pi and Arduino the relays could be activated through the Pi GPIO (buffered of course) as I assume a couple of ms latency of the Pi will not be critical. Be quicker and less overhead than telling the Arduino to turn them on.