How to Optimize MySQL: Indexes, Slow Queries, Configuration

MySQL remains the most popular relational database in the world, but it is also the easiest database to be used inefficiently - many people use the default settings without further research. This article will review some of the MySQL optimization techniques introduced before and combine them with the latest improvements.

Core points

• Optimize MySQL configuration by adjusting key parameters such as innodb_buffer_pool_size, innodb_log_file_size, and innodb_flush_method to better utilize server resources and improve database performance.
• Efficiently use indexes to speed up query processing; consider using unique indexes, primary key indexes and full-text indexes based on query requirements and data uniqueness.
• Use tools such as Percona Toolkit for MySQL to automatically identify and resolve issues such as duplicate or unused indexes to optimize database efficiency.
• Use tools such as MySQL's slow query logs and pt-query-digest to monitor and analyze slow queries to detect bottlenecks and optimize query performance.
• Run MySQL Tuner and other performance monitoring tools regularly to gather insights on database operations and fine-tune the configuration based on actual usage patterns.

Configuration optimization

The first and most overlooked performance improvement to MySQL is to adjust the configuration. Version 5.7 (current version) has better default values ​​than previous versions, but improvements can still be made.

We assume you are using a Linux-based host or a Vagrant virtual machine like our improved Homestead, so your configuration file is located at /etc/mysql/my.cnf. Your installer may load the auxiliary configuration file into it, so check it out-if the my.cnf file is not much, it may be a /etc/mysql/mysql.conf.d/mysqld.cnf file.

Edit configuration

You need to be familiar with using the command line. Even if you haven’t been exposed to it before, it’s a good time.

If you edit locally on a Vagrant virtual machine, you can use the cp /etc/mysql/my.cnf /home/vagrant/Code command to copy the file to a shared folder in the main file system, edit it using a normal text editor, and then copy it back to its original place after completion. Otherwise, use a simple text editor like vim and execute the sudo vim /etc/mysql/my.cnf command.

Note: Modify the above path to match the actual location of the configuration file - it may actually be located in /etc/mysql/mysql.conf.d/mysqld.cnf

Manual adjustment

The following manual adjustments should be made immediately. Based on these tips, add the following to the configuration file in the

section: [mysqld]

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

• innodb_buffer_pool_size – Buffer pool is a storage area used to cache data and indexes in memory. It is used to keep frequently accessed data in memory, and it makes sense to allocate the most RAM to this part of the application when you run a dedicated server or virtual server and the database is often a bottleneck. So we allocate 50-70% of all RAM to it. A guide to buffer pool resizing is provided in the MySQL documentation.
• Log file size is well explained here, but in short, it is the amount of data to be stored in the log before erasing it. Note that in this case, the log is not an error log or a log you may be used to, but rather indicates checkpoint time, because for MySQL, writes occur in the background, but still affect foreground performance. Larger log files mean better performance, as fewer new smaller checkpoints are created, but longer recovery time in case of crashes (need to rewrite more to the database).
• innodb_flush_log_at_trx_commit There is an explanation here, which indicates what happens to the log file. With 1, we have the safest setup because the logs are flushed to disk after each transaction. Using 0 or 2, it has less ACID performance but higher performance. In this case, the difference is not enough to exceed the stability advantage of Set 1.
• innodb_flush_method – To complete the refresh work, set it to O_DIRECT to avoid double buffering. This should always be done unless the I/O system performs very poorly. On most managed servers, such as DigitalOcean Droplets, you will have an SSD, so the performance of your I/O system will be high.

There is another tool from Percona that can help us automatically find remaining issues. Note that if we run it without the above manual tweaks, only 1 of the 4 fixes can be manually identified, as the other 3 depend on user preferences and the environment of the application.

Variable Inspector

To install the variable inspector on Ubuntu:

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

For other systems, follow the instructions.

Then, run the toolkit with the following command:

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

You should see output similar to the following:

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

None of these are key issues and do not need to be fixed. The only thing we can add is binary logging for replication and snapshots.

Note: In newer versions, the binlog size will default to 1G and PT won't notice it.

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

• max_binlog_size Set to determine the size of the binary log. These logs log your transactions and queries and create checkpoints. If the transaction is greater than the maximum, the log may be greater than the maximum when saved to disk—otherwise, MySQL will keep it within this limit.
The
• log_bin option enables binary logging completely. Without it, there is no snapshot or copy. Please note that this can put a lot of pressure on disk space. Server ID is a necessary option when activated for binary logging, so the logs know which server they come from (for replication) and the format is just the way to write to the log.

