MySQL 8 复制性能的增强

2024-11-15 14:22 abce 阅读(70) 评论(0) 编辑收藏举报

新的复制时间戳

MySQL主从复制环境，最常见的任务是确保复制确实在进行，而且主从之间没有发生错误。常用的命令是 show slave status，该命令提供了从库线程的状态信息。因此，通常需要每个从库上执行 show slave status并检查输出结果。

输出内容中，有个参数指标 "Seconds_Behind_Master"。虽然该指标完全适用于简单的主从设置，但对于更复杂的复制场景来说，该指标是不够的。Seconds_Behind_Master 指标主要有四个缺点：

1.它只报告从库与最上层主库之间的延迟

例如，在链式复制设置中，Seconds_Behind_Master 报告的是相对于原始主库的延迟，而不提供任何有关从库与其最近（即直接）主库之间延迟的信息。

2.它是相对于最上层主库的时区而言的

因此，服务器跨时区复制会导致测得的延迟被两台服务器之间的时区差抵消。

3.延迟是根据语句的执行开始时间按事件测量的

更具洞察力的测量方法是以每笔事务为单位，从事务在主库上实际提交的时间算起。

4.用于测量复制延迟的时间戳只能精确到秒

MySQL 8引入了两个新的时间戳，它们是对 “Seconds_Behind_Master ”的补充，可以避免上述问题。这些时间戳与写入二进制日志的每个事务（而不是每个事件）的全局事务标识符（GTID）相关联。GTID 是一个唯一标识符，与在主库上提交的每个事务相关联。由于与事务相关联，所有事务和所有 GTID 之间都有 1 对 1 的映射关系。

两个新的时间戳是：

· original commit timestamp (OCT)：即原始提交时间戳 (OCT)，当事务被写入原始主库(original master)的二进制日志时，自纪元（即 POSIX 时间/UNIX 时间/1970 年 1 月 1 日/1970-01-01T00:00:00Z）起的微秒数

· immediate commit timestamp (ICT)：即时提交时间戳 (ICT)，事务写入直接主库(immediate master)的二进制日志时，自纪元起的微秒数。

mysqlbinlog 的输出以两种格式显示新的时间戳：

1.微秒

2.以用户时区显示的 TIMESTAMP 格式（更易读）

# at 708
#241113 19:24:24 server id 30  end_log_pos 787 CRC32 0x0950fa02         GTID    last_committed=2        sequence_number=3 rbr_only=yes    original_committed_timestamp=1731497064388667   immediate_commit_timestamp=1731497064388667       transaction_length=326
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1731497064388667 (2024-11-13 19:24:24.388667 CST)
# immediate_commit_timestamp=1731497064388667 (2024-11-13 19:24:24.388667 CST)
/*!80001 SET @@session.original_commit_timestamp=1731497064388667*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'd59173d4-a168-11ef-bb5c-005056b04cdd:7'/*!*/;
# at 787
#241113 19:24:24 server id 30  end_log_pos 861 CRC32 0x2ce7b347         Query   thread_id=23    exec_time=0     error_code=0
SET TIMESTAMP=1731497064/*!*/;
BEGIN
/*!*/;
# at 861
#241113 19:24:24 server id 30  end_log_pos 912 CRC32 0xc13bf03f         Rows_query
# insert into a values(4),(3)
# at 912
#241113 19:24:24 server id 30  end_log_pos 958 CRC32 0xdf0e13db         Table_map: `abc`.`a` mapped to number 116
# has_generated_invisible_primary_key=0
# at 958
#241113 19:24:24 server id 30  end_log_pos 1003 CRC32 0x8e24a4c5        Write_rows: table id 116 flags: STMT_END_F
 
BINLOG '
aIw0Zx0eAAAAMwAAAJADAACAABtpbnNlcnQgaW50byBhIHZhbHVlcyg0KSwoMyk/8DvB
aIw0ZxMeAAAALgAAAL4DAAAAAHQAAAAAAAEAA2FiYwABYQABAwABAQEA2xMO3w==
aIw0Zx4eAAAALQAAAOsDAAAAAHQAAAAAAAEAAgAB/wAEAAAAAAMAAADFpCSO
'/*!*/;
### INSERT INTO `abc`.`a`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `abc`.`a`
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 1003
#241113 19:24:24 server id 30  end_log_pos 1034 CRC32 0x09af8ed0        Xid = 37
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Performance Schema 新增的表

MySQL 8.0 对Performance Schema添加了一些更改，从而提高了性能并增加了更多指标：

1.可以检测 server 错误

2.现在支持索引

3.为现有的Performance Schema复制状态表添加了新字段

检测 server 错误

MySQL 8 引入了五个新的汇总表，以帮助检测服务器错误。它们包括

