ulimits and upgrading from Oracle MySQL 5.0 to Percona patched MySQL 5.1

Sheeri

1

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).

One response

  1. Simon Mudd wrote on ::

    Another similar issue: back_log was found by me. See: http://bugs.mysql.com/bug.php?id=67997

    I am still affected by this as the need to restart all servers is very intrusive and on most servers it was not critical.
    It would certainly be very nice to have some way to dynamically configure these settings (were possible) from inside mysqld.
    Other settings like core-file (to allow core dumping comes to mind, though I’m not sure that can be adjusted once the server is running).

    So perhaps we can get some of these settings into 5.7 as I would guess that fixing this in 5.6 will not be acceptable as it is a functional change (even if trivial) and this would ensure that once up mysqld can stay up for as long as possible and not need restarting for many infrequent but sometimes necessary configuration changes? Certainly a nice to have.