DimitriK's (dim) Weblog

MySQL Performance: Hitting error "Can't create a new thread (errno 11)" on a high number of connections

Sorry if you'll find this post stupid, but probably it'll safe a day for somebody else who may hit a wall like it was for me last Friday ;-))

So far, I've started my test scenario to analyze a workload with up to 1600 concurrent users on the 32 cores Linux server. Load is growing step by step by 200 users every 5 min, and once the load is reached 800-1000 users I've started to observe all my new clients ejected with the following error message:

"ERROR 1135 (HY000): 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"

I may say, once you hit such an error on your MySQL server you're less than happy ;-)

What about the error?.. - this error is generic, and what is really important inside of the error message is its error code -- "errno 11" (well, of course you should check if you're really not short in memory, and in my case I've still got 64GB of RAM free, and I've also verified the swap space in case if MySQL cannot reserve a place in swap for some buffer allocations - who knows?..) - then to check what is exactly the meaning of "error 11" you have to use "perror" command:

  # perror 11
  OS error code 11: Resource temporarily unavailable

But the question is which resource if it's not a memory?..

Next thing which is coming in mind is the limit for number of file descriptors - and it's easy to check with "ulimit -n":

  $ ulimit -n
  1024
  $ ulimit -Hn
  1024
  $ ulimit -Sn
  1024

The value 1024 drives me mad - because if it's really true, I should not be able to open more than 200-300 connections (as MySQL is using 4-5 file descriptors per connection in general), but I've already reached 800 connections until now!.. - And another killing thing is that my "max_connections" is set to 2000 and it's not changed by MySQL on start (usually if you're requesting a too high value it'll be adjusted by MySQL automatically on the start).. So what is wrong here?..

Just in case, I've updated my "nofile" limit within my "/etc/security/limits.conf" file:

  # cat /etc/security/limits.conf
  mysql		 soft 	nofile 10240
  mysql		 hard 	nofile 40960
  

and recheck it's seen now by "ulimit" too:

  $ ulimit -n 
  10240
  $ ulimit -Hn 
  40960
  $ ulimit -Sn 
  10240
  $

Then re-test again - the problem is still here since 1000 concurrent sessions..

What's next?.. - once you've asking all around, Google is your friend.. :-) - and I was surprised how often people are complaining by getting the same error!.. - some are even logged a bug on the MySQL site, but seems there was no solution for them as the problem was not reproduced.. Another lucky blog post was about a file descriptor limit: Can't create a new thread  - but I've already check it, and MySQL is reporting as well "open_files_limit: 18010"..

The problem looks like a limit on the new thread creation on the system.. - but my threads limit on the system is:

  # cat /proc/sys/kernel/threads-max 
  2065067

Without having any answer, I've started to group all cases I've found via Google:

• every time the case less or more random -- you cannot say what is exactly the limit of the open sessions, sometime it's changing over a time..
• most of cases was hard or impossible to reproduce, so it's clear the problem is coming from the particular user's configuration, just that user was unable to find which one..
• in most cases users have not reached any limit in RAM, but in some cases there was a limit on open files..
• I don't have all these limits, and my limit is appearing on the thread creation..

Thread creation.. Thread - was the word which bring a light in my mind!! :-)) - working mainly with Solaris servers I've completely forget that threads on the Linux are designed as processes !!! :-)) - then checking my process number limit:

  $ ulimit -u
  1024

it explains 1000 sessions limitation ;-)

adding few lines more to my "/etc/security/limits.conf" file:

  # cat /etc/security/limits.conf
  mysql 	soft 	nofile 	10240
  mysql 	hard    nofile 	40960
  mysql 	soft 	nproc 	10240
  mysql 	hard    nproc 	40960

fixed my issue! :-))

Well, it's strange to see a system limiting a number of processes per user by 1024, but from the other hand it'll be fine to add into MySQL code additional check according the "nproc" limit on the system and reduce the "max_connections" settings automatically if the limit is too low -- it's still better to spend a time understanding why your max sessions runtime value is lower than expected rather to see that 2000 for max sessions is accepted on the start up by MySQL, but then since some level you have connections reject for the reason you need yet to understand..