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

[22 Dec 2005 10:22] J Rabbit
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
[22 Dec 2005 13:50] Valerii Kravchuk
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."
[24 Dec 2005 9:51] J Rabbit
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.
[27 Dec 2005 16:57] Valerii Kravchuk
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)
[5 Apr 2006 20:06] 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/4520
[5 Apr 2006 20:24] 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/4521
[7 Apr 2006 11:29] 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/4629
[12 Apr 2006 13:36] 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/4860
[23 Apr 2006 3:52] Konstantin Osipov
Fixed in 5.0.21, 5.1.10
[5 May 2006 22:54] Mike Hillyer
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>