MySQL Forums
Forum List  »  Partitioning

Number of Partitions
Posted by: Brian Clark
Date: April 12, 2006 09:01AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Number of Partitions
3646
April 12, 2006 09:01AM
3585
April 12, 2006 11:41AM


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.