韭菜生活
MySQL 配置优化
编辑
2024-08-24
高性能 MySQL
0

目录

什么不应该做
配置介绍
基本配置
内存相关配置
InnoDB 缓冲池
线程缓存
I/O 相关配置
InnoDB 事务日志
日志缓冲区
InnoDB表空间
并发相关配置
安全相关配置
高级 InnoDB 配置
最小资源配置

本文对应原书第5章-优化服务器设置。出乎意料的是，该章节建议正确地配置基本设置，并将更多的时间花在schema优化、索引和查询设计上。甚至有innodb_dedicated_server这种自动配置 MySQL 的选项。

限于本人水平不足，本文只是对相关配置的分类整理，列出的配置无法给出测试。文章的最后展示了自己使用的最小资源配置，仅供参考。

什么不应该做

建立基准测试套件

它需要大量的工作和研究，而且大多时候，潜在的回报非常小，因此是巨大的时间浪费。最好把这些时间花在其他事情上，比如检查备份、监控查询计划的变化等。

按比率调优

调优比率是一个经验法则。比如，InnoDB缓冲池命中率应该高于某个百分比，如果命中率过低，应该增加缓存大小。这是非常错误的建议。缓存命中率与缓存是太大还是太小无关。

  • 命中率取决于工作负载。不管缓存有多大，有些工作负载根本不能被缓存
  • 缓存命中是没有意义的。

使用调优脚本

“调优”服务器可能是一种惊人的时间浪费。在没有经过测试和理解的情况下进行应用也可能是危险的，因为它可能针对的是一种与你不同的情况，而你却没有理解。

相信流行的内存消耗公式

MySQL本身在崩溃时会输出的那个公式（没听说过，反正不建议用）。这个公式是很早之前的。这不是一个可靠的，甚至不是一个有用的方法来了解MySQL在最坏的情况下可以使用多少内存。事实是，你不能给MySQL的内存消耗设定上限。MySQL并不是一个严格控制内存分配的数据库服务器。

配置介绍

确定配置文件位置

shell
$ mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

配置文件格式

  • MySQL 配置文件是 INI 标准
  • 配置项全部使用小写字符，单词之间用下划线或短横线（等效的）。

配置项是有作用域的

  • 全局作用域（服务器）
  • 会话作用域（连接）
  • 对象作用域

能够在服务器运行时更改的配置称为动态配置变量，MySQL 重启后会失效。

  • 如果在服务器运行时设置变量的全局值，则当前会话和其他现有会话的值将不受影响。
  • MySQL 8.0引入了新的语法SET PERSIST，允许在运行时设置一次值，MySQL将把这个设置写入磁盘，以便在下次重启后继续使用该值。
  • 动态设置变量可能会产生意想不到的副作用。在线更改设置时要小心，可能会导致服务器执行大量工作。

基本配置

下面是一个最小化的配置文件，基于 MySQL 8.0。虽然看起来配置项不多，但是已经超过了很多人的需要。

ini
[mysqld]
# GENERAL
datadir = /var/lib/mysql
socket  = /var/lib/mysql/mysql.sock
pid_file= /var/lib/mysql/mysql.pid
user    = mysql
port    = 3306
# INNODB
innodb_buffer_pool_size         = <value>
innodb_log_file_size            = <value>
innodb_file_per_table           = 1
innodb_flush_method             = O_DIRECT
# LOGGING
log_error         = /var/lib/mysql/mysql-error.log
log_slow_queries  = /var/lib/mysql/mysql-slow.log
# OTHER
tmp_table_size    = 32M
max_heap_table_size            = 32M
max_connections   = <value>
thread_cache_size = <value>
table_open_cache  = <value>
open_files_limit  = 65535
[client]
socket  = /var/lib/mysql/mysql.sock
port    = 3306

内存相关配置

InnoDB 缓冲池

参考MySQL中innodb_buffer_pool_size的配置

线程缓存

thread_cache_size

指定了MySQL可以保存在缓存中的线程数。设置该值的一个好的方法是观察Threads_connected变量，并尝试将thread_cache_size设置得足够大，以处理工作负载中的典型波动。例如，如果Threads_connected通常保持在100到120之间，那么可以将缓存大小设置为20。如果停留在500到700之间，那么将缓存大小设置为200也足够大了。

