1

In the MySQL 5.7 manual, I read that the thread cache has a size determined by the thread_cache_size system variable.

The default value is 0 (no caching), which causes a thread to be set up for each new connection and disposed of when the connection terminates.

But what exactly is a "thread"?

A connection thread becomes inactive when the client connection with which it was associated terminates.

Again, I can't understand what is a "thread" that I'm reading about.

Threads_created: The number of threads created to handle connections.

What the word "thread" refers to in this case?

Threads_cached: The number of threads in the thread cache.

Again this word "thread".

Threads_connected: The number of currently open connections.

I can't understand what is a thread. It is not connection I guess...

But what is it? Maybe it's only a matter of translation in my language, but I can't make up my mind to understand what is a "thread"

Ian_H
  • 1,646
  • 10
  • 17
andrea
  • 341
  • 1
  • 6
  • 14
  • Please read the general notes on threads for MySQL 5.7 at: https://dev.mysql.com/doc/refman/5.7/en/mysql-threads.html – RLF Feb 21 '17 at 18:53

3 Answers3

1

I believe in all the cases you mention the word "thread" simply means a computational thread, in the sense that is well described in this Wikipedia article, for example.

Leaving cache aside and starting from 0 connections, each new connection will cause a new processing thread to be created for that connection exclusive use; the thread will perform all SQL processing and network communication for this specific connected client. Since each thread is scheduled independently by the operating system, they appear to (or indeed do, when multiple processor cores are available) run concurrently.

Since creating a thread has some latency, it's often beneficial to have a pool of threads created in advance; each thread is still assigned to a connection exclusively, but when the connection is closed, the thread is not destroyed but rather returned to the pool to wait for another incoming connection.

You can find a detailed discussion of MySQL thread management in this book.

mustaccio
  • 25,896
  • 22
  • 57
  • 72
1

On *nix, a MySQL thread is a relatively heavy-weight "process". You can see the "processes" in top or ps.

For Windows, thread_cache_size = 0 is OK. This is because threading is done differently.

On *nix, thread_cache_size should be a modest number, say, 20. It is the maximum number of unused processes to keep open. Suppose you have a burst of activity and briefly peak at 30 threads running (Threads_connected), but then 7 disconnect. That leaves 23 connected, plus the cache of 7 "processes" not yet terminated (Threads_cached). Let's say another 18 disconnect. You are down to 5 running and 20 (the cache size) held in reserve.

A "thread_pool" is something different. Furthermore, Oracle and MariaDB implemented different things here.

The STATUS values of Threads_cached/connected/created/running give a peak into whether the VARIABLE thread_cache_size needs tuning.

Threads_created/Uptime is how fast a new process is being created because the cache is too small. Recommend keeping this under 3/second.

Rick James
  • 78,038
  • 5
  • 47
  • 113
0

When MySQL people, or even their documentation says "thread", it can mean either a client connection or an OS thread in the server. It is not interchangeable, but historically MySQL was (and for majority of its users still is) thread-per-connection only, so there used to be 1-1 correspondence between threads and connections. With that

  • Threads_created - number of OS threads created so far
  • Threads_cached - number of OS threads cached. Those threads are reused when a new connection is created.
  • Threads_connected - number of client connections
Vladislav Vaintroub
  • 596
  • 5
  • 8