3

I have a problem where mysqld runs out of threads and throws errors when under heavy load. I have traced this back to a problem where the pid seems to be limited a soft limit of 1024 processes (have removed other limits for readability):

[root@db1 limits.d]# cat /proc/`pidof mysqld`/limits
Limit                     Soft Limit           Hard Limit           Units
Max processes             1024                 191967               processes

I have a file in /etc/security/limits.d/ called 99-mysql.conf which contains:

mysql soft nofile 20000
mysql hard nofile 20000
mysql soft nproc 20000
mysql hard nproc 20000

As far as I can see the problem comes from MySQL being started by a user. I haven't tested this, but I assume that if MySQL is started by the system at boot time, then it should have the correct limits.

Is there a way to increase the soft limit for nproc for this pid and for future mysqld processes?

Improve this question

2 Answers 2

Reset to default
2

It seems you can set the limits of a running pid by using:

echo -n "Max processes=20000:191967" > /proc/`pidof mysqld`/limits

This solves the immediate problem, but if I restart MySQL again I would need to reset the limits (unless I add this to the startup script), which seems like a pain.

Improve this answer
1
  • I this can be done, only on new kernel version, because i saw this file on Redhat 5 is readonly /proc/$$/limits, on redhat 6 you can use prlimit or your trick
    – c4f4t0r
    Dec 24, 2013 at 8:55
2

Look for and remove any entries in:

/etc/security/limits.d/90-nproc.conf

Also, add in your limits.d file:

root soft nofile 20000
root hard nofile 20000
root soft nproc 20000
root hard nproc 20000

That will cover restarts from the root prompt.

Improve this answer
5
  • That might work fine, until the pam rpm package is updated next.
    – Noodles
    Dec 2, 2013 at 3:02
  • True, edited my answer.
    – dmourati
    Dec 2, 2013 at 3:07
  • Interestingly, I removed the * soft nproc 1024 line from /etc/security/limits.d/90-nproc.conf, logged out, logged back in and restarted mysql. This didn't increase the process limits for mysql. It may require a reboot.
    – Noodles
    Dec 2, 2013 at 3:30
  • You may need to stop then start mysql, in case a restart re-uses the same process [?]
    – rogerdpack
    Sep 12, 2014 at 17:07
  • You're better off creating a file in /etc/security/limits.d/ that starts with 99_local (to show that you created it) and override anything you want there. This answer tells you how to do it on a systemd system.
    – Alastair Irvine
    Jan 24, 2017 at 10:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .