Archives for February 2021

Question: How to Retrieve Table Level or Global Read Lock Status from Running MySQL Server? Prior to MySQL 5.7, it is not possible to figure out table level or global read locks held by each sessions. As of MySQL 5.7, a new performance schema table, metadata_locks is added. This performance schema can achieve the goal. Please look at the example below. mysql> use performance_schema ... snip ... mysql> update performance_schema.setup_instruments set enabled='yes', timed='yes' where name = … [Read more...] about MySQL: how to figure out which session holds which table level or global read locks

In this post, we will see how to configure the Performance Schema and avoid it becomes a performance bottleneck. The default settings are a good starting point. Those have been extensively tested to ensure that in the vast majority of cases, they will not cause a severe overhead. It can be worth considering to enable some additional consumers and instruments. While the below suggestions usually only causes a small or no overhead, it is worth testing whether the overhead of additional … [Read more...] about Recommended Configuration of the MySQL Performance Schema

The preferred solution depends on the version of MySQL. If you are using MySQL 5.7 or later with metadata lock instrumentation enabled in the Performance Schema (available in MySQL 5.7 and later), it is possible to get the information directly from the performance_schema.metadata_locks table. Otherwise, there is no direct way to get the information. To determine whether metadata lock instrumentation is enabled, check the performance_schema.setup_instruments table like: mysql> SELECT * FROM … [Read more...] about MySQL: Identify what user and thread are holding on to a meta data lock that is preventing other queries from running

In this post, we will learn about what options are available if you need to automatically kill long-running queries. MySQL 5.7 and Later For SELECT Statement Starting from MySQL 5.7.4 there is support for automatic timeouts of read-only SELECT statements (the following discussion assumes 5.7.8 or later as the feature had changes to the option/hint name and syntax). There are two ways to set an upper limit of how long a read-only SELECT statement is allowed to execute: the … [Read more...] about MySQL: How to kill a Long Running Query using max_execution_time