Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Welcome to ServerFault! –  kmarsh Mar 30 '10 at 12:59
    
Are you checking ulimit in the same shell that launches mySQL? If not the answer may not be relevant. –  kmarsh Mar 30 '10 at 13:00
add comment

4 Answers

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

share|improve this answer
    
Ah, this is similar to serverfault.com/questions/73894/… I will give this a go; weird how it's pam limits for su and sudo when the process starts when the machines starts or when I do init.d/mysql start/restart –  Wizzard Dec 11 '09 at 7:51
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.