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:
None 
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
Triage: Needs Triage: D3 (Medium)

[3 Jul 2011 13:55] Ali Onur Uyar
Description:
The Handler_write Server Status Variable seems to get incremented each time rows are returned to a client as an answer to SHOW query.
This variable must track number of rows inserted into tables as documented in MySQL 5.1 Reference Manual: 
http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Handler_write

The  Handler_write variable is not incremented when rows are returned to a client executing a SELECT query. The variable is incremented correctly when new rows ara inserted. The bug only occurs when rows are returned to a client executing a SHOW query.

Further tests indicate that not all SHOW queries increment the Handler_write variable. The following SHOW queries increment the variable:
SHOW GLOBAL VARIABLES
SHOW SESSION VARIABLES
SHOW GLOBAL STATUS
SHOW SESSION STATUS
SHOW DATABASES
SHOW TABLES

While the following SHOW queries do not increment the variable:
SHOW AUTHORS
SHOW CONTRIBUTORS

How to repeat:
On MySQL Server without any other connections execute the following query two times:
SHOW GLOBAL STATUS LIKE 'Handler_%';

The query returns exactly 15 rows and the value of the "Handler_write" Server Global Status variable is incremented by 15 each time the query is executed.

No rows are being inserted, so no change in the "Handler_write" variable which is supposed to track row insertions was expected. The "Handler_write" variable is incremented each time a query which returns rows is executed.

Suggested fix:
The SHOW commands must not increment the Handle_write Server Status Variable.
[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.