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:
Tags: partitioning, performance

[17 Jul 2008 17:13] Eric Jensen
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
[31 Jul 2008 17:44] Valerii Kravchuk
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.
[4 Aug 2008 17:31] Eric Jensen
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
[5 Aug 2008 8:37] Susanne Ebrecht
In my eyes this is a duplicate of bug #37252. Do you agree with me?
[5 Aug 2008 13:59] Eric Jensen
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.
[6 Aug 2008 9:00] Susanne Ebrecht
Ok, I will set this bug as duplicate of bug #37252. I added a comment to the other bug report.