- MySQL Performance: Identifying Long Queries
- MySQL Performance: MyISAM vs InnoDB
- MySQL Performance: How To Leverage MySQL Database Indexing
- MySQL Performance: MySQL vs. MariaDB
- MySQL Performance: Converting MySQL to MariaDB
- MySQL System Config & Routine Maintenance
- MySQL Performance: InnoDB Buffers & Directives
- MySQL Performance: MyISAM
- MySQL Performance: MySQL/MariaDB Indexes
- MySQL Performance: Intro to JOINS in SQL
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
- 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 the sake of 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:
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 one time on a busy system, it becomes a problem when these queries are taking too long to finish on a regular basis. 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 the sake of brevity this series will focus on the most common methods used via Secure Shell (SSH) access:Using The MySQL Process Table: Method 1
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
Using The MySQL Process Table: Method 2
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 on a regular basis. There are several directives available to adjust this feature. However, the most commonly needed settings are:slow_query_log | enable/disable the slow query log |
slow_query_log_file | name and path of the slow query log file |
long_query_time | time in seconds/microseconds defining a slow query |
[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 that 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)
So concludes the first part of our Database Optimization series and gives us a solid basis to refer back 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.