MySQL file limit. Error Out of resources

MySQLresources

I was benchmarking my database the other day using mysqlslap, and I ran into a "Out of resources when opening file '/tmp/somefile' (Errcode: 24)" error.

I with a few click on google, I found a solution to a problem, which is to modify:
"/etc/security/limits.conf" file, by adding:
"mysql soft nofile 24000" and
"mysql hard nofile 32000" lines.
(REF.: http://www.smilecouple.org/2011/03/01/fix-out-of-resource-problem-with-mysqlhttp://www.smilecouple.org/2011/03/01/fix-out-of-resource-problem-with-mysql)

My question:
What are the drawbacks of this solution ? I assume it is something to do with a security, and I got a feeling where it is, but maybe someone has some real-life experience on this ?

Best Answer

The primary drawback is to memory consumption if you are on a low memory machine and have a huge number of file handles open. Each of those file handles consumes a small amount of memory.

Nowadays, I wouldn't really worry about it. I'd just monitor it and adjust accordingly as needed.

If you're continuing to run out of file handles, you have something else going on that can't likely be resolved with the information presented so far.

Run lsof -u mysql | wc -l where 'mysql' is your mysqld system user.

Related Solutions

Mysql – the.cnf parameters to enable binary logging theSQL 4.1.20

In general on Linux systems, an Errno 13 means Permissions Denied or Access Denied.

What's the output of

# ls -ld /var/log/mysql
# ls -l /var/log/mysql

Does this match with your MySQL user?

If not, suggested solution (providing mysql is your mysql user and mysql is your group):

chown -R mysql:mysql /var/log/mysql

MySQL – Out of Resources for mysqldump Solution

According to here - "OS error code 24: Too many open files" which lines up with the more general error 23 "Out of resources".

So it seems as though you are running out of file handles. This is usually server-end setting/problem, either in MySQL, or in the OS itself.

Perhaps check/adjust the --open-files-limit setting in MySQL itself and see if that helps.

Also, perhaps try running the dump, while no one else is using the DB, with the --single-transaction setting instead of --Lock-File, as several people suggest this will work one table at a time instead of opening them all at once (therefore using less file handles).

Beyond that you'll probably have to find a root cause as to why this particular server is running out of resources. Which would probably involve troubleshooting by disabling as many services/processes as possible and see if the dump goes through. Then figure out from there who the culprit is that's eating too many resources and perhaps not freeing them correctly.

Best Answer

Related Solutions

Mysql – the.cnf parameters to enable binary logging theSQL 4.1.20

MySQL – Out of Resources for mysqldump Solution

Related Topic