For the project I'm working on right now, I need to update my MySQL database with between 0 and 504 pairs of datapoints as quickly as possible.
I wanted to share the methodology I'm using both hopefully to help other people if they have similar needs and to see if anyone sees opportunities for improvement.
My normal method for sending information to MySQL is to call a Process each time that I need to add a certain line. This is very easy to implement, but the downside is that if you have to repeat the operation many times, the overhead time of establishing the MySQL connection quickly stacks up.
For the particular query that I'm running, establishing the connection and inserting 1 row takes about 0.7 seconds. If I have to perform the maximum update of 504 rows (which happens), sequentially executing the Process would mean almost 6 minutes of wait time.
This is how I ended up holding open a single MySQL connection and feeding data into it:
- Initiate a Process which starts a php script
- Run the php script asynchronously
- Immediately after starting, the php script opens a database connection and then waits for data to be fed into STDIN
- The arduino script now feeds each pair of data into STDIN
- When the last datapoints have been sent, the arduino sends two blank rows to STDIN
- Once the php script reaches the two blank rows, it closes the connection and sends a completion character back to the arduino
- The arduino waits on the completion character, otherwise the Process could be ended before php had made it all the way through the data
The result is a significantly faster query speed for an unknown number of queries.
1 query still takes 655ms
10 queries take 1,076ms
504 queries take 24,000ms (instead of 330,120ms)
If my possible number of queries had been smaller or more predictable, I could have say loaded 10 queries at once into my Process and then executed that as often as needed, but I went with this method because I would run out of memory trying to pass all 504 queries at once and there isn't a reason to use all that execution time when I might only be sending 1 data pair that cycle.
<?php
$runid = $argv[1]; //runid
$temp = $argv[2]; //temperature
$displayonly = $argv[3]; //displayonly
$DBServer = '*******';
$DBUser = '*********';
$DBPass = '***********';
$DBName = '**********';
$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
// check connection
if ($conn->connect_error) { trigger_error('Database connection failed: ' . $conn->connect_error, E_USER_ERROR);}
//sends an "R" after the connection is ready
printf ("%s \n", "R");
$thermostat = "999";
while ( $thermostat != '' ) {
//retrieves data send from the arduino to STDIN
$thermostat = trim(fgets(STDIN)); // reads one line from STDIN
$cont = trim(fgets(STDIN)); // reads one line from STDIN
$tray = (FLOOR($thermostat/85) + 1); //calculates which tray the thermostat belongs to
$traythermostat = $thermostat - (($tray - 1)*84);
if ( $thermostat != '' ) {
//uploads the data to MySQL
$sql="INSERT INTO ovenshifter_data (runid, thermostat, continuity, temperature, displayonly, tray, traythermostat) VALUES ($runid, $thermostat, $cont, $temp, $displayonly, $tray, $traythermostat)";
if($conn->multi_query($sql) === false) {trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);}
}
}
printf ("%s \n", "F"); //sends a "F" after the upload is finished
?>
//this function opens a php script and sends data to a remote MySQL server
void LinuxDataTrayInternal() {
String result;
if (traycontk > 0) { //only proceeds if a data pair is present
unsigned long speedtime = millis(); //timer to track processing speed
Process TrayWrite; //this sends information to the db
TrayWrite.begin("php-cli"); //calls php
TrayWrite.addParameter("/mnt/sda1/db_traywrite.php"); //adds the script path
TrayWrite.addParameter(runid); //insert the runid, which is the unix timestamp when the run began
TrayWrite.addParameter(temperature); //insert the temperature that the thermostats tripped at
TrayWrite.addParameter(String(displayonly)); //insert whether the readings should be used only for display purposes or used as data
TrayWrite.runAsynchronously(); //runs the process asynchronously, since data will be fed into it as it runs
while (TrayWrite.available() == 0) {} //waits until the database connection has been established
while (TrayWrite.available() > 0) { //clears out the completion characters
char c = TrayWrite.read();
if (c != '\n')
result += c;
}
result = "";
for (int kt = 0; kt < (traycontk); kt++) { //the data is in pairs in the array (thermostat and continuity) so it increments by 2 each time
String tempcont = traycontc[kt];
int temptherm = int(traycont[kt]);
TrayWrite.println(String(temptherm)); //insert the thermostat #
TrayWrite.println(tempcont); //insert the continuity reading
}
TrayWrite.println(); //prints an empty thermostat to indicate input is over
TrayWrite.println(); //prints an empty continuity to indicate input is over
while (TrayWrite.available() == 0) {} //waits until the script finishes (it will send an "F" back
while (TrayWrite.available() > 0) { //clears out the completion characters
char c = TrayWrite.read();
if (c != '\n')
result += c;
}
Console.println(result);
result = "";
//after all of the data pairs have been sent
TrayWrite.close(); //closes the php script
Console.println(String(String(traycontk) + "," + String(millis() - speedtime)));
}
}