Ubuntu – setting ulimit and ubuntu 8.04

filesMySQLUbuntuulimit

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.

Best Answer

[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.

Related Topic