MySQL Bugs: #38209: Partitioning becomes CPU-bound as number of partitions grows beyond 20

Bug #38209	Partitioning becomes CPU-bound as number of partitions grows beyond 20
Submitted:	17 Jul 2008 17:13	Modified:	6 Aug 2008 9:00
Reporter:	Eric Jensen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S5 (Performance)
Version:	5.1.25	OS:	Linux (x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	partitioning, performance

Description:
I have a simple one-to-many mapping table consisting of two integer ID's that are auto_increment in other tables, so keep increasing. It becomes huge. I set it up to range partition every X values in the auto_increment and let it run. What I found, was that instead of mysql being I/O bound as it normally is, as I added more partitions (about 20) it eventually became entirely CPU bound to the point where it was much slower than the I/O bound inserts into the huge unpartitioned tables. My large "insert into my_partitioned_mapping_table values ..." statements were taking 100% mysqld CPU (about half user and half system) in the "Update" thread state. My hypothesis from this is that while selects have efficient partition pruning, inserts are doing something CPU-intensive to check every value against every partition?

Another bug suggest this happens with hundreds of partitions, but I find problems much earlier:  http://bugs.mysql.com/bug.php?id=37252

Perhaps this is due to either of these: 
http://bugs.mysql.com/bug.php?id=35843
http://bugs.mysql.com/bug.php?id=35845

I asked about this on the forum but couldn't find a response
http://forums.mysql.com/read.php?106,216945,216945

How to repeat:
Partition an indexed table into 20 or so partitions by range and do big inserts

Thank you for a problem report. Please, try to repeat with a newr version, 5.1.26, and, in case of the same problem, send exact CREATE TABLE statement for the problematic table.

It's fairly time-consuming for me to recreate my test environment with 5.1.26 instead of 5.1.25.  Are there some specific fixes you think would have influenced this?  You can find my full environment (my.cnf, etc.) in http://bugs.mysql.com/?id=38205

In my eyes this is a duplicate of bug #37252. Do you agree with me?

If the metadata from this bug was migrated to 37252, i.e. that this happens with 10's of partitions and not just 100's, then they would be duplicates, yes.

Ok, I will set this bug as duplicate of bug #37252. I added a comment to the other bug report.