Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 4

Displaying posts with tag: metadata lock (reset)

Tracking Metadata Locks (MDL) in MariaDB 10.0
+1 Vote Up -1Vote Down

I recently blogged about tracking metadata locks in the latest MySQL, and now I want to discuss how to track these metadata locks in MariaDB.

In MySQL 5.7, there is a table named `metadata_locks` added to the performance_schema (performance_schema must be enabled *and* the metadata_locks instrument must be specifically enabled as well.

In the MariaDB 10.0 implementation (as of 10.0.7), there is a table named METADATA_LOCK_INFO added to the *information_schema*. This is a new plugin, so the plugin must be installed, but that is very simple with:

INSTALL SONAME 'metadata_lock_info';

Then, you will have the table.

To see it in action:

Connection #1:

mysql> create table t (id int) engine=myisam;
mysql> begin;
mysql> select * from t;

Connection #2:

mysql> alter table t
  [Read more...]
Tracking Metadata Locks (MDL) in MySQL 5.7
+3 Vote Up -1Vote Down

I’ve blogged about metadata locks (MDL) in the past (1 2 3) and in particular discussed how best to track them down and troubleshoot threads stuck waiting on metadata locks.

If you’ve had any experience with these, you’ll know finding them isn’t always the most straight-forward task.

So I was glad to see metadata lock instrumentation added to MySQL 5.7.3 as part of performance_schema, which makes tracking these down a breeze! (Note this is only in 5.7.3 currently, and therefore is some time from being GA as of today)!

To use these, performance_schema must be enabled (i.e., performance_schema=1 in your config file).

But, also, the metadata_locks instrument is disabled by default, so even if you enable the

  [Read more...]
Troubleshooting MySQL Crashes related to Metadata Locking
+2 Vote Up -0Vote Down

I just wrote an article about “Troubleshooting ‘Waiting for table metadata lock’ Errors for both MyISAM and InnoDB Tables” and then ran into a new, different metadata locking issue right after I posted it, and so I thought I’d share that too, just in case anyone ever encounters a similar situation.

In this case, mysqld kept crashing on restart, reporting “out of memory” errors:

/opt/app/mysql/product/mysql/bin//mysqld: Out of memory (Needed 840 bytes)
stack_bottom = 7fb4ebaeae58 thread_stack 0x40000
mysqld: /mysql/mysys/my_new.cc:52: int __cxa_pure_virtual():
  Assertion `! "Aborted: pure virtual method called."' failed.
mysqld: /mysql/mysys/my_new.cc:52: int __cxa_pure_virtual():
  Assertion `! "Aborted: pure virtual method called."' failed.
Fatal signal 6 while backtracing

There is a

  [Read more...]
Troubleshooting “Waiting for table metadata lock” Errors for both MyISAM and InnoDB Tables
+1 Vote Up -0Vote Down

With the introduction of metadata locking in MySQL 5.5, it is much more common to see queries stuck in the “Waiting for table metadata lock” state.

If you see a query stuck in the “Waiting for table metadata lock” state, waiting for a MyISAM table, then it is because this table has been included as part of some uncommitted transaction (whether intentionally or not).

In fact, it could be as simple as issuing SET AUTOCOMMIT=0 followed by a SELECT against a MyISAM table (a transaction can be started 3 ways: START TRANSACTION, BEGIN, and SET AUTOCOMMIT=0). After that, the table will be locked to other DDL statements until the transaction is committed (or ended).

This metadata lock is meant for DDL statements, and so that is what it blocks. However, once a DDL





  [Read more...]
Showing entries 1 to 4

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.