…here and from the Percona Software Repositories). Based on MySQL 5.5.23, including all the bug fixes ….5 series. All of Percona‘s software is open-source and free, all the details of the … the corruption and creating a new SYS_STATS table. Bug fixed #978036 (Laurynas Biveinis). Release notes for …
Comment: InnoDB's gap locks
April 13, 2012 By 14 Comments
… REPEATABLE READ transaction isolation level. mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `i` int(11) DEFAULT… READ) but you can modify it (READ COMMITED). I open a new transaction again on the session 2 without…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… of MySQL 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 definition, but we can see from strace: [pid 19876] open… 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: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Open_filesblogpercona.commanual open_files_limitblogpercona.commanual Open_streamsblogpercona.commanual Open_table_definitionsblogpercona.commanual Open_tablesblogpercona.commanual Opened_filesblogpercona.commanual Opened_table_definitionsblogpercona.commanual Opened…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… exists 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… open tables later – if you see this table in table cache again chances are it is being used. Note however if you’re starting MySQL…
Post: Opening Tables scalability
November 21, 2006 By 27 Comments
… MySQL on box with 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… Heikki 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…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
…Opening Tables‘ were waiting for the dict_sys->mutex, which is required for opening tables in Innodb. It would be better for the Table…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
… with the Sakila sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film… query consumed 0.18 seconds. Where did the time go? mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032….46 | 6 | 0.0001336667 | | Creating tmp table | 0.000548 | 0.32 | 1 | 0.0005480000 | | Opening tables | 0.000196 | 0.11 | 1 | 0.0001960000…
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_cache…
Post: table_cache negative scalability
November 16, 2009 By 23 Comments
…. 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… fixed in MySQL 5.1, lets see how MySQL 5.4 behaves. MySQL 5.4.2 MySQL 5.4.2 has higher default table_open_cache…
