[ERROR] Error in accept: Too many open files (MySQL)

 

I re-post this message here in case somebody else stumbles upon a similar issue. This is mail conversation between myself and a client.

 

Our MySQL issue should be solved now. I had done something faulty, call it naive, by setting the open_files_limit variable to an unrealistic high value (like 900000) but I didn't think for a second that there could be a max limit for this, so my value never stuck, it never was enabled. I found out this earlier today when I — again — saw this in the log:

131201 20:30:01 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/md_usermeta.frm' (errno: 24)
131201 20:30:01 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/md_users.frm' (errno: 24)
131201 20:30:11 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/phpbb_config.frm' (errno: 24)
131201 20:30:12 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/phpbb_config.frm' (errno: 24)
131201 20:30:12 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/ez_options.frm' (errno: 24)
131201 20:30:12 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/ez_options.frm' (errno: 24)
131201 20:30:12 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/ez_options.frm' (errno: 24)
131201 20:30:19 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/phpbb_config.frm' (errno: 24)
131201 20:30:20 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/wp_options.frm' (errno: 24)
131201 20:30:20 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/wp_options.frm' (errno: 24)
131201 20:30:20 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/wp_options.frm' (errno: 24)
131201 20:30:21 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/wp_options.frm' (errno: 24)
131201 20:30:21 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/wp_options.frm' (errno: 24)
131201 20:30:21 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/wp_options.frm' (errno: 24)
131201 20:30:21 [ERROR] /usr/sbin/mysqld: Can't open file: './[DELETION]/wp_options.frm' (errno: 24)


This raised a suspicion of my setting not being applied properly, I take a look at the relevant variables:

[root@srv51 mysql]# mysqladmin  variables |grep -i open
| have_openssl                                      | DISABLED                                                                                                               |
| innodb_open_files                                 | 300                                                                                                                    |
| open_files_limit                                  | 1024                                                                                                                   |
| table_open_cache                                  | 400

Yep, suspicion seems to be valid! So I confirmed that our open_files_limit was only at 1024 (an absurd number for production server with a lot of activity), by doing:
[root@srv51 mysql]# netstat -atnp|grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      17612/mysqld

And then:
[root@srv51 mysql]# cat /proc/17612/limits
Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             unlimited            unlimited            bytes
Max stack size            10485760             unlimited            bytes
Max core file size        0                    unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             63695                63695                processes
Max open files            1024                 4096                 files
Max locked memory         65536                65536                bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       63695                63695                signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us

Yeah, the value I had set never was enabled. Dumb k0nsl! So I re-edited the configuration file, this time setting it at a value of 90000. Re-did the checks after restarting MySQL and the value has now been changed to 90000.
So for now I keep monitoring everything, but that issue is resolved.
Faithfully,
k0nsl

Leave a Reply

Your email address will not be published. Required fields are marked *