Emergency

Search Results for: lock drop table

Innodb Table Locks

Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and rightfully so as Innodb table locking is a bit more complicated …


Why ALTER TABLE shows as two transactions in SHOW ENGINE INNODB STATUS

When executing an ALTER TABLE, InnoDB (and XtraDB) will create two InnoDB transactions: One transaction is created when the table being ALTERed is locked by the server. This will show up as something like “TABLE …


DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then …


How to recover a single InnoDB table from a Full Backup

Sometimes we need to restore only some tables from a full backup maybe because your data loss affect a small number of your tables. In this particular scenario is faster to recover single tables than …


MySQL opening .frm even when table is in table definition cache

or… “the case of Stewart recognizing parameters to the read() system call in strace output”. Last week, a colleague asked a question: I have an instance of MySQL with 100 tables and the table_definition_cache set …


Drop table performance

There have been recent discussions about DROP TABLE performance in InnoDB. (You can refer to Peter’s post https://www.percona.com/blog/2011/02/03/performance-problem-with-innodb-and-drop-table/ and these bug reports: http://bugs.mysql.com/bug.php?id=51325 and http://bugs.mysql.com/bug.php?id=56332.) It may not sound that serious, but if your workload …


Performance problem with Innodb and DROP TABLE

I’ve been working with an application which does a lot of CREATE and DROP table for Innodb tables and we’ve discovered DROP TABLE can take a lot of time and when it happens a lot …


Thinking about running OPTIMIZE on your Innodb Table ? Stop!

Innodb/XtraDB tables do benefit from being reorganized often. You can get data physically laid out in primary key order as well as get better feel for primary key and index pages and so using less …


Table locks in SHOW INNODB STATUS

Quite frequently I see people confused what table locks reported by SHOW INNODB STATUS really mean. Check this out for example:

This output gives us an impression Innodb has taken table lock on test/t1 …


Slow DROP TABLE

It is a known fact that ext3 is not the most efficient file system out there and for example file removals can be painfully slow and cause a lot of random I/O. However, as it …