MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

MariaDB 里影响 binlog 性能的参数剖析

2023-12-235.7k 阅读

MariaDB 中 binlog 概述

在 MariaDB 数据库中，二进制日志（Binlog）起着至关重要的作用。它记录了数据库执行的所有更改数据的语句，这些日志主要用于数据备份、主从复制以及崩溃恢复等场景。通过记录数据库的变更操作，当需要恢复数据到某个时间点或者搭建主从复制架构时，Binlog 就成为了关键的信息来源。

Binlog 以追加写的方式记录日志，不会像 InnoDB 的重做日志那样循环使用空间。它采用逻辑日志的形式记录，例如记录一条 INSERT 语句而不是记录物理层面的数据块修改。这使得 Binlog 可以跨存储引擎使用，而不仅仅局限于特定的存储引擎。

影响 binlog 性能的参数剖析

sync_binlog 参数

sync_binlog 参数含义

sync_binlog 参数决定了 MariaDB 将二进制日志刷新到磁盘的频率。该参数的取值为 0 到 N 之间的整数。

  • 当 sync_binlog = 0 时，MariaDB 不会主动将 binlog 刷新到磁盘，而是由操作系统来决定何时将缓存中的数据刷新到磁盘。这种情况下，写操作的性能最高，因为减少了磁盘 I/O 的次数。但是，如果系统崩溃，可能会丢失最后一些 binlog 记录，因为这些记录可能还在操作系统的缓存中，尚未真正写入磁盘。
  • 当 sync_binlog = 1 时，MariaDB 每执行一次事务提交（commit）操作，就会将 binlog 刷新到磁盘。这保证了事务的持久性，即使系统崩溃，也不会丢失已经提交的事务的 binlog 记录。然而，这种方式会显著增加磁盘 I/O 次数，因为每次提交都要进行一次磁盘写入操作，从而对性能产生较大影响。
  • 当 sync_binlog = N（N > 1）时，MariaDB 会在进行 N 次事务提交后，才将 binlog 刷新到磁盘。这种设置在一定程度上平衡了性能和数据安全性。例如，sync_binlog = 100 意味着每 100 次事务提交后才进行一次磁盘写入操作，相较于 sync_binlog = 1，减少了磁盘 I/O 次数，提高了性能，但同时也增加了系统崩溃时可能丢失 binlog 记录的风险，丢失的记录最多为最近 99 次事务提交的内容。

代码示例说明 sync_binlog 影响

下面通过一段简单的 Python 代码结合 MariaDB 驱动（mysql - connector - python）来展示不同 sync_binlog 设置对性能的影响。假设我们有一个简单的表 test_table，包含 id 和 data 两个字段。

import mysql.connector
import time

# 连接 MariaDB 数据库
mydb = mysql.connector.connect(
    host="localhost",
    user="your_user",
    password="your_password",
    database="your_database"
)

mycursor = mydb.cursor()

# 插入 1000 条数据的函数
def insert_data(sync_binlog_value):
    start_time = time.time()
    mycursor.execute(f"SET GLOBAL sync_binlog = {sync_binlog_value}")
    for i in range(1000):
        sql = "INSERT INTO test_table (id, data) VALUES (%s, %s)"
        val = (i, f"data_{i}")
        mycursor.execute(sql, val)
        mydb.commit()
    end_time = time.time()
    print(f"sync_binlog = {sync_binlog_value}, 插入 1000 条数据耗时: {end_time - start_time} 秒")

# 测试 sync_binlog = 0
insert_data(0)

# 测试 sync_binlog = 1
insert_data(1)

# 测试 sync_binlog = 100
insert_data(100)

mydb.close()

在上述代码中，我们通过 SET GLOBAL sync_binlog = {sync_binlog_value} 动态设置 sync_binlog 的值，然后进行 1000 次数据插入操作，并记录每次操作的耗时。从实际运行结果来看，sync_binlog = 0 时耗时最短，因为几乎没有磁盘 I/O 操作；sync_binlog = 1 时耗时最长，因为每次提交都进行磁盘写入；sync_binlog = 100 的耗时则介于两者之间。

binlog_cache_size 参数

binlog_cache_size 参数含义

binlog_cache_size 参数定义了每个线程用于缓存二进制日志数据的内存大小。当一个事务开始时，MariaDB 会为该事务分配一块大小为 binlog_cache_size 的缓存空间，用于临时存储该事务产生的 binlog 记录。

如果一个事务产生的 binlog 记录大小超过了 binlog_cache_size，MariaDB 会自动将超出部分的数据写入到磁盘上的临时文件中，这被称为“binlog 缓存溢出”。频繁的 binlog 缓存溢出会导致额外的磁盘 I/O 操作，从而影响性能。

