MariaDB 服务器变量配置说明

1.配置示例

[mysqld]

## * Basic Settings
datadir                 = /data/mysql
bind-address            = 0.0.0.0
skip-name-resolve
transaction-isolation = READ-COMMITTED  ## 事务隔离级别

## * Fine Tuning
max_connections = 300  ## 最大连接数
open_files_limit = 1048576  ## 打开文件数限制

## * Query Cache Configuration
query_cache_size = 0  ## 查询缓存，默认16M
query_cache_type = OFF  ## 默认开启QC

## * Logging and Replication
log_error = /var/log/mysql/error.log
log_bin = OFF  ## 默认关闭二进制日志记录
expire_logs_days = 14  ## 二进制日志保留时间，默认为14天
sync_binlog = 0  ## 设置二进制日志刷盘速度。0为操作系统处理，最不安全但性能最好；1为每次写入都会刷盘，最安全但最慢。

## * Character sets
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

## * InnoDB
default-storage-engine = innodb
innodb_file_per_table = 1
innodb_buffer_pool_size = 8G  ## InnoDB存储引擎的缓冲区
innodb_buffer_pool_instances = 1  ## 如果您有大量 RAM 并且使用的是 5.5（或更高版本），那么请考虑拥有多个池。 推荐 1-16 个，每个不小于 1GB。 （没有衡量这会有多大帮助，可能不是很多）
innodb_flush_log_at_trx_commit = 0
## 默认值为1，日志缓冲区被写入InnoDB重做日志文件，并在每次事务处理后刷新到磁盘，要完全符合ACID，这是必需的；
## 值0为提交时不做任何事情，而是将日志缓冲区每秒写入一次并刷新到InnoDB重做日志中，这样可以提供更好的性能，但是服务器崩溃可以清除最后一秒事务；
## 值2为每次提交后，日志缓冲区都会写入InnoDB重做日志，但刷新操作每秒发生一次，性能稍好一些，但是操作系统崩溃或停电会导致最后一秒的事务丢失；
## 值3为（来自MariaDB 10.0）模拟 MariaDB 5.5 组提交（每个组提交3个同步）。
innodb_flush_method = O_DIRECT
## O_DIRECT 或 directio() 用于打开数据文件，fsync() 用于刷新数据和日志，Unix 上 MariaDB 10.6.0 的默认值；
## fsync（Unix，>= MariaDB 10.3.7 ，<= MariaDB 10.5 ）；
## 未设置 (<= MariaDB 10.3.6 )
innodb_log_file_size = 100M
## InnoDB 重做日志中每个日志组文件的大小（以字节为单位）。合并后的大小不能超过 512GB。 
## 由于较少的刷新检查点活动，较大的值意味着较少的磁盘 I/O，但也会较慢的崩溃恢复。
## 在 MariaDB 10.5 中 ，崩溃恢复得到了改进，不应耗尽内存，因此增加了默认值。它可以安全地设置得更高以减少检查点刷新，甚至大于 innodb_buffer_pool_size 的值。
## 默认值： 100663296(96MB) (>= MariaDB 10.5 ), 50331648(48MB) (<= MariaDB 10.4 )
## 范围： 1048576 到 512GB（1MB 至 512GB）

## * MyISAM
key_buffer_size = 10M  ## MyISAM存储引擎的缓冲区

2.重要服务器变量说明

2.1 内存分配变量：key_buffer_size 和 innodb_buffer_pool_size

2.1.1 快速设置方法（服务器必须有4GB以上的内存）

  • 如果仅使用 MyISAM ，请将 key_buffer_size 设置为 20% 可用 RAM。 （并加上 innodb_buffer_pool_size=0 ）
  • 如果仅使用 InnoDB，请将 innodb_buffer_pool_size 设置为 70% 可用 RAM。 （并加上 key_buffer_size = 10M，足够小但不要为零。）

2.1.2 调整的经验法则

  • key_buffer_size 和 innodb_buffer_pool_size 根据engine使用和RAM来调整。
  • 慢查询通常可以通过索引、模式更改或 SELECT 更改来“修复”，而不是通过调整变量。
  • 如您不了解查询缓存，请不要对查询缓存进行调整， 否则您会感到困惑。
  • 除非遇到麻烦（例如，最大连接数），否则不要更改任何其他内容。
  • 确保更改位于 [mysqld] 部分，而不是其他部分。

2.2 最大连接数：max_connections

最大连接数，线程堆栈每个“线程”都需要一定数量的 RAM。 这曾经是大约 200KB； 100 个线程将是 20MB，不是一个重要的大小。 如果你有 max_connections = 1000，那么将是 200MB，也许更多。拥有这么多的连接可能意味着应该解决其他问题。

