背景

在 dbops 1.0 版本中，部署 MySQL 时就支持"规格"这个功能。随着 dbops 1.2 版本的即将发布，规格功能在 MySQL8.4 得到了进一步优化和应用。本文将解释我开发"规格"功能的原因，并为 dbops 1.2 版本发布预热。

如果你对 dbops 不太了解，可以点击这里了解更多 ->《Code as Standrad —— dbops 开源了》

请注意，尽管本文多次提及 dbops，但我的目的是为了帮助读者更好地使用 MySQL。建议大家耐心阅读全文，以免错过重要信息。

希望你喜欢这篇文章，期待你的反馈和交流！

dbops 如何保证 MySQL 参数最佳实践

在解释我为什么要开发"规格"功能之前，有必要让读者了解 dbops 如何保证 MySQL 参数的最佳实践。

我所在的运维团队最高峰时管理着 3000 多个数据库实例。我们对 MySQL 参数的最佳实践进行了深入研究，特别是在安全方面的设计。例如，我们建议使用 row 格式的 binlog，并设置"双一"参数。许多参数对数据库的性能和安全性至关重要，因此我们在 my.cnf 参数模板中收录了大量参数。

好消息是，只要你下载了 dbops，就可以立刻拥有这些参数设置！

在过去，dbops 在某个角度上，对参数的分类如下

强制默认值

强制默认值的意思是，即便设置的值与 MySQL 官方的默认值一致，我仍然会在 my.cnf 文件中明确指定这些值。

autocommit                               = ON                                        # ON

例如，autocommit 设置为 ON。这些参数非常重要，因此需要在交维检查时重点关注。同时，MySQL 8.0 及以前的版本经常修改参数默认值，强制默认值可以防止数据库升级后参数默认值变化，例如下面这个，不同小版本，默认值会发生变化。

最佳实践值

这些参数就更重要了，这些配置没有使用官方默认值，而是使用 dbops 认为的最佳实践值。例如这个，dbops 禁用了除了 InnoDB 外的其他存储引擎，用户无法创建这些存储引擎的对象。

disabled_storage_engines                 = archive,blackhole,example,federated,memory,merge,ndb,myisam  # empty

用户可改值

dbops 为生产级的 MySQL 部署而生，所以大多数配置都会使用 强制默认值 和 最佳实践值 固化下来，我称之为参数标准规范。但有时候用户依然有少部分个性化参数修改的需求，例如最大连接数、事务隔离级别，等等。dbops 支持用户自定义这部分内容，在部署前修改 common_config.yml 即可。暂时可供修改的参数有这些:

## mysql my.cnf
mysql_binlog_format: row
mysql_innodb_log_buffer_size: 64M
mysql_innodb_open_files: 65535
mysql_max_connections: 1000
mysql_thread_cache_size: 256
mysql_character_set_server: utf8mb4
mysql_transaction_isolation: READ-COMMITTED
mysql_default_time_zone: "+8:00"
mysql57_innodb_log_files_in_group: 16
mysql57_innodb_log_file_size: 256M
use_write_set: 1
sock_type: 1   # 1 for ${datadir}/mysql.sock,  2 for /tmp/mysql${port}.sock,  3 for ${mysql_data_dir_base}/run/mysql${port}.sock
mgr_use_random_uuid: 1

虽然 dbops 提供对这些参数修改，但无论如何，dbops 的参数默认值依然是更佳选择，适用场景更广。

我随机挑两个参数讲解，例如:

• binlog_format

• 可选设置项有 statement、mixed 和 row。dbops 参数规范值选择 row，这是最佳实践，因为 row 格式能够记录每行的数据变化，而不仅仅是 SQL 语句。
• MySQL 官方在 8.4 版本中已将 binlog_format 参数标记为已废弃，在未来的 9.0 版本中将移除此参数，届时只有 row 格式。
• 一些公司为了节省磁盘空间可能会采用 mixed 格式（包括一些云厂商）。但 mixed 格式的缺点是在发生数据误操作时，无法使用 binlog2sql 等工具进行闪回操作，只能依赖备份恢复技术进行回退。这大大降低了数据恢复的灵活性和效率。

