Wednesday, September 21, 2011

Stump the Murph: ulimit, pam and linux

There is a game that a small group of friends and I have been playing since my Friendster years. It's called Stump the Murph. Basically if there is some weird problem in Linux mainly but it's in a variety of subjects-that we can't figure out we pass it to one of our friends Kevin Murphy. In 8 years I believe I stumped him once but I can't remember what it is so it doesn't count.

Here is the problem

SQLSTATE[HY000] [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

"Obviously" this means you need to raise the ulimit for the process running mysql. I say "obviously" because this error means different things. In most cases it means that the server ran out of memory. perror 11 says OS error code  11:  Resource temporarily unavailable, yet when there is enough memory there may be a pam_limit issue. In my case there is.

So I did the following

in /etc/security/limits.conf I added this

mysql   soft    nofile  10240
mysql   hard    nofile  1537454
mysql   soft    nproc   32768
mysql   hard    nproc   65535

yet when I test the changes su - mysql
I get

su: pam_limits(su-l:session): Could not set limit for 'nofile': Operation not permitted

So my next course of action is to check


wait a second it has

session required

/etc/pam.d/su calls

session         include         system-auth

thus I don't need to add session required

Now the game of Stump the Murph begins:

In about 1/2 hour Murph figured out the solution! He deduced that since 

cat /proc/sys/fs/file-max

you can't set the hard limit of nofile to 1537454 because in theory you could starve the kernel from file descriptors thus from murph's suggestion I did

mysql      soft    nofile  10240
mysql      hard    nofile  768727
mysql      soft    nproc   32768
mysql      hard    nproc   65535

#MAKE SURE root has the same or greater settings!!!

root      soft    nofile  10240
root      hard    nofile  768727
root      soft    nproc   32768
root      hard    nproc   65535

Thanks Murph!


Trent Hornibrook said...

I dont believe the limits fix work. From my testing, adjusting limits.conf to change these values are not passed on to the mysqld spawned child of mysqld_safe.

You can see this examining the proc filesystem for the process id of mysqld.

The only way I have gotten nproc to change by running an explicit ulimit within the start script.

open files are set via open-files-limit within my.cnf in the mysqld block.


Dathan Pattishall said...

I verified it works. Might want to look at your pam settings. There was a bug in centos a while ago that didn't allow for pam to respect some changes. Other then that your method work as well.

Also make sure that file-nr-max (something like that) is difference of like 30K fds then what your setting in limits.conf

jr195 said...

On CentOS 6.3, Percona Server 5.5, starting mysql with the init script, which calls mysqld_safe, which calls mysqld, mysqld gets the ulimits from root, not the mysql user. As Trent says, /proc//limits proves this. Including `ulimit -u XXX` in the init script works because it is run by root. The last limits.conf you post has the identical settings for root as for mysql -- the settings of the running mysql process come from root.