pero on anything

First of all, I know this is a known problem, but it struck me so hard, I just had to write about it!

As Peter Zaitev points out calling SHOW STATUS might have a huge performance impact.

We recently replaced one of our servers with a DELL R900 with 96GB RAM. Having a disk-bound workload and > 1TB worth of data in InnoDB we expected a noticeable performance gain compared to the former server with 32GB. The new server even has better RAID and HDD.

But that was not the case. Things got even worse! A lot of queries “hang”, server load peaked at almost 7 and we saw a lot of cpu activity. Just before I started a deep analysis of what is going on inside I spotted a SHOW GLOBAL STATUS which ran every second. DOH!

Where did it came from? An administrator was running MySQL Administrator and used the Health chart to monitor some variables. So it periodically sent SHOW GLOBAL STATUS to the server. That resulted in a lot of queries waiting for the buffer pool (look at Peter’s post and the comments to understand why). And things get worse with bigger InnoDB buffer pool (this particular mysql instance uses 70GB!).

MySQL Administrator (the tool, not the person ) shut down - everything just looks great now!