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

This article introduces the knowledge of "what is the reason for the slow start-up time of MySQL in GTID". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

How do we find the problem?

We are looking at slow MySQL startup times in disk-based MySQL 5.7.21 deployments at the low end of GTID mode. The system is part of the master-slave pair and is under a moderate write load. When restarting during planned maintenance, we noticed that it took 5-10 minutes for the database server to start and start accepting connections. The delay was pointless, so we started to investigate.

Debug slow MySQL startup time

We use the popular Percona tool pt-ioprofile to see what the database is doing. Pt-ioprofile is a very important utility in Percona's popular toolkit for debugging MySQL problems, and you can see a complete list of features in its documentation. The pt-ioprofile tool uses strace and lsof to monitor the I / O of a process and print out a file and a table of I / O activity.

So, we start MySQL, wait for the mysqld process to generate, and start pt-ioprofile to see that the problem may be:

# PT-ioprofile-mysqld of the process-run-time Tuesday, October 9 15:42:24 UTC 2018 tracking process ID 18677total pread read pwrite write fsync fdatasync open close getdents lseek fcntl filename...216.550641 0.000000 216.550565 0.000000 0.000000 0.000000 0.000015 0.000040 0.000000 0.000021 0.000000 / mysql_data / binlogs / mysql-bin. 000014... What is the reason for your MySQL restart?

During multiple runs, we observed the following:

The mysqld process spends most of its time reading the latest binary log files. This is true even if the server has stopped normally and does not require crash recovery, and so on.

The server also takes a considerable amount of time to load InnoDB data files, but this time is much less than the time it takes to read the latest binary log files.

If the server restarts immediately, subsequent restarts will be faster.

Because database shutdown refreshes the binary log and creates a new log at startup, we did another experiment-we refreshed the binary log before shutting down the server. The subsequent server starts quickly again.

These observations clearly indicate that MySQL is spending a lot of time reading the latest binary log files. If the file is small, it starts quickly, just like refreshing the log file before shutting down.

Learn about Binlog GTID recovery

It turns out that in order to populate the values of gtid_executed and gtid_purged, the MySQL server must parse the binary log files.

The following is a summary of the recommended MySQL 5.7 documentation methods based on FALSE or TRUE readings:

When binlog_gtid_simple_recovery = FALSE:

To calculate the gtid_executed:

From the latest iterative binary log file, stop in the first file with the Previous_gtids_log_event entry.

Use all the GTID in Previous_gtids_log_event and Gtid_log_events from this binary log file, and store this GTID set internally. It is called gtids_in_binlog.

The value gtid_executed is calculated as the union gtids_in_binlog and in the GTIDs mysql.gtid_ executed table.

If there are a large number of binary log files without GTID (for example, created when gtid_mode = OFF), this process can be time-consuming.

Again, to calculate the gtid_purged:

Iterate from the oldest to the latest binary log file, stopping in the first binary log that contains a non-empty Previous_gtids_log_event (with at least one GTID) or at least one Gtid_log_event.

Read Previous_gtids_log_event from this file. The internal variable gtids_in_binlog_not_purged is calculated because this gTID set is subtracted from gtids_in_binlog.

The value gtid_purged is set to gtid_executed, minus gtids_in_binlog_not_purged.

Therefore, this forms the basis for our understanding of how it works in the old version. However, when binlog_gtid_simple_recovery is TRUE, some optimizations can be made. What we are interested in is this situation:

When binlog_gtid_simple_recovery = TRUE:

(note that this is the default setting in MySQL 5.7.7 and later)

Read only the oldest and latest binary log files.

Calculates the gtid_purged from the Previous_gtids_log_event or Gtid_log_event found in the earliest binary log file.

Calculates the gtid_executed from the Previous_gtids_log_event or Gtid_log_event found in the latest binary log file.

Therefore, only two binary log files are read while the server restarts or clears the binary log.

Therefore, for MySQL 5.7.7 and later, the latest and old binary log files are always read during system startup to properly initialize the GTID system variable. Reading the oldest binary log file is not expensive because the event that MySQL is looking for, Previous_gtids_log_event, is always the first event in the binary log file.

However, in order to calculate the gtid_executed correctly, the server must read the entire latest binary log file and collect all events in that file. Therefore, the system startup time is proportional to the size of the latest binary log file.

Note that when binlog_gtid_simple_recovery is FALSE, the situation is even worse. Since it is no longer the default option in recent releases, it is not a concern.

How to solve your slow start time

Knowing the cause of the problem we encountered, the solution we decided on was quite obvious-reducing the size of the binary log files. The default size of the binary log file is 1GB. Parsing files of this size takes time during startup, so it makes sense to reduce the value of max_binlog_size to a lower value.

If you cannot reduce the size of the binary log file, refreshing the binary log file before maintenance shuts down the mysqld process can help reduce the binlog GTID recovery time.

This is the end of the content of "what is the reason for the slow startup time of MySQL in GTID". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.