When any application begins, the amount of the data is limited, and the complexity of the business logic is simple; as time goes by, the data and complexity increases. The same queries running very fast in the beginning often start running slowly at some point. The data that used to be available freely starts to get locked, and queries that were sprinting, are now turning into long-running queries.
In this blog, we will discuss the locking scenario and long-running queries and their potential solution.
Locking, Blocking, and Deadlocking
First, let’s talk about three different but similar sounding words – Locking, Blocking, and Deadlocking.
Locking – Locking is the mechanism used by the database to protect data integrity during any transactions. Most of the time-locking situation resolves itself without any intervention.
Blocking – Blocking is the mechanism that a database uses to protect data integrity when two or more processes simultaneously attempt to access the data. Not all blocking scenarios are resolved by themselves.
Deadlocking – Deadlock is a unique blocking scenario where there are two or more processes, and every process is dependent on another process to complete the task. Deadlock resolution is an entirely automatic process, and it does not require any user intervention.
It is an event in which blocking and deadlocking do not require user intervention. However, monitoring the system and identifying the queries with longer wait times is vital if there is a blocking scenario. In MySQL, we can do this with the following two methods.
Method 1
MySQL Configuration
Setting the slow_query_log parameter creates a log of files that exceed the defined threshold. The threshold is set in seconds and is indicated with the long_query_time variable. Queries that take longer than the long_query_time run are identified and logged. The additional log_queries_not_using_indexes setting also lists all the queries which are not using indexes. This way, if a needed index is missed, it can be created by DBA.
MySQL configuration settings get changed by modifying the configuration file.
On Linux systems, the configuration file is – /etc/mysql/my.cnf.
On Windows systems, the configuration file is located here –
C: \ProgramData\MySQL\MySQL Server x.x\my.ini.
Method 2
MySQL Performance Schema
The Performance Schema contains information about recent statement events and aggregates that information in summary tables. One of the tables which can help us understand long-running queries is the event statement summary by digest table.
SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR,
SUM_TIMER_WAIT/1000000000000 SUM_TIMER_WAIT_SEC,
AVG_TIMER_WAIT/1000000000000 AVG_TIMER_WAIT_SEC,
SUM_LOCK_TIME/1000000000000 SUM_LOCK_TIME_SEC
FIRST_SEEN, LAST_SEEN
FROM events_statements_summary_by_digest;
The query gives lots of helpful information about how long any particular query was locked and how many times the occurrence of the same has happened. The query also provides lots of insight into when the digest was first seen and the latest occurrence.
Solution:
SQL Diagnostic Manager for MySQL
In this blog, we have only included two methods to identify the locking by query and the long-running queries. While both the methods look simple, they are not easy to use if you have more than one MySQL server to manage. DBAs can not decide on the slow running query on one factor or parameter. It is also essential to monitor another MySQL configuration parameter when any query takes too long to execute. I recommend using SQLDM for MySQL to track all the configuration changes and map them with the performance issues when encountering a long-running query.
