MySQL – How to Determine the Maximum Possible Number of Threads?

max-connectionsMySQL

What factors determine the maximum possible number of threads mysqld will create?

I only care about consideration for mysql and not limitations that may be imposed by the operating system. Further, I only care about Linux, though if you want to include information specific to Windows, feel free.

As far as I can tell, threads can be created for replication, aspects of innodb, one per client connection up to max_connections, with an additional +1 to handle super connections. What else is there, or is that it?

threads_max =   max_connections  
              + 1 for super  
              + 2 for replication slave threads  
              + ? for InnoDB ?

Best Answer

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
Related Question