MySQL can open a single table multiple times depending on how many
clients need to use it. This means that having a table_cache the same
as the total_tables will only work if your mysql server only has one
For more details read:
On Fri, Feb 27, 2009 at 2:53 PM, <dbrb2002-sql@stripped> wrote:
> Thanks Dan.. thats a valuable point.. and this actually happening with MyISAM tables
> But the question is; when I set the table_cache to higher than total tables.. then it
> should stop closing the table in first place..so that only un-opened tables will be opened
> and kept in cache.. it will avoid closing and re-opening.. but looks like it is not the
> Unless the table_cache is also used(unlikely) for temporary tables which are created
> by select queries..
> From: Dan Nelson <dnelson@stripped>
> To: dbrb2002-sql@stripped
> Cc: mysql@stripped
> Sent: Friday, February 27, 2009 1:15:25 PM
> Subject: Re: MySQL Closing/Opening tables
> In the last episode (Feb 27), dbrb2002-sql@stripped said:
>> Recently I noticed the server takes lot of time on and off when opening
>> and closing tables. And I tried to increase the table_cache more the the
>> total tables (file_limit is properly set); and the problem still continues
>> and lowering it also continues.. and tried to set in middle.. same
> MyISAM tables flush dirty index blocks at the end of every update; this can
> cause a long wait inside "closing tables". If you have just deleted a lot
> of rows or did some other update touching many rows, you might have to flush
> a lot of dirty blocks. Running "show status like 'Key_blocks_not_flushed'"
> during one of these periods should show the count starting out large,
> dropping rapidly, then leveling off when that table's blocks have been
> Fixes include:
> * Altering your troublesome tables and adding the DELAY_KEY_WRITE=1 option.
> This will force you to repair those tables after a mysql or OS crash,
> since the on-disk copies of the index will almost always be out of synch.
> * Switching to an engine with logging like InnoDB will allow mysql to write
> the changes to a transaction log immediately, then trickle out the actual
> key block updates over time. If you want to try out mysql 6.0, the maria
> engine is basically MyISAM with logging.
> Dan Nelson