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!
Hi James – this is on MySQL 5.1. There is no connection pool in this case, because it’s a backup server. There are hardly any connections, and the only activity besides backups is replication itself.
What does SELECT VERSION() say? We’re interested in cases where large amounts of RAM seem to remain tied up and have been gradually fixing them. If yours is a very recent version, a bug report would be a good thing so it can be investigated and fixed. If it’s not recent, you might be fortunate and discover that we’ve already fixed it if you upgrade. Or not, no guarantee.
If FLUSH TABLES fixes a problem, that would be a good sign that we’d probably be interested.
A workaround that can help if a connection pool is involved is having the pool configured to use a timeout and refresh connections after a while. That frees up any RAM that was lingering in the per-connection objects. Not directly related to the table_open_cache, just another area where we’ve been working on things.
Note also that in 5.6, table_open_cache is one of the variables that is used to decide the default sizes of the performance schema so it’s not good to make it far larger than required. Dimitri explains more about the new table_open_cache related variables in 5.6 at http://dimitrik.free.fr/blog/archives/2012/09/mysql-performance-table-open-cache-in-56.html .
Views are my own, for an official view, contact a PR person.
James Day, MySQL Senior Principal Support Engineer, Oracle