An optimized database server is key to ensuring that apps which rely on MySQL run efficiently. Sometimes all that’s needed to see website and application performance improvements are a few small adjustments, so in this article, we look at some of the tweaks that can help boost performance the most.
What Does it Cover?
Our advice here applies to the majority of MySQL servers running Linux and covers (among others) both dedicated and cloud VPS servers that run on various Linux distros and these 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/Obsidian
- Self-managed Linux servers
If you’ve chosen not to take advantage of direct support, you can still use these techniques, but we’re assuming that you’re familiar with these basic system admin principles:
- SSH connections and how to navigate the standard Linux command-line shell environment.
- Opening, Editing and Saving files in Vim or your preferred system editor.
- MySQL interactive mode and general MySQL query syntax.
What is MySQL Optimization?
MySQL Optimization doesn’t have an exact meaning, but the general idea is pretty self-evident. It’s just about making your database function as efficiently and effectively as possible, and what that means in practical terms will obviously vary depending on the needs of your organization. Here, we’re taking it to mean a server which has been set up to avoid the kind of typical bottlenecks that can arise.
What’s a bottleneck?
A wine bottle narrows at the neck, so there’s a tighter space for your tipple to squeeze through, so it serves as the perfect metaphor for a road that narrows from two lanes to one and slows traffic or a computer system that hinders data transit for some technical reason.
E.g. the server could cope with 10 simultaneous connections, but as it is, it can only manage 5. If no more than 5 connections are established then the system will hum along happily, but as soon as it hits 6 then the limits will become all too clear.
When Should a MySQL database be optimized?
The best time to tune your database is when doing so will have the lowest impact on whatever services rely on it. As for frequency, you should audit it every week or month to make sure that poor performance isn’t slowing down apps. It’s easy to see when this is happening because you’ll notice a few tell-tale signs:
- queries back up and never complete in the MySQL process table.
- websites or websites that rely on it become sluggish.
- more connection timeout errors, particularly during peak hours.
It’s fine to have a number of concurrently running queries at the same time on a system, but that becomes a burden when they regularly take more time than usual to complete. Even though the threshold for what constitutes a burden will vary from system to system and from app to app, average query times beyond several seconds will result in slower performance for websites and apps that depend on them. You may not notice that anything is amiss until a spike in traffic reveals that there is indeed a bottleneck. That’s why it pays to be proactive and optimize before this happens.
Tracking Down Performance Issues
To diagnose a particular bottleneck, you’ll need to know how to do an examination of the MySQL process table. You can view it in a few different ways, although this will vary according to your setup. To keep it short, we’ll just look at the most frequently used Secure Shell (SSH) access methods:
The MySQL Process Table: Method 1
The ‘mysqladmin’ command-line tool can be used with the flag ‘processlist’ or ‘proc’ for short. (You can add the flag ‘statistics’ or ‘stat’ for short to show running statistics for queries since MySQL’s most recent 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
For: can be used on the shell interface, so feeding output to other scripts and tools is no problem.
Against: The info column of the process table is always truncated so you won’t be able to view the full query if it’s longer.
The MySQL Process Table: Method 2
Run the query ‘show processlist;’ from the MySQL interactive mode prompt. (Add the ‘full’ modifier to the command to stop the Info column being truncated. You’ll be glad of it when long queries would otherwise get cut off.)
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 |
+-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+
For: Using the full modifier allows for seeing the full query on longer queries.
Against: MySQL Interactive mode can’t access the scripts and tools of the shell interface.
Using the slow query log
Another useful tool in MySQL is the included slow query logging feature, which is the preferred way of regularly finding long-running queries. You can adjust this feature using several directives, but 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
You can set these from the [mysqld] section of the MySQL configuration file that you’ll find at /etc/my.cnf. You’ll need to do a MySQL service restart to make them work. Take a look at this example for formatting:
Caution
The slow query log file requires a lot of disk space, and it needs continual attention while it’s running. Remember that the lower your long_query_time directive the quicker the slow query log will fill a disk partition.
[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
After the slow query log has been enabled you will need to follow up from time to time to evaluate troublesome queries that require your attention. To review the contents of the slow query log file, you can parse it directly. This example shows statistics for a sample query that ran over the allotted 5 seconds:
Caution
If you enable the slow query log feature, then be advised that performance will take a hit. That’s because extra routines will be analyzing every query and writing them to the log file which will increase the input/output burden. This additional overhead explains why it’s considered to be best practice to disable the slow query log on production systems. In fact, you should only enable it for a fixed period: when you’re actively hunting for queries that might be slowing down a website or app.
# Time: 180717 0:23:28
# [email protected]: 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();
Alternatively, there’s also the mysqldumpslow command line tool, which parses the slow query log file and puts like queries together but excludes the values of number and string data.
