I have a fairly large (4.6 million rows, 74 columns (average data length is 239)) InnoDB table which stores data for about 1100 sensors polled every 10 minutes (about 150k rows inserted per day).
I do not need data older than 30 days. Every day a query is executed to delete records older than 30 days.
In previous incarnations, it was a MyISAM table and the query (
DELETE FROM data WHERE timestamp < ...) seemed to run relatively quickly. Since conversion to InnoDB, delete operations are noticeably slower (I'm familiar with why).
I've converted the delete query to a stored procedure that iterates over the table in 1000-row chunks, which is more friendly to other clients accessing the table.
However there is still a significant amount of time involved waiting for the deletion to finish (sometimes an hour or more). Research led me to the concept of horizontal partitioning wherein the table would be split by date range, for example. Since the table is effectively a rolling log, my thought was to partition it by day, so that each day the oldest partition is simply dropped.
It appears that new partitions are not something that happen automatically once a table has been partitioned; in other words, it would not create a new partition at midnight. I was hoping that there is some way to define a scheme which MySQL would automatically write new rows to an appropriate new partition, which seems most logical for rolling logs such as this.
Is my understanding of partitioning incorrect, with regard to how new partitions are created and used? Ideally I'd like a "set and forget" approach; maybe partitioning is not the right solution.
Some additional info:
- Most queries filter on the date column, which has an index
- The hardware is a 2-processor Intel Xeon E5405 with 18 GB of RAM running Windows Server
The table create:
CREATE TABLE `data` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `sensor_id` int(10) unsigned NOT NULL, `timestamp` datetime NOT NULL, -- 71 other columns... PRIMARY KEY (`id`), UNIQUE KEY `IX_sensor_timestamp` (`sensor_id`,`timestamp`), KEY `IX_timestamp` (`timestamp`), KEY `IX_sensor_id` (`sensor_id`) ) ENGINE=InnoDB AUTO_INCREMENT=78542614 DEFAULT CHARSET=latin1