调整 binlog_cache_size 的影响

适当增大 binlog_cache_size 可以减少 binlog 缓存溢出的可能性，从而降低磁盘 I/O 操作，提高性能。但是，如果设置过大，会浪费系统内存资源，因为每个线程都会分配这么大的缓存空间，即使实际使用的空间可能远小于这个值。

示例分析 binlog_cache_size

假设我们有一个复杂的事务，会产生较大的 binlog 记录。以下是一段简单的 SQL 示例，通过模拟大事务来观察 binlog_cache_size 的影响。

-- 创建一个大表用于测试
CREATE TABLE big_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(1000)
);

-- 插入大量数据到表中
DELIMITER //
CREATE PROCEDURE insert_large_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 10000 DO
        INSERT INTO big_table (data) VALUES (REPEAT('a', 1000));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 设置 binlog_cache_size 为较小值，例如 102400（100KB）
SET GLOBAL binlog_cache_size = 102400;

-- 开启事务并执行大事务操作
START TRANSACTION;
CALL insert_large_data();
COMMIT;

-- 查看 binlog 缓存使用情况
SHOW STATUS LIKE 'Binlog_cache_use';
SHOW STATUS LIKE 'Binlog_cache_disk_use';

在上述代码中，我们首先创建了一个 big_table 并插入大量数据的存储过程。然后设置较小的 binlog_cache_size，执行大事务。通过 SHOW STATUS LIKE 'Binlog_cache_use'; 和 SHOW STATUS LIKE 'Binlog_cache_disk_use'; 可以查看 binlog 缓存的使用情况和磁盘使用情况。如果 Binlog_cache_disk_use 的值较大，说明发生了 binlog 缓存溢出，需要适当增大 binlog_cache_size。

max_binlog_cache_size 参数

max_binlog_cache_size 参数含义

max_binlog_cache_size 参数限制了单个事务可以使用的最大 binlog 缓存大小。即使 binlog_cache_size 设置得较小，如果一个事务需要的缓存空间超过了 max_binlog_cache_size，MariaDB 会拒绝执行该事务，并报错。

这个参数主要用于防止单个事务占用过多的内存资源，避免因大事务导致系统内存不足的情况发生。

对系统性能和稳定性的影响

如果 max_binlog_cache_size 设置过小，可能会导致一些正常的大事务无法执行。例如，在数据迁移或者大型批量更新操作中，事务可能会产生较大的 binlog 记录，如果超过了 max_binlog_cache_size，就会报错。

另一方面，如果设置过大，虽然可以允许大事务执行，但可能会在系统中同时存在多个大事务时，导致内存耗尽，影响系统的稳定性。

示例展示 max_binlog_cache_size 影响

以下示例通过调整 max_binlog_cache_size 来观察其对大事务执行的影响。

-- 设置 max_binlog_cache_size 为较小值，例如 5242880（5MB）
SET GLOBAL max_binlog_cache_size = 5242880;

-- 尝试执行一个大事务
START TRANSACTION;
-- 这里假设执行一些会产生大量 binlog 记录的操作，例如大量数据插入或更新
INSERT INTO large_table SELECT * FROM another_large_table;
COMMIT;

如果在执行上述大事务时，出现类似于“Transaction size too large, exceeds max_binlog_cache_size”的错误，说明 max_binlog_cache_size 设置过小，需要适当增大。

binlog_format 参数

binlog_format 参数含义

binlog_format 参数决定了二进制日志记录的格式。MariaDB 支持三种 binlog 格式：STATEMENT、ROW 和 MIXED。

  • STATEMENT 格式：在这种格式下，binlog 记录的是 SQL 语句本身。例如，一条 INSERT INTO users (name, age) VALUES ('John', 25) 语句会被完整记录到 binlog 中。这种格式的优点是日志文件较小，因为只记录 SQL 语句，而不是实际的数据修改。但是，它可能会在主从复制中出现数据不一致的问题，例如一些依赖于特定服务器环境的函数（如 NOW()），在主从服务器上执行结果可能不同，导致复制出现偏差。
  • ROW 格式：ROW 格式下，binlog 记录的是数据行的实际修改。例如，对于上述 INSERT 语句，binlog 会记录插入的具体数据行 ('John', 25) 以及表结构相关信息。这种格式能确保主从复制的一致性，因为记录的是实际数据变化。但缺点是 binlog 文件会较大，因为需要记录每一行数据的变化。
  • MIXED 格式：MIXED 格式是前两种格式的混合。MariaDB 会根据 SQL 语句的情况自动选择使用 STATEMENT 格式还是 ROW 格式。对于大多数语句，使用 STATEMENT 格式记录，以保持日志文件较小；对于可能导致主从复制不一致的语句（如包含特定函数的语句），则使用 ROW 格式记录。

