… MySQL 5.1 – in this version table_cache was significantly redone and split in table_open_cache and table_definition_cache and I assumed the behavior may… table_cache (or table_open_cache_ values indeed can cause significant performance problems. Interesting enough Innodb has a very similar task of managing its own cache…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… table_cache this command is rather helpful to understand if there is activity on the given table. Just run “FLUSH TABLES mytable” and examine open tables later – if you see this table in table cache again chances are it…
Post: More on table_cache
November 26, 2009 By 7 Comments
…’re better off with small table cache. What I have not checked though is how does table_cache (or table_open_cache in newer version) size affects the hit path. I started with the same test as last time – created 100000 tables…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… keep open file descriptor when table is open – open table is purely logical state and appropriate .ibd file may be open or closed. Furthermore besides MySQL table_cache Innodb maintains its own (called data dictionary) which keeps all tables ever accessed since table start…
Post: Opening Tables scalability
November 21, 2006 By 27 Comments
… and he confirmed MySQL has global table cache mutex (LOCK_open) which is held for all open table operation so table opens are serialized. Do not think… is fixed it is especially important to keep your table_cache large enough so table opens will be rare (I use 1/sec as… worry about) and also worry about possible limited performance while table cache is being warmed up in addition to all other caches 
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… with 100 tables and the table_definition_cache set to 1000. My understanding of this is that MySQL won’t revert to opening the FRM files to read the table… in the server: DROP TABLE RENAME TABLE DROP VIEW open table filling INFORMATION_SCHEMA tables (I think it is actually the TABLES table, but didn’t look…
Comment: table_cache negative scalability
April 14, 2010 By 23 Comments
… MyISAM and InnoDB table cache? 3) Would converting the MyISAM tables to InnoDB fix or help the os ulimit max open tables issue? Thanks for… table_cache = 20000 open_files_limit = 40000 max_binlog_size = 500M max_allowed_packet = 20M tmp_table_size = 100M max_heap_table_size = 100M query_cache…
Comment: table_cache negative scalability
April 14, 2010 By 23 Comments
… thread_cache_size = 100 max_connections = 400 table_cache = 20000 open_files_limit = 40000 max_binlog_size = 500M max_allowed_packet = 20M tmp_table_size = 100M max_heap_table_size = 100M query_cache…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
… | Query | 7 | NULL | drop table large_table | | 329 | root | localhost | test | Query | 7 | Opening tables | select sql_no_cache * from other_table limit 1 | +—–+——+———–+——+———+——+—————-+————————————————+ I tried… | 7 | rename result table | ALTER TABLE large_table ENGINE=MyISAM | | 679 | root | localhost | test | Query | 6 | Opening tables | select * from other_table limit 1 | The only…
Comment: Beware of MyISAM Key Cache mutex contention
August 14, 2008 By 10 Comments
… from the MyISAM tables with one thread and two threads. table_cache=495, Open_tables=120, Opened_tables=133. 1. Two threads and one table, the average response time is about 143ms Thread1: access table t1 Thread2: access table…
