Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to understand the comprehensive and rapid optimization of MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

0. Guide the comprehensive MySQL fast optimization reference.

Here to sort out some MySQL general optimization methods, to do a simple summary and sharing, aimed at helping those who do not have a full-time MySQL DBA enterprises to do a good job of basic optimization, as for specific SQL optimization, most of the results can be achieved by adding the appropriate index, more complex need specific analysis, you can refer to some optimization cases of this site or contact me, there is my contact information below.

1. Hardware layer related optimization

1.1related to CPU

In the server's BIOS settings, you can adjust the following configurations to maximize CPU performance or to avoid classic NUMA problems:

1. Choose Performance Per Watt Optimized (DAPC) mode to maximize the performance of CPU. Do not consider saving power when running DB, a service that usually requires a high amount of computation.

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

3. Memory Frequency (memory frequency) Select Maximum Performance (best performance)

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

1.2, disk Imax O related

The following are some measures that can be optimized for disk IOPS O, sorted by the magnitude of the improvement in performance:

1. Use SSD or PCIe SSD devices to achieve at least hundreds or even tens of thousands of times of IOPS improvement

2. The purchase of array cards with both CACHE and BBU modules can significantly improve the IOPS (mainly refers to the mechanical disk, except SSD or PCIe SSD. At the same time, it is necessary to check the health status of CACHE and BBU modules regularly to ensure that data will not be lost in case of accident.

3. When there is an array card, set the array write policy to WB, or even FORCE WB (if there is double electrical protection, or if the data security requirements are not particularly high), the use of WT policy is strictly prohibited. And the closed array pre-reading strategy is basically a chicken rib and is of little use.

4. Choose RAID-10 instead of RAID-5 whenever possible

5. If you use a mechanical disk, choose the one with high rotational speed as much as possible, such as 15KRPM instead of 7.2KRPM, which is not short of a few dollars.

2. System layer related optimization

2.1. File system layer optimization

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

1. Use deadline/noop, two kinds of I-hand O scheduler, and never use cfq (it is not suitable for running DB services)

2. When using the xfs file system, do not use ext3;ext4, but if you have a large amount of business, you must use xfs.

3. Add noatime, nodiratime and nobarrier to the file system mount parameter (nobarrier is unique to xfs file system)

2.2. Optimization of other kernel parameters

The purpose of setting appropriate values for key kernel parameters is to reduce the tendency of swap, and to prevent large fluctuations in memory and disk IPUBO, resulting in instantaneous peak load:

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

2. Set vm.dirty_background_ratio to 5-10 and set vm.dirty_ratio to about twice that of it to ensure that dirty data can be continuously flushed to disk to avoid instant write and serious wait (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 read_ahead_kb and nr_requests transmitted on the Internet, after testing, I found that it had little impact on the read-write mixed OLTP environment (it should be more effective for read-sensitive scenarios), but maybe there was something wrong with my test method, so I could decide whether to adjust it or not.

3. MySQL layer related optimization.

3.1. About version selection

The official version we call ORACLE MySQL, this is nothing to say, I believe the vast majority of people will choose it.

I personally strongly recommend that you choose the Percona branch version, which is a relatively mature and excellent MySQL branch version, which has made a lot of improvements in performance improvement, reliability and management. It is basically fully compatible with the official ORACLE MySQL version, and its performance has improved by more than 20%, so I recommend it as a priority, and I have been focusing on it since 2008.

Another important branch version is MariaDB, and it is no longer appropriate to say that MariaDB is a branch version, because its goal is to replace ORACLE MySQL. It mainly makes a lot of source-level improvements in the original MySQL Server layer, and it is also a very reliable and excellent branch version. However, it also gives rise to new features represented by GTID that are not compatible with the official version (starting with MySQL 5.7and also supporting GTID mode to be dynamically turned on or off online), and considering that most people will still follow the official version, MariaDB is not recommended as a priority.

3.2. Suggestions for adjusting the most important parameter options

It is recommended that you adjust the following key parameters for better performance (you can use the my.cnf generator provided on my website to generate a profile 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 deteriorate significantly in the case of high concurrency. In addition, there are extra_port functions, which are very practical and can save lives at critical moments. Another important feature is the QUERY_RESPONSE_TIME function, which also gives us an intuitive view of 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 that you no longer use the MyISAM engine. InnoDB engine can definitely meet more than 99% of the business scenarios.

3. Adjust the innodb_buffer_pool_size size. If it is a single instance and most of them are InnoDB engine tables, you can 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 the actual needs. If data is required not to be lost, then both are set to 1. If a bit of data loss is allowed, it can be set to 2 and 10, respectively. If it doesn't matter whether the care data is lost or not (for example, on slave, it can be redone at least once), it can be set to 0. These three settings affect the performance of the database as follows: high, medium and low, that is, the first will be the slowest, and the last is the opposite.

5. Set innodb_file_per_table = 1 and use independent tablespaces. I really can't think of any benefits of using shared tablespaces.

6. Set innodb_data_file_path = ibdata1:1G:autoextend, never use the default 10m, otherwise it will be affected by high concurrency transactions.

7. Set innodb_log_file_size=256M and innodb_log_files_in_group=2, which can basically meet more than 90% of the scenes

8. Set long_query_time = 1, but above version 5.5, it can already be set to less than 1. It is recommended to set it to 0.05 (50 milliseconds) to record those slow SQL execution for subsequent analysis and troubleshooting.

9. According to the actual needs of the business, adjust max_connection (maximum number of connections) and max_connection_error (maximum number of errors) appropriately. It is recommended to set more than 100000, while 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. The common misunderstanding is to set tmp_table_size and max_heap_table_size to 1G. These two options are assigned to every connection session, so do not set them too large, otherwise it will easily lead to 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 be set too large.

11. Since it is recommended that you no longer use the MyISAM engine, you can set the key_buffer_size to about 32m, and it is strongly recommended to turn off the query cache feature

3.3.Design specifications of Schema and suggestions on the use of SQL

Here are several common Schema design specifications and SQL usage recommendations that help improve MySQL efficiency:

1. All InnoDB tables design a self-increasing column with no business use as the primary key, which is true for most scenarios. There are not many pure read-only InnoDB tables, so it is not as cost-effective to use TokuDB as such.

2. Under the premise that the length of the field meets the requirements, choose the one with a small length as much as possible. In addition, field properties are constrained by NOT NULL as much as possible, which can improve performance to a certain extent.

3. Do not use the TEXT/BLOB type as much as possible. If necessary, it is recommended to split it into child tables and not put them together with the main table to avoid poor read performance when SELECT *.

4. When reading data, only select the required columns, do not SELECT * every time, to avoid serious random reading problems, especially read some TEXT/BLOB columns

5. When creating an index on a VARCHAR (N) column, it is usually enough to create a prefix index with a length of about 50% (or less) to meet more than 80% of the query requirements, and it is not necessary to create a full-length index of the whole column.

6. In general, the performance of subquery is relatively poor, so it is suggested to change it to JOIN writing method.

7. When querying with multiple table joins, the types of associated fields are as consistent as possible, and all of them should have indexes.

8. When multi-table join query, use the table with small result set (note, this refers to the filtered result set, not necessarily the whole table with a small amount of data) as the driver table.

9. When multiple tables are joined and there is a sort, the sort field must be in the driving table, otherwise the sort sequence cannot be used for the index

10. Use multiple composite indexes instead of multiple independent indexes, especially for columns whose cardinality (Cardinality) is too small (for example, the total number of unique values of the column is less than 255. do not create independent indexes.

11. For SQL with similar paging function, it is recommended to associate with the primary key first, and then return the result set, which will be much more efficient.

3.3. Other suggestions

Other suggestions for the management and maintenance of MySQL are:

1. Generally speaking, the physical size of a single table does not exceed 10GB, the number of rows per table does not exceed 100 million, and the average length of rows does not exceed 8KB. If the performance of the machine is sufficient, MySQL can handle these amounts of data completely, and there is no need to worry about performance problems. The main reason for this suggestion is to consider the high cost of ONLINE DDL.

2. Don't worry too much about the mysqld process taking up too much memory, as long as there is no OOM kill and a lot of SWAP is used.

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 has consumed most of its computing resources, there is no need to run more instances.

4. Use pt-duplicate-key-checker to check and delete duplicate indexes periodically. Regularly use the pt-index-usage tool to check and delete indexes that are rarely used

5. Slow query log is collected regularly and analyzed with pt-query-digest tools. Slow query management can be carried out with Anemometer system in order to analyze slow query and optimize it.

6. You can use pt-kill to kill SQL requests for a long time. There is an option in the Percona version that innodb_kill_idle_transaction can also implement this function.

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

8. Regularly use pt-table-checksum and pt-table-sync to check and repair the data differences between master and slave replication in mysql.

For this optimization reference, I have introduced the applicable scenarios in most cases. If your application scenario is different from that described in this article, it is recommended to adjust it according to the actual situation, rather than copy it mechanically. Welcome to question the patting brick, but refuse to resist without the habitual resistance of the brain.

The above is the editor for you to share how to understand the comprehensive and rapid optimization of MySQL, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.