When your application get error "too many connections" underlying problem might be caused by multiple things. For a in-depth understanding of how MySQL handles connections read this blog.
In this blog we will investigate why and how to solve these problems when running MySQL installed on Debian/Ubuntu using systemd (default MySQL packages).
OS: Debian 9
MySQL Server version: 5.7.25 MySQL Community Server (GPL)
(will most likely be the same for MySQL 8.0 versions)
Goal is to have 10.000 working connections to MySQL!
The default value for max_connections is 151 connections so first step is to increase the max_connections variable to 10.000.
This is documented in the manuals here:
- https://dev.mysql.com/doc/refman/5.7/en/too-many-connections.html
- https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connections
The max_connections is a dynamic setting so lets increase value to 10.000 and see what happens.
Hmmm, it looks like we only got 214 connections, lets look at the error log:
Looks like we hit some resource limit.
Lets look in the MySQL manual for running MySQL under systemd, that can be found here.
Looks like we need to increase the number of allowed open files for MySQL, 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). You can also create/modify the override file by using native systemctl command like: systemctl edit mysql
Add LimitNOFILE=10000 in file override.conf like:
After this we need to reload the systmed daemon and restart the MySQL service like:
MySQL is now saying we have 9190 connections:
So, MySQL is using some files for additional work and we need to set this a bit higher to get 10.000 connections, lets set it to 11.000 and reload the systemd daemon and restart the MySQL service.
Good, now we have 10.000 connections available according to MySQL.
Lets run our application and verify we can get 10.000 connections, I use this small perl script to open 10.000 connections.
Just below 5.000 connection I get a new error in the application "Can't create a new thread (errno 11)"
Lets have a look at the MySQL error log:
I found this new limit by running command below when perl script was running:
watch -n.5 "mysql -uroot -proot -se'show status like \"%threads%\"'"
Strange, where is this new limitation just below 5.000 connections coming from?
Looking at resource limits for my MySQL daemon I should have close to 8000 processes:
Lets looks at status report for my MySQL service:
There seem to be some additional limit on Tasks that limit me to 4915 connections.
Lets expand our override.conf configuration to cover for 11.000 tasks also.
(remember to reload systemd and restart MySQL service after each change in override.conf)
Now we got just under 8.000 connection and got the same error " Can't create a new thread (errno 11)" but this time it's because of the limit of max processes:
Lets increase this limit to 11.000 in our override.conf:
After reloading systemd configuration and restarting MySQL service I can now get 10.000 connections and the perl script runs without any errors!
Summary:
There are different limits when setting max_connections in MySQL:
- The default max connections is 151.
- At 214 connections you are limited by max open files.
- At 4915 you are limited by Max Tasks (systemd)
- Just below 8000 you are limited by max number of processes
By adding a override file (as showed above) you can overcome all these limits.
Remember to:
Look at error message in application and MySQL error logs.
Look at output from: cat /proc/$( pgrep -o mysql )/limits
Look at output from: systemctl status mysql
Test your application (or use my perl script) and monitor that it works!
Monitor how many connections you have: mysql -uroot -p -e'show status like "Threads_connected"'
In this blog we will investigate why and how to solve these problems when running MySQL installed on Debian/Ubuntu using systemd (default MySQL packages).
OS: Debian 9
MySQL Server version: 5.7.25 MySQL Community Server (GPL)
(will most likely be the same for MySQL 8.0 versions)
Goal is to have 10.000 working connections to MySQL!
The default value for max_connections is 151 connections so first step is to increase the max_connections variable to 10.000.
This is documented in the manuals here:
- https://dev.mysql.com/doc/refman/5.7/en/too-many-connections.html
- https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connections
The max_connections is a dynamic setting so lets increase value to 10.000 and see what happens.
| root@debian:~# mysql -uroot -proot -se "select @@max_connections" @@max_connections 214 |
Hmmm, it looks like we only got 214 connections, lets look at the error log:
| 2019-04-01T06:29:48.301871Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 50000) 2019-04-01T06:29:48.302000Z 0 [Warning] Changed limits: max_connections: 214 (requested 10000) 2019-04-01T06:29:48.302004Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 2000) |
Looks like we hit some resource limit.
Lets look in the MySQL manual for running MySQL under systemd, that can be found here.
Looks like we need to increase the number of allowed open files for MySQL, 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). You can also create/modify the override file by using native systemctl command like: systemctl edit mysql
Add LimitNOFILE=10000 in file override.conf like:
| root@debian:~# cat /etc/systemd/system/mysql.service.d/override.conf [Service] LimitNOFILE=10000 |
After this we need to reload the systmed daemon and restart the MySQL service like:
| root@debian:~# systemctl daemon-reload root@debian:~# systemctl restart mysql |
MySQL is now saying we have 9190 connections:
| root@debian:~# mysql -uroot -proot -se "select @@max_connections" @@max_connections 9190 |
So, MySQL is using some files for additional work and we need to set this a bit higher to get 10.000 connections, lets set it to 11.000 and reload the systemd daemon and restart the MySQL service.
| root@debian:~# mysql -uroot -proot -se "select @@max_connections" mysql: [Warning] Using a password on the command line interface can be insecure. @@max_connections 10000 |
Good, now we have 10.000 connections available according to MySQL.
Lets run our application and verify we can get 10.000 connections, I use this small perl script to open 10.000 connections.
Just below 5.000 connection I get a new error in the application "Can't create a new thread (errno 11)"
Lets have a look at the MySQL error log:
| root@debian:~# tail -1 /var/log/mysql/error.log 2019-04-01T06:50:35.657397Z 0 [ERROR] Can't create thread to handle new connection(errno= 11) |
I found this new limit by running command below when perl script was running:
watch -n.5 "mysql -uroot -proot -se'show status like \"%threads%\"'"
Strange, where is this new limitation just below 5.000 connections coming from?
Looking at resource limits for my MySQL daemon I should have close to 8000 processes:
| root@debian:~# cat /proc/$( pgrep -o mysql )/limits Max processes 7929 7929 processes Max open files 11000 11000 files |
Lets looks at status report for my MySQL service:
| root@debian:~# systemctl status mysql | grep Tasks Tasks: 127 (limit: 4915) |
There seem to be some additional limit on Tasks that limit me to 4915 connections.
Lets expand our override.conf configuration to cover for 11.000 tasks also.
| root@debian:~# cat /etc/systemd/system/mysql.service.d/ [Service] LimitNOFILE=11000 TasksMax=11000 |
(remember to reload systemd and restart MySQL service after each change in override.conf)
Now we got just under 8.000 connection and got the same error " Can't create a new thread (errno 11)" but this time it's because of the limit of max processes:
| root@debian:~# cat /proc/$( pgrep -o mysql )/limits Max processes 7929 7929 processes |
Lets increase this limit to 11.000 in our override.conf:
| root@debian:~# cat /etc/systemd/system/mysql.service.d/override.conf [Service] LimitNOFILE=11000 LimitNPROC=11000 TasksMax=11000 |
After reloading systemd configuration and restarting MySQL service I can now get 10.000 connections and the perl script runs without any errors!
Summary:
There are different limits when setting max_connections in MySQL:
- The default max connections is 151.
- At 214 connections you are limited by max open files.
- At 4915 you are limited by Max Tasks (systemd)
- Just below 8000 you are limited by max number of processes
By adding a override file (as showed above) you can overcome all these limits.
Remember to:
Look at error message in application and MySQL error logs.
Look at output from: cat /proc/$( pgrep -o mysql )/limits
Look at output from: systemctl status mysql
Test your application (or use my perl script) and monitor that it works!
Monitor how many connections you have: mysql -uroot -p -e'show status like "Threads_connected"'
