Though some may think status command performance is overrated, our code heavily relies on SHOW STATUS LIKE "Thread%", and I like to spam SHOW INNODB STATUS. Add monitoring systems, which rely on ‘SHOW GLOBAL STATUS’ – and performance starts to matter.
And things are a bit abandoned. See, in performance engineering people always look for low hanging fruit to pick, and there’re quite a few:
- Peter wrote a year ago about performance regression in ‘SHOW STATUS’. Of course, now that Mark has filed a bug – patch is already in there. This bug would byte us very very severely, if we’d run live site against unpatched 5.0 – as it slows down from 0.2ms to 20ms. There’s a small ha-ha in this issue – guess where these two lines come from. ;-)
- SHOW INNODB STATUS was taking 0.3s just because there were two rarely-looked at variables that needed traversing quite some structures. I filed bugs #36941 and #36942, to remind developers of this problem. Disabling these two counters allowed to run few thousand ‘SHOW INNODB STATUS’ a second, instead of just three.
- SHOW GLOBAL STATUS traverses statistics for each thread. Doesn’t matter on single-thread development box too much, but is very very unhappy at higher thread concurrency – walking every variable on every thread is quite a task even for modern hardware. Generally some SMP scaling has been added by reducing locking on global variables this way – at the cost of making the command unusable.
domas mituzas 
You missed one. SHOW commands create temp tables in MySQL 5. By default JDBC connections run SHOW VARIABLES and SHOW VARIABLES LIKE ‘tx_isolation’ immediately after creating a connection. I have seen a few critical servers made useless by the combination. There is an option for JDBC to cache the results (cacheServerConfiguration=True). It is False by default. This fixes the problem when a process creates many connections to the same server, otherwise you are still stuck.
I filed a bug for this — http://bugs.mysql.com/bug.php?id=37020. Peter Z and Jeremy Cole have written about it before.
JDBC clients also run this: select round(‘inf’), round(‘-inf’), round(‘nan’);
Given how far apart ieee754 and MySQL are, it is pointless to run that on recent versions of MySQL.
And the set_vm_stats() and set_malloc_stats() calls may have been removed in my next patch. But maybe not. I think you call the SHOW commands too frequently.
Yeah, eventually I’l need export status variables exported directly, without going to SHOW. On the other hand, that temp-table doesn’t seem to be a very huge overhead, if you limit SHOW STATUS to ‘Specific_Scope%’.
I have seen several servers become unusable because of the extra load from temp tables for SHOW commands. JDBC clients were the source of the commands.
Could be – variables is entirely different beast than status