不同格式对性能的影响

  • STATEMENT 格式性能：由于日志记录内容少，写入 binlog 的磁盘 I/O 操作相对较少，在一些简单的数据库操作场景下性能较好。但在复杂场景且涉及主从复制时，可能因复制问题导致性能问题，例如需要额外的修复操作来保证数据一致性。
  • ROW 格式性能：由于要记录每行数据的变化，日志量较大，写入 binlog 的磁盘 I/O 操作频繁，在高并发写入场景下可能会成为性能瓶颈。但在主从复制方面，能提供更可靠的一致性，减少因复制不一致带来的性能损耗。
  • MIXED 格式性能：试图在两者之间找到平衡，对于大多数简单语句采用 STATEMENT 格式提高性能，对于特殊语句采用 ROW 格式保证一致性。但在实际应用中，格式的自动切换可能会带来一定的性能开销，因为 MariaDB 需要判断每条语句应采用哪种格式。

示例展示不同 binlog_format 性能差异

下面通过一个简单的性能测试脚本来展示不同 binlog_format 对插入数据性能的影响。

import mysql.connector
import time

# 连接 MariaDB 数据库
mydb = mysql.connector.connect(
    host="localhost",
    user="your_user",
    password="your_password",
    database="your_database"
)

mycursor = mydb.cursor()

# 插入 10000 条数据的函数
def insert_data(binlog_format):
    start_time = time.time()
    mycursor.execute(f"SET SESSION binlog_format = '{binlog_format}'")
    for i in range(10000):
        sql = "INSERT INTO test_table (id, data) VALUES (%s, %s)"
        val = (i, f"data_{i}")
        mycursor.execute(sql, val)
        mydb.commit()
    end_time = time.time()
    print(f"binlog_format = {binlog_format}, 插入 10000 条数据耗时: {end_time - start_time} 秒")

# 测试 STATEMENT 格式
insert_data('STATEMENT')

# 测试 ROW 格式
insert_data('ROW')

# 测试 MIXED 格式
insert_data('MIXED')

mydb.close()

通过上述代码，我们分别在 STATEMENT、ROW 和 MIXED 三种 binlog_format 下执行 10000 次数据插入操作并记录耗时。一般来说，在这种简单插入场景下，STATEMENT 格式耗时最短，ROW 格式耗时最长，MIXED 格式耗时介于两者之间。

max_binlog_size 参数

max_binlog_size 参数含义

max_binlog_size 参数用于限制单个二进制日志文件的最大大小。当当前的 binlog 文件大小达到 max_binlog_size 时，MariaDB 会自动创建一个新的 binlog 文件，并将后续的日志记录写入新文件。

这个参数主要用于控制 binlog 文件的大小，避免单个 binlog 文件过大而难以管理，同时也有助于提高备份和恢复操作的效率。

对系统性能和维护的影响

如果 max_binlog_size 设置过小，会导致 binlog 文件切换过于频繁，增加系统开销。每次文件切换都需要进行一些额外的操作，如创建新文件、更新相关的元数据等。

相反，如果设置过大，单个 binlog 文件可能会变得非常大，在进行备份或者恢复操作时，处理大文件可能会消耗更多的时间和资源，而且如果在大文件写入过程中出现问题，可能会丢失更多的数据。

示例说明 max_binlog_size 影响

以下通过模拟 binlog 文件达到 max_binlog_size 并切换的过程来展示其影响。

-- 设置 max_binlog_size 为一个较小值，例如 10485760（10MB）
SET GLOBAL max_binlog_size = 10485760;

-- 执行一些会产生大量 binlog 记录的操作，例如大量数据插入
CREATE TABLE large_insert_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(1000));
DELIMITER //
CREATE PROCEDURE insert_large_amount()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 10000 DO
        INSERT INTO large_insert_table (data) VALUES (REPEAT('a', 1000));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL insert_large_amount();

在上述代码中，我们设置了较小的 max_binlog_size，然后执行大量数据插入操作。通过查看 binlog 文件目录，可以看到在操作过程中，binlog 文件会根据设置的大小进行切换。如果在这个过程中查看系统的性能指标（如 CPU 使用率、磁盘 I/O 等），会发现文件切换时会有一定的性能波动。

