Changing open-files-limit in mysql 5.5
I'm having an issue with mysql 5.5 running on Ubuntu 12.04 with the open-files-limit parameter.
I recently noticed some problems due to the 1024 limit, and actually the main system limit was set to 1024, so I modified /etc/security/limits.conf with the following:
* soft nofile 32000
* hard nofile 32000
root soft nofile 32000
root hard nofile 32000
After that I check the ulimit value for root and even for mysql user, both returned the new value: 32000, so I assume the change has already been done.
I also changed the value at the my.cnf file, setting open-files-limit to 24000, like this:
open-files-limit = 24000
Now comes the odd part, when I restart the mysql service and check the open_files_limit variable, it returns that it's still set to 1024, so I'm having the same problems that before (obviously), I tried to use open-files-limit instead open_files_limit in the my.cnf config file, same result, BUT if I override the service command to start the service and start only using mysqld (no additional parameters), the service starts and when I check the parameter it returns 32000... I don't know where it's taking that value from, as it's not set at my.cnf and it's not being given through command line, at least, not for myself.
Any ideas about why it's not working the change and how to solve it the normal way (launching it through service...)?
I finally found the problem, it seems upstart doesn't use the parameters defined at /etc/security/limits.conf, so when I launch mysql through the service command (and so, under upstart), it overrides those defined limits and uses the default 1024.
The solution is to modify the mysql.conf file that defines the upstart service, it is located at /etc/init/mysql.conf and add the following lines before the pre-start block:
# NB: Upstart scripts do not respect
# /etc/security/limits.conf, so the open-file limits
# settings need to be applied here.
limit nofile 32000 32000
limit nproc 32000 32000
The first value defines the soft limit, the other, the hard limit, once added those lines, the service works as expected applying the values defined at the my.conf file.
This limitation should apply for every upstart service that it is defined at /etc/init, so if any service has the same problem with the open files limit, this solution should work either.