MySQL Performance Optimization learning note (6) Database configuration optimization & Hardware optimization

First, the operating system configuration optimization:
1. On the network side, modify the/etc/sysctl.conf file, increase the number of TCP supported queues, reduce the resource recovery when disconnected.
2. Limit the number of open files. Modify the/etc/security/limits.conf file to add a bit of content to modify the limit on the number of open files.
3. Turn off firewall software such as Iptables,selinux.

Second, System configuration optimization
innodb_buffer_pool_size--recommended for 75% of total memory
innodb_buffer_pool_instances--can control the number of buffer pools
InnoDB Log buffer size
Innodb_read_io_threads, indoor_write_io_threads--determines the number of IO processes to read and write, default to 4
innodb_flush_log_at_trx_commit--Key Parameters
innodb_file_per_table--key parameters, controlling each table to use a separate table space, default to OFF, that is, all tables will establish shared tablespace. Recommended to ON, when deleting a table, you can reclaim space in time and improve IO efficiency
Innodb_stats_on_metadata configure MySQL to refresh the statistics of the InnoDB table.

Iii. third-party configuration tools
Https://tools.percona.com/wizard

Four, hardware optimization
CPU: The single core frequency is high, do not exceed 32 cores.
IO:RAID0 multiple disks connected to one hard drive, IO best
RAID1 Single Backup
RAID5 Parity Recovery
The combination of raid1+0 1 and 0.
Disk array: 1. Often used for high-availability 2. Some storage array sequential read and write efficiency is high, but random read and write unsatisfactory.

MySQL Performance Optimization learning note (6) Database configuration optimization & Hardware optimization