MySQL Bugs: #22875: "FLUSH STATUS" does not reset global Com

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

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:
-

Thank you for a bug report. Verified just as described, with 5.0.26-BK.

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.

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

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"?

It was found out that the current behaviour is intentional, FLUSH STATUS indeed is supposed to reset only local status variables.

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-

Agree, very reasonable feature request.

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

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.

Filed new bug report bug#24036 requesting this behaviour to be documented.

This should be in 5.1. I have met another victim.

Is this still a possible feature in 5.5 or 6.0, or are there reasons against a new syntax:

  FLUSH [GLOBAL|SESSION] STATUS

?

I also would very much like SESSION|GLOBAL switch here!

Is there any plan for this bug ..............

See related post:

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