1.events_errors_summary_by_account_by_error

2.events_errors_summary_by_host_by_error

3.events_errors_summary_by_thread_by_error

4.events_errors_summary_by_user_by_error

5.events_errors_summary_global_by_error

在上述所有表中，错误统计数据都是按错误汇总的。此外，除 events_errors_summary_global_by_error 外，每个表都存储与特定用户、主机、账户或线程有关的错误；events_errors_summary_global_by_error 包含整个服务器的错误。

表结构

每个表都包含以下字段：

+-------------------+---------------------+------+-----+---------------------+
| Field             | Type                | Null | Key | Default             |
+-------------------+---------------------+------+-----+---------------------+
| error_number      | int(11)             | yes  |     | null                |
| error_name        | varchar(64)         | yes  |     | null                |
| sql_state         | varchar(5)          | yes  |     | null                |
| sum_error_raised  | bigint(20) unsigned | no   |     | null                |
| sum_error_handled | bigint(20) unsigned | no   |     | null                |
| first_seen        | timestamp           | yes  |     | 0000-00-00 00:00:00 |
| last_seen         | timestamp           | yes  |     | 0000-00-00 00:00:00 |
+-------------------+---------------------+------+-----+---------------------+

其中：

· first_seen/last_seen 列：表示出现特定错误的第一次和最后一次。

· sum_error_raised 列：抛出特定错误的发生次数。

· sum_error_handled 列：列出特定错误被处理的次数。

在 sum_error_handled 下统计/汇总了所有在存储程序中处理过的错误。同时，sum_error_raised 是所有其他已抛出但未处理的错误的次数。因此，要查看服务器上遇到特定错误的次数，我们可以执行以下操作：

查看不存在的表：
>select * from abc.abce;
ERROR 1146 (42S02): Table 'abc.abce' doesn't exist
 
查看错误表中信息：
>select * from performance_schema.events_errors_summary_global_by_error where error_name = 'er_no_such_table';
+--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+
| ERROR_NUMBER | ERROR_NAME       | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+
|         1146 | ER_NO_SUCH_TABLE | 42S02     |                1 |                 0 | 2024-11-13 19:44:06 | 2024-11-13 19:44:06 |
+--------------+------------------+-----------+------------------+-------------------+---------------------+---------------------+
1 row in set (0.01 sec)

本示例查询的是全局表，但也可以从用户/主机/账户/线程各自的表中按用户/主机/账户/线程汇总检索这些错误的统计数据，以获得更精细的统计数据。

索引支持

自从在 MySQL 5.5 中加入 Performance Schema 后，该模式已增加到 93 个表，其中一些表存储了大量数据。新增的索引支持功能大大提高了 Performance Schema 的效率，从而显著提升了许多监控查询的速度。

在许多 sys Schema 查询中，都能很容易地看到索引带来的性能提升。例如，在有 1000 个空闲线程的情况下，查询 "SELECT * FROM sys.session" 从 34.70 秒下降到 1.01 秒，提高了 30 倍！

Performance Schema 中总共添加了 115 个索引。与 Information Schema 不同，Performance Schema 将数据作为存储引擎而不是临时表公开。

临时表无法公开优化器可能会使用的索引，而存储引擎却可以。对 Performance Schema 的数据访问也使用与常规表相同的（SQL）接口，因此它能受益于未来对查询优化器的改进。

现有 Performance Schema 复制状态表新增的字段

除了新的错误表，现有的 Performance Schema 表也增加了一些字段，以帮助检测和诊断多个点的延迟情况。复制流中的每个潜在延迟点都映射到自己的表中：

延迟点	Performance Schema 复制状态表
连接线程检索主库来的事务，并以丢列的方式将其加入relay log	replication_connection_status：与主库连接的当前状态
协调线程读取来自relay log的事务，并调度到worker线程(如果开启了多线程复制)	replication_applier_status_by_coordinator：协调器线程的当前状态，仅在启用 MTS 时显示信息
worker线程应用日志	replication_applier_status_by_worker：应用从主服务器接收的事务的线程的当前状态

查看示例：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

root@localhost (none)>select * from performance_schema.replication_connection_status\g
*************************** 1. row ***************************
                                      channel_name: 
                                        group_name: 
                                       source_uuid: 3dc957a8-4b53-13ee-81be-055056a0cda9
                                         thread_id: 290
                                     service_state: on
                         count_received_heartbeats: 12104
                          last_heartbeat_timestamp: 2024-11-13 20:03:49.797661
                          received_transaction_set: 3dc957a8-4b53-13ee-81be-055056a0cda9:90536365-92544861
                                 last_error_number: 0
                                last_error_message: 
                              last_error_timestamp: 0000-00-00 00:00:00.000000
                           last_queued_transaction: 3dc957a8-4b53-13ee-81be-055056a0cda9:92544861
 last_queued_transaction_original_commit_timestamp: 2024-11-13 20:00:49.794755
