MySQL Forums
Forum List  »  Partitioning

Re: timestamp, datetime as default value as current_timestamp [PARTITION]
Posted by: Aftab Khan
Date: August 31, 2012 01:58AM

>I recommend weekly partitions instead of monthly, unless there is some significance to months in your app. That would lead to about 28 partitions, which I find is a better number

The table in subject is highly transactional,

There are many drawbacks when using InnoDB partitioned tables :

1. the index lookups and locks especially when using a lot of partitions.

2. The number of partitions will influence the performance especially under load , even if you use partition pruning:

Poor MySQL Query Performance on Large Partitioned Tables with A Lot of Partitions (>50)
http://bugs.mysql.com/bug.php?id=37252
3. the optimizer will use a fraction of the partitions (10 first up to now, that may be fixed in a later release) to estimate the index cardinalities.

Work around is to use as less partitions as needed. Your requirement is 7-8 partition, which is ok. However, the performance penalty at run time should be benchmarked. Especially under concurrent insert load.



Edited 2 time(s). Last edit at 08/31/2012 02:10AM by Aftab Khan.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: timestamp, datetime as default value as current_timestamp [PARTITION]
2099
August 31, 2012 01:58AM


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.