EmergencyEMERGENCY? Get 24/7 Help Now!

Internals of InnoDB mutexes

 | September 7, 2006 |  Posted In: Insight for DBAs


InnoDB uses its own mutexes and read-write locks instead of POSIX-mutexes pthread_mutex*, the main reason for that is performance, but InnoDB’s implementation isn’t ideal and on modern SMP boxes can cause serious performance problems.
Let’s look on InnoDB mutex (schematic for simplification):

innodb_spin_locks is configurable via system variable innodb_sync_spin_loops (default value is 20)

There we have:
1. Spin-loop – InnoDB uses spin-loop in hopes thread locked mutex is very fast and will release mutex while current thread runs in spins, so there is saving of expensive context switching. However we can’t run spin very long as it eats CPU resources and after some loops it is reasonable to give CPU resource to concurrent threads.
2. Wait on condition variable using cells from synchronization array. Synchronization array becomes from age of dinosaurs and Windows 95, where count of condition variables was limited, so InnoDB had to implement workaround. The same synchronization array is performance problematic, as it by itself uses mutex inside to protect cells.

With big respect to InnoDB team they still take care of users of Windows 95 I think it is better to remove synchronization array and use condition variables directly.
I’ve made the patch which simply replace array. We have reports from partners that this patch increase scalability on Solaris/Opteron boxes dramatically – so you can test it if you have multi-CPU boxes and high-concurrency load on InnoDB.

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.


  • Hi,

    Sorry for the intrusion. I am a non-technical person who is beginning to sweat a bit over the viability of a system that is being developed for me in mysql.

    The system will allow users to build statistical reports on foreign trade data. There are dozens of reports and they basically consist of graphs and tables.

    My worry is that the size of the database might make performance an issue. If you could ease my mind I would appreciate it.

    The main table will initially have about 10-20 million rows. Each row about 60 fields. Probably about 10-15 GB to start. Each individual row contains information on one import/export transaction (product, quantity, price, date, country of origin, etc.). The system will also pull data from a secondary table that has info on individual companies.

    The system will grow by about 10 million rows per year.

    Is it realistic to think that a dedicated server (low end celeron 2.4? with 512mb ram) can handle multiple users building online reports against this database???

    Yes? No? Maybe?


  • Charles,

    First I should note it is best if you ask questions unrelated to blog post at forum – http://forums.mysqlperformanceblog.com.

    Speaking about your question it is hard to tell without seeing reports – how much data they will have need to analyze as well as which kind of reports are you looking for. Should they be done live or are they going to be requiested and become available at later time after they are created.

    Processing 10-15GB can take quite a while on low end Celeron box.

    As you probably know we do MySQL Performance Consulting and will be happy to help you with sizing and application optimization.

  • […] Amongst the bug fixes, there is one that I want to highlight. It is listed as the innocent-looking bullet “InnoDB showed substandard performance with multiple queries running concurrently. (Bug#15815)“. This is a fix to an issue especially surfacing on multiple-core processors. You testing in such environments is much appreciated. As a reference, you may want to read the Bugs database entry bugs.mysql.com/15815 as well as two articles from Peter Zaitsev — one from a week ago, another from last September. […]

Leave a Reply