… the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can… on a DDL statement if an XtraDB internal SYS_STATS table was corrupted or overwritten. This is now fixed by detecting the corruption and creating a new SYS_STATS table. Bug fixed #978036 (Laurynas Biveinis). Release notes for Percona Server…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… to complete locking tables. The query state says “Waiting for table flush” which indicates that FTWRL hasn’t closed a open table yet, hence it must wait until all other threads have closed the table in question. So, this…
Comment: InnoDB's gap locks
April 13, 2012 By 15 Comments
… transaction isolation level. mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `i` int(11) DEFAULT NULL, … READ) but you can modify it (READ COMMITED). I open a new transaction again on the session 2 without…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… is SHOW OPEN TABLES – it allows you to examine what tables do you have open right now: mysql> show open tables from test; +———-+——-+——–+————-+ | Database | Table | In_use… SHOW OPEN TABLES and flush only open tables, when run it again and see how many tables are open and in use and if FLUSH TABLES WITH…
Post: Opening Tables scalability
November 21, 2006 By 27 Comments
… 50.000 of Innodb tables and again it took couple of hours to reach decent performance because of “Opening Tables” stage was taking long. Part of the problem is Innodb is updating stats on each table open which is possibly… 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…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… keep open while working in innodb_file_per_table mode. Unlike MyISAM Innodb does not have to 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…
Post: table_cache negative scalability
November 16, 2009 By 23 Comments
… benchmark. Why ? Because for MyISAM tables table header has to be modified each time the table is opened. In this case the performance was…. So if you tune table cache is “normal” case and is able to bring down your miss rate (opened_tables) to 10/sec or less by using large table_cache you should do so. However if you have so many tables you…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
… | 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: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… 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 definition, but we can see from strace: [pid 19876] open(“./db/t1… 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: More on table_cache
November 26, 2009 By 7 Comments
… better off with small table cache. What I have not checked though is how does table_cache (or table_open_cache in newer version…/sec – note this is basically best case scenario for opening tables as MyISAM table headers which are being modified were fully cached in… large table cache though this should only affect someone who runs really large amount of simple queries – this is when opening table can…