I/O 相关配置

InnoDB 的缓冲区和文件 图5-1：InnoDB的缓冲区和文件

InnoDB 事务日志

事务在提交是不是直接写到数据表空间的，而是先写入事务日志中，这样做可以将随机I/O转为顺序I/O，减小开销。但是事务日志使用循环缓冲区，当发生环绕时需要将之前的日志刷新到磁盘。

日志文件的总大小由innodb_log_file_size和innodb_log_files_in_group控制。

日志缓冲区

InnoDB修改数据时会将修改记录写入日志缓冲区，并将其保存在内存中。当缓冲区满了、事务提交时，或者每秒1次（这三个条件以先满足者为准），InnoDB会将缓冲区刷新到磁盘上的日志文件中。如果有大型事务，增加缓冲区大小（默认为1MB）有助于减少I/O。

innodb_log_buffer_size

设置控制缓冲区大小，通常不需要将缓冲区设置得太大。建议的范围是1～8MB，一般来说足够了，除非写入很多大的BLOB记录。

innodb_flush_log_at_trx_commit

用来控制日志缓冲区的刷新位置和刷新频率。

  • 0: 每秒定时将日志缓冲区写入日志文件，并刷新日志文件，但在事务提交时不做任何操作。
  • 1: 每次事务提交时，将日志缓冲区写入日志文件，并将其刷新到持久存储中。这是默认的（也是最安全的）设置；它保证你不会丢失任何已提交的事务，除非磁盘或操作系统“假装”进行刷新操作（没有将数据真正写入磁盘）。
  • 2: 每次事务提交时都将日志缓冲区写入日志文件，但不执行刷新。InnoDB按计划每秒刷新1次。与0设置最重要的区别是，如果只是MySQL进程崩溃，设置为2不会丢失任何事务。但是，如果整个服务器崩溃或断电，仍然可能丢失事务。

了解将日志缓冲区写入日志文件和将日志刷新到持久存储之间的区别很重要。在大多数操作系统中，将缓冲区写入日志只是将数据从InnoDB的内存缓冲区移动到操作系统的缓存中，依然还是在内存中。

innodb_flush_method

该选项允许配置InnoDB与文件系统的实际交互方式。如果你使用的是类UNIX操作系统，并且RAID控制器有备用电池的写缓存，建议使用O_DIRECT。如果不是，则default或O_DIRECT都可能是最佳选择，具体取决于应用程序。

InnoDB表空间

innodb_file_per_table

该选项允许你将InnoDB配置为每个表使用单独的文件。它将数据存储在数据库目录下的tablename.ibd文件中。这使得删除表时更容易回收空间。然而，将数据放在多个文件中实际上会导致更多的空间浪费，因为跟InnoDB单个表空间中的内部碎片相比，每个.ibd文件中都会有一些浪费的空间。

该选项的缺点（从8.0.23版本开始，这应该不再是一个问题了。）

  • 删除表将在文件系统级别解除（删除）文件的链接，这在某些文件系统（ext3）中可能非常慢。
  • 删除表空间需要InnoDB在查找属于该表空间的页面时锁定和扫描缓冲池，这在服务器的缓冲池很大时是非常慢的。

最后的建议是使用innodb_file_per_table并限制共享表空间的大小。

如果事务长时间保持打开状态（即使没有做任何工作），并且使用默认的可重复读取事务隔离级别，InnoDB将无法删除行的旧版本，因为未提交的事务仍需要能够看到它们。InnoDB将旧版本存储在表空间中，因此随着更多数据的更新，它将继续增长。相关配置如下。

innodb_purge_threads

设置后台清除操作的线程数。

innodb_purge_batch_size

设置从历史列表中清除一批解析和处理的undo log页数。

innodb_max_purge_lag

  • 非0: 在InnoDB开始延迟更多修改数据的查询之前，可以等待清除的最大事务数。

其他 I/O 配置项

sync_binlog

选项控制MySQL如何将二进制日志刷新到磁盘，默认值是1，意味着MySQL将执行刷新并保持二进制日志的持久性和安全性。强烈推荐将其设置为1，不建议设置为任何其他值。

并发相关配置

