Performance of status commands

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.

5 thoughts on “Performance of status commands”

  1. 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 — 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.

  2. 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.

  3. 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%’.

  4. 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.

Comments are closed.