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

Leave a Reply to safari Cancel reply

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 )

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s