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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
