Attempts to mitigate MySQL write pressure and master-slave delays

Attempts to mitigate MySQL write pressure and master-slave delays

http://mp.weixin.qq.com/s?__biz=MzA5Njg5ODMzMg==&mid=208512935&idx=1&sn= A605bb3b2f944f7fdce820b940e0888b&scene=2&from=timeline&isappinstalled=0#rd

Most recent units need to use MySQL to store large amounts of log data.
The write pressure is high and there is a large master-slave delay.

The specific environment is as follows
MySQL 5.6.14
Server (single cpu,6 core, 12 thread 32G memory)
Server hard disk (total 33T,RAID5)

1 first try, scatter IO

Generally we use/dbdata mount points to store data files
/data mount point log file (redo log file,binlog,relay log, etc.)
The benefit is that the random Io is separated from the sequential IO and does not form contention.

The disadvantage is that the IO usage of the/data mount point is generally low.

Of course, this situation is not a big deal in a general-purpose database.

However, in an insert-dense database, the physical device usage of the data file is maintained at 100%. The physical device usage of the log file is typically around 5% or even lower.

At this point, you might consider moving some of the table's data files to the physical device where the log files reside to balance the use of IO resources.

MySQL's datadir at/dbdata mount point,
The Police_im_user_mac database is actually pointing to the mount point of the/data via a soft link.
In this way, a portion of the random Io is dispersed to the/data mount point, reducing the pressure on the/dbdata mount point and reducing the CPU waiting

2 second try, a library a table

Because MySQL 5.6 's multithreaded replication is database-based.
And we have a large write pressure on each table, so we have modified the structure, with only one table per database. Then start multi-threaded replication (which is actually multiple SQL threads), and set the transport compression

Slave_compressed_protocol=on (Master,slave all need to be configured) slave_parallel_workers=10

3 third attempt, modifying parameters

Innodb_flush_log_at_trx_commit = 0
Innodb_support_xa=0
Sync_binlog=0
Increase Innodb_buffer_pool_size
Set the innodb_max_dirty_pages_pct to a larger
Set the Redolog file size to a larger

With these modifications, the write pressure and the master-slave delay have been greatly alleviated.
However, when you start Slave_parallel_workers, you may receive a 1062th error after you recover from a replication interrupt that occurs due to an SQL thread exception

This is because the thread synchronization mechanism for multiple SQL threads may be problematic, and once replication is interrupted, the phenomenon is equivalent to slave an abnormal shutdown.

Attempts to mitigate MySQL write pressure and master-slave delays