EmergencyEMERGENCY? Get 24/7 Help Now!

Bug fix of InnoDB scalability problem

 | November 14, 2006 |  Posted In: Insight for DBAs

PREVIOUS POST
NEXT POST

I was pretty busy last month with project which will be annonced very soon (I hope), but I can’t miss bug fix
of my favorite bug 15815. I wrote about this problem before and also investigated in my presentation.
Finally bug fix was pushed into 5.0-bk tree and now I have it in my hands.

Let me refresh results with MySQL 5.0.27 (without bugfix):

select sql_calc_found_rows * from b limit 5;
executes for 20 sec (table b contains ~2 mil rows)

the same query but in 4 concurrent threads:
98 sec
101 sec
103 sec
103 sec

(I am still using 4-CPU box and expecting the same time of execution for each thread).

The results with 5.0.30-bk:

1 thread:
19 sec

4 threads:
28 sec
29 sec
39 sec
41 sec

Great improvement! But scalablity factor is not such perfect as I’d expect, because with my own patch
I was able to reach better performance. Also as you see there is a thread starvation – last thread executes 1.4 times slower. So I’d want InnoDB team continue to work on performance improvement.

Anyway I think it’s good news.

PREVIOUS POST
NEXT POST
Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

13 Comments

  • Woo it’s been ported back to 4.1 as well. I’m hoping to get this in my environment soon. The next patch I’d like to see is the innodb_file_io_threads patch that allows more threads for linux. Google has a patch that allows more io threads to get 100% performance increase from sequential reads, enough so innodb is able to saturate the PCIe Bus.

  • Dathan, still some discussion about 4.1 going on, apparently. The patchhas been applied, not sure who’s actually going to get it yet. It’s possible that it might be Enterprise/Network/Support contract people only. Too soon for me to say for certain. If you need it now, ask for it in a support issue and you’ll probably be able to get it fairly soon – not guaranteed, just my current expectation.

    Vadim, you should check 5.1 as well as 5.0. Apparently the fixes are different in each to see which produces the best result.

  • James,

    Testing 5.0 on customer site today. It does better than default however it is far from scalability it should have.
    We’ll test different versions and different patches later as time permits.

  • Dathan,

    As MySQL Customer you may request for patch to be included in MySQL 4.1 and more customers will scream about it the higher probability is MySQL will do it. Otherwise it might not be that interesting – the faster customers move from MySQL 4.1 the better it is for MySQL as this reduces maintainence effort.

    Now regarding Google patch – did they ever OpenSource it or anything ? It would be interesting to test it out at least.

  • Peter, it’s not just maintainence effort but people being free to work on other things. There are plenty of other things for the InnoDB team to be doing, so customer demand has to drive whether it’s worth doing this, in preference to something else that could be in 5.1 instead. Unless someone throws money at the InnoDB people sufficient to justify more employees, this is a zero sum game. Significant bugs have to be fixed, but adding support in say 4.0 for issues with multi-core CPU types that weren’t around when 4.0 was released is not strictly required, even though it is nice to have.

  • James,

    The way how Innodb does locking and appropriate code is very close in all MySQL versions, so it is not the question of it taking a lot of time to port it to 4.1

    Now speaking about mainainence do not you agree the more customer will be using newer MySQL versions the less effort would be need to spent on it. Even support gets more expensive with old version as support engineers usually remember well state of things for new versions.

    Now regarding choices – all I’m telling if it is important for Dathan he should voice his opinion. MySQL customers should be getting service for the money. If they voice what it important for them it would be helpful to identify how many customers actually care about 4.1

    Now about 4.0 and multi cores. No one is speaking about 4.0 only 4.1 which is still used rather widely. The problem we’re speaking about existed FOREVER, attaching it to multi core CPUs is nothing else but a trick to hide complete failure of MySQL/Innodb fixing this bug which should have been fixed years ago.

    I’ve complained about this problem to Heikki number of times even before joining MySQL which is 5 years ago.

    Number of CPUs however does matter – the effect with large CPU numbers changes from just slowdown performance to completely unacceptable. Boxes with over 4 CPUs however existed for long time… it is just only with multi-cores they came to commodity market so amount of complaints increased.

  • For those looking for the patch itself, it can be found here:

    http://mysql.bkbits.net:8080/mysql-5.0/cset@4552a85ddHCqJ_0Ar7GHnQkDE54iAg?nav=index.html|ChangeSet@-2w

    Direct link to diff is here: http://mysql.bkbits.net:8080/mysql-5.0/gnupatch@4552a85ddHCqJ_0Ar7GHnQkDE54iAg

  • Hi. Have you guys influenced the MySQL team to implement this soon ? Or is it already in 5.0.27 ?

    I mean I could of course patch it myself investing a (hopefully) small amount of time but since it is nice to be able to run standard packages I really would like it to be GA. We will have a launch soon of an application which indeed will have heavy concurrency so a concurrency fix would be helpful

    Regards

    //Marcus

  • Marcus,

    Yes we were highlighting this problem to MySQL and Innobase guys for a while. Innobase developed patch with similar ideas which is now included in 5.0.30 “Enterprise” version.

  • [NIN-15] CLONE -Slow front page load times and high CPU usage…

    Potential temporary solution in moving to MySQL 5.0.33 which has several performance enhancing benefits for innodb with dual core cpu’s. My hope is that the improved performance with this release will mask the inefficiencies with Notes in 77x as well…

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.