How Innodb Contention May Manifest Itself

Even though multiple fixes have been implemented in Percona Server and MySQL 5.5, there are still workloads in which case mutex (or rw-lock) contention is a performance limiting factor, helped by ever growing number of cores available in the systems. It is interestin that  the contention may manifest itself in a different form from the system monitoring standpoint. In many cases, as heavy contention happens, user CPU will be very high, and the context switches will be somewhat reasonable. In others you would see the CPU usage being low with a lot of CPU being idle, increased compared to normal workload portion of system CPU and high number of context switches. These correspond to different contention situations which can be handled differently.

The first situation often corresponds to Innodb spending a lot of CPU time running “loops” as part of spinlock implementation. In many cases busy wait is indeed more efficient than doing context switches, however sometimes there is just too much spinning and it becomes inefficient. Reducing innodb_sync_spin_loops variable to a smaller value is known to help in some of those cases.

The second situation corresponds to a lot of context switches being done, which well may be normal – if your workload has large portion of “critical section” where only one thread can be working at a time, you should expect a lot of threads waiting and the more CPU cores you have available the less CPU utilization you can have. Sometimes though you may be wasting time with context switching because Innodb is not spinning too much, thus you can consider increasing innodb_sync_spin_loops to a higher number.

It is worth to note that fine-tuning Innodb Contention with the number of spin locks loops is something I would consider last resort tuning, the performance improvements it provides is typically rather limited and can be used to buy a little bit more time while you’re upgrading to Percona Server or newer MySQL version or doing application/architecture changes. Reducing number of competing threads (such as limiting number of Apache Children) as well as adjusting innodb_thread_concurrency and doing other server tuning should be first on your list.

I also wanted to highlight how much context switches you can get from the system, as unlike CPU usage it is not obvious what to consider a high number. With modern hardware and a modern Linux process scheduler you can be looking at 500,000+ context switches which system can do. I would look at some 200,000 context switches a second as the point where context switching itself can be seen as a problem, though it is better not to look at the absolute number but at how it changes compared to your normal situation. In many cases when bad contention is happening I see number of context switches increasing 3x or more compared to normal state.