Temporary tables for SHOW VARIABLES in MySQL 5.0

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:

SELECT @@session.tx_isolation

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.

One thought on “Temporary tables for SHOW VARIABLES in MySQL 5.0

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s