last_queued_transaction_immediate_commit_timestamp: 2024-11-13 20:00:49.794755
     last_queued_transaction_start_queue_timestamp: 2024-11-13 20:00:49.795718
       last_queued_transaction_end_queue_timestamp: 2024-11-13 20:00:49.804412
                              queueing_transaction: 
    queueing_transaction_original_commit_timestamp: 0000-00-00 00:00:00.000000
   queueing_transaction_immediate_commit_timestamp: 0000-00-00 00:00:00.000000
        queueing_transaction_start_queue_timestamp: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)
 
root@localhost (none)>select * from performance_schema.replication_applier_status_by_coordinator\g
*************************** 1. row ***************************
                                         channel_name: 
                                            thread_id: 291
                                        service_state: on
                                    last_error_number: 0
                                   last_error_message: 
                                 last_error_timestamp: 0000-00-00 00:00:00.000000
                           last_processed_transaction: 3dc957a8-4b53-13ee-81be-055056a0cda9:92544861
 last_processed_transaction_original_commit_timestamp: 2024-11-13 20:00:49.794755
last_processed_transaction_immediate_commit_timestamp: 2024-11-13 20:00:49.794755
    last_processed_transaction_start_buffer_timestamp: 2024-11-13 20:00:49.795788
      last_processed_transaction_end_buffer_timestamp: 2024-11-13 20:00:49.830551
                               processing_transaction: 
     processing_transaction_original_commit_timestamp: 0000-00-00 00:00:00.000000
    processing_transaction_immediate_commit_timestamp: 0000-00-00 00:00:00.000000
        processing_transaction_start_buffer_timestamp: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)
 
root@localhost (none)>select * from performance_schema.replication_applier_status_by_worker\g
*************************** 1. row ***************************
                                           channel_name: 
                                              worker_id: 1
                                              thread_id: 292
                                          service_state: on
                                      last_error_number: 0
                                     last_error_message: 
                                   last_error_timestamp: 0000-00-00 00:00:00.000000
                               last_applied_transaction: 3dc957a8-4b53-13ee-81be-055056a0cda9:92544861
     last_applied_transaction_original_commit_timestamp: 2024-11-13 20:00:49.794755
    last_applied_transaction_immediate_commit_timestamp: 2024-11-13 20:00:49.794755
         last_applied_transaction_start_apply_timestamp: 2024-11-13 20:00:49.796949
           last_applied_transaction_end_apply_timestamp: 2024-11-13 20:00:49.852644
                                   applying_transaction: 
         applying_transaction_original_commit_timestamp: 0000-00-00 00:00:00.000000
        applying_transaction_immediate_commit_timestamp: 0000-00-00 00:00:00.000000
             applying_transaction_start_apply_timestamp: 0000-00-00 00:00:00.000000
                 last_applied_transaction_retries_count: 0
   last_applied_transaction_last_transient_error_number: 0
  last_applied_transaction_last_transient_error_message: 
last_applied_transaction_last_transient_error_timestamp: 0000-00-00 00:00:00.000000
                     applying_transaction_retries_count: 0
       applying_transaction_last_transient_error_number: 0
      applying_transaction_last_transient_error_message: 
    applying_transaction_last_transient_error_timestamp: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
                                           channel_name: 
                                              worker_id: 2
                                              thread_id: 295
                                          service_state: on
                                      last_error_number: 0
                                     last_error_message: 
                                   last_error_timestamp: 0000-00-00 00:00:00.000000
                               last_applied_transaction: 3dc957a8-4b53-13ee-81be-055056a0cda9:92543553
     last_applied_transaction_original_commit_timestamp: 2024-11-13 19:55:11.074457
    last_applied_transaction_immediate_commit_timestamp: 2024-11-13 19:55:11.074457
         last_applied_transaction_start_apply_timestamp: 2024-11-13 19:55:12.013437
           last_applied_transaction_end_apply_timestamp: 2024-11-13 19:55:12.015771
                                   applying_transaction: 
         applying_transaction_original_commit_timestamp: 0000-00-00 00:00:00.000000
        applying_transaction_immediate_commit_timestamp: 0000-00-00 00:00:00.000000
             applying_transaction_start_apply_timestamp: 0000-00-00 00:00:00.000000
                 last_applied_transaction_retries_count: 0
   last_applied_transaction_last_transient_error_number: 0
  last_applied_transaction_last_transient_error_message: 
