…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: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… 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… 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: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
… is safe to do even when temporary tables are open.) Then I created a temporary table on the master, inserted some rows into…_Log_File: mysql-bin.000007 …. omitted …….. Exec_Master_Log_Pos: 207 slave1 > show status like ‘%temp%’; +————————+——-+ | Variable_name | Value | +————————+——-+ | Slave_open_temp_tables | 1…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
… data permanently. The pmp-check-mysql-deleted-files plugin will alert when files have been deleted but remain open via a filehandle. Wrong filesystem…, a pile-up of connections all trying to open or close tables can indicate LOCK_open contention. Replication is delayed or stopped. Our…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… innodb-import-table-from-xtrabackup=1 innodb-io-capacity=1000 innodb-log-buffer-size=32M innodb-log-file-size=128M innodb-open-files=1000… table-definition-cache=16384 table-open-cache=128 thread-cache-size=32 thread-stack=256K tmp-table-size=64M transaction-isolation=READ-COMMITTED user=mysql…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
… | Opening tables | select sql_no_cache * from other_table limit 1 | +—–+——+———–+——+———+——+—————-+————————————————+ I tried some alternative approaches to trick MySQL into removing smaller files with DROP TABLE…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…mysql> SHOW PROFILE; +——————————+———–+ | Status | Duration | +——————————+———–+ | starting | 0.000054 | | checking permissions | 0.000004 | | checking permissions | 0.000004 | | init | 0.000008 | | Opening tables… uses temporary files in tmpdir…
Post: MySQL Crash Recovery
July 30, 2006 By 26 Comments
… mention if you have innodb_file_per_table=1 your recovery speed will depend on number of Innodb tables you have, as well… might run select 1 from _table_ limit 1 for each table or any other statement – it is table open which is important. There are other problems which you may experience related to MySQL…
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… or use innodb_file_per_table=0 for such tables (thankfully you can change this variable online in Innodb Plugin, MySQL 5.5 and…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
…transaction. If a transaction remains open for a very long time…INFORMATION_SCHEMA tables that make this simple. Just query the tables, … > /root/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist’ … –print” on the resulting tcpdump file (after inspecting the captured …
