Shulou(Shulou.com)05/31 Report--

This article is to share with you the content of a sample analysis of latency issues and data flushing strategies in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. MySQL replication process

The official documentation process is as follows:

MySQL delay problem and data flushing Strategy

1. Absolute delay, relative synchronization

2. Pure write operation, under the online standard configuration, the pressure of the slave library is greater than that of the master library, and at least the slave library can be written to relaylog.

II. Analysis of MySQL delay

1. Frequent DML requests in the main database

Reason: the master database writes data concurrently, while the slave database is a single-threaded application log, which can easily cause relaylog accumulation and delay.

Solution: do sharding, break up write requests. Consider upgrading to MySQL5.7+, to turn on parallel replication based on logical clocks.

2. The main library executes large transactions.

Reason: it takes a long time for the master database to update a large table. When the configuration of the master and slave libraries is similar, the slave database takes almost the same time to update the large table. At this time, the storage delay starts to accumulate, and the subsequent events cannot be updated.

Solution: split big affairs and submit them in time.

3. The main library executes DDL statements on large tables.

Reason: DDL did not start execution, blocked, check the site unchanged; DDL is executing, single-threaded application leads to increased latency, site unchanged.

Solution: find the query that is blocked DDL or write operation, kill the query, and let the DDL execute normally on the slave database; execute during the business trough, try to use the high version of MySQL that supports OnlineDDL.

4. The configuration of master and slave instances is inconsistent

Reason: hardware: the master database instance server uses SSD, while the slave database instance server uses ordinary SAS disk, cpu main frequency is inconsistent, etc.; configuration: such as inconsistent raid card writing policy, inconsistent setting of OS kernel parameters, inconsistent MySQL disk removal policy (innodb_flush_log_at_trx_commit, sync_binlog, etc.), etc.

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

5. Too much pressure from the library itself.

Reason: a large number of select requests are executed from the library, or most of the select requests are routed to the instance of the slave database, or even a large number of OLAP services, or the slave database is being backed up, which may result in excessive cpu load, high io utilization, and slow SQLThread application.

Solution: set up more Xi'an database training slave database, break up read requests, and reduce the pressure of existing slave database examples.

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

III. The problem of excessive CPU during the period of great promotion

Phenomenon:

High concurrency leads to high CPU load, lengthening the processing time of requests and gradually overstocking, resulting in service unavailability; a large number of slow SQL leads to excessive CPU load.

The solution is as follows:

Basically, the master-slave switching of the database is prohibited or carefully considered, which does not solve the fundamental problem. You need research and development to cure the SQL problem, or you can downgrade the service. For containers, you can dynamically expand CPU; and business negotiate to start pt-kill to check whether slow SQL can be solved by adding general indexes or federated indexes. However, the impact of DDL on the database should be considered.

Fourth, the strategy of InnoDB brushing.

The parameter innodb_flush_method of MySQL controls the opening and writing mode of innodb data files and redolog. For this parameter, it is described as follows:

There are three values: fdatasync (default), Olympus DSYNCrect

The default is fdatasync. Call fsync () to brush the data file and buffer of redolog.

When O_DSYNC, innodb uses O_SYNC to open and write redolog, and fsync () to write data files

When O_DIRECT, innodb uses O_DIRECT to open the data file, and fsync () to swipe the data file and redolog

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

The fsync (intfd) function most often mentioned above is used to flush the buffer related to the file referred to in the fd file descriptor to disk, and flush completes the metadata information (such as modification date, creation date, etc.) before flush is successful.

Opening the redo file with O_DSYNC means that when the write log is logged, the data is write to disk and the metadata needs to be updated before success is returned.

O_DIRECT means that our write operation is written directly to disk from MySQLinnodbbuffer.

The ways to write data in these three modes are as follows:

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

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

O_DIRECT mode: the data file is written directly from mysqlinnodbbuffer to disk, and does not need to be buffered by the operating system, and the real completion is in flush, and the log is still buffered by OS.

MySQL delay problem and data flushing Strategy

1. In a unix-like operating system, opening a file to O_DIRECT minimizes the impact of buffering on io. The io of the file operates directly on the buffer in user space, and the io operation is synchronous, so whether it is read () system call or write () system call, the data is guaranteed to be read from disk. Therefore, the pressure of IO is the least, the processing pressure of CPU is the least, and the footprint of physical memory is also the least. However, due to the lack of operating system buffering, the speed of writing data to disk will be significantly reduced (shown by an increase in write response time), but it will not significantly reduce the overall number of SQL requests (this depends on a large enough innodb_buffer_pool_size).

2. O_DSYNC means that the file is opened in a synchronous io mode, and any write operation will block until the data is written to the physical disk before returning. As a result, the waiting time of CPU is longer, the throughput capacity of SQL requests is reduced, and the insert time is prolonged.

3. The fsync (intfiledes) function only works on a single file specified by the file descriptor filedes, and waits for the write disk operation to finish, and then returns. The fdatasync (intfiledes) function is similar to fsync, but it only affects the data portion of the file. In addition to the data, fsync synchronizes the meta-information of the update file to disk.

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

The default datasync mode performs better as a whole, because it makes full use of the processing performance of the operating systems buffer and innodb_buffer_pool, but the negative effect is that the free memory decreases too fast, resulting in frequent page swapping and high disk IO pressure, which will seriously affect the stability of large concurrent data writes.

Thank you for reading! This is the end of the article on "sample analysis of latency problems and data flushing strategies in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.