binlog_stmt_cache_size 参数

binlog_stmt_cache_size 参数含义

binlog_stmt_cache_size 参数定义了每个线程用于缓存单个语句的 binlog 数据的内存大小。与 binlog_cache_size 不同，binlog_cache_size 是用于缓存整个事务的 binlog 数据，而 binlog_stmt_cache_size 是针对单个语句。

当执行一条 SQL 语句时，MariaDB 会先将该语句产生的 binlog 数据缓存到 binlog_stmt_cache_size 大小的缓存空间中。如果该语句产生的 binlog 数据超过了这个缓存大小，同样会发生缓存溢出，导致数据写入磁盘临时文件。

调整 binlog_stmt_cache_size 的影响

适当增大 binlog_stmt_cache_size 可以减少单个语句的 binlog 缓存溢出情况，降低磁盘 I/O 操作，从而提高性能。但如果设置过大，会浪费内存资源，因为每个线程针对每个语句都会分配这么大的缓存空间。

示例分析 binlog_stmt_cache_size

以下通过一段 SQL 示例来展示 binlog_stmt_cache_size 对大语句执行的影响。

-- 设置 binlog_stmt_cache_size 为较小值，例如 10240（10KB）
SET GLOBAL binlog_stmt_cache_size = 10240;

-- 执行一个会产生大量 binlog 记录的大语句
INSERT INTO large_table SELECT * FROM another_large_table WHERE some_condition;

-- 查看 binlog 缓存使用情况
SHOW STATUS LIKE 'Binlog_stmt_cache_use';
SHOW STATUS LIKE 'Binlog_stmt_cache_disk_use';

通过上述代码，我们设置较小的 binlog_stmt_cache_size 后执行一个大语句，然后通过 SHOW STATUS 查看 binlog 缓存的使用情况。如果 Binlog_stmt_cache_disk_use 有值，说明发生了 binlog 语句缓存溢出，需要适当增大 binlog_stmt_cache_size。

binlog_checksum 参数

binlog_checksum 参数含义

binlog_checksum 参数用于控制是否对二进制日志进行校验和计算。MariaDB 支持多种校验和算法，如 NONE、CRC32 等。当设置为 NONE 时，不进行校验和计算；当设置为 CRC32 时，会对每个 binlog 事件计算 CRC32 校验和，并将其存储在 binlog 中。

校验和主要用于在主从复制或者数据恢复过程中，检测 binlog 数据是否损坏或者在传输过程中发生错误。

对性能的影响

启用校验和（如 CRC32）会增加一定的 CPU 开销，因为每次写入 binlog 事件时都需要计算校验和。但是，这能显著提高数据的完整性和可靠性，在主从复制场景下，能及时发现因网络问题或者磁盘故障导致的 binlog 数据错误，避免数据不一致的问题。

示例展示 binlog_checksum 影响

以下通过简单的性能测试脚本来展示启用和禁用 binlog_checksum 对性能的影响。

import mysql.connector
import time

# 连接 MariaDB 数据库
mydb = mysql.connector.connect(
    host="localhost",
    user="your_user",
    password="your_password",
    database="your_database"
)

mycursor = mydb.cursor()

# 插入 1000 条数据的函数
def insert_data(checksum_value):
    start_time = time.time()
    mycursor.execute(f"SET GLOBAL binlog_checksum = {checksum_value}")
    for i in range(1000):
        sql = "INSERT INTO test_table (id, data) VALUES (%s, %s)"
        val = (i, f"data_{i}")
        mycursor.execute(sql, val)
        mydb.commit()
    end_time = time.time()
    print(f"binlog_checksum = {checksum_value}, 插入 1000 条数据耗时: {end_time - start_time} 秒")

# 测试 binlog_checksum = NONE
insert_data('NONE')

# 测试 binlog_checksum = CRC32
insert_data('CRC32')

mydb.close()

在上述代码中，我们分别在 binlog_checksum = NONE 和 binlog_checksum = CRC32 两种情况下执行 1000 次数据插入操作并记录耗时。一般来说，binlog_checksum = NONE 时耗时会稍短，因为没有校验和计算的开销，但数据完整性保障相对较弱；binlog_checksum = CRC32 时耗时会稍长，但能提供更好的数据完整性检测。

innodb_flush_log_at_trx_commit 与 binlog 性能的关联

innodb_flush_log_at_trx_commit 参数含义