As you can see, the new MySQL has reasonable default values ​​that make things go into production almost immediately. Of course, each app is different and there are additional custom tweaks that apply.

MySQL Tuner

Tuner will monitor the database at longer intervals (run around once a week on a live application) and suggest changes based on what it sees in the log.

Simply download and install it:

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

Running it with ./mysqltuner.pl will ask your database's administrator username and password and output a quick scan. For example, here is my InnoDB section:

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

Again, it is important to note that this tool should run about once a week after the server is running. After changing the configuration value and restarting the server, it should be run a week from then on. It is best to set up a cron job to do this for you and send you results regularly.

---

After each time you change the configuration, make sure to restart mysql server:

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

Index

Next, let's focus on indexing—the main pain points of many amateur database administrators! Especially those who jumped into ORM immediately and therefore never really touched the original SQL.

Note: The term keys and indexes can be used interchangeably.

You can compare MySQL index with the index in the book, which allows you to easily find the correct page containing the topic you are looking for. Without an index, you have to read through the entire book to search for pages containing the topic.

As you can imagine, searching by index is much faster than having to traverse each page. Therefore, usually adding indexes to a database can speed up select queries. However, indexes must also be created and stored. Therefore, update and insert queries will be slower and will take up more disk space. Generally, if you index the table correctly, you won't notice the differences in updates and inserts, so it is recommended to add indexes in the right place.

Tables containing only a few rows do not actually benefit from indexing. As you can imagine, searching for 5 pages is no slower than going to index first, getting page numbers and then opening a specific page.

So, how do we find out which indexes to add and what types of indexes exist?

Unique/primary key index

The primary key index is the index of data and is the default way to address data. For user accounts, this could be a user ID or username, or even a primary email. The primary key index is unique. The only index is an index that cannot be repeated in a set of data.

For example, if the user selects a specific username, no one else should be able to use it. Adding a "unique" index to the username column resolves this issue. If someone else tries to insert a row with an existing username, MySQL will report an error.

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

Primary keys/indexes are usually defined at table creation, and the only index is defined by changing the table.

Both primary and unique keys can be created on one or more columns. For example, if you want to make sure that there is only one username per country to define, you can create a unique index on both columns, like this:

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

The unique index is added to the columns you frequently access. So, if you request a user account frequently and there are many user accounts in the database, this is a good use case.

General Index

General index simplifies search. They are useful when you need to quickly find data for a specific column or combination of columns but that data does not need to be unique.

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

The above operations will speed up searching for usernames by country.

Indexes also help improve sorting and grouping speeds.

Full text index

Full-text index is used for full-text search. Only the InnoDB and MyISAM storage engines support full-text indexing and only the CHAR, VARCHAR, and TEXT columns are supported.

These indexes are very useful for all text searches you may need to perform. Full-text indexing is good at finding words in text body. If you often allow searching for posts, comments, descriptions, comments, etc. in the app, use these indexes on these contents.

Downward index

is not a special type, but a change. Starting with version 8.0, MySQL supports descending indexing, which means it can store indexes in descending order. This comes in handy when you have large tables or priority entries that often need to fetch the last added data first. It can always be sorted in descending order, but this will result in a little performance penalty. This speeds up further.

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

Consider applying DESC to indexes when processing logs written to the database, posts and comments loaded in back-to-front order, and similar content.

Assisted Tools: Explain

The EXPLAIN tool will be priceless when viewing optimization queries. Adding EXPLAIN before a simple query will handle it in a very deep way, analyzing the index being used, and showing the ratio of hits and misses. You will notice how many rows it has to process to get the result you are looking for.

<code>max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'</code>

You can extend it further using EXTENDED:

<code class="language-bash">wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl</code>

See How to Use It and Apply Discovery by Reading this Excellent Detailed Article.

Assisted Tool: Percona is used to detect duplicate indexes

Previously installed Percona Toolkit also provides a tool for detecting duplicate indexes that comes in handy when using a third-party CMS or just checking if more indexes are added unexpectedly than needed. For example, the default WordPress installed in the wp_posts table has a duplicate index:

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

