Analysis of MySQL latency issues and data flushing strategy process

1. MySQL replication process

The official document process is as follows:

MySQL latency issues and data flushing strategies

1. Absolute delay, relative synchronization

2. For pure write operations, under the online standard configuration, the pressure on the slave database is greater than that on the master database. At least the slave database has relaylog written to it.

2. Analysis of MySQL delay issues

1. Frequent DML requests to the main database

Cause: The master database writes data concurrently, while the slave database applies logs in a single thread, which can easily cause relaylog accumulation and delay.

Solution: Sharding is used to break up write requests. Consider upgrading to MySQL 5.7+ and enabling parallel replication based on logical clocks.

2. The main database executes large transactions

Cause: For example, the master database takes a long time to update a large table. When the master and slave databases have similar configurations, the slave database also needs to spend almost the same amount of time to update the large table. At this time, slave database delays begin to accumulate, and subsequent events cannot be updated.

Solution: Split large transactions and submit them in time.

3. The main database executes DDL statements on the large table

Cause: The DDL has not started to execute and is blocked. The position is checked to be unchanged. The DDL is being executed, and the single-threaded application causes increased latency and the position is unchanged.

Solution: Find the query that blocks the DDL or write operation, kill the query, and allow DDL to be executed normally on the slave database; execute during the off-peak period of business, and try to use a high version of MySQL that supports OnlineDDL.

4. Inconsistent configuration of master and slave instances

Reasons: Hardware: The master instance server uses SSD, while the slave instance server uses ordinary SAS disk, CPU main frequency is inconsistent, etc. Configuration: such as inconsistent RAID card write strategy, inconsistent OS kernel parameter settings, inconsistent MySQL disk write strategy (innodb_flush_log_at_trx_commit and sync_binlog, etc.), etc.

Solution: Try to unify the configuration of DB machines (including hardware and option parameters); even for some OLAP businesses, the hardware configuration of the slave database instance is higher than that of the master database.

5. The slave library itself is under too much pressure

Cause: The slave database executes a large number of select requests, or most of the select requests of the business are routed to the slave database instance, or even a large number of OLAP businesses, or the slave database is being backed up, etc. At this time, the CPU load may be too high, the IO utilization rate may be too high, etc., causing the SQLThread application to be too slow.

Solution: Create more Xi'an database training slaves to disperse read requests and reduce the pressure on existing slave instances.

You can also adjust the disk flushing parameters innodb_flush_log_at_trx_commit=0 and sync_binlog=0 to relieve IO pressure and reduce master-slave delay.

3. CPU overload problem during promotion period

Phenomenon:

High concurrency leads to excessive CPU load, which prolongs the request processing time and gradually causes backlog, eventually leading to service unavailability; a large number of slow SQL statements lead to excessive CPU load.

Solution:

Basically, database master-slave switching is prohibited or carefully considered. This cannot solve the fundamental problem and requires R&D cooperation to cure the SQL problem. Service downgrade can also be done. For containers, CPU capacity can be dynamically expanded. Negotiate with the business to start pt-kill to kill read-only slow SQL. Check whether the slow SQL problem can be solved by adding general indexes or joint indexes, but at this time, the impact of DDL on the database must be considered.

4. InnoDB flushing strategy

The MySQL innodb_flush_method parameter controls the opening and flushing mode of innodb data files and redolog. The document describes this parameter as follows:

There are three values: fdatasync (default), O_DSYNC, O_DIRECT

The default is fdatasync, calling fsync() to flush the data file and redolog buffer

When O_DSYNC is used, InnoDB will use O_SYNC to open and flush redolog, and use fsync() to flush data files.

When O_DIRECT is used, InnoDB uses O_DIRECT to open the data file and uses fsync() to flush the data file and redolog.

First, the file writing operation includes three steps: open, write, flush

The fsync(intfd) function mentioned most often above is used to flush the buffer related to the file pointed to by the fd file descriptor to the disk during flushing, and the flush is considered successful only after the metadata information (such as modification date, creation date, etc.) is flushed.

Using the O_DSYNC method to open the redo file means that when writing the log, the data is written to the disk and the metadata also needs to be updated before a success is returned.

O_DIRECT means that our write operation is written directly from MySQL innodb buffer to disk.

The specific data writing methods of these three modes are as follows:

fdatasync mode: When writing data, the write step does not need to be actually written to the disk to be completed (it may be returned as completed when it is written to the operating system buffer). The real completion is the flush operation, the buffer is handed over to the operating system to flush, and the file metadata information also needs to be updated to the disk.

O_DSYNC mode: log writing is done in the write step, while data file writing is done in the flush step through fsync

O_DIRECT mode: The data file is written directly from mysqlinnodbbuffer to disk without going through the operating system buffer, and the actual completion is in the flush step. The log still needs to go through the OS buffer.

MySQL latency issues and data flushing strategies

1. In Unix-like operating systems, opening a file in O_DIRECT mode will minimize the impact of buffering on IO. The IO of the file is directly operated on the buffer in the user space, and the IO operation is synchronous. Therefore, no matter it is a read() system call or a write() system call, the data is guaranteed to be read from the disk; therefore, the IO pressure is minimal, the CPU processing pressure is minimal, and the physical memory usage is minimal; however, due to the lack of operating system buffering, the speed of writing data to the disk will be significantly reduced (manifested as a longer write response time), but it will not significantly reduce the overall SQL request volume (this depends on a sufficiently large innodb_buffer_pool_size).

2. The O_DSYNC mode means opening the file in synchronous IO mode. Any write operation will be blocked until the data is written to the physical disk. This results in longer CPU wait times, lower SQL request throughput, and longer insert times.

3. The fsync(intfiledes) function only works on a single file specified by the file descriptor filedes, waits for the disk write operation to complete, and then returns. The fdatasync(int filedes) function is similar to fsync, but it affects only the data portion of the file. In addition to data, fsync also synchronously updates the file's metadata to disk.

O_DSYNC exerts the greatest pressure on the CPU, followed by datasync, and O_DIRECT exerts the least pressure. In terms of overall SQL statement processing performance and response time, O_DSYNC is poor. O_DIRECT has better SQL throughput (second only to datasync mode), but has the longest response time.

The default datasync mode has better overall performance because it fully utilizes the processing performance of the operating system buffer and innodb_buffer_pool. However, the negative effect is that free memory decreases too quickly, which ultimately leads to frequent page swaps and high disk IO pressure, which seriously affects the stability of large concurrent data writes.

Summarize

The above is the analysis of MySQL latency issues and data flushing strategy process introduced by the editor. I hope it will be helpful to everyone!