MySQL Storage write performance critical jitter analysis

Case Description:

The write performance of storage is maintained at around 10MB through Iostat, and the performance of database is worse because of poor write performance.

After two hours, Iostat found that the write performance of the system has been able to more than 100MB, the database performance is also back to normal.

In other words, in the system, database monitoring, there is a performance trough, storage write performance jitter, why?

First, the principle process

The process is analyzed by the above schematic diagram:

1, the transaction commits, modifies the data in the Buffer_pool form the dirty page, and simultaneously generates the redo log, writes the log to the disk redo logs;

2, the transaction submitted successfully;

Suppose that there are three groups of redo log files ,

3, continue to commit the transaction, modify the data, write redo log, if the value of innodb_log_file_size is very small, but the resulting redo log information is very large, so that the first group of redo logs will soon be written full, the log file will be switched;

4, if the three groups of redo log files are written full, you need to log coverage;

5, If the need to overwrite the redo log log file records of dirty page information has not been written to the disk, the database will be actively capitalized to the force of the Dirty page information to the disk ;

6, in the Redo log file corresponding to the dirty page brush into the disk process, the transaction has no way to submit, affecting the business.

Second, the principle analysis

1. Check the log file switching time by modifying the redo log

With shell> ls-l/mydata/ib_logfile*, check the time of the start and last modification, and calculate the logfile switch time.

2, if the Redo log file switching time is too short, that is, frequent switching, it is easy to cause write jitter

1, the normal business busy will be 10-20 minutes;

2, if is shorter than the general time , the document is small, switching frequently.

Third, solve the write jitter problem

1. Increase the number of redo log files

Mysql>Show global variables like 'Innodb_log_files_in_group';+---------------------------+-------+|Variable_name|Value|+---------------------------+-------+|Innodb_log_files_in_group| 2     |+---------------------------+-------+1Rowinch Set(0.01Sec

2. Enlarge log file capacity

Mysql>Show global variables like 'innodb_log_file_size';+----------------------+----------+|Variable_name|Value|+----------------------+----------+|Innodb_log_file_size| 50331648 |+----------------------+----------+1Rowinch Set(0.01Sec

3, improve the log file write performance: Put the log files on the write performance of high-quality disk

Mysql>Show global variables like 'Innodb_log_group_home_dir';+---------------------------+-------+|Variable_name|Value|+---------------------------+-------+|Innodb_log_group_home_dir|./    |+---------------------------+-------+1Rowinch Set(0.01Sec

Attention:

The above three parameters are non-dynamic parameters , need to be modified in the configuration file/etc/my.cnf save, and then restart the database instance to take effect.

MySQL Storage write performance critical jitter analysis