Facing excessive MySQL server load, or noticing some queries take too long to run? Here’s how to start when investigating MySQL performance problems.
Before beginning, you should be aware that each server and data set comes with its own performance challenges. This article aims to provide general guidance on how to check your server configuration and individual queries for lurking issues.
Tackling Server Load
The most obvious indication that something’s wrong often stems from a general slowdown. If you’re seeing sustained periods of high-resource utilization, tweaking MySQL’s configuration file might lead to more optimal settings.
A good place to start is to run MySQLTuner. This script automatically assesses your MySQL server against 300 possible performance indicators. It’ll produce a list of suggestions that could help you get more from your environment.
MySQLTuner is distributed as a Perl script, so you’ll need Perl installed on your system. Use the following commands to download and run MySQLTuner:
The connection syntax is similar to the mysql command-line client. You should connect as the root user so that the script has full access to your server.
MySQLTuner is helpful because its assessment is based on your server hosting your data set. Nonetheless, the output is merely suggestive—not every recommendation will have an impact, and some could actually reduce performance.
MySQLTuner is a purely read-only script. It will not make any changes to your server’s settings. If you accept a suggestion, you must manually update the indicated variable in your MySQL configuration file. The location of this file varies by operating system distribution. Common locations include /etc/mysql/my.cnf and /etc/mysql/mysql.conf.d/mysqld.cnf.
After changing a variable, restart the MySQL server:
You should now leave the server to run normally for a while. You can then run MySQLTuner again to reevaluate the server performance. It might suggest another change to the same variable. Keep making changes, but be sure to balance all the settings. You won’t necessarily be able to set every variable to its suggested value without new suggestions appearing. MySQLTuner’s output advises leaving the server to run for 24 hours to get accurate assessment data.
Customizing Buffer Sizes
Changing buffer and cache sizes can deliver a marked performance improvement. MySQL defaults to relatively small buffer sizes, which won’t work well for larger workloads. Values are written in the MySQL configuration file using K, M, or G to indicate the storage unit (e.g. 512M means 512 megabytes).
innodb_buffer_pool_size : As a rule of thumb, this is usually set to 70-80% of your available memory. It defines the size of the pool used to buffer queries to InnoDB tables. Try to make this at least as large as the total size of your data set, provided that you have sufficient memory available. innodb_buffer_pool_instances : A value between 1 and 64, defining the number of InnoDB buffer pools to operate. Each page stored in the buffer pool is randomly assigned to one of the instances. A larger number of instances can improve concurrency. innodb_log_file_size : Maximum size of redo log files in a log group. These files are used during crash recovery to restore incomplete transactions. Larger values improve performance but cause an increased recovery time in the event of a crash. key_buffer_size : This is similar to innodb_buffer_pool_size but is used for MyISAM tables. Note that if you’re exclusively using either MyISAM or InnoDB tables, you should set the relevant variable accordingly and change the other to a relatively low size, such as 32M. Otherwise, you’d be wasting RAM by provisioning a large buffer space for an unused table type. join_buffer_size : This sets the size of the buffer used for joins without indexes. Increasing the size of this buffer will accelerate queries, which use unindexed joins. Setting it too high can lead to memory issues, as a join buffer is allocated for each full join between tables. Complex joins between multiple tables will need multiple buffers, each of join_buffer_size capacity, which can quickly consume a lot of RAM. The default value is 256K. sort_buffer_size : Like join_buffer_size , but applicable to sorting operations using filesort. Higher values can accelerate the sorting of large result sets, but risk increasing memory usage on a highly active server.
You should always assess any changes against the size of your data set and your server’s hardware resources. Setting these values too low will impact query performance, while conversely, setting them too high could lead to excessive memory usage or even memory exhaustion. MySQLTuner will issue warnings if your configuration is at risk of consuming all available system memory.
Analyzing Slow Queries
Enabling the slow query log gives you insight into poorly performing queries. You can do this from a MySQL shell session:
This configuration will log any queries that take longer than five seconds to run into /slow-query.log. Examine this file periodically to identify long-running queries.
Once you’ve found a problematic query, you can use an EXPLAIN statement to get insight into what’s causing the slowdown. Prefix the query with EXPLAIN and run the command in a MySQL shell. You’ll get tabulated output showing how MySQL plans to execute the query.
The EXPLAIN output includes information on the indexes available, the keys used, and the number of records assessed. Interpreting EXPLAIN data is a topic unto itself. Detailed guidance on the meaning of each field is available in the MySQL documentation.
It can be easier to use MySQL Workbench to execute an EXPLAIN graphically. This can help you visualize the database engine’s approach to fetching the result set. Within MySQL Workbench, press Ctrl+T to open a new query tab. Write your query and press Ctrl+Alt+X to execute it as an EXPLAIN (You don’t need to add the EXPLAIN prefix manually.). In the results pane, you’ll see the visual execution plan highlighting the operations involved.
The Role of Indexes
It’s important to ensure that your data set contains appropriate indexes. Correct use of indexes substantially increases query performance.
This query should have an index on the users.Email field. Without an index, MySQL would need to conduct a Full-Table Scan, causing a slow examination of every record.
With the index, the database engine is able to identify records much more quickly. It does this by creating a new data structure that holds the field’s value and a pointer to the source record. The pointers can then be sorted so that MySQL can jump straight to relevant data.
To add an index to an existing field, use the ADD INDEX statement with ALTER TABLE:
You should then run OPTIMIZE TABLE my_table to index the existing data and recompute query statistics.
When you’re working with multiple fields, you can create a covering index. This is one index that incorporates all of the fields.
When using covering indexes, the order of fields matters. If you queried WHERE z = 1 ORDER BY x, the index created above would not be used.
You should make sure that fields used in WHERE or JOIN … ON clauses are covered by an index. Querying unindexed fields can quickly become a performance bottleneck. Be wary of indexing every field though—if you’ll never query that field, the index is an unnecessary overhead that must still be maintained by MySQL.
You can identify queries that would benefit from an index by enabling the unindexed queries log. Follow the directions above to enable the slow query log. You should then run SET GLOBAL log_queries_not_using_indexes = “On” from a MySQL shell. This will start logging queries with missing indexes into the slow query log. Unindexed queries will be included even if they don’t exceed the configured slow query time.
Conclusion
There’s no one-size-fits-all approach to improving MySQL performance. The steps you’ll need to take will depend on your server’s resources, the size of your data set, and the level of resource contention caused by other workloads running on the machine.
You shouldn’t overlook your application layer either—it may not be MySQL that’s the root of your performance problems. Inspect the way in which your code makes queries. If there’s excessive looping, such as using a query in an N+1 routine, refactoring that code might be much more impactful than micromanaging your MySQL server.
