From: Dan Nelson
Date: February 27 2009 9:15pm
Subject: Re: MySQL Closing/Opening tables
List-Archive: http://lists.mysql.com/mysql/216513
Message-Id: <20090227211525.GH45976@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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
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
	dnelson@stripped