November 24, 2014

Internals of InnoDB mutexes

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.

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. charles says:

    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?

    Thanks.

  2. peter says:

    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.

Speak Your Mind

*