And some information from the perspective of the MySQL user. I stopped mysql and hacked /usr/local/etc/rc.d/mysql-server to print out these variables, so this should represent the MySQL environment. Note that the number 184320 is consistent with the above.

So, can I see what the ulimit value is for the MySQL daemon, and can I change the ulimit value for the daemon without stopping the daemon? I know I can set ulimit in the startup script or the shell environment, but that would require that I interrupt the database.
–
Stefan LasiewskiMar 4 '13 at 20:31

1

Look in /proc, under the subdir with the PID of your mysql service. You can cat limits to see what mysql is running with. You can also change them on the fly (with newer kernels): echo -n "Max open files=soft_value:hard_value" > /proc/$PID/limits (as root of course)
–
lornixAug 17 '13 at 13:38

1

@lornix: this is FreeBSD. I've never used BSD myself, but I'm not sure if FreeBSD actually supports /proc/*/limits.
–
Martin von WittichSep 16 '13 at 22:37

1

/proc isn't mounted by default on FreeBSD, but do it yourself with sudo mount -t procfs proc /proc, see procfs(5) for more info. Once you have /proc mounted, look at /proc/$PID/rlimit file
–
zygisSep 17 '13 at 18:13

2 Answers
2

Check /etc/login.conf and figure out which login class your mysql user assigned to. It's probably default or daemon. If you want to alter the limits for your user, create a new class, assign your user to that class, change the limts for that class as you like and then run "cap_mkdb /etc/login.conf"

On some OS the limits are set to avoid security problem to regular user, you should consider reading whant in the man limits.conf
This file defines the limits per process like max number of thread or max number of open file.
The limitation use face could come from there.
/etc/security/limits.conf