• innodb_log_buffer_size：

• 该参数决定了 InnoDB 存储引擎的日志缓冲区大小。实际上，MySQL 8.0 的官方默认值为 16M。我们通过性能测试认为这个值太小了，所以 dbops 将默认值调整为 64M。
• 在 MySQL 8.4 版本中，官方也将该参数的默认值上调为 64M。也也证明了 dbops 的参数设计总是有前瞻性的，这只是其中的一个小例子。
• 我们直接将 innodb_log_buffer_size 固化为 64M，但仍然允许用户在 common_config.yml 中修改这个值。这是因为，如果你在部署前已经知道数据库会有较多的大事务，那么可以将这个值上调，例如设置为 128M，以适应更大的事务处理需求。

自动计算生成值

dbops 有很多参数是自动计算生成的，这些参数一般无需用户手动填写或关注。dbops1.2 的 MySQL8.4 参数模版包含以下 33 个参数，都是自动计算生成的。

user                                   
basedir                                
datadir                                
tmpdir                                 
log_bin                                
relay_log                              
innodb_log_group_home_dir              
port                                   
admin_port                             
mysqlx_port                            
socket                                 
mysqlx_socket                          
character_set_server                   
max_connections                        
transaction_isolation                  
innodb_open_files                      
innodb_log_buffer_size                 
default_time_zone                      
server_id                              
report_host                            
thread_cache_size                      
table_open_cache_instances             
replica_parallel_workers               
innodb_read_io_threads
innodb_purge_threads
innodb_parallel_read_threads
temptable_max_ram
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_redo_log_capacity
loose-group_replication_group_name
loose-group_replication_local_address
loose-group_replication_group_seeds

之前我写过一篇文章《为什么使用 dbops 你无需关心 server_id？》，讲解了其中一个参数 server_id 是如何自动生成的。

什么是"规格"功能

"规格"功能提供一个 dbops 参数，让用户在部署 MySQL 时，告知 dbops 部署的 MySQL 实例的 CPU 和内存配置。默认值为 auto，即自动获取部署主机的 CPU 核数和内存大小。用户也可以手动设置，例如 server_specs: "4c8g" 表示 4 个逻辑 CPU 和 8GB 内存。

# The 'server_specs' variable defines the hardware specifications for the server deployment.
# - If set to "auto", the script will automatically fetch and use the server's current hardware specifications.
# - If set to a specific value like "4c8g", the script will deploy the server based on these specifications (4 cores and 8GB RAM in this example).
# Example usage:
# server_specs: "auto"
# server_specs: "4c8g"
server_specs: "auto"

我为什么要开发"规格"这个功能

前面我介绍了 dbops 如何保证 MySQL 参数最佳实践，而"规格"功能的诞生之初是为了在 MySQL 混合部署时，仍能自动计算出 MySQL 参数的最佳实践值。

以下情况，你可能会使用到"规格"功能:

1) 多实例混合部署的场景

我们最常需要修改的 MySQL 性能相关参数是 innodb_log_buffer_size，目前 dbops 固化的设置是内存总量的 60%。在独享服务器上部署 MySQL 时，这样的设置运作良好。但是，如果在一台服务器上混合部署 10 个 MySQL 实例，这样的自动化设置会导致内存超卖 10 倍，最终导致数据库实例 OOM（内存不足错误）。"规格"功能的诞生就是为了在 MySQL 混合部署时，依然能够自动计算出适当的 innodb_log_buffer_size 值。对于一台 40c512g 的服务器，你可以利用"规格"功能创建十台 4c50g 规格的 MySQL 实例，确保每个实例都能正确设置 innodb_log_buffer_size，避免内存不足的情况。

2) 主从机器资源配置不一致场景

