【MySQL 8.0】标准配置文件详解(下)

1.Replication

• relay_log
  指定中继日志的名称
  默认基本名称是 host_name-relay-bin

• relay_log_index
  指定relay log的索引名称

• relay_log_recovery
  开启后，服务器启动后立即启用自动中继日志恢复。恢复过程创建一个新的中继日志文件，将 SQL（应用程序）线程位置初始化到这个新的中继日志，并将 I/O（接收方）线程初始化到应用程序线程位置。然后继续从源读取中继日志

• replica_parallel_type
  用于决定允许哪些事务在副本上并行执行的策略
  1.LOGICAL_CLOCK：基于复制源写入二进制日志的时间戳，事务在副本上并行应用
  2.DATABASE：更新不同数据库的事务是并行应用的。仅当数据被分区到多个数据库中并在源上独立并同时更新时，此值才适用。

从 MySQL 8.0.27 开始，副本服务器默认启用多线程

• replica_parallel_workers
  设置用于并行执行复制事务的应用程序线程数

• binlog_transaction_dependency_tracking
  指定写入二进制日志的依赖信息

  • sequence_number：给定二进制日志中的第一个事务为 1，第二个事务为 2，依此类推。每个二进制日志文件中的编号从 1 重新开始。

  • last_committed：这是指 sequence_number发现与当前事务冲突的最近提交的事务。该值始终小于 sequence_number。

COMMIT_ORDER：如果第一个事务的提交时间窗口与第二个事务的提交时间窗口重叠，则两个事务被认为是独立的。这是默认值。
WRITESET :依赖信息是从源的写集生成的，任何写不同元组的事务都可以并行化

• replica_preserve_commit_order
  从MySQL 8.0.27开始，默认开启
  确保事务在副本上执行和提交的顺序与它们在副本的中继日志中出现的顺序相同
  正在执行的工作线程会等到所有先前的事务都已提交后再提交。当给定的线程正在等待其他工作线程提交它们的事务时，它将其状态报告为Waiting for preceding transaction to commit

• replica_checkpoint_period
  更新多线程副本的状态之前允许通过的最长时间（以毫秒为单位）

• skip_replica_start
  服务器重启后，复制不会自动开启

• read_only
  服务器不允许客户端更新，除非具有特权 CONNECTION_ADMIN
  如果super_read_only启用，服务器将禁止客户端更新，包括具有 CONNECTION_ADMIN或 SUPER权限的用户。

• gtid_mode
  控制是否启用基于 GTID 的日志记录以及日志可以包含的事务类型

• enforce_gtid_consistency
  ON: 不允许任何事务违反 GTID 一致性。

WARN：允许所有事务违反 GTID 一致性，但在这种情况下会生成警告。

• replica_transaction_retries
  设置单线程或多线程副本上复制 SQL 线程在停止前自动重试失败事务的最大次数 。默认值为 10

2.Innodb

• transaction_isolation
  事务隔离级别

• innodb_buffer_pool_size
innodb缓冲池大小

• innodb_buffer_pool_instances
  InnoDB 缓冲池划分的区域数 减少不同线程读取和写入缓存页面时的争用

• innodb_data_file_path
  定义系统表空间数据文件的名称

• innodb_flush_log_at_trx_commit
  完全符合 ACID 需要默认设置 1。日志在每次提交事务时写入并刷新到磁盘
  设置为 2 时，日志在每次事务提交后写入并每秒刷新到磁盘一次。尚未刷新日志的事务可能会在崩溃中丢失。

• innodb_log_buffer_size
  日志缓冲区大小

• innodb_log_file_size
  redo log大小设置

• innodb_log_files_in_group
  redolog组中的文件数

• innodb_max_undo_log_size
  undolog表空间阈值

• innodb_thread_concurrency
  定义内部允许的innodb 最大线程数 一旦达到限制，额外的线程将被放入“先进先出” (FIFO) 队列中等待线程

• innodb_read_io_threads
  读取操作的io线程数

• innodb_write_io_threads
  写入操作的io线程数

• innodb_commit_concurrency
  可以同时提交的线程数

• innodb_io_capacity
  后台任务可用的每秒 I/O 操作数 (IOPS)

• innodb_io_capacity_max
  最大io_capacity数限制

• innodb_open_files
  InnoDB指定一次可以打开的 最大文件数 。最小值为 10

• innodb_flush_method
  定义用于将数据 刷新到InnoDB 数据文件和日志文件 的方法 ，这会影响 I/O 吞吐量

• innodb_buffer_pool_dump_pct
  每个缓冲池要读出和转储的最近使用页面的百分比

• innodb_lru_scan_depth
  为每个缓冲池实例指定页面清理器线程扫描缓冲池 LRU 页面列表的多远以查找要刷新的脏页面。这是每秒执行一次的后台操作

• innodb_print_all_deadlocks
  记录所有死锁信息到错误日志中

• innodb_online_alter_log_max_size
  online ddl时临时表的最大空间

• innodb_print_ddl_logs
  输出ddl日志

• innodb_status_file
  开启定时15s写入engine innodb status到innodb_status.pid中

3.Innodb Monitor

• innodb_monitor_enable
  启用 InnoDB 指标计数器

4.Pfs

• performance_schema_instrument
  收集性能事件

#配置参考

#innodb settings
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:16M:autoextend
innodb_flush_log_at_trx_commit = 2 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能
innodb_log_buffer_size = 500M
innodb_log_file_size = 2G #如果线上环境的TPS较高，建议加大至1G以上，如果压力不大可以调小
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G
innodb_thread_concurrency = 0
innodb_read_io_threads = 48
innodb_write_io_threads = 48
innodb_commit_concurrency = 48
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 20000
innodb_io_capacity_max = 50000
innodb_open_files = 65535
innodb_flush_method = O_DIRECT
innodb_buffer_pool_dump_pct = 75
#Innodb_io_capacity/innodb_buffer_pool_instances Innodb_io_capacity>innodb_lru_scan_depth*innodb_buffer_pool_instance
innodb_lru_scan_depth = 4000
#innodb_lock_wait_timeout = 10
#innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 1G
innodb_print_ddl_logs = 1
innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log_error文件增长较快
#innodb_status_output = 0
innodb_status_output_locks = 1
innodb_sort_buffer_size = 67108864
innodb_adaptive_hash_index = OFF
#提高索引统计信息精确度
innodb_stats_persistent_sample_pages = 500

#innodb monitor settings
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"
#innodb_monitor_enable = "module_adaptive_hash"

#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'