Re: MySQL Server crashes - spawning too many threads.

[Erin ONeill]

Ok, I went down this dark linux rabbit hole to find a resolution to this problem (which is why I haven't been on this board a whole lot lately!).

As I've stated in a number of posts, our COLO won't give us any access to our MySQL server except thru phpMyAdmin. But I can ask them to run a command and then they'd send me the results (sort of like some bizare tv game). Naturally, I had to spell it all out in great detail or I wouldn't get the answer to the correct question! :)

At work they gave me a box to install RH fedora 3 core on it (that's what my MySQL server is on). I learned how to raise the file descriptors on linux. It is different than Solaris and the documentation on how to do it was found in some online Oracle install guides. My file descriptors were raised for the whole system but not for any users. So while root would see this high number of allowed open files, mysql was still limited to 1024. Why mysql would allow me to set the table cache to 1240 is still a mystery to me. I went to LinuxWorld and tried to find a kernel guru there. It was futile. I found one guy and he just couldn't tell me how to set a user's file limit in fedora (he was working the fedora booth too). But he did tell me that 1240 is some magical number he's seen before (what does that mean??)

Anyways here are the steps to increasing your file descriptors: (remember to keep the size of your physical RAM when choosing these numbers. They could probably go higher if we had 8 GB of RAM). This is for Fedora Core 3.

You need to do the following:
1.) Edit this file for documentation:
/etc/sysctl.conf
ADD:
fs.file-max = 406720 <--- this number would be smaller for less than 2 GB of ram

This does the same as this:
echo 406720 > /proc/sys/fs/file-max

Personally, I always try to keep my kernel parameters in conf files so others can maintain servers in my absence. It's worked for a long time. If I win the lottery I don't want phone calls about the kernel while I'm running around Paris! :)

2.) Edit this file --> /etc/security/limits.conf. This file sets the user limits. Here you can name the user. So if you want apache to have different limits than mysql you could.

ADD:
mysql soft nofile 202860
mysql hard nofile 405720
mysql soft nproc 33792
mysql hard nproc 67584

# remember NOT to set your /etc/security/limits.conf file hard limit to your fs.file-max number or above it. This is the real user limit - the system has overhead and needs some too. Some people set the soft limit to = the hard limit but I don't think that's necessary nor really desireable on a production machine. The operating system will allocate more file handles in emergencies up to the hard limit.

3.) I also set the pam limits. You'll have to restart the sshd daemon once you make this change:
edit this file: /etc/pam.d/login

# between the line "must be first session" rule and the rule that must be the last one
session required /lib/security/pam_limits.so

then restart the sshd daemon:
/etc/init.d/sshd restart

REBOOT the machine (yea, what a drag, I couldn't figure out how else to get the mysqld daemon to run with the new limits.)

Hope this helps someone with performance issues similar to mine!

erin