Skip to content

# MySQL terminology: processes, threads & connections

MySQL Configuration Syntax November 3, 2010

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
3
Leave a Reply

3 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
3 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
LenZ

Very well explained. Thanks for the writeup!

Guest
Ovais Tariq

This explanation will really help out a lot of guys and I hope SHOW PROCESSLIST gets changed to SHOW THREADLIST 🙂

Guest
SacTiw

>>> 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… Read more »

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.