MySQL FATAL: error 1040: Too many connections & 1135 Can’t create a new thread

Hi folks,

Some of you probably already got error 1040 by playing with sysbench for instance and multiplying the number of MySQL threads. Documentation and blogs usually simply state that you have to increase the number of max_connections and the open files limit. If you tried and you didn’t succeed this blog is for you.

Despite the following entries in /etc/my.cnf the max_connections and open_files_limit are not set correctly:

max_connections = 8000
open_files_limit = 8192

You have the following variables set in MySQL

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 214   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql> show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

If you reach the max_connections limits, you will for sure get the error below and the connection will abort:

FATAL: error 1040: Too many connections
(last message repeated 1 times)

or 

FATAL: pthread_create() for thread #4016 failed. errno = 11 (Resource temporarily unavailable)
FATAL: unable to connect to MySQL server, aborting...
FATAL: error 1135: Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

You can test it with sysbench for instance by starting 512 parallel threads.

This behavior can be easily changed by setting some parameters in the configuration file for the pam_limits module (limits.conf)

[root@myhost ~]# vi /etc/security/limits.conf

mysql   soft   nofile    8192
mysql   hard   nofile    8192
mysql   soft   nproc     8192
mysql   hard   nproc     32768
mysql   soft   core    unlimited
mysql   hard   core    unlimited

When you log on with the mysql user, the new parameters will be set to the new value.

mysql@mysql:/home/mysql/ [mysqld1] ulimit -a mysql
core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 63476
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 8192
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 8192
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
mysql@qual-neos-data1:/home/mysql/ [mysqld1]

Afterwards you simply have to restart your mysql servers in order to take changes into consideration.

mysql@myhost:/home/mysql/ [mysqld1] mysqld_multi stop
mysqld_multi log file version 2.16; run: Wed Aug 19 12:13:09 2015

Stopping MySQL servers
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
mysql@qual-neos-data1:/home/mysql/ [mysqld1]

mysql> show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 8192  |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 7382  |
+-----------------+-------+
1 row in set (0.00 sec)

You can now set up the number of threads to 512 in sysbench and test !

Have fun!