In MySQL 5.0, with the introduction of information_schema, the SHOW commands were changed to really be wrappers around SELECTs against information_schema. This means that when you issue e.g., a SHOW VARIABLES command, Created_tmp_tables is incremented once, and Handler_write and Handler_read_rnd_next are incremented about once per row in the result. Keep this in mind in your monitoring systems; these numbers could be wildly inaccurate if some part of your application is doing many SHOW commands.
This came to my attention because Connector/J’s ReplicationDriver runs SHOW VARIABLES LIKE 'tx_isolation' whenever you switch between setReadOnly(true) and setReadOnly(false), so in large, complex Java applications don’t be surprised if you see a high number1 of Created_tmp_tables. How can this be solved? Use SELECT instead of SHOW. If you need to access one variable, instead of doing:
SHOW VARIABLES LIKE 'tx_isolation'
This will copy all variables into a temporary table, and apply the LIKE filter to that temporary table.
Use this instead:
This will just fetch the tx_isolation variable from memory directly. This is much more efficient and to-the-point.
1 Like, say, 8,000 per second. Creating so many temporary tables burns up a pretty big chunk of CPU time for nothing.
thanks for very great article, another secret of mysql?! ;-)