The Geek Diary

MySQL Server Error – “Can’t Create A New Thread (errno 11)”

by

OS error 11 is “Resource temporarily unavailable”. The most common cause of receiving this error when trying to create a new thread is having hit the process’s kernel enforced a limit on open file descriptors. The second most common cause is having hit the process’s kernel enforced limit on the number of processes/threads.

Here are the factors involved:

  1. The system-wide limits: sysctl -e fs.file-max && sysctl -e kernel.threads-max
  2. The per-process limits (in a *new* shell): ulimit -n, grep nofile /etc/security/limits.conf and ulimit -u, grep nproc /etc/security/limits.conf.
  3. The open_files_limit setting in mysqld (mysql> show global variables like “open_files_limit”;).
  4. The other settings within mysqld that can affect the number of open FDs and threads that the process may use (max_connections, thread_cache_size, table_open_cache, table_definition_cache, innodb_open_files, etc.)

Assuming that it was in fact that we hit the open-files-limit, which defaults to 1024 on most Linux systems – we should be able to avoid the issue by taking these steps:

1. Increasing the explicit open files limit in mysqld by making this change to the my.cnf file:

[mysqld]

# Here X could be (max_connections+table_cache)*3  OR you could simply set it very high, for example 32000
open-files-limit = X

2. Restart mysqld service:

/etc/init.d/mysql restart

NPROC limits

If it instead appears that the nproc limit is the cause (CentOS 6 puts a low limit on this in /etc/security/limits.conf), then we can increase the nproc limit in a few different ways.

1. Edit limits.conf:

We can set the limits for the mysql user in the limits.conf file, for example:

mysql soft nproc 10240
mysql hard nproc 40960

Note that limits.conf only applies to login shells and does not affect processes started by init or systemd. Any changes to limits.conf will only apply to new logins, you must log out and log in to start a new session.

2. Edit the mysqld_safe script:

We can also simply add a ulimit call near the top of the mysqld_safe script, for example:

$ ulimit -u 40000

3. Use a wrapper script:

Refer below post on “How to use a Wrapper Script to set Custom Per-Process Attributes for MySQL Server”.

How to use a Wrapper Script to set Custom Per-Process Attributes for MySQL Server

We will need to restart mysqld in order for the changes to take effect.

You May Also Like