Mutex contention in MySQL

If you run MySQL on an SMP server and your server is busy, it is good to understand where the bottlenecks are in the server. Mutex contention is one such source. InnoDB provides mutex contention statistics via the SHOW MUTEX STATUS command. Alas, there are no statistics for the mutexes in the rest of MySQL -- unless you are willing to do a lot of refactoring. I used my favorite refactoring tool, sed, to replace many of the uses of pthread_mutex with my_mutex. my_mutex is a simple wrapper for pthread_mutex that provides contention and usage statistics and uses spin waiting prior to blocking on the pthread_mutex.

People who get to use MySQL on Solaris tell me that dtrace and mpstat provide information similar to this. Can someone post details with sample output on a blog post?

Sample output from a recently started server.

• Locks - number of times the mutex was locked
• Spins - number of times the caller tried to get the lock before returning or blocking on the pthread_mutex. Must be >= Locks.
• Sleeps - number of times the caller possibly blocked by calling pthread_mutex_lock after failing to lock the mutex with pthread_mutex_trylock and spinning for ~20 microseconds.
  

# mysql -e 'show global mutex status'
+-------+-------+--------+----------------------------+
| Locks | Spins | Sleeps | Name           |
+-------+-------+--------+----------------------------+
|    21 |    21 |      0 | LOCK_open          |
|    18 |    18 |      0 | LOCK_thread_count|
|     0 |     0 |      0 | LOCK_status          |
|     4 |     4 |      0 | LOCK_error_log     |

Sample output from a benchmark that reloads tables using 16 database connections on an 8 core server. The mutexes are ranked by the number of Sleeps. The test runs for a few minutes. Contention doesn't appear to be that bad in this case. A mutex for the binlog has the most contention. This is what I hoped for as the statements used for the test insert ~1000 rows, so there is a lot of work done per statement. A benchmark with simpler/faster statements would likely have more contention.

        Locks    Spins     Sleeps  Name

816258  883234  2995    LOCK_log for bin log
544304  558754  602     LOCK_open
548667  552807  189     LOCK_thread_count
273654  274334  12      LOCK_global_user_stats
1088548 1088803 1       LOCK_global_read_lock
544000  544000  0       LOCK_prep_xids
459     459     0       LOCK_xid_cache
4       4       0       LOCK_error_log
274533  274581  0       LOCK_user_conn
272004  272004  0       LOCK_global_table_stats