Shulou(Shulou.com)06/01 Report--

MySQL master-slave delays can be divided into the following four categories

I. mixed mode has no index or SQL is slow

On the slave library

Pager grep-v Sleep;show processlist sees the SQL that is being executed

Solution:

If 1.SQL is relatively simple, check if the index is missing and add the index. For example, the update operation where condition has no index.

2.SQL is more complex, such as insert into select from statements. If group by is included in select, it may be inefficient to associate multiple tables. You can change binlog_format to row in the main library.

two。 There are large transactions on the master database, resulting in slave library delay

Solution:

Communicate with developers, increase the cache, write to the database asynchronously, and reduce the large number of writes to DB.

three。 The master database is written frequently, and the pressure of the slave library can not keep up with the master library, resulting in delay.

The main phenomenon of this kind of reason is that there are a lot of IUD (add, change and delete) operations in the database, and slave can not catch up with the write operation of the main database due to the single thread of sql_thread.

Solution:

1. Upgrade the hardware configuration of the main library, such as ssd and fio

two。 Set the log refresh rate:

Set global_sync_binlog=0

Innodb_flush_log_at_trx_commit=0 flushes the contents of log_buffer to logfile every second and synchronously flushes the logs to disk. In this mode, writing to disk is not actively triggered when the transaction is committed. Innodb_flush_log_at_trx_commit=1 flushes the log_buffer to logfile each time commit and flushes the logs synchronously to disk. Innodb_flush_log_at_trx_commit=2 flushes the log_buffer to logfile every time commit, but the flush (flush to disk) operation does not occur at the same time, flushing the log to disk every second.

four。 There are a large number of myisam tables in the database, causing delays in the slave library during backup

Because the xtrabackup tool performs flush tables with read lock at the end of the backup, the database is locked for consistent backup, and then the myisam table lock prevents the slave sql_thread thread from running and leads to hang.

At present, a better solution to this problem is to modify the table whose structure is innod storage engine.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.