A more comprehensive MySQL optimization reference (previous)

Transferred from: http://imysql.com/2015/05/24/mysql-optimization-reference-1.shtml

This article has compiled some common methods of MySQL optimization, to do a simple summary sharing, to help those who do not have full-time MySQL DBA to do basic optimization work, as for the specific SQL optimization, most of the appropriate index to achieve the results, more complex needs to be specific analysis, can refer to the site of some optimization cases or contact me, below have my contact information. This is the last article.

1, hardware layer related optimization 1.1, CPU-related

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

1, choose Performance Per Watt Optimized (DAPC) mode, play CPU maximum performance, run DB This usually requires a high computational capacity of the service should not consider power saving;2, turn off C1E and C states options, The purpose is also to improve the CPU efficiency, 3, memory Frequency (RAM frequency) Select Maximum performance (best performance);4, in the Memory settings menu, enable node interleaving, Avoid NUMA issues;

1.2. Disk I/O related

The following are some of the measures that can be optimized for disk I/O, based on the magnitude of the IOPS performance boost:

1, the use of SSD or PCIe SSD devices, at least hundreds of times times or even tens of times the IOPS increase;
2, the acquisition of the array card with the cache and the Bbu module, can significantly increase the IOPS (mainly refers to mechanical disk, SSD or PCIe SSD except. At the same time, the health of the cache and the Bbu module needs to be checked regularly
   Ensure that data is not lost when accidental);
3, when there is an array card, set the array write policy is WB, even Force WB (if there is double protection, or the data security requirements are not particularly high), the use of WT policy is strictly forbidden. and close the array read-ahead policy,
   Basically is the chicken, the use is not big;
4, as far as possible to choose RAID-10, rather than RAID-5;
5, the use of mechanical disk, as far as possible to choose high-speed, such as the use of 15KRPM, rather than 7.2KRPM disk, a few money;

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 these two kinds of i/o Scheduler, do not use CFQ (it is not suitable for running DB class service);
2, the use of XFS file system, do not use EXT3;EXT4 reluctantly, but the volume of business is very large, you must use XFS;3, File system mount parameter added: Noatime, Nodiratime, Nobarrier several options (Nobarrier is specific to the XFS file system);

2.2. Optimization of other kernel parameters

The appropriate values are set for key kernel parameters in order to reduce the propensity for swap and to allow no significant fluctuations in memory and disk I/O, resulting in an instantaneous peak load:

1, set the vm.swappiness to 5-10 or so, or even set to 0 (RHEL 7 is carefully set to 0, unless you allow oom kill to occur), to reduce the chance of using swap;
2, set Vm.dirty_background_ratio to 5-10, set Vm.dirty_ratio to twice times of it, to ensure that the dirty data can be continuously flushed to the disk, to avoid an instant i/o Write,
   produce a severe wait (similar to innodb_max_dirty_pages_pct in MySQL);
3,the net.ipv4.tcp_tw_recycle, Net.ipv4.tcp_tw_reuse are set to 1, reduce time_wait, improve TCP efficiency;
4, as for the network read_ahead_kb, nr_requests These two parameters, I tested, found that the read-write mixed-based OLTP environment is not very significant (should be more effective in reading sensitive scenes),
   But maybe it's me. There are problems with the test method, and I can adjust it at my discretion;

A more comprehensive MySQL optimization reference (previous)