MySQL Forums
Forum List  »  Partitioning

When can we have range partitioning to_hour?
Posted by: Michael Gargiullo
Date: September 28, 2006 09:42AM

We've been running tests using partitions for a few days. Last night we came up with our best performance yet, 101M rows in 2.25 hours (with 5 indexes). The problem is 13 Hours later, we've barely doubled the number of rows in the table and each load of 150000 rows takes an additional 3-5 minutes then the last. (all data is currently from the date). It currently takes 22m 32.058s.

We currently use range partitions to_days(<date field>) with Hash sub partitions using the datewith the Hour tacked on like so:

CREATE TABLE `LogsFirewall` (
`eventid` bigint(1) unsigned NOT NULL,
`logfileid` int(1) unsigned NOT NULL,
`logrecnum` int(1) unsigned NOT NULL,
`time_` datetime NOT NULL,
`action` varchar(15) DEFAULT NULL,
`orig` varchar(150) DEFAULT NULL,
`if_name` varchar(25) DEFAULT NULL,
`proto` char(4) DEFAULT NULL,
`src` int(1) unsigned NOT NULL,
`dst` int(1) unsigned NOT NULL,
`service` varchar(25) DEFAULT NULL,
`s_port` int(1) DEFAULT NULL,
`rule` tinyint(1) NOT NULL,
`user_` varchar(25) DEFAULT NULL,
`xlatesrc` varchar(25) DEFAULT NULL,
`xlatedst` varchar(25) DEFAULT NULL,
`xlatesport` int(1) DEFAULT NULL,
`xlatedport` varchar(25) DEFAULT NULL,
`nat_rulenum` int(1) DEFAULT NULL,
`nat_addtnl_rulenum` varchar(25) DEFAULT NULL,
`message_info` varchar(150) DEFAULT NULL,
`sys_msgs` varchar(50) DEFAULT NULL,
`methods` varchar(50) DEFAULT NULL,
`peergateway` varchar(16) DEFAULT NULL,
KEY `eventid` (`eventid`),
KEY `src` (`src`),
KEY `dst` (`dst`),
KEY `rule` (`rule`),
KEY `dt` (`time_`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE( to_days(time_) )
SUBPARTITION BY HASH( cast(date_format(time_,'%Y%m%d%H') as signed) ) (
PARTITION p0 VALUES LESS THAN (20060918)(
SUBPARTITION s0a
DATA DIRECTORY = '/FW_Data1'
INDEX DIRECTORY = '/FW_Indx1',
SUBPARTITION s0b
DATA DIRECTORY = '/FW_Data2'
INDEX DIRECTORY = '/FW_Indx2',
SUBPARTITION s0c
DATA DIRECTORY = '/FW_Data3'
INDEX DIRECTORY = '/FW_Indx3',
SUBPARTITION s0d
DATA DIRECTORY = '/FW_Data4'
INDEX DIRECTORY = '/FW_Indx4',
SUBPARTITION s0e
DATA DIRECTORY = '/FW_Data5'
INDEX DIRECTORY = '/FW_Indx5',
SUBPARTITION s0f
DATA DIRECTORY = '/FW_Data6'
INDEX DIRECTORY = '/FW_Indx6'
),... several more partitions created.

Each Directory is a seperate disk spindal.

When will we be able to partition to a range down to the hour instead of day?



Edited 2 time(s). Last edit at 09/28/2006 09:46AM by Michael Gargiullo.

Options: ReplyQuote


Subject
Views
Written By
Posted
When can we have range partitioning to_hour?
4374
September 28, 2006 09:42AM


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.