September 17, 2014

Bug fix of InnoDB scalability problem

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.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. 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.

  2. James Day says:

    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.

  3. peter says:

    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.

  4. peter says:

    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.

  5. JoeB says:

    If dirty reads are being used, this should not repro, correct?

  6. peter says:

    Nope,

    It is rather unrelated to isolation level. It should be the same with READ UNCOMMITTED.

  7. Google is looking to open source the patch, but at this time no.

  8. James Day says:

    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.

  9. peter says:

    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.

  10. Marcus Herou says:

    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

  11. peter says:

    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.

Speak Your Mind

*