运维规范里强调，主从数据库实例应保持配置一致性，包括参数一致性、系统配置一致性和主机资源配置一致性等。dbops 能很好地保证前两点，但主机资源配置一致性有时难以保证。例如，业务提供了两台机器分别是 4c8g 和 16c64g 来部署 MySQL。在过去，dbops 会自动计算出第一台机器的 innodb_log_buffer_size 为 4.8GB（8GB 的 60%），第二台机器的 innodb_log_buffer_size 为 38.4GB（64GB 的 60%），这显然是不一致的。根据短板效应，这个数据库集群的性能只能达到 innodb_log_buffer_size 为 4.8GB 的水平。此时可以采用"规格"功能，设置 server_specs: "4c8g"，使得所有实例的配置一致，同时节省第二台机器的 CPU 和内存资源，可用于其他用途。

3) 超卖的场景

如果你对 dbops 计算出的默认值不满意，认为总体偏小，你也可以选择超卖。因为"规格"是你告诉 dbops 的服务器配置规格。例如，实际服务器是 4c8g，但你可以通过设置 server_specs: "8c16g" 来调大所有配置，使得系统能够在更高的规格下运行。

dbops1.2 版本的即将发布，规格功能在 MySQL8.4 得到了进一步优化和应用

在 dbops1.2 版本里，我重新梳理了 MySQL8.4 的参数，并对参数模版做了以下优化

• 更明确的参数开关
• 删除过期或将要弃用的参数
• 修正了参数默认值
• 修正了参数名称
• 更好的参数分类
• 利用规格功能模拟官方的参数计算

更明确的参数开关

sync_binlog                             = 1                                         # 1
replica_preserve_commit_order           = ON                                        # ON

在过去，由于 MySQL 布尔值开关类型参数允许用 1 替代 ON，0 替代 OFF，这会导致我们在设置某些参数时产生混淆。为了解决这个问题，在 dbops1.2 的 MySQL8.4 参数模板中，所有的布尔类型参数都只使用 ON 和 OFF 两种参数值，并且强制大写。这样用户可以更清晰地区分哪些参数设置的是 int 类型的 0 或 1，而不会误以为它们是布尔类型的 ON 或 OFF。通过这种方法，我们消除了参数设置中的混淆，使配置过程更加明确和可靠。

删除过期或将要弃用的参数

例如这些参数，他们过期，配置上去会导致 MySQL 无法启动，或者由于弃用(启动时有警告)，而被我删除了。

binlog_format 删了
master_info_repository 删了
relay_log_info_repository 删了
slave_rows_search_algorithms 删了
transaction_write_set_extraction 删了
binlog_transaction_dependency_tracking 删了
group_replication_allow_local_disjoint_gtids_join 删了
group_replication_allow_local_lower_version_join 删了

修正了参数默认值

在 dbops 的参数模板中，会自带 MySQL 的官方默认值，以便运维同学不用翻阅官方文档就能了解这些默认值。具体方法是在参数后面加注释，例如：

auto_increment_offset                    = 1                                         # 1
lower_case_table_names                   = 1                                         # 0
open_files_limit                         = 65536                                     # 5000, with possible adjustment
table_open_cache                         = 4000                                      # 4000
table_definition_cache                   = 2000                                      # MIN(400 + table_open_cache / 2, 2000)
local_infile                             = OFF                                       # OFF

值得注意的是，dbops 的 5.7 和 8.0 参数模板中的官方默认值注释并不总是准确的，而在 8.4 版本中，我对比了 MySQL 8.4.0 的官方文档，重新核对和修正了这些默认值。通过这样的注释方法，用户可以更加清楚地了解每个参数的默认值，从而减少配置过程中的误解和错误，提高工作效率。

修正了参数名称

由于 Oracle 官方此前开展的去除种族歧视参数名称的运动，以下参数名称得到了修正：

plugin_load                              = "rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so"
loose-rpl_semi_sync_source_enabled      = ON                                        # OFF
loose-rpl_semi_sync_source_timeout      = 1000                                      # 10000(10 second)
loose-rpl_semi_sync_replica_enabled     = ON                                        # OFF
replica_parallel_workers                = 32    # 0
log_replica_updates                     = ON                                        # ON
replica_preserve_commit_order           = ON                                        # ON
skip_replica_start                      = OFF                                       # OFF
#replica_parallel_type                   = LOGICAL_CLOCK                             # LOGICAL_CLOCK, is deprecated and will be removed in a future release

