February 7, 2020

How MySQL handles connection ? Troubleshooting MySQL ERROR 1040, Too many connections!

Understanding how MySQL handles connections and Troubleshooting MySQL error 1040 – Too many connections!


How to interpret “MySQL error 1040 – Too many connections ! ” ?

When a client tries to log into MySQL it may sometimes be rejected and receive an error message saying that there are “too many connections“. This means that the maximum number of clients that may be connected to the server has been reached. Either the client will have to wait for another client to log off, or the administrator will have to increase the maximum number of connections allowed.

Information about connections to a server can be found using the SHOW STATUS statement:

Prerequisite – Few points to remember before working or troubleshooting MySQL ” Too many connections ! ” error

  1. MySQL does not have it’s own thread handling mechanism / implementation and it completely relies on the thread handling implementation of the underlying operating system.
  2. MySQL system variable max_connections control the maximum number of clients the server permits to connect simultaneously,   You may have to increase max_connections if more clients attempt to connect simultaneously then the server is configured to handle (Explained more in detail –  “Too many connections”).
  3. How MySQL connection handling (both connects and disconnects) works  ?
    1. MySQL Clients connects to MySQL Server via a simple and regular TCP-IP connect message though port 3306 on the Server instance
    2. The incoming connection requests are queued and then processed by the receiver thread one by one, All that receiver thread does is create user thread.  It’s actually user thread which handles the client-server protocol for connection management, Which involves allocate and initialize the corresponding THD for user authentication based on credentials stored on THD’s security policies / directories and finally if successfully authorized in the connection phase, the user thread will enter command phase
    3. The  receiver  thread will either create a new OS thread or reuse and existing “free” OS thread if available in the  thread cache. So we strongly recommend increasing  thread cache in  cases where number of  connections fluctuates  between ver few connections  and having  many connections. But there are three things which a thread might need to wait for:  A mutex, a database lock, or IO.
    4. THD basically is a large data structure used for several purposes like connection management, authorization and even unto to query execution, So how  much THD consumes memory is directly proportionate to the query execution complexities and connection traffic.

MySQL error – Too many connections, How to fix ?

Recently one of customers ( among the top 5 largest e-commerce companies in the world ) called us to check how graceful their connection handling works during peak hours of business, They had issues in  the past with ” ERROR 1040: Too many connections “ and that clearly explains the maximum number of clients that may be connected to the server has been reached so either the client will have to wait for another client to log off, or the administrator will have to increase the maximum number of connections allowed. so wanted us to do a detailed health-check on MySQL connection management and address “Too many connections” error proactively, We have explained below on how we could successfully reproduce this issue and recommended the fix:

Goal: Manage  50,000 connections on MySQL 8.0 (Ubuntu)

The default setting for system variable max_connections is “151”and we are benchmarking 50K connections so the first step before benchmarking  is to increase max_connections to 50000. we increased max_connections to 50000 dynamically and what happened after that was not expected, We have copied the results below:

We got only 697 connections, Let’s interpret MySQL error log before proceeding to next steps.. We have copied the same below:

This is due to open files limitations for MySQL so let’s increase now the number of allowed open files for MySQL, The following steps we did to fix this resource limit issue:

  • Option  1 – Locate the systemd configuration folder for MySQL and create file /etc/systemd/system/mysqld.service.d/override.conf (file can be called anything ending with .conf).
    • Add LimitNOFILE=55000 in the file override.conf
    • Add TasksMax=55000 in the file override.conf
    • Add LimitNPROC=55000 in the file override.conf
  • Option 2 – We can also create/modify the override file by using native systemctl command like: systemctl edit mysql

** MySQL uses some files for additional work and we need to set LimitNOFILE, TasksMax and LimitMPROC higher to get 50000 connections, lets set it to 55000 and reload the systemd daemon and restart the MySQL service.

Reload the systmed daemon and restart the MySQL service:

Now let’s check max_connections to confirm the change applied:

Conclusion

We have no fixed value recommendations for system variable max_connections, It completely depends on your application load and how your application does connection handling. We advice our customers to avoid too many connections opened concurrently because each thread connected needs memory and there is also resource intensive context switching causing overall performance degradation, Thanks for reading and comments are welcome !

References

About MinervaDB Corporation 53 Articles
Independent and vendor neutral consulting, support, remote DBA services and training for MySQL, MariaDB, Percona Server and ClickHouse with core expertize in performance, scalability and high availability . We are an virtual corporation, all of us work from home on multiple timezones and stay connected via Email, Skype, Google Hangouts, Phone and IRC supporting over 100 customers worldwide

Be the first to comment

Leave a Reply

Your email address will not be published.


*