Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.5Mb
PDF (A4) - 38.6Mb
PDF (RPM) - 33.3Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 134.2Kb
Man Pages (Zip) - 190.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  How MySQL Handles Client Connections

8.12.4.1 How MySQL Handles Client Connections

This section describes aspects of how the MySQL server manages client connections.

Network Interfaces and Connection Manager Threads

The server is capable of listening for client connections on multiple network interfaces. Connection manager threads handle client connection requests on the network interfaces that the server listens to:

  • On all platforms, one manager thread handles TCP/IP connection requests.

  • On Unix, the same manager thread also handles Unix socket file connection requests.

  • On Windows, a manager thread handles shared-memory connection requests, and another handles named-pipe connection requests.

  • On all platforms, an additional network interface may be enabled to accept administrative TCP/IP connection requests. This interface can use the manager thread that handles ordinary TCP/IP requests, or a separate thread.

The server does not create threads to handle interfaces that it does not listen to. For example, a Windows server that does not have support for named-pipe connections enabled does not create a thread to handle them.

Client Connection Thread Management

Connection manager threads associate each client connection with a thread dedicated to it that handles authentication and request processing for that connection. Manager threads create a new thread when necessary but try to avoid doing so by consulting the thread cache first to see whether it contains a thread that can be used for the connection. When a connection ends, its thread is returned to the thread cache if the cache is not full.

In this connection thread model, there are as many threads as there are clients currently connected, which has some disadvantages when server workload must scale to handle large numbers of connections. For example, thread creation and disposal becomes expensive. Also, each thread requires server and kernel resources, such as stack space. To accommodate a large number of simultaneous connections, the stack size per thread must be kept small, leading to a situation where it is either too small or the server consumes large amounts of memory. Exhaustion of other resources can occur as well, and scheduling overhead can become significant.

MySQL Enterprise Edition includes a thread pool plugin that provides an alternative thread-handling model designed to reduce overhead and improve performance. It implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections. See Section 5.6.3, “MySQL Enterprise Thread Pool”.

To control and monitor how the server manages threads that handle client connections, several system and status variables are relevant. (See Section 5.1.7, “Server System Variables”, and Section 5.1.9, “Server Status Variables”.)

The thread_cache_size system variable determines the thread cache size. By default, the server autosizes the value at startup, but it can be set explicitly to override this default. A value of 0 disables caching, which causes a thread to be set up for each new connection and disposed of when the connection terminates. To enable N inactive connection threads to be cached, set thread_cache_size to N at server startup or at runtime. A connection thread becomes inactive when the client connection with which it was associated terminates.

To monitor the number of threads in the cache and how many threads have been created because a thread could not be taken from the cache, check the Threads_cached and Threads_created status variables.

When the thread stack is too small, this limits the complexity of the SQL statements which the server can handle, the recursion depth of stored procedures, and other memory-consuming actions. To set a stack size of N bytes for each thread, start the server with thread_stack. set to N at server startup.

Connection Volume Management

To control the maximum number of clients the server permits to connect simultaneously, set the max_connections system variable at server startup or at runtime. It may be necessary to increase max_connections if more clients attempt to connect simultaneously then the server is configured to handle (see Section B.5.2.6, “Too many connections”).

mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the CONNECTION_ADMIN or SUPER privilege. By granting the privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.7.6.29, “SHOW PROCESSLIST Syntax”. (The server also permits administrative connections on a dedicated interface. See Administrative Connection Management.)

If the server refuses a connection because the max_connections limit is reached, it increments the Connection_errors_max_connections status variable.

The maximum number of connections MySQL supports (that is, the maximum value to which max_connections can be set) depends on several factors:

  • The quality of the thread library on a given platform.

  • The amount of RAM available.

  • The amount of RAM is used for each connection.

  • The workload from each connection.

  • The desired response time.

  • The number of file descriptors available.

Linux or Solaris should be able to support at least 500 to 1000 simultaneous connections routinely and as many as 10,000 connections if you have many gigabytes of RAM available and the workload from each is low or the response time target undemanding.

Increasing the max_connections value increases the number of file descriptors that mysqld requires. If the required number of descriptors are not available, the server reduces the value of max_connections. For comments on file descriptor limits, see Section 8.4.3.1, “How MySQL Opens and Closes Tables”.

Increasing --open-files-limit may be necessary, which may also require raising the operating system limit on how many file descriptors can be used by MySQL. Consult your operating system documentation to determine whether it is possible to increase the limit and how to do so. See also Section B.5.2.17, “File Not Found and Similar Errors”.

Administrative Connection Management

As of MySQL 8.0.14, the server permits a TCP/IP port to be configured specifically for administrative connections. This provides an alternative to the single administrative connection that is permitted on the network interfaces used for ordinary connections even when max_connections connections are already established (see Connection Volume Management).

The administrative network interface has these characteristics:

  • The interface is available only if the admin_address system variable is set at startup to indicate the IP address for the administrative interface. If no admin_address value value is specified, the server maintains no administrative interface.

  • The admin_port system variable specifies the interface TCP/IP port number (default 33062).

  • There is no limit on the number of administrative connections.

  • Connections are permitted only by users who have the SERVICE_CONNECTION_ADMIN privilege.

The create_admin_listener_thread system variable enables DBAs to choose at startup whether the administrative interface is implemented using the listener thread used for ordinary connections, or has its own separate thread. The default is to implement the administrative interface using the listener thread used for ordinary connections.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.