MySQL optimization core theory and practice

Background Description: Friends Unit OA system soon completed upgrade big transformation, back end with the MySQL storage data, on-line ran for a month, complaining about the phone began to dozen, not here, is not open, there is no response, some analogy after the upgrade into a classic car, more and more slowly, the problem is imminent, must immediately want to respond to it. As a result of the deployment of standardized documents, before the launch also done a variety of tests, so, on-line troubleshooting, did not, turned out the implementation of the document, read by article, did not, then remembered once a business system, also encountered a similar situation, later through a variety of optimization to alleviate the following, "MySQL optimization core theory and practice."
Description: This paper is part of the source of the teacher's blog, the practice of some sources of work accumulated and many love the MySQL technology to share the Netizen, the original intention is to more deeply understand MySQL optimization, master more MySQL optimization technology, improve themselves, feedback love technology to share all the netizens.

Optimization of the hardware layer

The newly purchased server runs in power-saving mode by default, and in a business scenario with a large number of concurrent accesses, it can cause database performance to keep up, causing a lot of delays and eventually bringing down the business system. At the same time, improper disk selection and array card setup can also make database performance a bottleneck for the entire business system.

Goal one: Completely turn off the power saving mode and let MySQL run in high-performance mode

1. Turn off CPU power saving mode

找到OPI Link Speed Select选项，选择Max Performance

2. Turn off Memory Saver mode

找到Memory Speed选项，选择Max Performance找到Power C-States选项，选择Disable找到C1 Enhanced Mode选项，选择Disable

Goal two: Turn off NUMA so that the CPU can always use memory efficiently

Turn off NUMA

找到Socket Interleave选项，选择Non-NUMA

Goal Three: Improve IOPS performance All the way, so that disk I/O is no longer dragged back

1. When funds are plentiful, the purchase of SSDs even PCIE-SSD

SSD和PCIe-SSD带来的不只是惊喜，更有踏实，从此磁盘I/O不再是恶魔

2. Mechanical disk with array card, cache policy, Bbu battery, raid-10,15krpm

阵列卡从容面对多块机械盘，BBU电池保障高性能模式下的Cache策略不丢数据Cache策略选择Write Back甚至Always Write Back阵列预读的Read Policy选项，选择Normal使用RAID-10,性能高于RAID-5使用15KRPM高速磁盘，性能高于7.2KRPM磁盘

Server hardware setup parameters from IBM x3650m3

System-Level optimization

There are also several areas of the operating system that deserve optimization, as well as significantly improved IOPS performance. In addition, swap to less use, not only can not help, but will let the business system on the brink of collapse.

Goal One: Improve IOPS performance so that the database no longer backs the pot

1. Configure a reasonable I/O Scheduler

机械盘配deadline，执行命令echo deadline >/sys/block/sda/queue/scheduler固态盘配noop，执行命令echo noop >/sys/block/sda/queue/scheduler注意sda是数据文件所在分区

2. The file system uses XFS as much as possible, if EXT4 is still in use, hopefully only over-stage

3.mount parameter Increase Noatime,nodiratime,nobarrier

vi /etc/fstab/dev/sda1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0/dev/sda2 / xfs defaults,noatime,nodiratime,nobarrier 0 0/dev/sda3 swap swap defaults,noatime,nodiratime,nobarrier 0 0mount -o remount /datamount

Goal two: Reduce swap usage and even ban, stabilize disk I/O and network latency, and make MySQL performance more stable

1.vm.swappiness set to 5 or even 0, if not concerned about the occurrence of oom

echo ‘vm.swappiness = 5‘ >>/etc/sysctl.conf/sbin/sysctl -p

2.vm.dirty_background_ratio set to 5,vm.dirty_ratio is set to 10, so that dirty pages continue to be brushed into the disk, to avoid disk I/O write-down instantly time_wait

echo ‘vm.dirty_background_ration = 5‘ >>/etc/sysctl.confecho ‘vm.dirty_ratio = 10‘ >>/etc/sysctl.conf/sbin/sysctl -p

3.net.ipv4.tcp_tw_recycle and Net.ipv4.tcp_tw_reuse are set to dual 1, reducing network wait times and increasing efficiency

echo ‘net.ipv4.tcp_tw_recycle = 1‘ >>/etc/sysctl.confecho ‘net.ipv4.tcp_tw_reuse = 1‘ >>/etc/sysctl.conf/sbin/sysctl -p

MySQL Layer optimization

Choosing the MySQL version is especially important to find the right version for your business system to get the most out of your performance. The operating parameters are also the same, and need to be re-calibrated. Standardize the schema design and SQL writing, as well as standardize the operation and maintenance of the management process, the same will bring a small profit.

