The effect of InnoDB's redo log refresh policy on the insertion speed

Core control parameters

InnoDB Storage Engine controls the policy of the Redo log flushing to disk through the Innodb_flush_log_at_trx_commit parameter

To view parameter values:

 like ' Innodb_flush_log_at_trx_commit ';

To modify a parameter value:

Set = 1;

Three Refresh Strategies

Policy one: The parameter value is 0, indicating that transaction commits are not forced to write to the redo log, the time to flush to disk control has the master thread control, the master thread will be synchronized every 1 seconds to refresh the operation.

Policy two: The parameter value is 1, indicating that all logs of the transaction must be written to disk when the transaction commits.

Policy three: The parameter value is 2, which indicates that the redo log is written to the redo log file when the transaction commits, but is only written to the file system cache, and does not immediately synchronize the flush operation.

Test Cases

Create a table Load_data table to store data:

CREATE TABLE load_data (    INT  UNSIGNED,    CHAR(+));

Create a stored procedure loaddata to load data:

CREATE PROCEDURELoadData ( Number INTUNSIGNED)BEGINDECLAREIINTUNSIGNEDDEFAULT 1;DECLAREVCHAR( -)DEFAULTREPEAT ('T', -);  whileI<=  Number DoINSERT  intoLoad_dataSELECTI, V;COMMIT;SETI=I+ 1;END  while;END;

Speed Comparison of inserting 1 million data

Test of strategy One:

Mysql>Show variables like 'Innodb_flush_log_at_trx_commit';+--------------------------------+-------+|Variable_name|Value|+--------------------------------+-------+|Innodb_flush_log_at_trx_commit| 0     |+--------------------------------+-------+1Rowinch Set(0.00sec) MySQL>Call LoadData (1000000); Query OK,0Rows Affected (38.84Sec

the test of strategy two (can not endure, have seen a episode of TV series):

Mysql>Show variables like 'Innodb_flush_log_at_trx_commit';+--------------------------------+-------+|Variable_name|Value|+--------------------------------+-------+|Innodb_flush_log_at_trx_commit| 1     |+--------------------------------+-------+1Rowinch Set(0.00sec) MySQL>Call LoadData (1000000); Query OK,0Rows Affected ( to min 38.67Sec

test for strategy three:

Mysql>Show variables like 'Innodb_flush_log_at_trx_commit';+--------------------------------+-------+|Variable_name|Value|+--------------------------------+-------+|Innodb_flush_log_at_trx_commit| 2     |+--------------------------------+-------+1Rowinch Set(0.03sec) MySQL>Call LoadData (1000000); Query OK,0Rows Affected (1 min 34.67Sec

Advantages and disadvantages of each strategy

Strategy one: significantly faster than the strategy two faster, because there is a lot less flush to disk IO operations, these IO operations are very expensive, it is a waste of time, strategy at this point saves a lot of time, but the price is likely to occur in the last second of the transaction loss situation, which does not meet the persistence of the transaction. If that second just pick up 10 million of the list, the result of the server crashed, can not find the data back, it is only hehe.

Strategy Two: The advantages are obvious, is serious in line with the persistence of the transaction, each write redo log, when the outage occurs, you can accurately recover. The disadvantage is also obvious, is the time cost of pay is very big.

Strategy Three: The use of caching mechanisms to reduce unnecessary IO operations, or the integration of multiple IO operations into one IO, increase the rate. The downside is that you lose the data that has not been flushed from the file system cache to the redo log when the outage occurs.

In general, there is a reason for the existence of each strategy. Existence is reasonable, various strategies have its application scenarios, only understand their situation, in order to better use these strategies.

The effect of InnoDB's redo log refresh policy on the insertion speed