1

Our application is collecting data points at different rates (between 500ms - 10,000ms) depending on user settings and that data is queried to plot points on a chart.

I started with the following table structure:

CREATE TABLE `monitored_parameter_data_378` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `command_id` int(11) DEFAULT NULL,
  `data_value` varchar(255) DEFAULT NULL,
  `system_unit` varchar(255) DEFAULT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Once we collected over 20,000 points (which will obviously keep growing) performance slowed down quite a bit. It is taking between 950-1,100ms to get results back so I changed the schema to the following in hopes of benefitting from having the timestamp column indexed:

CREATE TABLE `monitored_parameter_data_378_test` (
  `command_id` int(11) DEFAULT NULL,
  `data_value` varchar(255) DEFAULT NULL,
  `system_unit` varchar(255) DEFAULT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `CREATED_IDX` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Unfortunately I am not seeing any improvement in response time after making the change.

For testing and establishing a baseline I am using the following query:

SELECT * FROM <table_name> WHERE created BETWEEN '2017-01-01' AND '2017-03-13';

The indexed version of the table takes between 850ms-1,100ms to return so I am unsure of how best to proceed. When I run EXPLAIN on the queries I see that the index is being used and that 50% of the data is filtered which is much better than the 11.11% in the non-indexed version.

Does anyone have any insight on how I might take performance to the next level given the fairly simple structure and approach to pulling the data?

Note: the machine in question is Raspberry Pi

Update: I marked @a_vlad's answer as accepted yesterday because based on the original question it is correct. That said, I rewrote the logic to be done in 3 steps, first I get back the first/last point within the date range along with count of total points, second I calculate the interval between those points to based on the number of points the user wants to plot, last I query for the data by doing the following:

'SELECT * FROM monitored_parameter_data_378 WHERE id >= <variable containing first point in range> AND id <= <variable containing last point in range> AND id MOD <variable containing interval> = 0';

This gets the data back onto a chart in ~4 seconds in a table with 1,000,000 records.

7
  • 2
    With these straightforward queries there isn't much you can do except throw more memory and faster storage at the task. You might also want to try partitioning the table by the timestamp range. I'm curious how you measure the response time; I hope you don't include your console output performance into the picture.
    – mustaccio
    Mar 13, 2017 at 22:45
  • @mustaccio Definitely not factoring in output, just going off the # of seconds it takes to execute the query. Table is at 650,000 rows and taking ~12 seconds to grab all points. Trying to add some logic to grab a subset of points equidistant and plot those. Working on a Pi is proving to make this quite a challenge!
    – niczak
    Mar 14, 2017 at 19:26
  • 1
    You may want to rethink your approach -- running an enterprise-grade database on a Raspberry Pi will not be fun. Consider shipping your metrics elsewhere, where you can run a proper database, using some lightweight messaging, such as MQTT.
    – mustaccio
    Mar 14, 2017 at 19:39
  • 1
    Why two "unit" columns? Why (255)?
    – Rick James
    Mar 15, 2017 at 2:08
  • 1
    we all try to answer for direct question, but really the best advice is from @mustaccio - use any method to ship events from Raspberry to anywhere with powerful resources (MQTT, ActiveMQ) and use Raspberry for collect information only. 1M records,it in any case - outside of 1Gb of total memory, as I wrote - look around for the Best SD card what possible to found.
    – a_vlad
    Mar 16, 2017 at 0:37

1 Answer 1

2

First of all, better to combine both of your structures:

  • and have PK
  • and have index for "created"
  • as a variant, think about a natural PK - created + something which uniquely identifies a row
  • added - also think about units (system_unit) - if it is identification of your point(?), it may be a good idea to change it to INT format, it reduces memory and disk space usage for this column.

PK could be good for some other queries, and InnoDB still creates it even if you do not define it, but you can not use it in this case.

More importantly, response time depends not only on indexes (of course it MUST HAVE), but also from number of records. You can change the query to:

SELECT count(*) FROM <table_name> WHERE created BETWEEN '2017-01-01' AND '2017-03-13';

and it will show you - how many records the server must return.

The only few ways for feature improvements:

  • reduce timeframe
  • reduce number of columns - but this is not your case
  • faster server - SSD (if still not), increase memory for InnoDB buffer at least (if it is less than necessary)
4
  • Thank you for the info! As far increasing resources I cannot do so since this is running on a Raspberry Pi but I will try some of the other suggestions and report back.
    – niczak
    Mar 14, 2017 at 13:06
  • 1
    I personally use couple A10/A20 - Raspbery alternative from Olimex.
    – a_vlad
    Mar 14, 2017 at 20:57
  • Checking them out now - what has your experience been?
    – niczak
    Mar 14, 2017 at 20:58
  • 1
    I'm happy, A10 - was navigation heart of my sailing yacht when we cross Pacific - Share GPS and sensors over WiFi, charts (OpenCPN). A20 - A10 - long time work as Master-Slave MySQL tandem - what important it use as much faster SD as possible. To A20 I attache SSD disk, it work briliant
    – a_vlad
    Mar 14, 2017 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.