2.3 最大用户连接数：max_user_connections（全局缓存一次，根据需要动态增长和缩小到最大限制）

2.4 table_open_cache（全局缓存一次，根据需要动态增长和缩小到最大限制）

操作系统对允许进程打开的文件数量有一定的限制。 每个表需要 1 到 3 个打开的文件。 每个 PARTITION 实际上是一个表。 分区表上的大多数操作都会打开 all 个分区。

在 *nix 中，ulimit 会告诉您文件限制是多少。 最大值为数万，但有时仅设置为 1024。这将您限制为大约 300 个表。

（这一段有争议。）另一方面，表缓存的实现（曾经）效率低下 —— 查找是通过线性扫描完成的。 因此，将 table_cache 设置为数千实际上可能会降低 mysql 的速度。（基准已经表明了这一点。）

您可以通过 SHOW GLOBAL STATUS； 了解您的系统运行情况并通过 Opened_files / Uptime 计算，如果这超过 5， 则应增加 table_open_cache 。 如果它小于1，你可能会通过减少 table_open_cache 得到改进。

从 MariaDB 10.1 开始 ， table_open_cache 默认为 2000。

2.5 table_definition_cache（全局缓存一次，根据需要动态增长和缩小到最大限制）

2.6 thread_cache_size（全局缓存一次，根据需要动态增长和缩小到最大限制）

从 MariaDB 10.2.0 开始没有必要调整 thread_cache_size 。 以前，它是次要的可调变量。 0会减慢线程（连接）的创建速度。 一个小的（比如 10）、非零的数字是好的。 该设置基本上对 RAM 使用没有影响。

它是要挂起的额外进程的数量。不限制线程数； max_connections 确实如此。

2.7 查询缓存：query_cache_type 和 query_cache_size

建议关闭QC，设置：

query_cache_type = OFF
query_cache_size = 0

如果QC适合您，建议设置：

  • query_cache_size = no more than 50M
  • query_cache_type = DEMAND
  • 所有 SELECT 中 的 SQL_CACHE 或 SQL_NO_CACHE，基于哪些查询可能受益于缓存。

为什么要关闭 QC：http://dba.stackexchange.com/a/136814/1876
关于QC大小的讨论：https://haydenjames.io/mysql-query-cache-size-performance/

2.8 二进制日志：log_bin

如果您打开了二进制日志记录 （log_bin = ON，通常是为了复制和时间点恢复），系统将永远创建二进制日志。也就是说，它们可以慢慢填满磁盘。建议设置 expire_logs_days = 14 以仅保留 14 天的日志。

2.9 Swappiness

RHEL 以其无穷的智慧决定让您控制操作系统抢先交换 RAM 的积极程度。 这总体上很好，但对 MariaDB 来说很糟糕。

MariaDB 希望 RAM 分配合理稳定——缓存（大部分）是预先分配的； 线程等（主要是）范围有限。 任何交换都可能严重损害 MariaDB 的性能。

如果 swappiness 值很高，你会失去一些 RAM，因为操作系统试图为将来的分配保留大量可用空间（MySQL 可能不需要）。

如果 swappiness = 0，操作系统可能会崩溃而不是交换。 我宁愿让 MariaDB 一瘸一拐地死去。 最新的建议是 swappiness = 1。（2015）

2.10 NUMA（非统一内存访问）

每个 CPU（或者可能有多个内核的插槽）都有一部分 RAM 挂在每个 CPU 上。 这导致本地 RAM 的内存访问速度更快，但挂在其他 CPU 上的 RAM 访问速度更慢（慢了几十个周期）。

dmesg | grep -i numa  # 查看是否有numa

另一种可能的解决方案：关闭 numa（如果操作系统有办法做到这一点）

整体性能损失/收益：百分之几。

2.11 Huge Pages

整体性能提升：几个百分点。 太麻烦而收益太少。

Huge Pages？ 关掉。

# vim /etc/systemd/system/disable-transparent-huge-pages.service
[Unit]
Description=Disable Transparent Huge Pages (THP)
DefaultDependencies=no
After=sysinit.target local-fs.target
Before=mongod.service

[Service]
Type=oneshot
ExecStart=/bin/sh -c 'echo never | tee /sys/kernel/mm/transparent_hugepage/enabled > /dev/null'

[Install]
WantedBy=basic.target

# systemctl daemon-reload
# systemctl start disable-transparent-huge-pages
# systemctl enable disable-transparent-huge-pages
# cat /sys/kernel/mm/transparent_hugepage/enabled
posted @ 2021-09-29 13:56  Varden  阅读(579)  评论(0)  编辑  收藏  举报