MySQL Forums
Forum List  »  Partitioning

Re: Reasonable number of partitions?
Posted by: Mikael Ronström
Date: March 13, 2007 09:53PM

From what I understand the major advantage you'll get is that
you split the B-tree indexes and thus ensure that there are
less disk seeks in a query. This will be most advantegous with
a growing number of partitions. However growing number of
partitions will mean more files to open (a bit dependent on the
storage engine used) when a table is used. What I've seen with
sqlbench is that upto 30-40 partitions is no problem. Beyond that
the number of partitions has an effect. However as usual it's very
much upto what queries you use as to where the optimum lies.

The actual performance gain is too much effected by query mix to give
any general guidelines.

Rgrds Mikael

Shlomi Noach Wrote:
> Thanks for the detailed reply!
> I do indeed expect to have a good performance gain
> using partition pruning.
> My database resembles somewhat a web-based mail
> service, where a certain table holds data for all
> users notifications.
> Suppose, for example, that you have a Yahoo! mail
> account, and the table in which your emails reside
> also holds data for all other users.
> What I wish to partition is the HASH for the user
> ID on this table. Since user queries are only
> relevant to his/her own data, and never to other
> users data, I expect a performance boost using
> partition pruning.
> My real scheme, though, also has sub-users, and
> that's where I wanted to add sub-partitions.
> At any case, I took notice of the total 1024
> partitions limit, and the fact it's not possible
> (yet!) to sub partition a HASH partition.
> Is there a rough estimation, for some benchmark,
> for the performance gain achieved by partitioning
> a table to 10 partitions, to 50, etc.?
> Thanks
> Shlomi

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:

Options: ReplyQuote

Written By
March 08, 2007 10:09AM
Re: Reasonable number of partitions?
March 13, 2007 09:53PM
March 14, 2007 12:34PM
September 20, 2007 08:36PM
September 12, 2007 11:26AM

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.