… 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: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… realize 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… TABLES on ALL tables one by one you can run SHOW OPEN TABLES and flush only open tables, when run it again and see how many tables are open…
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…
Comment: MySQL Server Memory Usage
September 1, 2006 By 18 Comments
…_show_new_master’, ’0′ ‘Com_show_open_tables‘, ’0′ ‘Com_show_privileges’, ’0′ ‘Com_show_processlist’, ’20′ ‘Com_show_slave_hosts’, ’61′ ‘Com_show_slave_status’, ’13834′ ‘Com_show_status’, ’194766′ ‘Com_show_storage…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… LOCK TABLES was used. In addition to Innodb specific information, there is generic statement information which is visible in SHOW PROCESSLIST showed in SHOW… row lookup replacing btree search with hash search. This section shows hash table size, number of used cells and number of buffers… queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 10099, id 88021936, state…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
Recently I looked at table_cache sizing which showed larger table cache does not always provides the best performance. So I … 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: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… the updates aside from the first opening of the table or manually running ANALYZE TABLE on it? The 2 instances below …SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS) When 1 / 16th of the table…
Post: Recovering CREATE TABLE statement from .frm file
December 17, 2008 By 4 Comments
…open table test/test_innodb from the internal data dictionary of InnoDB though the .frm file for the table…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
…’t revert to opening the FRM files to read the table definition, but we can see from strace: [pid 19876] open(“./db/t1… API is called. But back to what the above strace shows. In MySQL 5.5 you can find in sql/datadict… 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: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… were over a hundred user queries in SHOW PROCESSLIST that were stuck in the ‘Opening Tables‘ state, uh-oh. I dug through the… table statistics being regenerated, and the SHOW PROCESSLIST connections stuck in ‘Opening Tables‘ were waiting for the dict_sys->mutex, which is required for opening tables…
