Speed test using database on parser

[lwillmeth]

Per Benham's suggestion, set up a mysql database on the parser and run a speed comparison for CSV file vs database inserts.

• [x] sudo apt-get install mariadb-server
• [x] Set up mariadb credentials
• [x] pip3 install PyMySQL
• [x] cp Database/config.yml.example Avionics/config.yml
• [x] vim Avionics/config.yml (add mariadb credentials)
• [x] Modify Mariadb to directly insert into the avionics table
• [x] Modify mainPayload to use Mariadb
• [x] Run a speed test

[lwillmeth]

Here's my best-case scenario so far, using blocks of 500 lines and running everything except the BMP:

pi@esra-a:~/demoMySQL $ time python3 mainPayload.py 
real    0m13.074s
user    0m3.070s
sys 0m0.990s

Compare to the current implementation using file.write(), which uses blocks of ~50 lines:

pi@esra-a:~/demo $ time python3 mainPayload.py 
real    0m11.361s
user    0m1.800s
sys 0m1.080s

Conclusion:

Mysql is really nice in some ways. It can simplify problems with the timestamp. It can make exporting/importing data really easy. It is not fast.

Mysql runs at about 1000/13.074 = 76.5 Hz File.write() runs at about 1000/11.361 = 88 Hz

Is that a huge difference? Kinda, maybe. 12 Hz sounds like a lot to me, and these conditions already favor mysql. Using file.write with a larger buffer may widen the gap.

We should talk about how long we're comfortable holding data before committing it.

[lwillmeth]

Here's a longer result using 10,000 samples:

pi@esra-a:~/demo $ time python3 mainPayload.py 

real    1m49.596s
user    0m16.710s
sys 0m7.820s

pi@esra-a:~/demoMySQL $ time python3 mainPayload.py 

real    2m9.111s
user    0m22.700s
sys 0m8.520s

Results: file.write gets even better on larger samples.

file.write = 91.24 Hz mysql = 77.45 Hz