…with 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 …
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 | Name… 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…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… first opening of the table or manually running ANALYZE TABLE on it? The 2 instances below are documented from the MySQL and InnoDB plugin’s manual: Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES…
Post: Recovering CREATE TABLE statement from .frm file
December 17, 2008 By 4 Comments
…MySQL creative error message: mysql> show tables; +—————-+ | Tables_in_test | +—————-+ | queue | | test_innodb | +—————-+ 2 rows in set (0.00 sec) mysql> show create table…
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… above strace shows. In MySQL 5.5 you can find in sql/datadict.cc a function named dd_frm_type(). In MySQL 5… 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
…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…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
….18 seconds. Where did the time go? mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032… | 0.0001336667 | | Creating tmp table | 0.000548 | 0.32 | 1 | 0.0005480000 | | Opening tables | 0.000196 | 0.11 | 1…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
…Table database_name/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL…error only happened when the table was opened and not on every table access. So, it … a way to compare the output of SHOW CREATE TABLE to what Innodb thinks. What …
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
…mysql> show create table am; +——-+————————————————————————————————————————————————————————+ | Table | Create Table…
