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
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 <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
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

Thread
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