After upgrading to Percona’s patched MySQL 5.1*, end users were having connectivity problems, and reporting errors such as:
OperationalError: (2003, "Can't connect to MySQL server on 'db-amo-ro' (110)")
TimeoutError: Request timed out after 5.000000 seconds
OperationalError: (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")
We had these same problems a while back, before increasing ulimit settings in
/etc/sysconfig/mysqld. Oracle’s MySQL startup script specifically sources this file:
[ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog
However, we saw these errors again when we upgraded to Percona’s MySQL 5.1. At first we thought that it was because Oracle’s startup script is
/etc/init.d/mysqld and Percona’s is named
/etc/init.d/mysql (so we would put ulimits in
/etc/sysconfig/mysql). However, when we looked, we saw that Percona’s startup script does NOT source anything in /etc/sysconfig.
So then we put the following in /etc/security/limits.d/99-nproc-mysql.conf:
root soft nproc 32768
root hard nproc 65535
We restarted MySQL and all was good. Even though we are long past having this problem, I thought it was important enough to blog about.
* We finished upgrading all of our servers to MySQL 5.1 at the end of 2012. We ran into this interesting snag that I wanted to blog about, even though we’re in the middle of upgrading to MySQL 5.5 right now (and by the end of the year, we will upgrade to MySQL 5.6 – the performance schema stuff is definitely something we want to utilize).