InnoDB uses mutexes for exclusive access and rw-locks for the shared access of the resources. rw-locks are used to control access to the common shared resources like buffer pool pages, tablespaces, adaptive search systems, data-dictionary, informaton_schema, etc… In short, rw-locks play a very important role in the InnoDB system and so tracking and monitoring them is important too.
InnoDB provides an easy way to track them using “SHOW ENGINE INNODB STATUS”.
RW-shared spins 38667, rounds 54868, OS waits 16539
RW-excl spins 6353, rounds 126218, OS waits 3936
RW-sx spins 1896, rounds 43888, OS waits 966
Spin rounds per wait: 1.42 RW-shared, 19.87 RW-excl, 23.15 RW-sx
In this article we will try to understand how these stats are calculated and what is the significance of each of these numbers. We will also try to draw inferences using different use-cases and touch base important stats …
[Read more]