Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

Ubuntu: 12.04 LTS (Linux mysql02 3.2.0-40-generic #64-Ubuntu SMP Mon Mar 25 21:22:10 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux)

MySQL: Ubuntu distro 5.5.31

Apparmor: REMOVED!

Server has been running rock solid for over a year. Then this Monday MySQL began failing. An update has caused the problem and we cannot figure it out what it is. We have even tried to roll back to MySQL 5.5.30 but with no luck. We returned at 5.5.31.

MySQL error log entries:

130430  7:55:46 [ERROR] Error in accept: Too many open files
130430  7:55:46 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/fclvod.frm' (errno: 24)
130430  7:55:46 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/fcnote.frm' (errno: 24)
130430  7:55:47 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/ffcont.frm' (errno: 24)
130430  7:55:47 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/ffcontv.frm' (errno: 24)
130430  7:55:47 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/ffnote.frm' (errno: 24)
130430  7:55:47 [ERROR] /usr/sbin/mysqld: Can't open file: './eci_elite_test/frcfcl.frm' (errno: 24)

It appears we are running into ulimit problem. We have removed APPARMOR completely. We have increased the /etc/security/limits.conf and still no luck:

# Out of desperation....
* soft  nofile  49152
* hard  nofile  65536

# No effect!?!!?
#mysql  soft  nofile  49152
#mysql  hard  nofile  65536

And to show the limits.conf is working:

root@mysql02:/etc/security# ulimit -Sa | grep "open files"
open files                      (-n) 49152

root@mysql02:/etc/security# ulimit -Ha | grep "open files"
open files                      (-n) 65536

And here are the important entries in my.cnf

[mysqld_safe]
open_files_limit = 16384

[mysqld]
open_files_limit = 16384

However:

root@mysql02:/etc/mysql# mysqladmin -u root -pThePassword variables| grep open_files_limit
open_files_limit                                  | 1024

We are totally stumped and down. Any assistance would be greatly appreciated.

share|improve this question
    
What error messages are there in the logs BEFORE the one about Too Many Open Files? You have restarted mysqld since changing open_files_limit, right? –  Bert Apr 30 '13 at 12:31
    
yes, we have restarted MySQL every time we make changes. We have one table that is being reported as missing (and it is for some reason): 30430 8:36:39 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './oti_lw_prod/apinvoice_charges.ibd'! –  Van Apr 30 '13 at 12:45
    
FYI, we moved our users to our other master (duel master setup) sever (01) and it is now exhibiting the exact same symptoms. It (01) had the same exact configuration as this failing server (02) and is our fail over master should this one (02) die. Well, so much for that plan. We are pretty sure this is an OS problem. –  Van Apr 30 '13 at 13:00

1 Answer 1

up vote 4 down vote accepted

OS: Ubuntu (Debian) deployments

MySQL Server Option: open-files-limit

It seems that the Debian upstart doesn't use the parameters defined in /etc/security/limits.conf, so when you 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 in /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

References:

share|improve this answer
    
Disappointing this isn't documented clearly somewhere. :( We just happened upon David's post on serverfault. –  Van Apr 30 '13 at 14:47
    
And this isn't a bug, according to this: bugs.launchpad.net/mysql-server/+bug/938669 –  Van Apr 30 '13 at 16:32

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.