List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:February 27 2009 11:25pm
Subject:Re: MySQL Closing/Opening tables
View as plain text  
MySQL can open a single table multiple times depending on how many
clients need to use it. This means that having a table_cache the same
as the total_tables will only work if your mysql server only has one

For more details read:

On Fri, Feb 27, 2009 at 2:53 PM,  <dbrb2002-sql@stripped> wrote:
> 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 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

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