Bug #37252 Partitioning performance drops drastically with hundreds of partitions
Submitted: 6 Jun 2008 19:56 Modified: 13 Jun 2012 13:57
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1, 6.0 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R3 (Medium) / E5 (Major)

[6 Jun 2008 19:56] Philip Stoev
Description:
When trying to insert data into a table with hundreds of partitions, performance drops drastically. This means that it is impossible to create data warehouses with one partition per day of year.

How to repeat:
The attached script measures the speed of inserting 10000 records into a table containing 356 partitions:

INSERT Engine: MyISAM, partitioning YES, time 9.86143398284912.
INSERT Engine: MyISAM, partitioning NO, time 2.6672580242157.

INSERT Engine: Innodb, partitioning YES, time 11.0261478424072.
INSERT Engine: Innodb, partitioning NO, time 7.79081797599792.

INSERT Engine: Falcon, partitioning YES, time 14.0795571804047.
INSERT Engine: Falcon, partitioning NO, time 8.14853501319885.

This ammounts to almost halving the performance of Falcon.

Suggested fix:
The manual specifies a 1024 limit for partitions. We should scale nicely up to that limit, or we should note in the documentation that performance degrades so much.
[6 Jun 2008 19:57] Philip Stoev
Test case for bug 37252

Attachment: bug37252.pl (text/plain), 1.06 KiB.

[6 Jun 2008 19:58] Philip Stoev
Workaround - use a smaller number of partitions.
[30 Jun 2008 16:56] Mattias Jonsson
Might be related to bug#35845, since it will rebuild indexes for all partitions, not only the one that has been used.
[6 Jul 2008 1:21] Mattias Jonsson
Bug#35845 does not impact the performance in this case, it is probably the lock/unlock of all partitions that takes time.

A better workaround is to issue 'LOCK TABLE <table> WRITE' before issuing a lot of inserts to a heavily partitioned table, since this avoids locking of all partitions in the table for every query. (And of course grouping as many rows as possible into as few queries as possible, INSERT INTO <table> VALUES (x), (y), ...).
[1 Aug 2008 0:20] Philip Stoev
Results for 5.1:

Without LOCK TABLE:

INSERT Engine: MyISAM, partitioning YES, time 82.6796810626984.
INSERT Engine: MyISAM, partitioning NO, time 23.5491971969604.
INSERT Engine: Innodb, partitioning YES, time 74.2537798881531.
INSERT Engine: Innodb, partitioning NO, time 40.6036159992218.

With LOCK TABLE:

INSERT Engine: MyISAM, partitioning YES, time 33.6400599479675.
INSERT Engine: MyISAM, partitioning NO, time 26.1747939586639.
INSERT Engine: Innodb, partitioning YES, time 40.7382659912109.
INSERT Engine: Innodb, partitioning NO, time 36.2722289562225.

Note that LOCK TABLE is not an acceptable workaround if the individual records are inserted by separate clients.
[6 Aug 2008 8:59] Susanne Ebrecht
Bug #38209 is a duplicate of this bug here. The only different, it already happens with less then 100 partitions. Please look also into that bug to get deeper informations from discussions.
[27 Aug 2008 9:05] Mikael Ronström
This is handled by WL#4443
[28 Nov 2008 6:53] Eric Jensen
That worklog number doesn't exist.  Do you mean http://forge.mysql.com/worklog/task.php?id=3513 ?
[12 Dec 2008 11:53] Mattias Jonsson
The work log WL#4443 is now accessible now.
[12 Feb 2009 22:18] Mattias Jonsson
The problem is verified to be locking, if one disables the locking in ha_partition.cc (external_lock, store_lock returns without locking and lock_count returns 0) the time for insert and select by primary key is near constant from non partitioned to 1024 partitions (used MyISAM).

After some discussions with Dmitri, there is no real way to solve the locking by delaying the locks inside the partitioning handler (primarily due to lock failure and table reopen handling), so maybe it can be fixed by doing the pruning earlier, before locking and also prune on inserts and updates.
[22 Oct 2009 15:54] Miguel Solorzano
See bug: http://bugs.mysql.com/bug.php?id=48229
[6 Jan 2010 21:25] James Day
See bug #47261 to track work on a partial solution to this problem. If you subscribed to this bug you probably want to be subscribed to that one as well.
[18 Mar 2010 21:13] James Day
Bug #48846 "Too much time spent in ha_partition::records_in_range if not able to prune" introduced an improvement for this that can be ten times as fast with MyISAM or twice as fast with InnoDB when there are 1024 partitions.
[7 May 2010 16:40] Mattias Jonsson
The problem with this bug is that MySQL server always starts with opening and locking all tables before optimizing and executing the query.

Since pruning is done as an early step in the optimization, open and lock is done before pruning and is done for all partitions.

This means that even for queries that only will use one partition, all partitions must do open and lock (just like they where tables) and that is an unnecessary operations, since in some queries it would be enough to only open and lock one partition.

Note that the locks is ordinary storage engine dependent locks (table/partition locks on MyISAM, row-locks on InnoDB etc.), so there are no additional locks specific to partitioning (such as a table lock), it only means that all partitions needs to be opened and locked, since at this stage the information about which partitions will be used are not yet available.
[23 Oct 2010 14:51] Matthew Lord
Marked http://bugs.mysql.com/bug.php?id=57668 as a duplicate of this one.
[3 Mar 2011 15:24] Roberto Spadim
hum, a 'pseudo' lock could help partition? for example, lock only .frm file (1 file lock only, not X*partition files/rows locks)... and after optimize flock real files, just for partitioned tables...
[13 Jun 2012 13:52] Jon Stephens
Fixed in 5.6.6. Documentation in progress.
[13 Jun 2012 13:57] Jon Stephens
Documenting the fix for this bug will be handled in the course of documenting WorkLog 4443 (https://forge.mysql.com/worklog/task.php?id=4443) of which this work forms a part, closing.
[6 Feb 2015 19:01] Jon Stephens
See also BUG#64498 and BUG#75753.