MySQL Performance: Identifying Long Queries

Every MySQL backed application can benefit from a finely tuned database server. The Liquid Web Heroic Support team has encountered numerous situations over the years when some minor adjustments have made a world of difference in website and application performance. In this series of articles, we have outlined some of the more common recommendations that have had the largest impact on performance.

Preflight Check

This article applies to most Linux-based MySQL VPS servers. This includes, but is not limited to, both Traditional Dedicated and Cloud VPS servers running a variety of common Linux distributions. The article can be used with the following Liquid Web system types:

• Core-managed CentOS 6x/7x
• Core-managed Ubuntu 14.04/16.04
• Fully managed CentOS 6/7 cPanel
• Fully managed CentOS 7 Plesk Onyx 17
• Self-managed Linux servers

This series of articles assumes familiarity with the following basic system administration concepts:

• SSH connections and basic navigation of the standard Linux command line shell environment.
• Opening, Editing and Saving files in Vim or a chosen system editor.
• MySQL interactive mode and general MySQL query syntax.

What is MySQL Optimization?

There is no clearly defined definition for the term MySQL Optimization. It can mean something different depending on the person,  administrator, group or company. For this series of articles on MySQL Optimization, we will define MySQL Optimization as:  The configuration of a MySQL or MariaDB server which has been configured to avoid commonly encountered bottlenecks discussed in this series of articles.

What is a bottleneck?

Very similar to the neck on a soda bottle, a bottleneck as a technical term is a point in an application or server configuration where a small amount of traffic or data can pass through without issue. However, a larger volume of the same type of traffic or data is hindered or blocked and cannot operate successfully as-is. See the following example of a configuration bottleneck:

In this example, the server is capable of handling 10 connections simultaneously. However, the configuration only accepts 5 connections. This issue would not manifest so long as there were 5 or fewer connections at once. However, when traffic ramps up to 10 connections, half of them start to fail due to unused resources in the server configuration. The above examples illustrates the bottleneck shape where it derives its name versus an optimized configuration which corrects the bottleneck.

When Should I Optimize My MySQL database?

Ideally, database performance tuning should occur regularly and before productivity is affected. It is best practice behavior to conduct weekly or monthly audits of database performance to prevent issues from adversely affecting  applications. The most obvious symptoms of performance problems are:

• Queries stack up and never completing in the MySQL process table.
• Applications or websites using the database become sluggish.
• Connection timeouts errors, especially during peak hours.

While it is normal for there to be several concurrent queries running at once on a busy system, it becomes a problem when these queries are taking too long to finish regularly. Although the specific threshold varies per system and per application, average query times exceeding several seconds will manifest as a slowdown within attached websites and applications. These slowdowns can sometimes start out small and go unnoticed until a large traffic surge hits a particular bottleneck.

Identifying Performance Issues

Knowing how to examine the MySQL process table is vital for diagnosing the specific bottleneck being encountered. There is a number of ways to view the process table depending on your particular server and preference. For brevity this series will focus on the most common methods used via Secure Shell (SSH) access:

Method 1. Using The MySQL Process Table

Use the ‘mysqladmin’ command line tool with the flag ‘processlist’ or ‘proc’ for short. (Adding the flag ‘statistics’ or ‘stat’ for short will show running statistics for queries since MySQL’s last restart.)

Command:

mysqladmin proc stat

Output:

 +-------+------+-----------+-----------+---------+------+-------+
 | Id    | User | Host      | db        | Command | Time | State | Info               | Progress |
 +-------+------+-----------+-----------+---------+------+-------+--------------------+----------+
 | 77255 | root | localhost | employees | Query   | 150  |       | call While_Loop2() | 0.000    |
 | 77285 | root | localhost |           | Query   | 0    | init  | show processlist   | 0.000    |
 +-------+------+-----------+-----------+---------+------+-------+--------------------+----------+
 Uptime: 861755  Threads: 2  Questions: 20961045  Slow queries: 0  Opens: 2976  Flush tables: 1  Open tables: 1011  Queries per second avg: 24.323

Method 2: Using The MySQL Process Table

Run the ‘show processlist;’ query from within MySQL interactive mode prompt. (Adding the ‘full’  modifier to the command disables truncation of the Info column. This is necessary when viewing long queries.)

Command:

show processlist;

Output:

MariaDB [(none)]> show full processlist;
 +-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+
 | Id    | User | Host      | db        | Command | Time | State | Info                  | Progress |
 +-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+
 | 77006 | root | localhost | employees | Query   |  151 | NULL  | call While_Loop2()    |    0.000 |
 | 77021 | root | localhost | NULL      | Query   |    0 | init  | show full processlist |    0.000 |
 +-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+

Using The slow query log

Another valuable tool in  MySQL is the included slow query logging feature. This feature is the preferred method for finding long-running queries regularly. There are several directives available to adjust this feature. However, the most commonly needed settings are:

These directives are set within the [mysqld] section of the MySQL configuration file located at /etc/my.cnf and will require a MySQL service restart before they will take effect. See the example below for formatting:

[mysqld]
 log-error=/var/lib/mysql/mysql.err
 innodb_file_per_table=1
 default-storage-engine=innodb
 innodb_buffer_pool_size=128M
 innodb_log_file_size=128M
 max_connections=300
 key_buffer_size = 8M
 slow_query_log=1
 slow_query_log_file=/var/lib/mysql/slowquery.log
 long_query_time=5

Once the slow query log is enabled you will need to periodically follow-up with it to review unruly queries that need to be adjusted for better performance. To analyze the slow query log file, you can parse it directly to review its contents. The following example shows the statistics for the sample query which ran longer than the configured 5 seconds:

# Time: 180717  0:23:28
 # User@Host: root[root] @ localhost []
 # Thread_id: 32  Schema: employees  QC_hit: No
 # Query_time: 627.163085  Lock_time: 0.000021  Rows_sent: 0  Rows_examined: 0
 # Rows_affected: 0
 use employees;
 SET timestamp=1531801408;
 call While_Loop2();

Optionally, you can use the mysqldumpslow command-line tool, which parses the slow query log file and groups like queries together except values of number and string data:

~ $ mysqldumpslow -a /var/lib/mysql/slowquery.log
 Reading mysql slow query log from /var/lib/mysql/slowquery.log
 Count: 2  Time=316.67s (633s)  Lock=0.00s (0s)  Rows_sent=0.5 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
 call While_Loop2()

(For usage information visit MySQL documentation here - mysqldumpslow — Summarize Slow Query Log Files)

Conclusion

So concludes the first part of our Database Optimization series and gives us a solid basis to refer to for benchmark purposes. Though database issues can be complicated, our series will break down these concepts to provide means to optimize your database through database conversion, table conversion, and indexing.

How Can We Assist?

We pride ourselves on being The Most Helpful Humans In Hosting™!

Our Support Teams are filled with experienced Linux technicians and talented system administrators who have intimate knowledge of multiple web hosting technologies, especially those discussed in this article.

Should you have any questions regarding this information, we are always available to answer any inquiries with issues related to this article, 24 hours a day, 7 days a week 365 days a year.

If you are a Fully Managed VPS server, Cloud Dedicated, VMWare Private Cloud, Private Parent server, Managed Cloud Servers, or a Dedicated server owner and you are uncomfortable with performing any of the steps outlined, we can be reached via phone at @800.580.4985, a chat or support ticket to assisting you with this process.