Statistics for mutexes (historical document)
This code has been built on an x86-64 server that uses gcc and Linux 2.6. I don't know if it works on other platforms.
This patch provides mutex contention statistics for MySQL via the SQL command SHOW GLOBAL MUTEX STATUS. It is similar to the output provided for InnoDB mutexes by the SQL command SHOW MUTEX STATUS. It optionally reports lock waits by caller.
To enable it, apply this patch, run configure with the
--with-fast-mutexes option and build. To get lock waits by caller, run configure with
The fast mutex code uses busy-wait loops before blocking on lock requests. If the lock is not obtained during the busy-wait loop, the code assumes that the caller has to block on the lock request. The duration of the busy-wait loop is determined by the my.cnf variable
mysql_spin_wait_loops. The default value is 100 which produces a ~6 microsecond delay on a current x86_64 CPU. The delay is measured at mysqld startup and printed in the database error log. It is also displayed in the SHOW STATUS variable
This patch also changes all pthread_mutex_init calls to use MY_MUTEX_INIT_FAST.
I won't claim that fast mutexes improve performance. But they make it possible to estimate mutex contention. I use this for performance testing builds.
The data is exported by the SQL command SHOW GLOBAL MUTEX STATUS. Sample output is displayed below. The output has several columns:
Locksis the number of times a blocking pthread call had to be used to lock the mutex or rw-lock. This means that the lock could not be obtained by the non-blocking trylock commands during the busy-wait loop.
Spinsis the number of rounds done by the busy-wait loop. This is at most 4 times
Sleepsis the number of blocking lock attempts. This is <=
Locks. When MySQL is built to count mutex contention by lock callers, values are positive when the line reports contention for a mutex and negative when the line reports contention for a lock caller.
Nameis the name of the file at which the mutex or rw-lock is created.
Lineis the line number in the file at which the mutex or rw-lock is created.
Usersis the number of times the mutex or rw-lock is created.
Locks Spins Sleeps Name Line Users 1 0 0 my_thr_init.c 287 68 172 0 0 mysqld.cc 3081 1 0 0 0 slave.h 453 1 4229999 114473 1883 thr_lock.c 319 111742 0 0 -225 thr_lock.c 325 0
This displays the estimated busy-wait time.
show status like "%spin%"; Variable_name Value Mysql_spin_wait_microseconds 6
The statistics may be inaccurate:
- locks are not used to read or write the statistics counters
- a fixed-size array of counters is used. An array entry may be used by multiple mutexes and rw-locks because the entry to use for a given mutex or rw-lock is determined by computing a hash function from the filename and line number from the code that creates the lock
I copied code from InnoDB for hashing and random number generation. Thanks InnoDB.