Thanks Dan.. thats a valuable point.. and this actually happening with MyISAM
tables only..
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 case..
Unless the table_cache is also used(unlikely) for temporary tables which are
created by select queries..
________________________________
From: Dan Nelson <dnel...@allantgroup.com>
To: dbrb2002-...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Friday, February 27, 2009 1:15:25 PM
Subject: Re: MySQL Closing/Opening tables
In the last episode (Feb 27), dbrb2002-...@yahoo.com 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
flushed.
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
dnel...@allantgroup.com
