Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Out of Resources? Check table_open_cache
+3 Vote Up -0 Vote Down

We have a backup server that, from time to time, gets errors when doing mysqldump backups (we do physical backups and logical backups, but the physical backups work fine). The errors look like this:
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.
But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.
When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).
Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.
I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.