MySQL Latency solution from server

To perform the show slave status from the server, you can view many of the synchronized parameters, and we need to pay special attention to the following parameters:
The name of the primary server binary log file that is currently being read by the I/O thread in Master_log_file:slave
Read_master_log_pos: In the current primary server binary log, the I/O thread in slave has already read the location
Relay_log_file:sql the name of the trunk log file that the thread is currently reading and executing
Relay_log_pos: Where the SQL thread has been read and executed in the current trunk log
Relay_master_log_file: Name of the primary server binary log file containing most recent events executed by SQL thread
Whether the SLAVE_IO_RUNNING:I/O thread is started and successfully connected to the primary server
Whether the Slave_sql_running:sql thread is started
Seconds_behind_master: The time gap between the subordinate server SQL thread and the subordinate server I/O thread, in seconds.

Synchronization latency from the library occurs
1, show slave status display parameter Seconds_behind_master not 0, this value may be very large
2, show slave status display parameters Relay_master_log_file and Master_log_file display bin-log number difference is very large, the bin-log in from the library is not synchronized in time, So recently executed Bin-log and the current IO thread read the Bin-log difference is very large
3, MySQL from the Library Data directory has a large number of Mysql-relay-log logs, the log synchronization is completed after the system will be automatically deleted, there are a large number of logs, the master-slave synchronization delay is very serious

A, MySQL database master-Slave synchronization delay principle
MySQL master-Slave synchronization principle:
The main library for write operations, sequential write Binlog, from the library single-threaded to the main library sequential read "write Operation Binlog", from the library to Binlog in the local execution (random write), to ensure that the master-slave data logically consistent.
MySQL master-slave replication is a single-threaded operation, the main library for all DDL and DML generation Binlog,binlog is sequential write, so efficient, slave slave_io_running thread to the main library to fetch logs, high efficiency, next, the problem comes, The slave slave_sql_running thread implements the DDL and DML operations of the main library in slave. The IO operations of DML and DDL are immediately, not sequential, expensive, and may also generate lock contention on other queries on slave, since slave_sql_running is also single-threaded, so a DDL card master has to be executed for 10 minutes, Then all subsequent DDL will wait for the DDL to execute before continuing, which results in a delay.
A friend will ask: "The same DDL on the main library also needs to perform 10 points, why slave delay?" "The answer is that master can be concurrent and slave_sql_running threads are not."

b, MySQL database master-Slave synchronization delay is how to produce?
When the TPS for the main library is high, the number of DDL generated exceeds the range that slave a SQL thread can withstand, and the delay is generated, and of course there is the possibility of a lock wait with slave's large query statement.
The first reason: the database in the business of reading and writing pressure is too large, CPU load, network card load, HDD random io too high
Secondary reason: The performance impact of read-write binlog, network transmission delay.

C, MySQL database master-Slave synchronization delay solution.

Architectural aspects
1. The implementation of the business Persistence layer adopts the sub-Library architecture, and the MySQL service can be expanded in parallel and distributed under pressure.
2. Single library read/write separation, one master multi-slave, main write from read, decentralized pressure. This protects the main library from library pressure higher than the main library.
3. The service infrastructure joins the Memcache or Redis cache layer between the business and MySQL. Reduce the read pressure on MySQL.
4. Different business MySQL physically placed on different machines, decentralized pressure.
5. Use a better hardware device than the main library as a slave

Summary, MySQL pressure is small, the delay will naturally become smaller.

Hardware aspects

1. Use good server, such as 4u than 2u performance is obviously good, 2u than 1u performance is obviously good.
2. Storage with SSD or disk array or SAN, improve the performance of random write.
3. The master-Slave guarantee is under the same switch and is a million-gigabit environment.
Summary, the hardware is strong, the delay will naturally become smaller. In a word, the solution to narrowing the delay is spending and spending time.

MySQL master-slave sync acceleration