更好的参数分类

我在参数模版里，用下面这种注释的方式，把参数分成了三大类。

##: User Configuration
####: Global Configuration Defaults
####: Customizable User Settings
##: Automatic Settings
####: Auto-Generated by dbops
####: Special Calculations by dbops
####: Dedicated Server Settings or Auto-Generated by dbops Specifications
##: Standard Configurations

本来我写了一大段，被我删除了，有兴趣了解的同学可以直接阅读我的 my.cnf ansible jinja2 模版源代码，路径:

https://gitee.com/fanderchan/dbops/blob/dbops1.2/mysql_ansible/roles/mysql_server/templates/8.4/my.cnf.j2

利用规格功能模拟官方的参数计算

MySQL 8.4 的一大改进是参数优化，具体体现在：

• 许多参数的默认值被修改为更合理的值。
• 废弃了一些没有实际好处的参数。
• 许多性能相关的参数不再是固定值，而是根据服务器规格动态计算。

对于最后一项改进，在独享服务器上通常能很好地运行，但在混合部署的环境下，可能会导致计算错误。这就是 dbops 的"规格"功能解决的问题。

参考文章: MySQL 8.4 LTS - New Production-Ready Defaults for InnoDB

在 dbops 中，8.4 参数模板的生成逻辑如下：

1. 独享服务器设置 (server_specs: "auto" 默认值)：

当 server_specs 设置为 "auto" 时，dbops 会认为这是一个独享的 MySQL 服务器。在这种情况下，MySQL 官方自动计算的性能参数不会在 my.cnf 文件中明确指定。这样可以利用官方的自动计算优势，并且会启用 innodb_dedicated_server=ON 参数，以自动设置两个核心性能参数：innodb_buffer_pool_size 和 innodb_redo_log_capacity。

2. 指定服务器规格设置 (server_specs: "4c8g")：

当 server_specs 设置为类似 "4c8g"（表示 4 核 CPU 和 8GB 内存）的规格时，dbops 会根据这个规格来设置 MySQL 服务器。这通常用于 MySQL 多实例混合部署场景。dbops 会模拟官方的计算公式，生成以下参数的最佳实践：

####: Dedicated Server Settings or Auto-Generated by dbops Specifications
innodb_read_io_threads=4  # (available logical processors / 2), min of 4
innodb_purge_threads=1  # 1 if # of available logical processors is <= 16; otherwise 4
innodb_parallel_read_threads=4  # Default value: calculated by the number of available logical processors on the system divided by 8, with a minimum default value of 4
temptable_max_ram=1GB  # 3% of total memory: min 1 GB, max 4 GB
innodb_buffer_pool_size=4916M  # 134217728(128M)  # dbops set it 60% of total memory
innodb_buffer_pool_instances=1 # 1 if innodb_buffer_pool_size <= 1 GiB; otherwise min(1/2*(innodb_buffer_pool_size/innodb_buffer_pool_chunk_size), 1/4*CPU cores), range 1-64.
innodb_redo_log_capacity=2GB  # Setup by dbops, refer to the implementation of innodb_dedicated_server. Redo log capacity: (logical processors / 2) GB, max 16 GB.
innodb_dedicated_server=OFF  # OFF

关于 dbops

在 dbops1.0 之前，dbops 已经支持性能参数的自动计算。为了更好地计算参数的最佳实践，dbops 引入了规格功能，并在 8.4 版本后进一步优化了官方的计算公式。dbops 1.2 的新特性主要是支持 MySQL 8.4 LTS 的部署。

gitee 仓库地址:

https://gitee.com/fanderchan/dbops

dbops1.2 开发版下载方式

git clone https://gitee.com/fanderchan/dbops.git
git checkout dbops1.2

dbops1.2 仍在开发中，如有开发需求或遇到 Bug，请提交问题(issue)，您添加我的微信我邀请您加入我们的微信群，与我们一起探讨 dbops，以及 MySQL、openGauss、ansible 的相关话题。dbops 是高效的 playbook 工具集，保证绝对不收费。该微信群仅供学习交流使用，没有任何广告或商业活动。