There’s some confusion in the MySQL terminology for processes, threads & connections, which I will try to resolve. I can’t explain the full nature of what processes and threads are; please see Wikipedia [1] [2] for that. But here’s some basics with regard to MySQL:
- MySQL server is a single process application.
- It is multithreaded.
- It (usually) acts as a TCP/IP server, accepting connections.
- Each connection gets a dedicated thread.
- These threads are sometimes named processes, and sometimes they’re referred to as connections.
The last part is where confusion arises, so let me discuss again the use of threads and connections in MySQL.
MySQL truly is a single process server. It is multi threaded, in that there are many obvious and less obvious threads comprising the server. Such threads are the InnoDB I/O threads, the DELAYED INSERT thread, etc. Oh, and of course: the connection threads. More on this in a short while.
On older Linux versions or on glibc-static versions, one may view MySQL as a multi-process server. This is not so: it is merely because threads are mapped to OS processes. For the sake of this discussion this is irrelevant. mysqld is a single process.
So, every new connection gets its own thread. Assuming no thread pool is in use, every new connection makes for the creation of a new thread, and a disconnect causes for that thread’s destruction. Hence, there is a 1-1 mapping between connections and active threads. But then, there is a thread pool, which means there can be threads which are not associated with any connection. So, the number of threads is greater than or equal to the number of connections.
Here’s where terminology gets confusing. When you want to see what’s executing on the server, you issue SHOW PROCESSLIST:
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 4
User: root
Host: localhost
db: mycheckpoint
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
1 row in set (0.02 sec)
Perhaps this should have been called SHOW THREADLIST; the acting queries are not really processes.
OK, so there’s process #4 which is executing a query. What’s my process id? Turns out I don’t have a process id. I do get to have a CONNECTION_ID():
mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 4 | +-----------------+
So how many processes or connections are now actually doing anything? We now must check for ‘Threads_running’.
mysql> SHOW GLOBAL STATUS LIKE 'Threads_running'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | Threads_running | 1 | +-----------------+-------+
And so we have ‘Threads_cached’, ‘Threads_connected’ & ‘Max_used_connections’.
Confusing?
Most of the time one can simply think of processes, threads and connections as 1-1-1 mapped, and not bother with it.
Very well explained. Thanks for the writeup!
This explanation will really help out a lot of guys and I hope SHOW PROCESSLIST gets changed to SHOW THREADLIST 🙂
>>> But then, there is a thread pool, which means there can be threads which are not associated with any connection. So, the number of threads is greater than or equal to the number of connections.
Actually that’s quite opposite to philosophy of having thread pool, idea with thread pool is to have minimum no. of threads running for serving ‘n’ no. of connections. Basically all the connections are spread across thread groups and scheduler initializes each thread group with single thread called ‘listener’ thread, it listens for incoming statement request and tries to execute it immediately if it can else it puts it in a queue to be executed later. Statement waits in queue for specific time and then a new thread is spawned to execute the statement.
In other words, with thread pool enabled, max_threads = max_connections + no. of thread groups (in worst case scenario)