I came across this post about scalability issues in InnoDB, and I’m wondering if something akin to this issue is contributing to the poor performance of my DB.
The DB I’m running is 4.1.20 with InnoDB on RHEL4.4 64-bit. The machine is a 4-way Xeon (2 dual core) with 8GB of RAM. We already know that the majority of queries that we do are less than optimal due to ORDERing by COUNT(*) (more complicated than that, but the queries can’t be helped right now). What I’m seeing though is pretty high CPU utilization when the box is not seemingly doing much real work. The thread_concurrency value is set to 4. The workload is heavily biased toward reads.
Here is 5-sec interval output of SHOW INNODB STATUS showing mutex info (4.x doesn’t have SHOW MUTEX STATUS?):
Mutex spin waits 76071690092, rounds 69761976145, OS waits 53461495
Mutex spin waits 76071776968, rounds 69762092711, OS waits 53462299
Mutex spin waits 76071795096, rounds 69762111203, OS waits 53462323
Mutex spin waits 76071812184, rounds 69762127126, OS waits 53462340
Mutex spin waits 76071817349, rounds 69762129313, OS waits 53462344
Mutex spin waits 76071817546, rounds 69762129644, OS waits 53462344
Mutex spin waits 76071826720, rounds 69762138863, OS waits 53462358
Mutex spin waits 76071829308, rounds 69762139928, OS waits 53462361
Mutex spin waits 76071845532, rounds 69762154968, OS waits 53462382
Mutex spin waits 76071873423, rounds 69762175901, OS waits 53462414
Mutex spin waits 76072563718, rounds 69762759601, OS waits 53462756
Mutex spin waits 76072803002, rounds 69762947332, OS waits 53462961
Mutex spin waits 76072850087, rounds 69762979574, OS waits 53463007
That’s an average of ~20k spin waits/sec…
Your thoughts on this are appreciated.