How to optimize mysql? mysql related optimization

1 Hardware layer related optimization

1.1. CPU related

In the BIOS settings of the server, you can adjust the following configurations in order 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. Do not consider saving when running services such as DB that usually require a high amount of calculations. Power off;

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

　3. Memory Frequency Frequency) select Maximum Performance;

4. In the memory settings menu, enable Node Interleaving to avoid NUMA problems;
1.2. Disk I/O related

The following are some measures that can be optimized for disk I/O in order of IOPS performance improvement:

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

　2. Purchase an array card equipped with both CACHE and BBU modules, which can Significantly improve IOPS (mainly refers to mechanical disks, excluding SSD or PCIe SSD. At the same time, the health status of the CACHE and BBU modules need to be checked regularly to ensure that data will not be lost in an accident);

　3 , when there is an array card, set the array write strategy to WB, or even FORCE WB (if there is dual power protection, or the data security requirements are not particularly high), it is strictly prohibited to use the WT strategy. 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 one, for example, choose a 15KRPM disk instead of a 7.2KRPM disk, which is not worth 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. Do not use cfq (it is not suitable for running DB services);

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

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

　2.2. Optimization of other kernel parameters

Setting appropriate values ​​for key kernel parameters is to reduce the tendency of swap and prevent large fluctuations in memory and disk I/O, resulting in instantaneous peak loads:

　1. Set vm.swappiness to about 5-10, or even set it to 0 (be careful to set it to 0 for RHEL 7 or 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 set vm.dirty_ratio to about twice it to ensure that dirty data can be continuously flushed to the disk and avoid instantaneous I/O writes. Serious wait occurs (similar to innodb_max_dirty_pages_pct in MySQL);

3. Set net.ipv4.tcp_tw_recycle and net.ipv4.tcp_tw_reuse 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 Internet, after testing, I found that they have little impact on the OLTP environment where reading and writing are mainly mixed (it should be on the reading and writing). Sensitive scenes are more effective), but maybe there is 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 most people will Select 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 my. cnf generatorGenerate configuration file template):

1. If you choose the Percona or MariaDB version, it is strongly recommended to enable the thread pool feature, which can improve performance under high concurrency conditions. There won't be a big drop. 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, that is The InnoDB engine is used by default. It is strongly recommended not to use the MyISAM engine. 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 absolutely If most of them are InnoDB engine tables, you can consider setting them to about 50% to 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, use independent table space, I really can’t think of any benefits of using shared table space;

　6. Set innodb_data_file_path = ibdata1:1G:autoextend, thousand Do not use the default 10M, otherwise it will be greatly affected when there are high concurrent transactions;

7. Set innodb_log_file_size=256M, set innodb_log_files_in_group=2, which can basically satisfy 90 % or more 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 executions Slower SQL, used 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 10 More than ten thousand, and 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 is relatively large, and I have seen it set to 1G. These two options are allocated for each connection session, so do not set them too large, otherwise it will easily cause OOM; 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 them too large;

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

　3.3. About Schema design specifications and SQL usage suggestions

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

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. Not many InnoDB tables are truly purely read-only. This is true. If so, it is better to use TokuDB;

2. On 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;

　3. Try not to use TEXT/BLOB types. If necessary, it is recommended to split into sub- table, do not put it together with the main table 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 needs. , 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;

　7. When querying with multi-table joins, the types of related fields must be consistent as much as possible, and all must have indexes;

8. When querying with multi-table joins, put the tables with small result sets (Note that this refers to the filtered result set, not necessarily the small amount of data in the entire table) as a driving table;

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

10. Use more composite indexes and less use of multiple independent indexes, especially if some cardinality is too small ( For example, if the total number of unique values ​​of the column is less than 255, do not create an independent index; Result set, the efficiency will be much higher;

　3.4. Other suggestions

Other suggestions on the management and maintenance of MySQL are:

1. Generally, a single table The physical size 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. This suggestion is mainly to consider 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 maximize the use of computing resources. If a single instance can already exhaust most of the computing resources, there is no need to run multiple instances;

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

　5. Collect slow query log regularly and use the pt-query-digest tool for analysis, which can be combined with The Anemometer system performs slow query management to analyze slow queries and perform subsequent optimization work;

6. You can use pt-kill to kill long-term SQL requests. There is an option in the Percona version. innodb_kill_idle_transaction can also implement 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 the data differences in mysql master-slave replication;

Written at the end: This optimization reference, big In some cases, I have introduced applicable scenarios. If your application scenario is different from what is described in this article, it is recommended to adjust it according to the actual situation instead of copying it mechanically. Questions and suggestions are welcome, but habitual resistance without going through the brain is rejected.

The above is the detailed content of How to optimize mysql? mysql related optimization. For more information, please follow other related articles on the PHP Chinese website!