Partitioning performance drops drastically with many partitions

[Tessa]

This has been documented and apparently fixed in this upstream bug in MySQL 5.6:

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

Basically, more partitions == much worse performance. I just used the example script from that bug to verify the behaviour on a fresh install of Percona Server 5.5.25a-27.1-log. I changed the script to use 100000 rows because 10000 rows completed too quickly on my hardware, and got the following results:

INSERT Engine: MyISAM, partitioning YES, time 42.2766079902649.
INSERT Engine: MyISAM, partitioning NO, time 8.36221385002136.
INSERT Engine: Innodb, partitioning YES, time 46.502082824707.
INSERT Engine: Innodb, partitioning NO, time 12.8495020866394.
INSERT Engine: Falcon, partitioning YES, time 32.1179881095886.
INSERT Engine: Falcon, partitioning NO, time 12.4245889186859.

We're starting to use larger partition counts pretty heavily, so we'd definitely like to see the fix backported to Percona Server 5.5 and 5.1 if possible.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Tested on PS 5.5.27-28.1

./bug37252.pl
INSERT Engine: MyISAM, partitioning YES, time 7.48948097229004.
INSERT Engine: MyISAM, partitioning NO, time 5.67519211769104.
INSERT Engine: Innodb, partitioning YES, time 13.065337896347.
INSERT Engine: Innodb, partitioning NO, time 7.52108716964722.
INSERT Engine: Falcon, partitioning YES, time 13.8746740818024.
INSERT Engine: Falcon, partitioning NO, time 7.78384900093079.

(Don't consider absolute numbers (since I ran it in a VM), just the percentage decrease - looks to be around 30 - 50%)

my.cnf used - http://sprunge.us/BWcI

[Raghavendra D Prabhu (raghavendra-prabhu)]

With 5.1 I noticed a slight change

./bug37.pl
INSERT Engine: MyISAM, partitioning YES, time 3.33585810661316.
INSERT Engine: MyISAM, partitioning NO, time 4.85951614379883.
INSERT Engine: Innodb, partitioning YES, time 7.12093615531921.
INSERT Engine: Innodb, partitioning NO, time 6.41398596763611.
INSERT Engine: Falcon, partitioning YES, time 3.44343209266663.
INSERT Engine: Falcon, partitioning NO, time 4.89797210693359.
raghu archie:/tmp [145]% ./bug37.pl
INSERT Engine: MyISAM, partitioning YES, time 3.62929797172546.
INSERT Engine: MyISAM, partitioning NO, time 2.85231685638428.
INSERT Engine: Innodb, partitioning YES, time 8.00331902503967.
INSERT Engine: Innodb, partitioning NO, time 5.43992114067078.
INSERT Engine: Falcon, partitioning YES, time 3.49170398712158.
INSERT Engine: Falcon, partitioning NO, time 2.90274119377136.
raghu archie:/tmp [145]% ./bug37.pl
INSERT Engine: MyISAM, partitioning YES, time 3.29621601104736.
INSERT Engine: MyISAM, partitioning NO, time 4.06698107719421.
INSERT Engine: Innodb, partitioning YES, time 7.71323299407959.
INSERT Engine: Innodb, partitioning NO, time 5.90629196166992.
INSERT Engine: Falcon, partitioning YES, time 3.4575982093811.
INSERT Engine: Falcon, partitioning NO, time 3.62359595298767.
raghu archie:/tmp [145]% ./bug37.pl
INSERT Engine: MyISAM, partitioning YES, time 3.2925751209259.
INSERT Engine: MyISAM, partitioning NO, time 5.12522602081299.
INSERT Engine: Innodb, partitioning YES, time 7.13540196418762.
INSERT Engine: Innodb, partitioning NO, time 6.45262217521667.
INSERT Engine: Falcon, partitioning YES, time 3.36198401451111.
INSERT Engine: Falcon, partitioning NO, time 4.75099515914917.
raghu archie:/tmp [145]% ./bug37.pl
INSERT Engine: MyISAM, partitioning YES, time 3.80118298530579.
INSERT Engine: MyISAM, partitioning NO, time 4.99271512031555.
INSERT Engine: Innodb, partitioning YES, time 7.88108611106873.
INSERT Engine: Innodb, partitioning NO, time 6.56556415557861.
INSERT Engine: Falcon, partitioning YES, time 3.7354850769043.
INSERT Engine: Falcon, partitioning NO, time 2.86660194396973.
raghu archie:/tmp [145]% ./bug37.pl
INSERT Engine: MyISAM, partitioning YES, time 3.64997482299805.
INSERT Engine: MyISAM, partitioning NO, time 4.66956400871277.
INSERT Engine: Innodb, partitioning YES, time 7.97481799125671.
INSERT Engine: Innodb, partitioning NO, time 5.43419909477234.
INSERT Engine: Falcon, partitioning YES, time 3.52900695800781.
INSERT Engine: Falcon, partitioning NO, time 4.95927381515503.
raghu archie:/tmp [145]% ./bug37.pl
INSERT Engine: MyISAM, partitioning YES, time 3.61078691482544.
INSERT Engine: MyISAM, partitioning NO, time 4.98564505577087.
INSERT Engine: Innodb, partitioning YES, time 9.51455307006836.
INSERT Engine: Innodb, partitioning NO, time 4.33102607727051.
INSERT Engine: Falcon, partitioning YES, time 3.89526104927063.
INSERT Engine: Falcon, partitioning NO, time 4.65808081626892.

For MyISAM, with partitioning the time was actually lower.

I presume it may be due to

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

[Stewart Smith (stewart)]

The best way to get this fix backported soon is to file a support request or otherwise engage Percona to backport the fix (as always, customer work takes priority) - otherwise this is a bug that we're unlikely to work on too soon (as there are many other ones of higher priority for us to fix as non-customer work)

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1928