Hi,
I am not really sure why you use convert(right(...)).
Is there a particular reason of not simply using
itemid directly.
At first it looks more like a HASH or KEY partitioning scheme.
I did some tests using partitioned tables and upto 40-50 partitions
there is very little overhead in partitioning. With 400 partitions
the processing overhead is still very small but there is a overhead
in opening the table, this overhead is mostly a response time issue.
Apart from those questions it looks fine.
Rgrds Mikael
Brian Clark wrote:
> I have a large database used for monitoring and
> reporting by a Zabbix monitoring system. It is
> entirely InnoDb and I am running under the 4GB
> InnoDb config that is distributed with MySql. In
> order to speed data retieval and cleanup I am
> looking into implementing partitioning on several
> of my largest tables. One table in particular is
> over 2 billion rows and the database sees an
> average of about 650 queries/sec at this point. I
> am currently looking at doing range partitioning
> based on a field called "itemid" as follows.
>
>
> CREATE TABLE `history` (
> `itemid` int(4) NOT NULL default '0',
> `clock` int(4) NOT NULL default '0',
> `value` double(16,4) NOT NULL default '0.0000',
> KEY `itemidclock` (`itemid`,`clock`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
> PARTITION BY RANGE
> (convert(right(itemid,2),unsigned)) (
> PARTITION p00 VALUES LESS THAN (1),
> PARTITION p01 VALUES LESS THAN (2),
> PARTITION p02 VALUES LESS THAN (3),
> PARTITION p03 VALUES LESS THAN (4),
> PARTITION p04 VALUES LESS THAN (5),
> PARTITION p05 VALUES LESS THAN (6),
> PARTITION p06 VALUES LESS THAN (7),
> PARTITION p07 VALUES LESS THAN (8),
> PARTITION p08 VALUES LESS THAN (9),
> PARTITION p09 VALUES LESS THAN (10),
> .
> .
> .
> PARTITION p89 VALUES LESS THAN (90),
> PARTITION p90 VALUES LESS THAN (91),
> PARTITION p91 VALUES LESS THAN (92),
> PARTITION p92 VALUES LESS THAN (93),
> PARTITION p93 VALUES LESS THAN (94),
> PARTITION p94 VALUES LESS THAN (95),
> PARTITION p95 VALUES LESS THAN (96),
> PARTITION p96 VALUES LESS THAN (97),
> PARTITION p97 VALUES LESS THAN (98),
> PARTITION p98 VALUES LESS THAN (99),
> PARTITION p99 VALUES LESS THAN (100));
>
>
> Is this the best option for me to use and is there
> a recommended limit to the number of partitions
> that should be used for a table ?
>
> Brian
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com