List:General Discussion« Previous MessageNext Message »
From:dbrb2002-sql Date:February 27 2009 10:53pm
Subject:Re: MySQL Closing/Opening tables
View as plain text  
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 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

MySQL Closing/Opening tablesdbrb2002-sql27 Feb
  • Re: MySQL Closing/Opening tablesBaron Schwartz27 Feb
    • Re: MySQL Closing/Opening tablesWm Mussatto27 Feb
    • Re: MySQL Closing/Opening tablesdbrb2002-sql27 Feb
      • Re: MySQL Closing/Opening tablesWm Mussatto27 Feb
      • Re: MySQL Closing/Opening tablesBaron Schwartz28 Feb
  • Re: MySQL Closing/Opening tablesDan Nelson27 Feb
    • Re: MySQL Closing/Opening tablesdbrb2002-sql27 Feb
      • Re: MySQL Closing/Opening tablesEric Bergen28 Feb
      • Re: MySQL Closing/Opening tablesDan Nelson28 Feb