setting ulimit and ubuntu 8.04

Question

We have two ubuntu 8.04 servers. With the database server I set the table_cache to 1000 however when I restart mysql the status only shows 257 and the open files limit says 1024

I adjusted ulimit by doing ulimit -n 8192 and then restarting mysql; this seemed to do the tick however after a few hours I did ulimit -n and saw it had returned back to 1024

Bit of a worry.

I edited the /etc/security/limits.conf and added

mysql soft nofile 8192

mysql hard nofile 8192

then rebooted, no change. I then edited and change mysql to * rebooted, no change I then edited and changed it to one line

* - nofile 8192

and rebooted, no change.

cat /proc/sys/fs/file-max gives me 768730

sysctl fs.file-max gives me fs.file-max = 768730

I am at a bit of a loss to how I can set and keep the ulimit value set so I can increase the table cache properly on mysql.

Answer 1

[edit: explained details for clarity]

I suspect you checked the ulimit for the different user than mysql ;)

You don't have to reboot after changing limits.conf. You have to turn on use of this file in the corresponding PAM service in /etc/pam.d/.

Do grep pam_limits /etc/pam.d/* to have a clue in which situation limits.conf will be used.

For example, change in limits.conf for can be visible in the shell invoked as sudo -u user bash but doesn't when run as sudo su - user - that's because on Ubuntu default setting goes like this:

$ grep limits /etc/pam.d/*|grep su
/etc/pam.d/su:# session required pam_limits.so
/etc/pam.d/sudo:session required pam_limits.so

So if you checked limits using sudo su - mysql then there was a mess -- su didn't turn the limits on. You can check which pam service is being run by watching /var/log/auth.log.

For all possible types of invocation of your mysql it should be safe to modify pam.d/other or just pam.d/common-session.

Answer 2

A more straightforward way (and kinda of a hack) is just add the ulimit -n 8192 command inside the init script for mysql /etc/init.d/mysql. The control is valid for the shell and the child processes/shells that it opens.

EDIT: the su and sudo 'weirdness' is because the limits.conf file is related to PAM limits that, if I am not mistaken, only applies to login shells. Also, there is some info on start-stop-daemon being unable to use the pam limits.

Answer 3

If you are unsure about current limits MySQL has, check out the current process id for mysqld with the method of your choice (top, ps aux, pgrep, whatever). Then just type

sudo cat /proc/mysql_process_id_you_found/limits

If it doesn't tell you 8192 for the open files, you can always hack around the init script as someone suggested.

Answer 4

Did you add session required pam_limits.so to /etc/pam.d/common-session?