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-...@yahoo.com> 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 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 <dnel...@allantgroup.com> > To: dbrb2002-...@yahoo.com > Cc: mysql@lists.mysql.com > Sent: Friday, February 27, 2009 1:15:25 PM > Subject: Re: MySQL Closing/Opening tables > > In the last episode (Feb 27), dbrb2002-...@yahoo.com 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 > dnel...@allantgroup.com > -- Eric Bergen eric.ber...@provenscaling.com http://www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
