Shulou(Shulou.com)06/01 Report--

Background description: the OA system of my friend unit recently completed an upgrade and transformation. The MySQL storage data used at the back end has been online for a month, complaining that phone calls began to come one after another, either here could not be opened, or there was no response. Some people likened the upgrade to an old car, which became slower and slower, and the problem was imminent. We must think of countermeasures immediately. Due to the deployment of standard documents, and all kinds of tests were done before going online, so, online investigation, failed, turned out the implementation documents, read one by one, failed, so I remembered that once a business system, also encountered a similar situation, and then alleviated through various optimizations, then there is the following, "MySQL optimization core theory and practice".

Explanation: the theoretical part of this article comes from teacher Ye's blog post, and the practical part comes from work accumulation and many netizens who love MySQL technology sharing. The original intention of finishing is to gain a more in-depth understanding of MySQL optimization, master more MySQL optimization technology, improve yourself, and give back to all netizens who love technology sharing.

Optimization of hardware layer

The newly purchased server runs in the energy-saving mode by default. In the business scenario with large concurrent visits, the database performance will lag behind and cause a lot of delay, which will eventually drag down the business system. At the same time, improper disk selection and array card setting will also make the database performance become the bottleneck of the whole business system.

Goal 1: completely turn off the energy-saving mode and let MySQL run in high-performance mode

1. Turn off CPU energy saving mode

Find the OPI Link Speed Select option and select Max Performance

two。 Turn off memory energy saving mode

Find the Memory Speed option, select the Max Performance find Power C-States option, select the Disable find C1 Enhanced Mode option, select Disable goal 2: turn off NUMA, so that CPU can always use memory efficiently

Close NUMA

Find the Socket Interleave option and select Non-NUMA goal 3: comprehensively improve the performance of IOPS, so that the disk Icano is no longer delayed.

1. When the funds are sufficient, purchase SSD or even PCIe-SSD

SSD and PCIe-SSD bring not only surprises, but also more down-to-earth. From then on, disk Icano is no longer a demon.

two。 Mechanical disk with array card, Cache strategy, BBU battery, RAID-10,15KRPM

Array cards face multiple mechanical disks calmly. BBU battery ensures that the Cache strategy in high-performance mode does not lose data. Cache strategy selects the Read Policy option of Write Back or even Always Write Back array pre-reading, and chooses Normal to use RAID-10. The performance is higher than that of RAID-5 using 15KRPM high-speed disk, and the performance is better than 7.2KRPM disk.

Note: the parameters set by the server hardware come from IBM X3650M3.

Optimization of system layer

There are also many aspects worth optimizing in the operating system, which can also significantly improve the performance of IOPS. In addition, SWAP should be used less, which will not save lives, but will put the business system on the brink of collapse.

Goal 1: comprehensively improve the performance of IOPS, so that the database is no longer responsible

1. Configure a reasonable Imax O scheduler

Mechanical disk with deadline, execute the command echo deadline > / sys/block/sda/queue/scheduler solid state disk with noop, execute the command echo noop > / sys/block/sda/queue/scheduler note that sda is the partition where the data file resides

two。 Try to use XFS in the file system. If you are still using ext4, I hope it is just a transitional phase.

Add noatime,nodiratime,nobarrier to the 3.mount parameter

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 2: reduce the tendency to use SWAP or even ban it, stabilize the disk and reduce the waiting time on the network, so as to make MySQL performance more stable.

Set 1.vm.swappiness to 5 or even 0, if you don't care about the occurrence of OOM

Echo 'vm.swappiness = 5' > > / etc/sysctl.conf/sbin/sysctl-p

2.vm.dirty_background_ratio is set to 5dyvm.dirtypages ratio to 10, which allows dirty pages to be brushed onto the disk continuously, so as to avoid the instant writing of TIME_WAIT on disk Ibino.

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 double 1s to reduce network waiting time and improve efficiency

Optimization of echo 'net.ipv4.tcp_tw_recycle = 1' > > / etc/sysctl.confecho 'net.ipv4.tcp_tw_reuse = 1' > > / etc/sysctl.conf/sbin/sysctl-pMySQL layer

It is particularly important to choose the right version of MySQL to find the version that is suitable for the business system in order to achieve greater performance. The same is true of operating parameters, which need to be repeatedly considered and adjusted. Standardizing schema design and sql writing, as well as standardizing the operation and maintenance management process after launch, will also bring a lot of benefits.