旧版本的MySQL（低于5.7）面临许多高并发可伸缩性的挑战。所有的东西都在诸如缓冲池互斥锁之类的全局互斥锁上排队，导致服务器几乎停止运行。如果升级到较新版本的MySQL，在大多数情况下不需要限制并发性。

innodb_thread_concurrency

该变量限制了内核中同时可以有多少线程。

  • 0表示对线程的数量没有限制。
  • 建议设置为与可用CPU核数相同的值，然后根据需要调整大小。

innodb_thread_sleep_delay

如果内核中已经有超过允许数量的线程，则新的线程不能进入内核。InnoDB使用一个两阶段的过程来尝试让线程尽可能高效地进入内核。两阶段策略减少了操作系统调度器导致的上下文切换开销。线程首先休眠innodb_thread_sleep_delay指定的微秒数，然后再重试。如果仍然不能进入，它将进入一个等待线程队列，将控制权交给操作系统。

innodb_concurrency_tickets

一旦线程进入内核，InnoDB就有一定数量的“门票”，可以“免费”返回内核，而无须任何并发性检查。这限制了它在返回到其他等待的线程队列之前可以完成的工作量。innodb_concurrency_tickets选项控制“门票”的数量。除非有很多非常长时间运行的查询，否则很少需要更改这个选项。“门票”是根据查询而不是事务授予的。一旦查询完成，未使用的门票将被丢弃。

innodb_commit_concurrency

该变量控制着可以同时提交的线程数。表示允许同时提交任意数量的事务。

安全相关配置

max_connect_errors

相当于连接重试次数，超过后服务器将拒绝该客户端进行连接，除非刷新 Host Cache。如果启用了skip_name_resolve，则max_connect_errors选项将无效，因为其行为取决于主机缓存，而主机缓存被skip_name_resolve禁用。

max_connections

此设置类似于紧急刹车，以防止服务器被来自应用程序的大量连接压垮。注意观察随时间变化的max_used_connections状态变量。这是一个高水位线，可以显示服务器是否在某个时刻出现了连接高峰。如果到达max_connections，则客户端可能至少被拒绝了一次。

skip_name_resolve

当客户端连接服务器时，服务器会进行主机名检查，这需要反向DNS。如果DNS出现问题会出现连接超时。建议关闭该选项。

sql_mode

设置服务器SQL模式。模式会影响 MySQL 支持的 SQL 语法及其执行的数据验证检查。

sysdate_is_now

确保sysdate和now返回的日期是一样的。

read_only和super_read_only

read_only选项可防止未经授权的用户对副本进行更改，副本应仅通过复制而不是从应用程序接收更改。更严格的只读选项super_read_only，它甚至可阻止拥有SUPER权限的用户写入数据。启用此功能后，唯一可以将更改写入数据库的就是复制。

高级 InnoDB 配置

innodb_autoinc_lock_mode

参考MySQL innodb_autoinc_lock_mode 详解

innodb_buffer_pool_instances

在MySQL 5.5及更新的版本中，此设置将缓冲池划分为多个段，这可能是提高多核机器上MySQL在高并发工作负载下可伸缩性最重要的方法之一。多个缓冲池对工作负载进行分区，这样一些全局互斥体就不会成为争用热点。

innodb_io_capacity

参考MYSQL io_capacity 哥俩，你调了吗？

innodb_read_io_threads和innodb_write_io_threads

这些选项控制有多少后台线程可用于I/O操作。

innodb_strict_mode

此设置使InnoDB在某些情况下抛出错误而不是警告，尤其是无效或可能导致危险的CREATE TABLE选项。

innodb_old_blocks_time

参考Innodb存储引擎的几个小知识点

最小资源配置

自己也在低配置的 VPS 上用 Docker 搭建了 MySQL，以下是资源占用最小的配置，MySQL 版本为9.0，刚启动时内存占用150MB。

ini
[mysqld]
default-time-zone='+08:00'

performance_schema=off

innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
max_connections=50
key_buffer_size=8
thread_cache_size=1
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000
table_open_cache=1000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8192
read_rnd_buffer_size=8192
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K

innodb_log_file_size=4194304
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
skip_name_resolve=ON
sync_binlog=0
skip-log-bin

本文作者:jdxj

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！