MySQL Forums
Forum List  »  Partitioning

Thoughts on partitioning by hour - and which partition type?
Posted by: Adrian Snow
Date: June 03, 2008 06:09AM

I've recently upgraded to 5.1.24 specifically in order to make use of table partitioning.

We are recoring into an 'events' table at the rate of about 100 million records per day, and this is set to increase over the coming months.

Most of the queries specify a tight time period, usually over the last few hours, but sometimes over the last 24.

Records are purged from the events table after they exceed a 24hour limit, but I am looking into providing longer periods of time by moving these records to an 'events_archive' table.

Now, having read just about every post in the partioning section of this site I'm considering the following implementation.

1. Partitioning my events table into 24 partitions, one for each hour of the day.
2. Using a bespoke integer column for the hour_part of the date_time of each record.
3. Setting the value of this hour_part column using a trigger on the insert, or more probably in the application that performs the insert.

Simplified my table looks like this:

CREATE TABLE `events` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`site_id` tinyint(3) unsigned NOT NULL,
`raw_packet_id` bigint(20) unsigned DEFAULT NULL,
`date_time` datetime NOT NULL,
`time_part` int(9) unsigned zerofill NOT NULL DEFAULT '000000000',
`hour_part` tinyint(3) unsigned NOT NULL DEFAULT '0',
`frame_number` int(10) unsigned NOT NULL DEFAULT '0',
`protocol_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`message_id` mediumint(5) unsigned DEFAULT '0',
`event_src_ip` int(11) unsigned NOT NULL DEFAULT '0',
`event_dst_ip` int(11) unsigned NOT NULL DEFAULT '0',
`f1` varchar(64) DEFAULT NULL,
PRIMARY KEY (`hour_part`, `id`),
UNIQUE KEY `id` (`hour_part`, `id`),
KEY `id_2` (`id`),
KEY `ind_protocolid` (`protocol_id`),
KEY `idx_dt_tp` (`date_time`,`time_part`),
KEY `ind_message_id` (`message_id`),
KEY `ind_frame` (`frame_number`),
KEY `ind_event_src_ip` (`event_src_ip`),
KEY `ind_event_dst_ip` (`event_dst_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY LIST( hour_part ) (
PARTITION p00 VALUES IN (0),
PARTITION p01 VALUES IN (1),
PARTITION p02 VALUES IN (2),
PARTITION p03 VALUES IN (3),
PARTITION p04 VALUES IN (4),
PARTITION p05 VALUES IN (5),
PARTITION p06 VALUES IN (6),
PARTITION p07 VALUES IN (7),
PARTITION p08 VALUES IN (8),
PARTITION p09 VALUES IN (9),
PARTITION p10 VALUES IN (10),
PARTITION p11 VALUES IN (11),
PARTITION p12 VALUES IN (12),
PARTITION p13 VALUES IN (13),
PARTITION p14 VALUES IN (14),
PARTITION p15 VALUES IN (15),
PARTITION p16 VALUES IN (16),
PARTITION p17 VALUES IN (17),
PARTITION p18 VALUES IN (18),
PARTITION p19 VALUES IN (19),
PARTITION p20 VALUES IN (20),
PARTITION p21 VALUES IN (21),
PARTITION p22 VALUES IN (22),
PARTITION p23 VALUES IN (23)
);


Checking some explains, as long as we extract the hour out of the date_time paramters supplied in the SQL statement and use these in addition to any date_time constraints the partition pruning seems to work.

(Note the table is empty at the moment so 'ROWS' is very low in the explain)

explain partitions select * from events
where protocol_id = 123
and (hour_part >= '01' and hour_part <= '03')
and date_time > '2008-06-02 01:30:00' and date_time < '2008-06-02 02:30:00';

+----+-------------+--------+-------------+
| id | select_type | table | partitions |
+----+-------------+--------+-------------+
| 1 | SIMPLE | events | p01,p02,p03 |
+----+-------------+--------+-------------+

+-------+-----------------------------+------+
| type | possible_keys | key |
+-------+-----------------------------+------+
| range | id,ind_protocolid,idx_dt_tp | id |
+-------+-----------------------------+------+

+---------+------+------+-------------+
| key_len | ref | rows | Extra |
+---------+------+------+-------------+
| 1 | NULL | 3 | Using where |
+---------+------+------+-------------+

Now, as part of the archiving process, each hour I should be able to copy the whole of one partiion across to my event_archive table, which I was considering a rolling partition by day (as many examples in this forum show) keeping about 1 weeks worth of records. Then I can just drop and recreate the relevant hour partition.


Do you think I'm onto a winner here and also have I chosen the best parition type (LIST) for this type of scenarion.

Many thanks for any suggestions
best
Ade

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.