How to debug and monitor MySQL locks with Performance Schema

Scroll this

MySQL has facilities to monitor locking in real time by using MySQL feature to monitor database server at the low level. It is called Performance Schema. In this post you will learn how to use it in order to monitor and debug MySQL locks. Detailed explanation of Performance Schema is out of scope of this blog post. If you want to learn more please check official documentation: https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html.

Follow this steps:

1. Enable Performance Schema

Check if Performance Schema is already enabled

if enabled you should see line like this:

or execute this query from mysql client:

Enable Performance Schema

If Perforamnce Schema is not enabled, edit my.conf. If you don’t where is MySQL config file execute:

and you will see something like this:

Now edit the file

And add performance_schema=ON under [mysqld] section

Locking information

Locking information can be accessed through metadata_locks table. It is not enabled by default. To enable it put this line performance_schema_instrument = 'wait/lock/metadata/sql/mdl=ON'  in your my.conf below the line we added in previous step. Now your my.conf should look something like:

For more info check: https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html.

Restart the mysql server

CentOS:

Ubuntu:

Check did you correctly set the options:

and search for performance_schema and performance_schema_instrument.

See it in action

Lock timeout is 10s and script is holding the lock for 30s before terminates and releases the lock. This will give us enough time to see what is going on. Execute the script from cmd line, in parallel, 2-3s apart.

The metadata_locks will show the locks, one GRANTED one PENDING.

The problem is – we can’t really match the rows in the metadata_locks table with one of the php scripts which is executed in parallel. OWNER_THREAD_ID, for now, is not useful enough.

Checkout the CONNECTION_ID() . It is one of the MySQL information functions. As the name suggests it returns connection id. The same one which is displayed as:

  1. ID column of information_schema.processlist table
  2. Id column of SHOW PROCCESSLIST output
  3. PROCESSLIST_ID column of information_schema.threads table

But connection id is not present in the output of the metadata_locks table.

First update the test script to output CONNECTION_ID(). Add those line before GET_LOCK() query.

New complete test script:

Again, execute the new script from cmd line, in parallel, 2-3s apart.

First script output:

Second script output:

But before  scripts terminate execute this sql query in your favorite mysql client:

Result:

Now we can match the CONNECTION_ID() from the script output with PROCESSLIST_ID and data from metadata_locks table.

References

Submit a comment