MySQL Forums
Forum List  »  Partitioning

Re: Number of Partitions
Posted by: Mikael Ronström
Date: April 12, 2006 11:41AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
3282
April 12, 2006 09:01AM
Re: Number of Partitions
3256
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.