Handling high I/O resource usage of MySql database (InnoDB storage type)

1. Check and confirm processes with high I/O usage

Windows

1. Open Task Manager to view processes that use more resources.

2. Resource Monitor-Disk-Sort by read/write for further verification:

Linux

1. Run the top command to view processes that use more resources.

Parameters: 78.2%wa : The value is relatively high, indicating a serious hardware I/O wait problem in the CPU, usually caused by disk read/write I/O

2. Use the iotop command for further verification

[root@ebs-xxx ~]# iotop -o    # Display only processes occupying I/O
# If prompted that iotop is not installed, run the following command to install it:
[root@ebs-xxx ~]# yum install -y iotop

2. Optimize innodb-format table database configuration

Modify the mysql configuration file by adjusting or adding the following parameters to solve the problem of mysql occupying I/O resources:

innodb_flush_log_at_trx_commit = 2    # Restart the mysql service after saving

Note:

★ This solution is mainly effective for database table storage type: InnoDB.
★ innodb_flush_log_at_trx_commit=2 will sacrifice a certain degree of data security. If you have high requirements for data security and reliability, such as payment, financial, and large order system types, it is recommended not to adjust it.