MySQL Tuning strategy

1, hardware layer related optimization

• Modify Server BIOS settings

• Choose Performance Per Watt Optimized (DAPC) mode to maximize CPU performance.

• Memory Frequency (RAM frequency) Select Maximum performance (best performance)

• In the Memory settings menu, enable node interleaving to avoid NUMA issues

2. Disk I/O related

• Using SSD drives

• In the case of disk array storage, it is recommended that the array card be equipped with both a cache and a BBU module to significantly increase IOPS.

• RAID level try to choose RAID10 instead of RAID5.

3. File System Layer Optimization

• Use the Deadline/noop two I/O schedulers, and never use CFQ

• Use XFS file system, do not use EXT3;EXT4 reluctantly, but the volume of business is very large, you must use XFS;

• File system mount parameter added: Noatime, Nodiratime, nobarrier several options (Nobarrier is XFS file system specific);

4. Kernel parameter optimization

• Modify the vm.swappiness parameter to reduce swap utilization. Rhel7/centos7 above is carefully set to 0, may occur oom

• Adjust the vm.dirty_background_ratio, vm.dirty_ratio kernel parameters to ensure that dirty data is continuously flushed to disk, avoiding instantaneous I/O writes. Produce wait.

• Adjust net.ipv4.tcp_tw_recycle, net.ipv4.tcp_tw_reuse are set to 1, reduce time_wait, improve TCP efficiency.

5, MySQL parameter optimization recommendations

• It is recommended to set DEFAULT-STORAGE-ENGINE=INNODB, it is strongly recommended not to use the MyISAM engine.

• Adjust the size of the innodb_buffer_pool_size, if it is a single instance and most of the InnoDB engine table, consider setting it to about 50%-70% of physical memory.

• Set innodb_file_per_table = 1, using a stand-alone tablespace.

• Adjust Innodb_data_file_path = Ibdata1:1g:autoextend, do not use the default 10M, in high concurrency scenarios, performance will be greatly improved.

• Setting up the innodb_log_file_size=256m, setting the innodb_log_files_in_group=2, basically satisfies most of the application scenarios.

• Adjust the max_connection (maximum number of connections), Max_connection_error (maximum number of errors) settings, set according to the size of the business volume.

• In addition, Open_files_limit, Innodb_open_files, Table_open_cache, Table_definition_cache can be set about 10 times times the size of max_connection.

• Key_buffer_size recommended small, 32M or so, it is also recommended to close the query cache.

• Mp_table_size and max_heap_table_size set not too large, in addition Sort_buffer_size, Join_buffer_size, Read_buffer_size, Read_rnd_buffer_ Size and so on are not too large.

This article is from the "Little Water Drop" blog, please make sure to keep this source http://wangzan18.blog.51cto.com/8021085/1754804

MySQL tuning policy