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?

Noodles
  • 1,386
  • 3
  • 18
  • 29

2 Answers2

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.

Noodles
  • 1,386
  • 3
  • 18
  • 29
  • 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 '13 at 08: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.

dmourati
  • 25,540
  • 2
  • 42
  • 72
  • That might work fine, until the pam rpm package is updated next. – Noodles Dec 02 '13 at 03:02
  • True, edited my answer. – dmourati Dec 02 '13 at 03: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 02 '13 at 03:30
  • You may need to stop then start mysql, in case a restart re-uses the same process [?] – rogerdpack Sep 12 '14 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](http://stackoverflow.com/a/38155364/2067682) tells you how to do it on a systemd system. – Alastair Irvine Jan 24 '17 at 10:03