MySQL Bugs: #30038: SHOW VARIABLES increments Handler_read_rnd

Bug #30038	SHOW VARIABLES increments Handler_read_rnd_next counter
Submitted:	25 Jul 2007 13:14	Modified:	7 Aug 2007 21:48
Reporter:	Stefan Hinz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.19/5.0	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Issuing SHOW (GLOBAL) VARIABLES increments Handler_read_rnd_next while it shouldn't. This has a negative side effect on Merlin which relies on that status variable to identify excessive table scans. In other words, Merlin alerts of excessive table scans if there really aren't any but someone happens to issue SHOW VARIABLES.

How to repeat:
mysql> show status like 'Handler_read_rnd_next';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_rnd_next | 541   |
+-----------------------+-------+

mysql> show variables;

mysql> show status like 'Handler_read_rnd_next';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_rnd_next | 810   |
+-----------------------+-------+

mysql> show global variables;

mysql> show status like 'Handler_read_rnd_next';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_rnd_next | 1079  |
+-----------------------+-------+

Suggested fix:
Make sure SHOW statements don't increment Handler_read_rnd_next.

Thank you for the bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31960

ChangeSet@1.2495, 2007-08-01 18:58:46+04:00, evgen@moonbone.local +5 -0
  Bug#30038: SHOW command was increasing status counters.
  
  SHOW command was increasing handler status counters making in difficult to see
  which reads/writes made by a select and which by the SHOW command.
  
  Now SHOW commands which is converted to a SELECT query backs up status
  counters before execution of the query and restores them back on exit.
  This is done in the mysql_execute_command() function.
  Added the is_show_command() function to the LEX class.
  It returns TRUE when the LEX object describes a SHOW command.

See also bug#10210.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32156

ChangeSet@1.2560, 2007-08-06 22:46:14+04:00, evgen@moonbone.local +3 -0
  Bug#30038: SHOW command was increasing status counters.
  
  SHOW VARIABLES command was increasing handler status counters making in
  difficult to see which reads/writes made by a select and which by the
  SHOW VARIABLES command.
  
  Now SHOW VARIABLES is handled in the same way as the SHOW STATUS command.
  The global counters are still but the per thread counters  - aren't.

See also BUG#10210: for that bug, Monty expressed an opinion that SHOW STATUS
should update global status variables to allow one to monitor what the server
is really doing (as SHOW STATUS calls themselves might affect the
performance). 

Evgen and SergeyP agree with this reasoning and think it should apply to SHOW
VARIABLES as well. Therefore, we think SHOW VARIABLES should update global
statistics.

For local statistics, SHOW STATUS is a special case, as it is the command that
is used to read the statistics, and it is frequently used to do performance 
troubleshooting in this manner:

FLUSH STATUS;
SELECT /* the explored query here */ ;
SHOW STATUS;

and typically the user is not interested in statistic increments made by the 
SHOW STATUS command itself (and if he is, those are a function of number of
returned status variables).

Other SHOW commands, like SHOW VARIABLES, SHOW TABLES, etc are not used for
tracking counters, so it is ok if they update the statistics.

Regarding the Merlin alerts of excessive table scans:

1. If Merlin needs to exclude the statistics increments made by its own SHOW
   commands, this can be done as follows:

   For every issued "SHOW STATUS [LIKE ...]" command, substract 
     1 from Questions,
     1 from Created_tmp_tables,
     1 from Handler_read_rnd (for server after BUG#30285 fixed, 0 for the
                              current server)
     (#output_rows + 1) from Handler_read_rnd_next
     (#output_rows) from Handler_write
     
   For all other issued SHOW commands, 
     substract the value of Merlin's connection local counters.

2. See above Monty's point about need to know what's going on on the server.

3. At the moment, 5.1 has 267 variables. For 300-row table, doing a table scan
   can actually be the cheapest query execution plan. The gain of not having
   indexes is even greater when one takes into account that presense of
   indexes increases cost of UPDATE/DELETE operations and also slows down the
   optimizer. Sometimes we give a performance advise to split queries as
   follows:
     "CREATE TEMPORARY TABLE tmp AS SELECT ... " ; --- note, no indexes
     " SELECT ... "; -- this will do a sequential scan on table tmp
     
   Merlin should be smarter when checking for table scans. Perhaps, calculate
   average number of records read in the table scan 
     
      (Handler_read_rnd_next / Handler_read_rnd)
    
   and analyze that value?

Considering the above points, setting to "Not a bug". Feel free to re-open if
you have arguments for make the change that are more compelled than the
provided arguments againist.

Correction: in the previous comment, those lines:

<<
     1 from Handler_read_rnd (for server after BUG#30285 fixed, 0 for the
                              current server)
>>,

<<
   Merlin should be smarter when checking for table scans. Perhaps, calculate
   average number of records read in the table scan 
     
      (Handler_read_rnd_next / Handler_read_rnd)
    
   and analyze that value?
>>

are invalid and should be ignored. I misunderstood the meaning of Handler_read_rnd counter. I've filed the request to count the number of sequential table scan intializations as BUG#30288.