MySQL Configuration – How to Calculate max_connections Variable

Three points:

• I don't see the immediate connection between your "too many connections" and the table cache. There might be such connection, but you have not provided info which suggests the two relate.

• I disagree with the advice about "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."

• You haven't listed the table_open_cache & table_definition_cache settings. The table_cache variable is an old one.

Some suggestions:

• With regard "too many connections", try the following: Set max_connections to some reasonable value, like 300, throwing around a number. Set open_files_limit to a very high values, like 8192. By default you are only allowed 1024 open files on linux. Sockets, threads, table descriptors -- all map to files. Do you have some connection pool which holds many open (idle) connections, with some keepalive? How many processes do you get on a typical SHOW PROCESSLIST?

• With regard table cache: I suggest set a high value. With 3,000 queries per hour you're not remotely near the problem of having a problem with that. Why don't you set: table_open_cache=2048 table_definition_cache=512 (even though you may only have a couple tables)

• With regard the advice about open tables, you should be more interested in the number of tables opened per second (or minute, whatever) in any given time period, rather than looking at the overall number of tables opened since the server started to run. So you should monitor your opened_tables status variable every few minutes, and do the math. You would typically want to see less than 1 table opened in a second. Again, I'm throwing numbers around, but just to give you proportion. The open_tables is nice to know, as well. On good usage it can be very high, and that means your server is actively reading from tables. Fine! As long as the number of opened_tables/sec does not rise too high, this is no worry.

• There could be other reasons for "too many connections". Are the queries very long? Such that they just accumulate? Are some background tasks failing to complete? (You do have a wait_timeout=300 which is good; however some PHP job might be issuing new queries every few seconds, failing to close properly?

• Noting again: watch over SHOW PROCESSLIST and see what comes up.

On Linux for example, whichever is smaller among:

grep -F 'Max process' /proc/$(/sbin/pidof mysqld)/limits|awk '{print $3}'

and:

mysql -e'show global variables like "max_connections"'

UPDATE: As you correctly pointed out, we should add at least the background threads to the list. More information below.

As far as your last update is concerned, for InnoDB we have innodb_read_io_threads and innodb_write_io_threads.

I've just checked and a MySQL 5.5.34 on debian with default values we have:

mysql> pager grep -i thread
PAGER set to 'grep -i thread'
mysql> show engine innodb status\G
BACKGROUND THREAD
srv_master_thread loops: 1 1_second, 1 sleeps, 0 10_second, 1 background, 1 flush
srv_master_thread log flush and writes: 2
MySQL thread id 36, OS thread handle 0xa6c32b40, query id 105 localhost root
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Main thread process no. 21508, id 2758552384, state: waiting for server activity
1 row in set (0.00 sec)

mysql>

And the OS sees:

root@ubuntu:~# ps -Lp$(pidof mysqld) |wc -l
18

After connecting as root, the thread count remained the same (the thread count increased when I made another one tough):

root@ubuntu:~# mysql -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.34-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \! ps -Lp$(pidof mysqld) |wc -l
18
mysql> show global status like '%thread%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threads_cached                           | 0     |
| Threads_connected                        | 1     |
| Threads_created                          | 1     |
| Threads_running                          | 1     |
+------------------------------------------+-------+
8 rows in set (0.01 sec)

mysql> show global variables like '%thread%';
+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| innodb_purge_threads                    | 0                         |
| innodb_read_io_threads                  | 4                         |
| innodb_thread_concurrency               | 0                         |
| innodb_thread_sleep_delay               | 10000                     |
| innodb_write_io_threads                 | 4                         |
| max_delayed_threads                     | 20                        |
| max_insert_delayed_threads              | 20                        |
| myisam_repair_threads                   | 1                         |
| performance_schema_max_thread_classes   | 50                        |
| performance_schema_max_thread_instances | 1000                      |
| thread_cache_size                       | 8                         |
| thread_concurrency                      | 10                        |
| thread_handling                         | one-thread-per-connection |
| thread_stack                            | 196608                    |
+-----------------------------------------+---------------------------+
14 rows in set (0.00 sec)

So, as I said, this could be version specific, but we seem to have:

• srv_master_thread
• n I/O threads (innodb_read_io_threads,innodb_write_io_threads, insert buffer thread, log thread)
• other background threads (7 in this case: total 18 - 10(I/O) - 1(main)).

So, if you need to do some kind of capacity planning, I would suggest to check the number of background threads after the startup without user/app connection and add to that:

1. 1 for users with super privilege
2. the value of max_connections
3. 2 x replication slave