Bug #22875 "FLUSH STATUS" does not reset global Com_% status variables
Submitted: 1 Oct 2006 19:53 Modified: 28 Feb 2007 22:46
Reporter: Christian Hammers (Silver Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.0.26-BK, 5.0.24 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[1 Oct 2006 19:53] Christian Hammers
Description:
Hello

I would have expected "mysqladmin flush-status" to reset almost everything that "mysqladmin extended-status" outputs but all or at least the Com_% fields are not resettet. A "FLUSH STATUS;" in the mysql console seems to reset the variables just for this thread...

bye,

-christian-

How to repeat:
# mysqladmin extended-status | grep Com_insert
| Com_insert                        | 22       |
| Com_insert_select                 | 0        |

# mysqladmin flush-status
# mysqladmin extended-status | grep Com_insert
| Com_insert                        | 22       |
| Com_insert_select                 | 0        |

# echo "FLUSH STATUS;" |mysql
# mysqladmin extended-status | grep Com_insert
| Com_insert                        | 22       |
| Com_insert_select                 | 0        |

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45 to server version: 5.0.24a-Debian_6-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE "Com_in%"; 
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Com_insert        | 0     | 
| Com_insert_select | 0     | 
+-------------------+-------+
2 rows in set (0.00 sec)

mysql> Bye
# mysqladmin extended-status | grep Com_insert
| Com_insert                        | 22       |
| Com_insert_select                 | 0        |

Suggested fix:
-
[2 Oct 2006 8:39] Valeriy Kravchuk
Thank you for a bug report. Verified just as described, with 5.0.26-BK.
[23 Oct 2006 19:55] Magnus Blåudd
It might actually be a feature that the global status vars are not reset by FLUSH STATUS.

But adding the threads status vars to the global status vars, resetting the threads status variables and then finally resetting some global variable doesn't really make sense.

I think FLUSH STATUS should only reset thread local variables OR all global variables. Not a mix.
[23 Oct 2006 20:01] Bugs System
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/14211

ChangeSet@1.2323, 2006-10-23 21:49:12+02:00, msvensson@neptunus.(none) +4 -0
  Bug#22875 "FLUSH STATUS" does not reset global Com_% status variables
   - Instead of adding the current thread's status variables to global status vars when
     calling FLUSH STATUS, reset the global status vars 
   - Improve comments
[28 Oct 2006 12:04] Christian Hammers
As a patch has already been submitted, I guess the status change to "not a bug" was a mistake and should have been "pending" or "closed"?
[28 Oct 2006 13:36] Sergei Golubchik
It was found out that the current behaviour is intentional, FLUSH STATUS indeed is supposed to reset only local status variables.
[28 Oct 2006 16:43] Christian Hammers
Hm, for what is "mysqladmin flush-status" good then? Or at least, can you make this to a feature request for a "FLUSH GLOBAL STATUS" command? Sometimes when benchmarking it would be handy to reset everything without having to restart the whole server.

bye,

-christian-
[30 Oct 2006 12:10] Sergei Golubchik
Agree, very reasonable feature request.
[30 Oct 2006 12:27] Magnus Blåudd
Setting the bug to documenting so we can have this in the manual.

Status variables are currently as of 5.0 counted per thread and accumulated in global counters when the thread ends.

It's _not_ possible to reset all of the global varaibles with FLUSH STAUS, it will only reset some global variables and all thread status variables.

FLUSH STATUS currently does this:
 -  Adds thread's status variabes to global status
 -  Reset thread's status variables
 -  Reset the global variables which are declared with type SHOW_LONG in the "status_vars" array
 - Reset the counters of all key caches (default and named).
 - Set max_used_connections to the number of currently open
   connections. 

Please document this and set the bug to verified as "Feature request" for FLUSH GLOBAL STATUS
[30 Oct 2006 14:05] Konstantin Osipov
As this behaviour got broken in 4.1, I personally consider it a regression and believe that it should be fixed as a regression.
In order to fix it in line with the design of SHOW [SESSION/GLOBAL] STATUS we should implement FLUSH GLOBAL STATUS.

FLUSH STATUS behaviour should be modified to not change any global data, 
such as:
 - key cache counters
 - connection counters
 - SHOW_LONG global variables.

In other words, FLUSH STATUS should behave as FLUSH SESSION STATUS and only reset session-scope status variables. [SESSION] keyword should be added to the gammar and as optional.

Key cache counters, connection counters, SHOW_LONG variables and server-scope status variable (those that have session counterparts) should be reset by FLUSH GLOBAL STATUS.
FLUSH SESSION STATUS should no longer require SUPER privilege (connection privilege is enough, and as long as it is checked at connection, no privilege check is necessary).

FLUSH GLOBAL STATUS should require SUPER privelege.

This is an incompatible change that introduces new syntax, so it has to go to 5.1 (at least). A worklog entry is necessary too.
[7 Nov 2006 9:26] Magnus Blåudd
Filed new bug report bug#24036 requesting this behaviour to be documented.
[8 Oct 2008 16:11] Johan Andersson
This should be in 5.1. I have met another victim.
[1 Sep 2011 18:40] Derek Downey
Is this still a possible feature in 5.5 or 6.0, or are there reasons against a new syntax:

  FLUSH [GLOBAL|SESSION] STATUS

?
[25 Nov 2011 13:08] Peter Laursen
I also would very much like SESSION|GLOBAL switch here!
[2 Dec 2011 5:19] Satyapal Garhwal
Is there any plan for this bug ..............
[2 Dec 2011 11:40] Marc ALFF
See related post:

http://marcalff.blogspot.com/2011/12/performance-schema-or-com-status.html