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.

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 ?
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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
share|improve this answer
    
So if I set max_connections to 1, I can expect mysqld to only ever spawn 1 thread? –  Preston Jan 10 at 5:20
1  
@Preston, I've updated my post. Thanks! –  Dimitre Radoulov Jan 10 at 10:20
1  
Using your answer as a starting point I did a slightly more in depth analysis and came up with this: preston4tw.blogspot.com/2014/01/… –  Preston Jan 14 at 14:58
1  
@Preston, good job! Thanks for sharing your analysis! –  Dimitre Radoulov Jan 14 at 20:38
add comment

The best way to see current threads (including all foreground and background threads) is now with performance_schema. For example:

mysql 5.7.3> select thread_id, name, type, PROCESSLIST_ID from performance_schema.threads;
+-----------+----------------------------------------+------------+----------------+
| thread_id | name                                   | type       | PROCESSLIST_ID |
+-----------+----------------------------------------+------------+----------------+
|         1 | thread/sql/main                        | BACKGROUND |           NULL |
|         2 | thread/innodb/io_log_thread            | BACKGROUND |           NULL |
|         3 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         4 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         5 | thread/innodb/io_ibuf_thread           | BACKGROUND |           NULL |
|         6 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         7 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         8 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|         9 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|        10 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|        11 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|        14 | thread/innodb/srv_lock_timeout_thread  | BACKGROUND |           NULL |
|        15 | thread/innodb/srv_error_monitor_thread | BACKGROUND |           NULL |
|        16 | thread/innodb/srv_monitor_thread       | BACKGROUND |           NULL |
|        17 | thread/innodb/srv_master_thread        | BACKGROUND |           NULL |
|        18 | thread/innodb/srv_purge_thread         | BACKGROUND |           NULL |
|        19 | thread/innodb/page_cleaner_thread      | BACKGROUND |           NULL |
|        20 | thread/sql/signal_handler              | BACKGROUND |           NULL |
|        22 | thread/sql/one_connection              | FOREGROUND |              2 |
+-----------+----------------------------------------+------------+----------------+
19 rows in set (0.00 sec)

I believe the background threads are fixed in number (the actual count will depend on some configuration settings such as innodb_read_io_threads and the number of innodb_purge_threads etc.) The FOREGROUND threads are one per connection + a potential of thread_cache_size on top of that.

share|improve this answer
add comment

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.