Hi,
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:
http://mikaelronstrom.blogspot.com