MySQL crash - Too many open files

Submitted by bigwombat on Thu, 08/30/2012 - 01:50

Hi VM

I'm am getting this occasionally on our VM server, generally during a MySQL import from the CLI. Unfortunately, as the server is in production it then causes all the sites to go off-line and mysql server must be restarted.

Here's the error when performing the MySQL import: ERROR 23 (HY000) at line 2008: Out of resources when opening file './ae/' (Errcode: 24)

And the error in /var/log/mysql.log 120806 17:46:18 [Note] Event Scheduler: Loaded 0 events 120806 17:46:18 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.61' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 120830 16:20:26 [ERROR] Error in accept: Too many open files

Do you know a simple fix? I'm just concerned that this may happen when no one is around to restart mysql and cause the service to be offline for longer than 2 mins.

Thanks!

Status: 
Active

Comments

Submitted by bigwombat on Thu, 08/30/2012 - 02:10

Just an update, it appears that MySQL is keeping all tables open - not releasing them at all so I'm seeing about 904 files open at anyone time.

Submitted by bigwombat on Thu, 08/30/2012 - 02:45

Another update, it appears to be related to this setting in /etc/my.cnf

Which was set to: table_cache = 512

After changing this to: table_cache = 256

Saw a significant drop in the number of open files..

I believe this is set by Virtualmin at install time, so possibly this may be something you guys could provide feedback on?

Submitted by andreychek on Thu, 08/30/2012 - 08:27

During installation, Virtualmin does indeed assist in choosing a my.cnf file, though Virtualmin doesn't actually create it.

The choices are either using the default my.cnf provided by your distro, or using one of the my.cnf files provided by MySQL, and included as examples with the MySQL package. You can see all the examples in the directory "/usr/share/doc/mysql-server-5.1.61/".

I'm wondering though if maybe there's a large number of databases/tables on your system, and perhaps a particular my.cnf setting is pushing things "over the edge" as far as how many open files you have?

To see what the current open files limit is, you can log into MySQL and run this command:

SHOW VARIABLES LIKE 'open%'

You can try changing that by editing /etc/my.cnf, and adding a line like this to the [mysqld] section:

SET open_files_limit=16000

And then restart MySQL:

/etc/init.d/mysql restart

After that, does the open files limit show up as being 16000?

Submitted by bigwombat on Thu, 08/30/2012 - 15:47

Thanks will try it and report back.

Do you think it could be related to the MySQL memory size step which is part of the first-time Virtualmin setup?

Submitted by andreychek on Thu, 08/30/2012 - 15:53

Well, while it's possible that some parameters within that config could affect things, I think it's affected more by how many tables and databases are being used by your applications.

Or, put another way -- I think I'd spend my time working on increasing the file limit, rather than trying to tune parameters like table_cache. You really should be able to keep that at a higher value without needing to worry about MySQL crashing due to an open files problem.

Submitted by bigwombat on Thu, 08/30/2012 - 16:10

OK.

This server was recently upgraded from CentOS 5 which was not experiencing any issues at all. So possibly CentOS 6 requires more tuning out of the box than CentOS 5? Not all websites have been imported yet, only 5 of the about 40. The issue occurred at the outset of the import - so a much lower threshold than CentOS 5.

Submitted by bigwombat on Thu, 08/30/2012 - 19:08

Thanks for your feedback.

After comparing the /etc/my.cnf with those available in /usr/share/doc/mysql-server-5.1.61

It appears that Virtualmin is doing 2 things incorrectly: 1. It is setting the older, deprecated variable name 'table_cache' whereas it should be using the new name 'table_open_cache' 2. The value for table_cache is being set incorrectly, as it is using the value as defined for the 'my-huge.cnf' whereas all the other values are set as defined by the 'my-large.cnf' defaults. /usr/share/doc/mysql-server-5.1.61/my-huge.cnf:31:table_open_cache = 512 /usr/share/doc/mysql-server-5.1.61/my-large.cnf:31:table_open_cache = 256

Changing the value for 'table_cache' back to 256 in line with the my-large.cnf configuration appears to have solved the problem.

Here's a copy of the Virtualmin generated my.cnf. Note that the table_cache has been set back to 256. All other values appear to correspond to the my-large.cnf configuration.

/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

symbolic-links=0
innodb_file_per_table = 1
thread_concurrency = 8
query_cache_size = 16M
thread_cache_size = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 4M
read_buffer_size = 1M
sort_buffer_size = 1M
table_cache = 256
max_allowed_packet = 128M
key_buffer = 256M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Submitted by andreychek on Fri, 09/07/2012 - 22:48

You're right, it does appear that they've renamed it to table_open_cache in MySQL 5.1.2.

However, I haven't been able to reproduce the problem with it incorrectly setting "table_cache".

When I run the wizard, and set it to use the "Large System (1GB)", it sets table_cache to 256.

You're right though, these docs here show that the table_cache setting is indeed affected by the open files limit:

http://dev.mysql.com/doc/refman/5.1/en/table-cache.html

I'd still recommend increasing your open files limit if you haven't tried that -- there's no need to risk running over that.