Number of Partitions
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