Goal 1: choose the right version and let MySQL start with confidence.

1. Oracle MySQL is preferred, and more and more new systems are embracing the official 5.7.x version.

two。 Secondly, the branch version of Percona is recommended, where you can enjoy free thread pool and audit plugin

3. Finally, there is the branch version of MariaDB, where you can enjoy the free cool techs, in addition to thread pool and audit plug-in.

Goal 2: adjust the appropriate parameters to make the performance of MySQL more stable

1. If you choose to use Percona or MariaDB branch version, it is highly recommended to enable thread pool

two。 Configure default-storage-engine=innodb,innodb to meet more than 99% of the business scenarios

3. Set the appropriate innodb_buffer_pool_size size. Most of the single instances are innodb tables. It is recommended to set 50% of physical memory.

4. Set appropriate innodb_flush_log_at_trx_commit and sync_ binlog values

Set double 1, do not lose data, lower performance settings 2 and 10, lose a little data, generally set double 0, data is not × × complete, and have the highest performance.

5. Set innodb_file_per_table = 1 to use independent tablespaces

6. Set innodb_data_file_path = ibdata1:1G:autoextend to achieve good performance in highly concurrent transactions

7. Set up innodb_log_file_size=256M,innodb_log_files_in_group=2

8. Set long_query_time = 0.05to record slow SQL of more than 50 milliseconds.

9. Adjust max_connection appropriately. It is recommended to set max_connection_error to more than 100000, and set open_files_limit, innodb_open_files, table_open_cache and table_definition_cache to be about 10 times higher than max_connection.

10. It is not appropriate to set excessively large 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 and turn off query cache function

To close QC, you need to configure query_cache_type = 0query_cache_size = 0 before starting MySQL. Goal 3: Schema design and SQL writing are set according to the reference specification, which helps to improve the efficiency of MySQL.

1. All innodb tables design a self-incrementing column with no business use as the primary key

two。 When the field type is satisfied, the length is as small as possible, and the field properties are constrained by NOT NULL as far as possible.

3. Try not to use TEXT and BLOB field types, and split them into child tables when necessary

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

5. Generally speaking, the varchar (n) column is indexed by taking the first 50% of the length.

6. The performance of subquery processing is low. It is recommended to use JOIN to rewrite SQL instead.

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

8. When multiple tables join queries, the tables with small filtered result sets are used as driving tables.

Advantages: unwanted data will not appear, SQL query scope is small, execution efficiency is high.

9. When multiple tables join queries and there is sorting, the sort field must be in the driving table, otherwise sorting will not leave the index.

10. It is not recommended to create indexes with multiple composite indexes and less independent indexes, especially for columns with too small cardinality.

11. When using SQL with paging function, selecting the keyword and primary key to do the index, and then executing it, it will be much more efficient.

Goal 4: optimize management and maintenance to make operation and maintenance more efficient

When the cost of 1.online DDL is too high and the machine performance is sufficient, it is recommended that the physical size of a single table should not exceed 10G, the number of rows in a single table should not exceed 100 million, and the average row length should not exceed 8KB.

two。 Without OOM KILL and heavy use of SWAP, you don't have to worry about MySQL processes taking up too much memory

3. Do not run multiple instances when the hardware resources are still tight in single instance operation.

4. Check and delete duplicate indexes regularly with pt-duplicate-key-checker, and check and delete less used indexes with pt-index-usage periodically

5. Collect slow query log regularly, analyze it with pt-query-digest tools, and manage slow query with Anemometer and other systems, so as to facilitate analysis and optimization.

6. You can use pt-kill to kill long-time SQL requests, and there is an option in the Percona version that innodb_kill_idle_transaction can also do this.

7. You can use pt-online-schema-change to fulfill the ONLINE DDL requirements of large tables

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

Core program: before the launch, to change any parameter, you should do a stress test to avoid causing a variety of CRASH in MySQL.

Write at the end: plan to carry out theoretical analysis and stress test on each detail later, and sort out the writing for the first time, there may be imperfections, welcome to leave a message and communicate.

Two powerful teachers are highly recommended: Ye Jinrong and Wu Bingxi

Original text link:

More comprehensive MySQL optimization reference (part I)

More comprehensive MySQL optimization reference (part two)

Release time: 17:00 on March 8, 2018

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.