Still creating the partitions to see if I can benefit from this feature, but I believe the biggest issue was server configuration.
The innodb_buffer_pool_size was far too low. Set to 8M as default, Rick James also recommends 70% of RAM so I increased it to 4G on a 12G shared server and the results were astounding. The slow query completles in 1.6 seconds and everything is much faster, even my web application is flying. I'll post results once I have partitioning in place too, however I do feel that this is probably the best solution to my lag problem.
EDIT 16-11-11 SOLUTION
I am in the process of refactoring a large table that logs telemetry data, it has been running for about 4-5 months and has generated approx. 54 million records with an average row size approx. 380 bytes.
I have started to see some performance lag on one of my raw data queries that returns all logs for a device over a 24 hour period.
Initially I thought it was indexing, but I think it is the amount of I/O that needs to be processed by MySQL. A typical 24 hour query would contain
2.2k 3k to 9k records and I’d actually like to support an export of about 7 days.
I am not experienced in database performance tuning so still just learning the ropes. I am considering a few strategies.
CREATE TABLE `message_log` (
`db_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`db_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created` datetime DEFAULT NULL,
`device_id` int(10) unsigned NOT NULL,
`display_name` varchar(50) DEFAULT NULL,
`ignition` binary(1) DEFAULT NULL COMMENT 'This is actually IO8 from the falcom device',
`sensor_a` float DEFAULT NULL,
`sensor_b` float DEFAULT NULL,
`lat` double DEFAULT NULL COMMENT 'default GPRMC format ddmm.mmmm \n',
`lon` double DEFAULT NULL COMMENT 'default GPRMC longitude format dddmm.mmmm ',
`heading` float DEFAULT NULL,
`speed` float DEFAULT NULL,
`pos_validity` char(1) DEFAULT NULL,
`device_temp` float DEFAULT NULL,
`device_volts` float DEFAULT NULL,
`satellites` smallint(6) DEFAULT NULL, /* TINYINT will suffice */
`navdist` double DEFAULT NULL,
`navdist2` double DEFAULT NULL,
`IO0` binary(1) DEFAULT NULL COMMENT 'Duress',
`IO1` binary(1) DEFAULT NULL COMMENT 'Fridge On/Off',
`IO2` binary(1) DEFAULT NULL COMMENT 'Not mapped',
`msg_name` varchar(20) DEFAULT NULL, /* Will be removed */
`msg_type` varchar(16) DEFAULT NULL, /* Will be removed */
`msg_id` smallint(6) DEFAULT NULL,
`raw` text, /* Not needed in primary query, considering adding to single table mapped to this ID or a UUID correlation ID to save on @ROWID query */
PRIMARY KEY (`db_id`),
KEY `Name` (`display_name`),
KEY `Created` (`created`),
KEY `DeviceID_AND_Created` (`device_id`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ml.db_id, ml.db_created, ml.created, ml.device_id, ml.display_name, bin(ml.ignition) as `ignition`,
bin(ml.IO0) as `duress`, bin(ml.IO1) as `fridge`,ml.sensor_a, ml.sensor_b, ml.lat, ml.lon, ml.heading,
ml.speed,ml.pos_validity, ml.satellites, ml.navdist2, ml.navdist,ml.device_temp, ml.device_volts,ml.msg_id
FROM message_log ml
WHERE ml.device_id = @IMEI
AND ml.created BETWEEN @STARTDATE AND DATE_ADD(@STARTDATE,INTERVAL 24 hour)
ORDER BY ml.db_id;
WHERE ml.IMEI = @IMEI
(SELECT 1 as '' FROM devices where imei_number = @IMEI AND customer_id = 403)
AND ml.created BETWEEN '2016-11-06 13:30:00 ' AND DATE_ADD('2016-11-06 13:30:00 ',INTERVAL 24 hour)
ORDER BY db_id;
Implement ranged partitioning by date: a) Keep monthly partitions. E.g. last 6 months b) Move anything older to archive table.
This is a very good idea. I gues all the writes will be in the newest partition and you will query recent data only. You always want a situation where your data and index fits in memory. So no disk i/o on reads.
Depending on your use case it might even be wise to have one partition per week. Then you only have to keep max two weeks of data in memory for reading the last 7 days.
You might also want to tune your buffer sizes (i.e. innodb_buffer_pool_size) if you are using innodb as a engine or myisam_key_cache when using myisam engine.
Also adding ram to the DB machine usually helps as the os can then have the data files in memory.
If you have heavy writes you can also tune other options (i.e. how often writes are persisted to disk with innodb_log_buffer_size). This is in order to let dirty pages be in memory for longer to avoid writing them back to disk too often.