As shown in the last line, it also provides suggestions on how to remove duplicate indexes.

Assisted Tools: Percona is used to detect unused indexes

Percona can also detect unused indexes. If you are logging slow queries (see the Bottlenecks section below), you can run the tool and it will check if the queries for those records are using indexes in the tables related to the queries.

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

See here for detailed usage of this tool.

Bottleneck

This section explains how to detect and monitor bottlenecks in the database.

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

The above should be added to the configuration. It monitors queries that have execution time of more than 1 second and those that have not used the index.

Once this log has some data, you can use the aforementioned pt-index-usage tool or pt-query-digest tool to analyze its index usage, which will produce the following results:

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

If you prefer to analyze these logs manually, you can do this too—but first you need to export the logs into a more "analyzed" format. This can be done by:

<code>max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'</code>

Other parameters can further filter the data and ensure that only important content is exported. For example: the top 10 queries sorted by average execution time.

<code class="language-bash">wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl</code>

See the documentation for other parameters.

Conclusion

In this comprehensive MySQL optimization article, we examine various ways to make MySQL run faster.

We handled configuration optimization, we completed the index, and we got rid of some bottlenecks. However, this is mostly theoretical – for practical use cases to apply these technologies in real-world applications, please pay attention to our upcoming performance boost project!

Did we miss any techniques and tricks? Please tell us!

MySQL Indexing and Slow Query Optimization FAQ (FAQ)

What is the importance of MySQL indexing in query optimization?

MySQL indexes are critical for query optimization because they can significantly speed up data retrieval. They work similar to the indexes in the book, allowing the database to find and retrieve data without scanning every row in the table. This can result in faster query execution, especially in large databases. However, it is important to note that while indexes increase read speed, they may slow down write speeds, as the index needs to be updated when inserting or updating data.

How to identify slow queries in MySQL?

MySQL provides a useful tool called Slow Query Log. This tool records information about all SQL queries that have been executed for more than the specified time. You can enable it in the MySQL configuration file and set long_query_time to the number of seconds the query should take before it is considered a slow query.

What are the different types of MySQL indexes?

MySQL supports multiple types of indexes, including B-tree, hash, R-tree, and full-text index. The B-tree is the default index type and is suitable for various queries. Hash indexes are used for equal comparisons and are faster than B-tree for such queries. R-tree index is used for spatial data types, and full-text index is used for full-text search.

How to optimize my MySQL configuration?

MySQL configuration optimization involves tuning various server variables for performance. This includes adjusting the buffer pool size, log file size, query cache size, etc. It is important to monitor the performance of the server regularly and adjust these variables as needed.

What tools can help me optimize MySQL queries and indexes?

There are several tools available for MySQL query and index optimization. These tools include MySQL's built-in EXPLAIN statements that provide information about how MySQL performs queries, as well as third-party tools such as Percona Toolkit and MySQL Workbench.

How does the EXPLAIN statement help with query optimization?

The EXPLAIN statement in MySQL provides information about how MySQL performs queries. This includes information about the tables accessed, the order in which the tables are accessed, the specific index used, and the estimate of the number of rows read. This information can help identify potential performance issues and guide index optimization.

What impact does the index have on write operations in MySQL?

Although indexes significantly improve read operations by speeding up data retrieval, it may slow down write operations. This is because each time data is inserted or updated, the corresponding index needs to be updated. Therefore, when creating an index, it is important to strike a balance between read and write operations.

How to use index optimization for JOIN operations in MySQL?

Indexes can significantly improve the performance of JOIN operations in MySQL. By creating an index on the columns used in the JOIN condition, MySQL can quickly find matching rows in the connected table. This reduces the need for full-text scanning and results in faster query execution.

What is the role of query cache in MySQL performance optimization?

The query cache in MySQL stores the results of the SELECT query as well as the query itself. When the same query is received, MySQL can retrieve the results from the cache instead of executing the query again. This can significantly improve performance, especially for complex queries or frequently executed queries.

How to monitor the performance of my MySQL server?

MySQL provides several tools for monitoring server performance. These tools include performance patterns (providing detailed performance metrics) and information patterns (providing information about database metadata). In addition, the SHOW STATUS command can be used to obtain information about the server's running status.

The above is the detailed content of How to Optimize MySQL: Indexes, Slow Queries, Configuration. For more information, please follow other related articles on the PHP Chinese website!