Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Interesting...where do you get the idea that flushing the status of max_used_connections solves the problem of too many connections? –  Derek Downey Nov 14 '12 at 16:03
    
@DTest what i mean to say is flushing the status is only a partial solution,well in mysql we dont have option to flush specific server status variable,i knew flushing the status will reset all the status variables. if the problem persist still,only option to increase the max_connections.After flushing the status i observed in many situations the max_used_connections dopped down..but when ever it reach to max_connections, it keeps constant without changing. seeking some clarity –  Gopinath Nov 14 '12 at 16:58

1 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.

share|improve this answer
    
Thanks Michael for the detailed explanation. –  Gopinath Nov 15 '12 at 17:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.