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 number¹ 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.

¹ Like, say, 8,000 per second. Creating so many temporary tables burns up a pretty big chunk of CPU time for nothing.

<script type="text/javascript"><!--google_ad_client = "pub-7354715452290341";google_alternate_ad_url = "http://jcole.us/ads/";google_ad_width = 468;google_ad_height = 60;google_ad_format = "468x60_as";google_ad_type = "text_image";google_ad_channel ="7730850061";//--></script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script> 原文地址:http://jcole.us/blog/archives/2007/04/04/temporary-tables-for-show-variables-in-mysql-50/