MySQL Bugs: #37252: Partitioning performance drops drastically with hundreds of partitions

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

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.

Test case for bug 37252

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

Workaround - use a smaller number of partitions.

Might be related to bug#35845, since it will rebuild indexes for all partitions, not only the one that has been used.

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), ...).

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.

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.

This is handled by WL#4443

That worklog number doesn't exist.  Do you mean http://forge.mysql.com/worklog/task.php?id=3513 ?

The work log WL#4443 is now accessible now.

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.

See bug: http://bugs.mysql.com/bug.php?id=48229

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.

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.

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.

Marked http://bugs.mysql.com/bug.php?id=57668 as a duplicate of this one.

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...

Fixed in 5.6.6. Documentation in progress.

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.

See also BUG#64498 and BUG#75753.