Bug #67142 Optionally close MyISAM tables if "idle" for a specified period
Submitted: 9 Oct 2012 7:56 Modified: 9 Oct 2012 12:15
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S4 (Feature request)
Version:ALL OS:Any
Assigned to: CPU Architecture:Any
Tags: windmill

[9 Oct 2012 7:56] Simon Mudd
Description:
I have several servers which use a large number of MyISAM tables (as Innodb tables occupy much more space the MyISAM tables, even when compression is taken into account) and a recent crash of an old version (5.5.16) left 10,000 tables open, and thus requiring a "CHECK TABLE ...."

Nevertheless of these tables a fair number have not been accessed recently so it would have saved us a lot of time to check/repair these tables if there were an option to close tables which have not been used in a certain amount of time.

How to repeat:
Create one or more databases with say 10,000 tables. Open them (ensuring that the configuration of MySQL keeps all file descriptors open. now do a kill -9 <mysqld_pid> to kill the server. now notice that mysql notices that all these tables are broken and if configured that way need checking and repairing.

If the tables are large this process can be time-consuming as it's very disk intensive.

Suggested fix:
I'd like to suggest therefore that there is a new variables myisam_close_after_idle_seconds (default value 0 to not change existing behaviour, or default 1000 to do this after 1000 seconds) is added and that periodically tables which have not been opened after this period are (flushed to disk and) closed.

This will ensure that on an idle MyISAM server the tables will be flushed to disk and closed and so a server crash or mysqld crash will generate less damage that now.
[9 Oct 2012 12:15] Sinisa Milivojevic
I find your feature request potentially very useful and I am hence, verifying it .
[10 Oct 2012 10:09] Shane Bester
http://karlssonondatabases.blogspot.com/2012/10/too-many-or-too-big-open-myisam-tables.htm...