A more comprehensive MySQL optimization reference

This article has compiled some general optimization methods for MySQL and made a simple summary to share, aiming to help companies that do not have a full-time MySQL DBA to do basic optimization work. As for specific SQL optimization, most of them are done by adding appropriate indexes. The effect can be achieved, and more complex ones require detailed analysis.

1. Hardware layer related optimization

1.1. CPU related

In the BIOS settings of the server, you can adjust the following Several configurations are designed to maximize the performance of the CPU or avoid classic NUMA problems:

1. Select the Performance Per Watt Optimized (DAPC) mode to maximize the performance of the CPU. Running DB usually requires high computing Don’t consider power saving if you need a large amount of services;

　2. Turn off options such as C1E and C States, also to improve CPU efficiency;

　3. Select Maximum Performance for Memory Frequency (Best performance);

　4. In the memory settings menu, enable Node Interleaving to avoid NUMA problems;

　1.2. Disk I/O related

　The following are Sorting according to the magnitude of IOPS performance improvement, some measures that can be optimized for disk I/O:

1. Use SSD or PCIe SSD devices to obtain at least hundreds or even ten thousand times IOPS improvement;

　2. Purchasing an array card equipped with both CACHE and BBU modules can significantly increase IOPS (mainly refers to mechanical disks, except for SSD or PCIe SSD. At the same time, it is necessary to regularly check the health status of the CACHE and BBU modules to ensure that data will not be lost in an accident. ; And the closed array read-ahead strategy is basically useless and of little use;

4. Use RAID-10 instead of RAID-5 as much as possible; 5. If you use a mechanical disk, Try to choose a high-speed disk as much as possible, for example, choose a 15KRPM disk instead of a 7.2KRPM disk, which is not less than a few dollars;

　2. System layer related optimization

　2.1. File system layer optimization

At the file system layer, the following measures can significantly improve IOPS performance:

1. Use the two I/O schedulers deadline/noop, and never use cfq (it is not suitable Running DB services);

　2. Use xfs file system, do not use ext3; ext4 is barely available, but if the business volume is large, you must use xfs;

　3. Added several options to the file system mount parameters: noatime, nodiratime, and nobarrier (nobarrier is unique to the xfs file system);

　2.2. Optimization of other kernel parameters

Set appropriate key kernel parameters The purpose of the value is to reduce the tendency of swap and prevent large fluctuations in memory and disk I/O, resulting in instantaneous peak load:

　1. Set vm.swappiness to about 5-10 , or even set to 0 (be careful to set it to 0 for RHEL 7 and above, unless you allow OOM kill to occur) to reduce the chance of using SWAP;

　2. Set vm.dirty_background_ratio to 5-10, and vm .dirty_ratio is set to about twice it to ensure that dirty data can be continuously flushed to the disk to avoid instantaneous I/O writes and serious waits (similar to innodb_max_dirty_pages_pct in MySQL);

　3. Net .ipv4.tcp_tw_recycle and net.ipv4.tcp_tw_reuse are both set to 1 to reduce TIME_WAIT and improve TCP efficiency;

4. As for the two parameters of read_ahead_kb and nr_requests transmitted over the network, after testing, I found that the read The impact of write-mixed OLTP environments is not great (it should be more effective in read-sensitive scenarios), but there may be something wrong with my testing method, you can decide whether to adjust it;

　3. MySQL layer related Optimization

　3.1. About version selection

The official version is called ORACLE MySQL. There is nothing to say about this. I believe that most people will choose it.

I personally strongly recommend choosing the Percona branch version. It is a relatively mature and excellent MySQL branch version that has made many improvements in performance, reliability, and management. It is basically fully compatible with the official ORACLE MySQL version, and its performance has been improved by about 20% or more, so I recommend it first, and I have been using it since 2008.

Another important branch version is MariaDB. It is actually inappropriate to say that MariaDB is a branch version, because its goal is to replace ORACLE MySQL. It mainly makes a lot of source code level improvements on the original MySQL Server layer, and is also a very reliable and excellent branch version. However, this has also resulted in new features represented by GTID that are incompatible with the official version (starting from MySQL 5.7, GTID mode is also supported to be dynamically turned on or off online). Considering that the vast majority of people will still follow the official version, Therefore, MariaDB is not recommended first.

3.2. Suggestions on adjusting the most important parameter options

It is recommended to adjust the following key parameters to obtain better performance (you can use the my.cnf generator provided by this site to generate the configuration file template):

　1. If you choose the Percona or MariaDB version, it is strongly recommended to enable the thread pool feature so that the performance will not drop significantly under high concurrency conditions. In addition, there is the extra_port function, which is very practical and can save lives at critical moments. Another important feature is the QUERY_RESPONSE_TIME function, which also allows us to have an intuitive feel for the overall SQL response time distribution;

　2. Set default-storage-engine=InnoDB, which means the InnoDB engine is used by default, which is strongly It is recommended not to use the MyISAM engine anymore. The InnoDB engine can definitely meet more than 99% of business scenarios;

3. Adjust the innodb_buffer_pool_size size. If it is a single instance and most of them are InnoDB engine tables, consider setting it to About 50% ~ 70% of the physical memory;

4. Set the values ​​of innodb_flush_log_at_trx_commit and sync_binlog according to actual needs. If data cannot be lost, set both to 1. If a little data loss is allowed, they can be set to 2 and 10 respectively. And if there is no need to care whether the data is lost (for example, on the slave, it will be redone anyway), then all can be set to 0. The degree to which the performance of the database is affected by these three setting values ​​are: high, medium, and low, that is, the first one will make the database the slowest, and the last one will be the opposite;

　5. Set innodb_file_per_table = 1, Using an independent table space, I really can’t think of any benefits of using a shared table space;

6. Set innodb_data_file_path = ibdata1:1G:autoextend. Do not use the default 10M, otherwise there will be high concurrency. Transactions will be greatly affected;

7. Set innodb_log_file_size=256M, set innodb_log_files_in_group=2, which can basically meet more than 90% of scenarios;

8. Set long_query_time = 1, In version 5.5 and above, it can be set to less than 1. It is recommended to set it to 0.05 (50 milliseconds) to record those SQLs that execute slowly for subsequent analysis and troubleshooting;

9. According to actual business needs , appropriately adjust max_connection (maximum number of connections) and max_connection_error (maximum number of errors). It is recommended to set it to more than 100,000, while the parameters open_files_limit, innodb_open_files, table_open_cache, and table_definition_cache can be set to about 10 times the size of max_connection;

10. A common misunderstanding is to set tmp_table_size and max_heap_table_size relatively large. I have seen them set to 1G. These two options are allocated for each connection session, so do not set them too large, otherwise it will easily lead to OOM. ; Some other connection session-level options such as: sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size, etc., also need to be careful not to set too large;

11. Since it has been recommended that the MyISAM engine is no longer used, you can set key_buffer_size It is about 32M, and it is strongly recommended to turn off the query cache function;

3.3. Regarding Schema design specifications and SQL usage suggestions

The following lists several common Schema design specifications that help improve MySQL efficiency. And SQL usage suggestions:

1. All InnoDB tables are designed with an auto-increment column that has no business purpose as the primary key. This is true for most scenarios. There are not many InnoDB tables that are truly purely read-only. , if this is the case, it is better to use TokuDB;

2. Under the premise that the field length meets the needs, choose the smallest length possible. In addition, try to add NOT NULL constraints to field attributes, which can improve performance to a certain extent; Put the tables together to avoid poor reading performance when SELECT * is executed.

4. When reading data, select only the columns you need. Do not SELECT * every time to avoid serious random reading problems, especially when reading some TEXT/BLOB columns;

5. When creating an index on a VARCHAR(N) column, usually taking about 50% (or even smaller) of its length to create a prefix index is enough to meet more than 80% of query requirements. There is no need to create a full-length index for the entire column;

　6. Under normal circumstances, the performance of subqueries is relatively poor, and it is recommended to change it to JOIN writing method;

　7. When querying multi-table joins, the related field types should be as consistent as possible, and there must be indexes;

8. When querying with multi-table connections, use the table with a small result set (note that this refers to the filtered result set, not necessarily the small amount of data in the entire table) as the driving table;

　9. When multiple tables are joined and sorted, the sorting field must be in the driving table, otherwise the sorting column cannot use the index;

　10. Use more compound indexes and less use of multiple independent indexes, especially Do not create independent indexes for some columns whose cardinality is too small (for example, the total number of unique values ​​of the column is less than 255);

11. For SQL with similar paging function, it is recommended to use primary key association first. Then return the result set, the efficiency will be much higher;

3.4. Other suggestions

Other suggestions about the management and maintenance of MySQL are:

1. Usually, single The physical size of the table does not exceed 10GB, the number of rows in a single table does not exceed 100 million, and the average row length does not exceed 8KB. If the machine performance is sufficient, MySQL can completely handle this amount of data. There is no need to worry about performance issues. The main suggestions are Consider that ONLINE DDL is more expensive;

　2. Don’t worry too much about the mysqld process taking up too much memory. As long as OOM kill does not occur and a lot of SWAP is used, it will be fine;

　3. In the past, the purpose of running multiple instances on a single machine was to be able to Maximize the use of computing resources. If a single instance can already consume most of the computing resources, there is no need to run multiple instances;

　4. Regularly use pt-duplicate-key-checker to check and delete duplicates index. Regularly use the pt-index-usage tool to check and delete indexes with very low usage frequency;

5. Collect slow query log regularly and analyze it with the pt-query-digest tool. It can be combined with the Anemometer system for slow query management. In order to analyze slow query and carry out subsequent optimization work;

6. You can use pt-kill to kill long-term SQL requests. There is an option innodb_kill_idle_transaction in the Percona version to also achieve this function;

7. Use pt-online-schema-change to complete the ONLINE DDL requirements of large tables;

8. Regularly use pt-table-checksum and pt-table-sync to check and repair mysql master-slave replication Data differences;

Written at the end: For this optimization reference, in most cases I have introduced applicable scenarios. If your application scenario is different from what is described in this article, then it is recommended to proceed based on the actual situation. Adapt instead of mechanically adapting. Questioning and making suggestions are welcome, but habitual resistance without going through the brain is rejected.

The above is a relatively comprehensive MySQL optimization reference. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!