innodb_flush_log_at_trx_commit 参数主要控制 InnoDB 存储引擎的重做日志（redo log）刷新到磁盘的频率。它有三个取值：0、1 和 2。

  • 当 innodb_flush_log_at_trx_commit = 0 时，InnoDB 每秒将重做日志缓冲区中的数据刷新到磁盘一次，而不是在每次事务提交时刷新。这种设置下，事务提交时不会立即进行磁盘 I/O 操作，性能较高，但如果系统崩溃，可能会丢失最后一秒内提交的事务数据。
  • 当 innodb_flush_log_at_trx_commit = 1 时，InnoDB 在每次事务提交时，都会将重做日志缓冲区中的数据立即刷新到磁盘。这保证了事务的持久性，即使系统崩溃，也不会丢失已经提交的事务数据，但同时会增加磁盘 I/O 操作，对性能有一定影响。
  • 当 innodb_flush_log_at_trx_commit = 2 时，InnoDB 在每次事务提交时，会将重做日志缓冲区中的数据写入到操作系统的缓存中，但不保证立即刷新到磁盘，而是由操作系统决定何时将缓存中的数据真正写入磁盘。这种设置在性能和数据安全性之间取得了一定的平衡，如果操作系统崩溃，可能会丢失最后一些事务数据，但如果只是 MariaDB 进程崩溃，数据不会丢失。

与 binlog 性能的关系

innodb_flush_log_at_trx_commit 的设置会间接影响 binlog 的性能。因为 binlog 和 redo log 存在一定的协调关系，特别是在保证事务的原子性、一致性、隔离性和持久性（ACID）方面。

  • 当 innodb_flush_log_at_trx_commit = 1 且 sync_binlog = 1 时，每次事务提交都要进行两次磁盘 I/O 操作，一次是刷新 redo log，一次是刷新 binlog，这会对性能产生较大影响。但这种设置能提供最高的数据安全性，保证事务的持久性。
  • 如果 innodb_flush_log_at_trx_commit 设置为 0 或 2，虽然减少了 InnoDB 存储引擎的磁盘 I/O 操作，但可能会在系统崩溃时出现数据不一致的情况，因为 binlog 和 redo log 的同步可能出现问题。例如，在 innodb_flush_log_at_trx_commit = 0 时，如果系统在事务提交后但重做日志尚未刷新到磁盘时崩溃，而此时 binlog 已经刷新，可能会导致恢复数据时出现不一致。

示例展示两者关联影响

以下通过一段简单的性能测试代码来展示 innodb_flush_log_at_trx_commit 和 sync_binlog 不同设置组合对性能的影响。

import mysql.connector
import time

# 连接 MariaDB 数据库
mydb = mysql.connector.connect(
    host="localhost",
    user="your_user",
    password="your_password",
    database="your_database"
)

mycursor = mydb.cursor()

# 插入 1000 条数据的函数
def insert_data(innodb_value, sync_binlog_value):
    start_time = time.time()
    mycursor.execute(f"SET GLOBAL innodb_flush_log_at_trx_commit = {innodb_value}")
    mycursor.execute(f"SET GLOBAL sync_binlog = {sync_binlog_value}")
    for i in range(1000):
        sql = "INSERT INTO test_table (id, data) VALUES (%s, %s)"
        val = (i, f"data_{i}")
        mycursor.execute(sql, val)
        mydb.commit()
    end_time = time.time()
    print(f"innodb_flush_log_at_trx_commit = {innodb_value}, sync_binlog = {sync_binlog_value}, 插入 1000 条数据耗时: {end_time - start_time} 秒")

# 测试组合 1: innodb_flush_log_at_trx_commit = 1, sync_binlog = 1
insert_data(1, 1)

# 测试组合 2: innodb_flush_log_at_trx_commit = 0, sync_binlog = 1
insert_data(0, 1)

# 测试组合 3: innodb_flush_log_at_trx_commit = 1, sync_binlog = 0
insert_data(1, 0)

# 测试组合 4: innodb_flush_log_at_trx_commit = 0, sync_binlog = 0
insert_data(0, 0)

mydb.close()

通过上述代码，我们可以看到不同的 innodb_flush_log_at_trx_commit 和 sync_binlog 设置组合下，插入 1000 条数据的耗时情况。一般来说，innodb_flush_log_at_trx_commit = 1 且 sync_binlog = 1 时耗时最长，而 innodb_flush_log_at_trx_commit = 0 且 sync_binlog = 0 时耗时最短，但同时数据安全性也是最低的。

在实际应用中，需要根据业务对数据安全性和性能的要求，仔细权衡这两个参数的设置，以达到最佳的性能和数据保护效果。同时，还需要结合其他 binlog 相关参数，如 binlog_cache_size、binlog_format 等，综合优化 MariaDB 的性能。