Goal One: Choose the right version and let MySQL start with a full confidence

Priority recommendation for Oracle MySQL, more and more new systems embracing the official 5.7.x version

Next, we recommend the Percona Branch version, where you can enjoy the free thread pool and audit plugin

Finally, the MARIADB branch version, in addition to the thread pool and audit plugin, here to enjoy the free black technology

Goal Two: Adjust the appropriate parameters, so that MySQL performance more stable

1. If you choose to use the Percona or MARIADB branch version, it is strongly recommended to turn on the thread pool

2. Set Default-storage-engine=innodb,innodb to meet more than 99% of business scenarios

3. Set the appropriate innodb_buffer_pool_size size, most of the single instance is the InnoDB table, it is recommended to set the physical memory 50%-70%

4. Set the appropriate innodb_flush_log_at_trx_commit and Sync_binlog values

设置双1，不丢数据，性能较低设置2和10，丢失一点数据，性能一般设置双0，数据不×××全，性能最高

5. Set innodb_file_per_table = 1, using a stand-alone table space

6. Set Innodb_data_file_path = Ibdata1:1g:autoextend to get good performance in high concurrency transactions

7. Set innodb_log_file_size=256m,innodb_log_files_in_group=2

8. Set long_query_time = 0.05 to log slow SQL for more than 50 milliseconds

9. Adjust the max_connection appropriately, the proposed setting Max_connection_error is more than 100,000, set Open_files_limit, Innodb_open_files, Table_open_cache, Table_definition_cache about 10 times times the Max_connection

10. It is not advisable to set oversized parameters tmp_table_size, max_heap_table_size, Sort_buffer_size, Join_buffer_size, Read_buffer_size, Read_rnd_ Buffer_size

11. Set key_buffer_size = 32M to turn off the query cache function

关闭QC需要在启动MySQL前配置query_cache_type = 0query_cache_size = 0

Goal Three: schema design and SQL writing according to the reference specification to help improve MySQL efficiency

1. All InnoDB tables are designed with a non-business purpose self-increment key

2. When the field type is sufficient, the length of the selection is as small as possible; Field properties try to add not NULL constraints

3. Do not use the text and BLOB field types, and must be split into child tables when necessary

4. When querying, try to fill in the required columns, do not query all columns, avoid serious random reading problems

5. General varchar (n) column index Yes, take the first 50% length can be

6. Sub-query processing performance is low, it is recommended to use join rewrite SQL

7. When multiple-table connection queries, the keyword type is as consistent as possible, and must have an index

8. When multi-table connection query, the filtered result set of small table as the driver table

优势：不需要的数据不会出现，SQL查询范围小，执行效率高

9. When a multi-table connection query is ordered, the sort field must be the driver table, otherwise the sort column will not go through the index

10. Multi-use composite index, less use of multiple independent indexes, especially columns with too small cardinality is not recommended to create an index

11. When using the paging function of SQL, it is much more efficient to select the keyword and primary key to be indexed and then execute.

Goal four: Optimize management and maintenance to make operations more efficient

1.online DDL cost is too high, machine performance is sufficient, it is recommended single table physical not more than 10G, single table row number not more than 100 million, the average line length does not exceed 8KB

2. No oom kill and lots of swap, don't worry about the MySQL process consuming too much memory

3. Do not run multiple instances when the hardware resources in a single instance run are still relatively tense

4. Regularly use Pt-duplicate-key-checker to check and delete duplicate indexes, periodically check and remove unused indexes with Pt-index-usage

5. Collect slow query log regularly, analyze it with pt-query-digest tools, and combine anemometer and other systems for slow query management to facilitate analysis and optimization

6. You can use Pt-kill to kill long-time SQL requests, there is an option in the Percona version innodb_kill_idle_transaction can also implement this function

7. You can use Pt-online-schema-change to complete the online DDL requirements for large tables

8. Regular use of pt-table-checksum, pt-table-sync to check and repair MySQL master-slave replication data differences

Core program: Before going online, change any one parameter, should do stress test, avoid slip through the line cause MySQL appear various crash.

Write at the end: The plan later on every detail of the theoretical analysis and stress testing, the first collation of writing, there may be imperfections, welcome messages and exchanges.

Highly recommended two teachers: Ye Jinlong and Wu Bingxi

Original link:
A more comprehensive MySQL optimization reference (previous)
More comprehensive MySQL optimization reference (next article)

Publication date: March 8, 2018 17 O'Clock

MySQL optimization core theory and practice