From: Eric Bergen
Date: February 27 2009 11:25pm
Subject: Re: MySQL Closing/Opening tables
List-Archive: http://lists.mysql.com/mysql/216521
Message-Id: <11b1bd990902271525r29cdad5r8f41ebe98ea75828@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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

For more details read:
http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

On Fri, Feb 27, 2009 at 2:53 PM,  <dbrb2002-sql@stripped> wrote:
> Thanks Dan.. thats a valuable point.. and this actually happening with My=
ISAM tables only..
>
> But the question is; when I set the table_cache to higher than total tabl=
es.. 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. =A0And I tried to increase the table_cache more the =
the
>> total tables (file_limit is properly set); and the problem still continu=
es
>> and lowering it also continues.. =A0and tried to set in middle.. =A0same
>
> MyISAM tables flush dirty index blocks at the end of every update; this c=
an
> cause a long wait inside "closing tables". =A0If you have just deleted a =
lot
> of rows or did some other update touching many rows, you might have to fl=
ush
> a lot of dirty blocks. =A0Running "show status like 'Key_blocks_not_flush=
ed'"
> 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=3D1 opt=
ion.
> =A0This will force you to repair those tables after a mysql or OS crash,
> =A0since the on-disk copies of the index will almost always be out of syn=
ch.
>
> * Switching to an engine with logging like InnoDB will allow mysql to wri=
te
> =A0the changes to a transaction log immediately, then trickle out the act=
ual
> =A0key block updates over time. =A0If you want to try out mysql 6.0, the =
maria
> =A0engine is basically MyISAM with logging.
>
> --
> =A0 =A0Dan Nelson
> =A0 =A0dnelson@stripped
>



--=20
Eric Bergen
eric.bergen@stripped
http://www.provenscaling.com