When can we have range partitioning to_hour?
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.