1, Sync_binlog at slave end set to 0
2. –logs-slave-updates the updates received from the server from the primary server are not credited to its binary log.
3, directly disable the slave end of the Binlog
4, slave end, if the storage engine used is Innodb,innodb_flush_log_at_trx_commit =2

Optimized from the file system's own attribute perspective
Master side
Modifies the ETime property of a file in a Linux, UNIX file system, because the OS writes the read operation back to disk whenever the file is read, which is not necessary for database files with frequent read operations and only increases the burden on the disk system to affect I/O performance. You can organize the operating system to write atime information by setting the Mount property of the file system, and the operation on Linux is:
Open/etc/fstab, plus noatime parameter
/dev/sdb1/data ReiserFS noatime 1 2
Then mount the file system again
#mount-oremount/data

PS:
The main library is write, the data security is high, such as Sync_binlog=1,innodb_flush_log_at_trx_commit = 1 settings are required
and slave do not need this high data security, It can be said that Sync_binlog is set to 0 or close Binlog,innodb_flushlog can also be set to zero to improve the efficiency of SQL execution
1, sync_binlog=1 o
MySQL provides a sync_ Binlog parameters to control the database Binlog brush to disk. The
Default, sync_binlog=0, means that MySQL does not control the refresh of Binlog, which is controlled by the file system by its own cache. The performance at this time is the best, but the risk is also the largest. Once the system crash, all binlog information in the Binlog_cache will be lost.
If sync_binlog>0 represents every Sync_binlog transaction commit, the MySQL call to the file system refreshes the cache brush down. The safest is sync_binlog=1, indicating that each transaction commits, MySQL will binlog brush down, is the most secure but the most performance loss of the settings. In this case, the system can lose 1 transaction data if the host operating system where the database resides is damaged or suddenly loses power.
However, although binlog is sequential io, setting sync_binlog=1, multiple transactions are committed at the same time, which also greatly affects MySQL and IO performance. Although the
can be mitigated by a group commit patch, the high frequency of refreshes has a significant impact on Io. For systems with high concurrency transactions, the system write performance gap of
Sync_binlog set to 0 and set to 1 can be up to 5 times times or more.
so many MySQL dba Sync_binlog are not the safest 1, but 2 or 0. This sacrifices a certain amount of consistency, allowing for higher concurrency and performance.
By default, Binlog is not synchronized with the hard disk each time it is written. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the Binlog is lost. To prevent this, you can use the Sync_binlog global variable (1 is the safest value, but also the slowest), so that Binlog synchronizes with the hard disk after every n binlog write. Even if Sync_binlog is set to 1, there is a possibility of inconsistency between the table content and the Binlog content when a crash occurs.

2, Innodb_flush_log_at_trx_commit (this works well)
Complaining that InnoDB is 100 times times slower than MyISAM? Then you probably forgot to adjust the value. The default value of 1 means that every single transaction commit or out-of-transaction instruction requires the log to be written to (flush) the hard disk, which is time consuming. Especially when using the battery-powered cache (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table is possible, it means to write to the system cache instead of writing to the hard disk.
The log will still flush to the hard drive every second, so you will generally not lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security side is poor, even if MySQL hangs may also lose the transaction data. A value of 2 will only lose data if the entire operating system is hung.

3, LS (1) command can be used to list the files Atime, CTime and Mtime.
The Atime file's access time is changed when you read the file or execute the file.
The Create time of the CTime file changes with the contents of the Inode as it is written to the file, changing owner, permission, or link settings
Modified time of the Mtime file changes when writing to a file with changes to the contents of the file
LS-LC filename Lists the CTime of the file
Ls-lu filename Lists the atime of the file
ls-l filename Lists the mtime of the file
Stat filename Lists Atime,mtime,ctime
Atime is not necessarily modified after accessing the file
Because: When using the Ext3 file system, if the Noatime parameter is used on mount, the Atime information is not updated.
These three time stamp are placed in the inode. If the mtime,atime changes, the inode will be changed, since the inode changed, then the CTime also changed.
The reason to use Noatime in Mount option is not to make too many changes to the file system and improve read performance

MySQL Latency solution from server