Showing entries 1 to 3
Displaying posts with tag: metadata locks (reset)
Why You Should Avoid Using “CREATE TABLE AS SELECT” Statement

In this blog post, I’ll provide an explanation why you should avoid using the CREATE TABLE AS SELECT statement.

The SQL statement “create table <table_name> as select …” is used to create a normal or temporary table and materialize the result of the select. Some applications use this construct to create a copy of the table. This is one statement that will do all the work, so you do not need to create a table structure or use another statement to copy the structure.

At the same time there are a number of problems with this statement:

  1. You don’t create indexes for the new table
  2. You are mixing transactional and non-transactional statements in one transaction. As with any DDL, it will commit current and unfinished transactions
  3. CREATE TABLE … SELECT …
[Read more]
Exploring MySQL Metadata Lock Instrumentation in Closer Detail

I recently wrote a post on tracking metadata locks (MDL) in MySQL 5.7, and I wanted to take a moment to expand on it by explaining a couple of the associated variables in more detail.

First off, once you have enabled the performance_schema *and* the metadata lock instrumentation, you can verify it with:

mysql> SELECT * FROM performance_schema.setup_instruments
    -> WHERE NAME = 'wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | YES     | YES   |
+----------------------------+---------+-------+

“ENABLED” will report “YES” if it is enabled properly, and “NO” if not.

“TIMED” (referring to event timing) reports “YES” if it was enabled via the my.cnf or my.ini file, and it …

[Read more]
Mitigating the Effect of Metadata Lock (MDL) Contention

If you see the “Waiting for table metadata lock” error, you may be wondering what is the best course of action to prevent it in the future. I have briefly discussed troubleshooting metadata locks before, however, that post more illustrated how easy it is to encounter the “Waiting for table metadata lock” error in both MyISAM and InnoDB. It provides simple examples for reproducing using both storage engines, so one could hopefully more easily identify where the locks are stemming from in their particular case.

Pin-pointing which transaction holds the locks is a different story. There are feature requests filed in MySQL and MariaDB bugs databases to track this information, but they are recent, and no ETA is scheduled for either yet, as far as I know:

http://bugs.mysql.com/bug.php?id=69527

[Read more]
Showing entries 1 to 3