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 21:56	Modified:	5 Aug 16:26
Reporter:	Philip Stoev
Status:	Verified
Category:	Server: Partition	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Mattias Jonsson	Target Version:
Triage:	Needs Triage: D2 (Serious) / R6 (Needs Assessment) / E6 (Needs Assessment)

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