Bug #61728 | Server Status Variable Handler_write incremented with SHOW or SELECT | ||
---|---|---|---|
Submitted: | 3 Jul 2011 13:55 | Modified: | 7 Jun 2012 15:47 |
Reporter: | Ali Onur Uyar | Email Updates: | |
Status: | Won't fix | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 5.1.54 | OS: | Linux (Ubuntu 10.10) |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
Tags: | Handler_write, SELECT, server, show, STATUS, variable |
[3 Jul 2011 13:55]
Ali Onur Uyar
[3 Jul 2011 20:42]
Ali Onur Uyar
The bug is also triggered for certain SELECT queries. Eventhough no rows are inserted, deleted or modified, Handler_write is incremented. Sample query on MyISAM table: SELECT DISTINCT dst FROM cdr;
[4 Jul 2011 4:08]
Valeriy Kravchuk
Probably all these statements create temporary tables. Check with: show status like '%tmp%'; before and after problematic statements.
[4 Jul 2011 9:44]
Ali Onur Uyar
I've confirmed that the Created_tmp_tables counter is incremented by one each time one of the problem queries is executed. I do not know the internal workings of MySQL, but I do not understand why temporary tables are being created for simple SQL SELECT or SHOW queries. The current behaviour renders the Handler_write counter totally useless for monitoring, because each time a full table scan is performed on a large table by certain SELECT queries, the Handler_write counter is incremented by the number of rows scanned, jumping up by a few millions, when no rows are being inserted into regular tables.
[4 Jul 2011 10:56]
Valeriy Kravchuk
Please, check if this manual page, http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html, explains all our findings. For me this looks like documented (even if unexpected) behavior and, thus, not a bug.
[9 Jul 2011 18:53]
Ali Onur Uyar
The use of Internal Temporary Tables documented in the MySQL Reference Manual seems to explain most of my findings, but the SHOW query is not among the case cases that are listed. It seems quite strange to me that internal temporary tables are created for simple SHOW queries on status variables. Apart from this, in my humble opinion the status variables for Internal Temporary Tables must be separated from the status variables of other tables so that these variables would be useful for monitoring purposes. In fact a three tier status variables could be highly desirable: Regular Tables, In-memory Temporary Tables, On-disk Temporary Tables.
[10 Jul 2011 9:08]
Valeriy Kravchuk
I see 2 things here: 1. There is a valid documentation request to add information on temporary tables usage to every SHOW statement. We have to document, explicitly, when temporary table is used for SHOW <something>, and when it will be on-disk temporary table. 2. Feature request to add new status variables to present more details on the reasons why/what for temporary tables are used. I'd prefer to concentrate on documentation request here and suggest you to open separate feature request referring to this bug.
[7 Jun 2012 15:47]
Paul DuBois
"1. There is a valid documentation request to add information on temporary tables usage to every SHOW statement. We have to document, explicitly, when temporary table is used for SHOW <something>, and when it will be on-disk temporary table." Sorry, no. This can change depending on how the optimizer handles things, and thus is subject to optimizer development. SHOW statement descriptions are not going to track that.