… 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: Opening Tables scalability
November 21, 2006 By 27 Comments
… because of “Opening Tables” stage was taking long. Part of the problem is Innodb is updating stats on each table open which is possibly… 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…
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: 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…
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…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
…, but require at least 16K page in Innodb), keeping all tables open in Innodb dictionary and number of other challenges in IO… significant because table_cache can’t be made large enough and so a lot of table reopens needs to happen which requires table header… of tables dramatically – merging say 1000 of users to the same table as VIEWs are significantly less expensive and cheap to “open“. How…
Comment: Opening Tables scalability
December 11, 2011 By 27 Comments
…? We run a WP multisite with about 200k tables and as tables in open_table cache increase the execution time of the query increase and… is always 400 querys running and most of them in opening/closing state. If I restart the process it works like…
Post: Performance problem with Innodb and DROP TABLE
February 3, 2011 By 23 Comments
… discovered DROP TABLE can take a lot of time and when it happens a lot of other threads stall in “Opening Tables” State. Also contrary to my initial suspect benchmarking create/drop table was CPU bound rather than… large buffer pool. Worst of all this is done while table_cache lock is being held so no other queries can start…
