未加星标

Slow MySQL Start Time in GTID? Binary Log File Size May Be The Issue

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

Have you been experiencing slow mysql startup times in GTID mode? We recently ran into this issue on one of ourMySQL hostingdeployments and set out to solve the problem. In this blog, webreak down the issue that could be slowing down your MySQL restart times, how to debug for your deployment, and what you can do to decrease your start time and improve your understanding of GTID-based replication .

How We Found The Problem

We were investigating slow MySQL startup times on a low-end, disk-based MySQL 5.7.21 deployment which had GTID mode enabled. The system was part of a master-slave pairand was under a moderate write load. When restarting during a scheduled maintenance, we noticed that the database server took 5-10 minutes to start up and begin accepting connections. That kind of delay didn’t make sense, so we setout to investigate.

Debugging Your Slow MySQL Start Time

We used the popular Percona tool pt-ioprofile to see what the database was doing. pt-ioprofile is a very important utility in Percona’s popular toolkit that’s used to debug MySQL issues, and you can see the complete list of features in their documentation . The pt-ioprofile tool uses strace and lsof to watch a process’s I/O and print out a table of files and I/O activity.

So, we started MySQL, waited for the mysqld process to get spawned, and started pt-ioprofile to see what the issue might be:

# pt-ioprofile --profile-process mysqld --run-time 200
Tue Oct 9 15:42:24 UTC 2018
Tracing process ID 18677
total 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.000000 0.000015 0.000040 0.000000 0.000021 0.000000 /mysql_data/binlogs/<strong>mysql-bin.000014</strong>
... What’s Slowing Your MySQL Restart?

On running this multiple times, we observed the following:

The mysqld process was spending most of its time reading the latest binary log file. This was the case even when the server had been stopped gracefully and there was no need for a crash recovery , etc. The server also spent a considerable amount of time loading the InnoDB data files , but that time was much smaller compared to the time spent reading the latest binary log file. If the server was restarted again immediately, this subsequent restart would be much faster. Since a database shutdown flushes the binary log and creates a new one at startup, we did an additional experiment before shutting down the server, we flushed the binary logs . The subsequent server start was fast again.

The se observations clearly pointed to the fact that MySQL was spending a lot of time reading the latest binary log file. If the file was small, like it would be when the log file was flushed before a shutdown , the startup was fast.

Slow MySQL Start Time in GTID? Your Binary Log File Size May Be The Issue Click To Tweet Understanding Binlog GTID Recovery

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

Here’s the summary the MySQL 5.7 documentation method recommendation based on a FALSE or TRUE reading:

Whenbinlog_gtid_simple_recovery= FALSE:

To compute gtid_executed:

Iterate binary log files from the newest, stopping at the first file that has a Previous_gtids_log_event entry. Consume all GTIDs from Previous_gtids_log_event and Gtid_log_events from this binary log file, and store this GTID set internally. It is referred to as gtids_in_binlog. Value of gtid_executed is computed as the union of gtids_in_binlog and the GTIDs in the mysql.gtid_executed table .

This process can be very time consuming if thereare a large number of binary log files without GTIDs, for example, created when gtid_mode =OFF.

Similarly, to compute gtid_purged:

Iterate binary log files from the oldest to the newest, stopping at the first binary log that contains either a nonempty Previous_gtids_log_event (has at least one GTID), or that has at least one Gtid_log_event . Read Previous_gtids_log_event from this file. Computethe internal variable gtids_in_binlog_not_purged as this GTID set subtracted from gtids_in_binlog. Value of gtid_purged is set to gtid_executed , minus gtids_in_binlog_not_purged .

So, this forms the basis of our understanding on how things used to work in older versions. However, certain optimizations can be made when binlog_gtid_simple_recovery is TRUE. This is the case we are interested in:

Whenbinlog_gtid_simple_recovery= TRUE:

(Note, this is the default in MySQL 5.7.7 and later)

Read just the oldest and the newest binary log files. Compute gtid_purged from the Previous_gtids_log_event or Gtid_log_event found in the oldest binary log file. Compute gtid_executed from the Previous_gtids_log_event or Gtid_log_event found in newest binary log file. Thus, only two binary log files are read during theserver restart or when purging binary logs. So, for MySQL versions 5.7.7 and above, the latest and the old binary log files are always read during system startup to correctly initialize

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

代码区博客精选文章
分页:12
转载请注明
本文标题:Slow MySQL Start Time in GTID? Binary Log File Size May Be The Issue
本站链接:https://www.codesec.net/view/621253.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(71)