last_applied_transaction_last_transient_error_timestamp: 0000-00-00 00:00:00.000000
                     applying_transaction_retries_count: 0
       applying_transaction_last_transient_error_number: 0
      applying_transaction_last_transient_error_message: 
    applying_transaction_last_transient_error_timestamp: 0000-00-00 00:00:00.000000
*************************** 3. row ***************************
                                           channel_name: 
                                              worker_id: 3
                                              thread_id: 296
                                          service_state: on
                                      last_error_number: 0
                                     last_error_message: 
                                   last_error_timestamp: 0000-00-00 00:00:00.000000
                               last_applied_transaction: 3dc957a8-4b53-13ee-81be-055056a0cda9:92543080
     last_applied_transaction_original_commit_timestamp: 2024-11-13 19:54:48.822867
    last_applied_transaction_immediate_commit_timestamp: 2024-11-13 19:54:48.822867
         last_applied_transaction_start_apply_timestamp: 2024-11-13 19:54:51.491876
           last_applied_transaction_end_apply_timestamp: 2024-11-13 19:54:54.896176
                                   applying_transaction: 
         applying_transaction_original_commit_timestamp: 0000-00-00 00:00:00.000000
        applying_transaction_immediate_commit_timestamp: 0000-00-00 00:00:00.000000
             applying_transaction_start_apply_timestamp: 0000-00-00 00:00:00.000000
                 last_applied_transaction_retries_count: 0
   last_applied_transaction_last_transient_error_number: 0
  last_applied_transaction_last_transient_error_message: 
last_applied_transaction_last_transient_error_timestamp: 0000-00-00 00:00:00.000000
                     applying_transaction_retries_count: 0
       applying_transaction_last_transient_error_number: 0
      applying_transaction_last_transient_error_message: 
    applying_transaction_last_transient_error_timestamp: 0000-00-00 00:00:00.000000
*************************** 4. row ***************************
                                           channel_name: 
                                              worker_id: 4
                                              thread_id: 297
                                          service_state: on
                                      last_error_number: 0
                                     last_error_message: 
                                   last_error_timestamp: 0000-00-00 00:00:00.000000
                               last_applied_transaction: 3dc957a8-4b53-13ee-81be-055056a0cda9:92540369
     last_applied_transaction_original_commit_timestamp: 2024-11-13 19:43:11.017099
    last_applied_transaction_immediate_commit_timestamp: 2024-11-13 19:43:11.017099
         last_applied_transaction_start_apply_timestamp: 2024-11-13 19:43:11.088638
           last_applied_transaction_end_apply_timestamp: 2024-11-13 19:43:11.091951
                                   applying_transaction: 
         applying_transaction_original_commit_timestamp: 0000-00-00 00:00:00.000000
        applying_transaction_immediate_commit_timestamp: 0000-00-00 00:00:00.000000
             applying_transaction_start_apply_timestamp: 0000-00-00 00:00:00.000000
                 last_applied_transaction_retries_count: 0
   last_applied_transaction_last_transient_error_number: 0
  last_applied_transaction_last_transient_error_message: 
last_applied_transaction_last_transient_error_timestamp: 0000-00-00 00:00:00.000000
                     applying_transaction_retries_count: 0
       applying_transaction_last_transient_error_number: 0
      applying_transaction_last_transient_error_message: 
    applying_transaction_last_transient_error_timestamp: 0000-00-00 00:00:00.000000
4 rows in set (0.00 sec)
 
root@localhost (none)>

每个表中增加了九个字段，用于存储上一个事务、相应处理的线程以及该线程当前正在处理的事务的信息。这些信息包括:

· 事务的 GTID

· OCT 和 ICT（从库的中继日志中获取）

· 线程开始处理的时间

· 如果是最后处理的事务，线程完成处理的时间

复制线程之间的关系更高效

从 MySQL 8.0.1 开始，由于复制线程之间关系的改进，从库的改进将使其比以前的 MySQL 版本更高效。

MySQL 从库侧的复制核心由两个线程（有时更多）组成：

connection ：处理与主库的连接，检索事件并按队列写入 relay log。
applier：从 relay log 中读取排队的事件，并将其应用到副本中。

旧的仲裁线程模型

由于"仲裁"的原因，当 connection 和 applier 处理同一个 relay log 时，connection 和 applier 之间的关系就会出现问题。relay log 一次只能由一个线程访问，导致复制线程相互排斥。因此，当 connection 写入 relay log 时，applier 无法读取要应用的内容，只能等待。同样，当 applier 从 relay log 中读取内容时，connection 也无法向其中写入新内容，从而进入空闲状态。

仲裁是为了防止 applier 程序将只部分写入 relay log 的事件发送给 worker 线程。虽然这种仲裁有时对资源有限的从库有益，但也限制了多线程 (MTS)复制的可扩展性。