MySQL Bugs: #15933: max_used_connections is wrong after FLUSH STATUS if connections are cached

Bug #15933	max_used_connections is wrong after FLUSH STATUS if connections are cached
Submitted:	22 Dec 2005 10:22	Modified:	5 May 2006 22:54
Reporter:	J Rabbit	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.0.16, 4.1	OS:	Linux (Linux, FreeBSD)
Assigned to:		CPU Architecture:	Any
Tags:	bfsm_2007_05_03

Description:
If you issue a FLUSH STATUS command, max_used_connections is reset to 0.

However, it is not increased again until a new thread is created - if threads from the thread cache are reused, it is not incrememented.

How to repeat:
set the thread cache size to 10 threads
make 5 connections to the server
disconnect these so 5 threads are cached
issue a flush status command
make 6 more connections to the server

max_used_connections remains 0 until you make the 6th connection.

Suggested fix:
Either:

a) On flushing the status, set max_used_connections to threads_connected, not to 0.

b) Check if it is necessary to increment max_used_connections when taking a thread from the cache as well as when creating new threads

Or:

On flushing the status, set max_used_connections to threads_connected + threads_cached

Thank you for a problem report. It looks like a (reasonable) feature request for me, because current behaviour of FLUSH STATUS is clearly documented (http://dev.mysql.com/doc/refman/5.0/en/flush.html):

"STATUS

Resets most status variables to zero. This is something you should use only when debugging a query."

I classified it as a minor bug rather than a feature request, as it is not the fact it is "reset to zero" by FLUSH STATUS that is the problem, but that given the right circumstances it can inaccurately remain zero afterwards when there are subsequent connections. 

All the other variables that are reset to zero increment correctly after issuing the flush command.

I argee that this is a misleading behaviour. It should at least be documented in a prominent place.

Verified just as described (with 4 connections only) on 5.0.19-BK (ChangeSet@1.2003, 2005-12-24 14:32:50+01:00) on Linux:

mysql> show status like 'max%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 4     |
+----------------------+-------+
1 row in set (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'max%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql>
[2]+  Stopped                 bin/mysql -uroot test
[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.19

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

mysql> show status like 'max%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql>
[3]+  Stopped                 bin/mysql -uroot test
[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.19

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

mysql> show status like 'max%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql>
[4]+  Stopped                 bin/mysql -uroot test
[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.19

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

mysql> show status like 'max%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql>
[5]+  Stopped                 bin/mysql -uroot test
[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 5.0.19

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

mysql> show status like 'max%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 5     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'thread%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| thread_cache_size | 10     |
| thread_stack      | 196608 |
+-------------------+--------+
2 rows in set (0.00 sec)

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/4520

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/4521

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/4629

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/4860

Fixed in 5.0.21, 5.1.10

Documented in changelog:

 <listitem>
        <para>
          After calling <literal>FLUSH STATUS</literal>, the
          <literal>max_used_connections</literal> variable did not
          increment for existing connections and connections which use
          the thread cache. (Bug #15933)
        </para>
      </listitem>