There're MySQL global variable settings that will help for improving the speed of SQL apply in slave db.
That would solve the issue of slave lagging.
slave_rows_search_algorithms comes to the rescue.
slave_rows_search_algorithms
default: TABLE_SCAN,INDEX_SCAN
improvement: INDEX_SCAN,TABLE_SCAN,HASH_SCAN
select @@global.slave_rows_search_algorithms;
set @@global.slave_rows_search_algorithms="INDEX_SCAN,TABLE_SCAN,HASH_SCAN";
my.cnf:
slave_rows_search_algorithms="INDEX_SCAN,TABLE_SCAN,HASH_SCAN"
The second global variable (innodb_flush_log_at_trx_commit) can be set to 2
if the slave lagging issue continues.
default: 1
improvement: 2
select @@global.innodb_flush_log_at_trx_commit;
set @@global.innodb_flush_log_at_trx_commit=2;
my.cnf:
global.innodb_flush_log_at_trx_commit=2
Side Effect:
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash
留言列表