Why does max_used_connections status not get refreshed automatically once it reaches max_connections, even though the Threads_connected falls down

Question

I always wonder, why does the max_used_connections value not come down, once it reaches the peak value of max_connections?

I always flush the status and bring down this value to avoid DB connection refused errors and warning messages in the error log:

=====
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
121112 14:04:36 [Warning] Too many connections
=======

mysql> select version();show variables like "%max_connections%";show global status 
         like "%Max_used%";show status like "%thread%";
            +--------------+
            | version()    |
            +--------------+
            | 5.1.52-2-log |
            +--------------+
            1 row in set (0.00 sec)

            +-----------------+-------+
            | Variable_name   | Value |
            +-----------------+-------+
            | max_connections | 150   |
            +-----------------+-------+
             1 row in set (0.00 sec)

            +----------------------+-------+
            | Variable_name        | Value |
            +----------------------+-------+
            | Max_used_connections | 151   |
            +----------------------+-------+
            1 row in set (0.00 sec)

            +----------------------------+-------+
            | Variable_name              | Value |
            +----------------------------+-------+
            | Com_show_thread_statistics | 0     |
            | Delayed_insert_threads     | 0     |
            | Slow_launch_threads        | 0     |
            | Threads_cached             | 89    |
            | Threads_connected          | 2     |
            | Threads_created            | 1344  |
            | Threads_running            | 2     |
            +----------------------------+-------+
            7 rows in set (0.00 sec)

mysql> show variables like "%thread_cache%";
            +-------------------+-------+
            | Variable_name     | Value |
            +-------------------+-------+
            | thread_cache_size | 90    |
            +-------------------+-------+
            1 row in set (0.00 sec)

Here is the Aborted Connections Status:

mysql> show global status like "%Aborted%";
            +------------------+----------+
            | Variable_name    | Value    |
            +------------------+----------+
            | Aborted_clients  | 119      |
            | Aborted_connects | 43855304 |
            +------------------+----------+
            2 rows in set (0.00 sec)

1. I observed in some scenarios that the max_used_connections value comes down dynamically, once the threads_connected falls down.

2. Is this only an indication variable to increase the max_connections? Posting this query for better understanding. Any detailed explanations please?

I read somewhere thread_cache_size should be greater than max_connections, what will be the impact if it is less ?

Max_used_connections

The maximum number of connections that have been in use simultaneously since the server started.

Answer 1

It doesn't come down because it isn't supposed to come down.

Max_used_connections is a status variable, and is defined as "the maximum number of connections that have been in use simultaneously since the server started."

It turns out, you can also reset the value to the current number of connections using FLUSH STATUS.

But that isn't a "partial solution" to anything because it doesn't change anything. The only thing you are accomplishing with FLUSH STATUS is sort-of baselining the value of an information-only status variable: resetting the value you see, down to the current value of the status variable Threads_connected -- the current number of connections.

This action doesn't change anything about the server's behavior. If you're seeing a change in behavior after doing that, it's coincidental.

mysql> SHOW STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 225   |
+-------------------+-------+
1 row in set (0.00 sec)

The above would be a more useful value for you to watch, while troubleshooting. It shows the current count.

But addressing your problem, you either have an application that does not properly release its connections -- which needs to be fixed elsewhere, not in MySQL -- or you may be right -- you need to increase the value of the max_connections global variable -- after you consider what's written in C.5.2.7 Too many connections. That page also explains why your output above shows a Max_used_connections of 151 compared to max_connections of 150.

Setting thread_cache_size to a value larger than max_connections seems like tremendously unhelpful advice... the cache can't possibly grow larger than max_connections and even a cache anywhere close to that size could only make sense if you have a tremendous amount of churn on your threads... which, in a well-behaved application, won't be the case.