Mysql参数配置、性能调优

2024-09-17 2024-09-17 约 2400 字预计阅读 5 分钟 - 次阅读

通过调整 MySQL 配置参数，使数据库服务器能够更高效地利用系统资源（如 CPU、内存、磁盘 I/O 等），从而提高数据库的性能、稳定性和可扩展性。

配置示例

以下是一份针对 4 核 8 GB 内存的机器的 MySQL 8.0 调优配置文件（my.cnf）。这份配置文件旨在优化 MySQL 的性能，使其在你的硬件配置上运行得更高效。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77


[mysqld]
# 基本设置
user = mysql
port = 3306
bind-address = 0.0.0.0
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# 最大连接数，根据项目实际节点数及实例的配置进行调整。
# 每一个连接mysql都会创建一个线程来维护，此值不宜过大，一般在1000以内。
max_connections = 200

# 线程缓存大小
thread_cache_size = 30

# 表缓存大小
table_open_cache = 500

# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

# 排序缓冲区大小
sort_buffer_size = 4M

# 连接缓冲区大小
join_buffer_size = 4M

# 查询缓存（MySQL 8.0 已弃用查询缓存）
query_cache_type = 0
query_cache_size = 0

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询的时间阈值，单位是秒，可以是小数或整数。
long_query_time = 2

# 错误日志
log_error = /var/log/mysql/error.log

# InnoDB 设置
innodb_buffer_pool_size = 5G  # 设置为物理内存的 70-80%
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 16
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# 其他优化参数
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

# 网络设置
max_allowed_packet = 64M
net_buffer_length = 16K

# 连接超时设置
wait_timeout = 600
interactive_timeout = 600

# 日志文件大小
log_bin_trust_function_creators = 1
expire_logs_days = 10
max_binlog_size = 100M

# 复制设置（如果使用复制）
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
relay_log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = 1

释义

基本设置

user：运行 MySQL 的用户。
port：MySQL 监听的端口。
bind-address：绑定的 IP 地址。
datadir：数据目录。
socket：套接字文件。

最大连接数

max_connections：设置最大并发连接数。

线程缓存

thread_cache_size：设置线程缓存的大小，以减少线程创建和销毁的开销。

表缓存

table_open_cache：设置表缓存的大小，以减少表打开和关闭的开销。

临时表

tmp_table_size 和 max_heap_table_size：设置临时表的最大大小。

排序和连接缓冲区

sort_buffer_size 和 join_buffer_size：设置排序和连接操作的缓冲区大小。

查询缓存

query_cache_type 和 query_cache_size：MySQL 8.0 已弃用查询缓存，设置为 0。

慢查询日志

slow_query_log：启用慢查询日志。
slow_query_log_file：慢查询日志文件的位置。
long_query_time：定义慢查询的时间阈值，时间单位：秒，可以是整数或小数。

错误日志

log_error：设置错误日志文件的位置。

InnoDB 设置

innodb_buffer_pool_size：设置 InnoDB 缓冲池的大小，通常为物理内存的 50%。
innodb_log_file_size：设置 InnoDB 日志文件的大小。
innodb_flush_log_at_trx_commit：控制事务日志的刷新频率。
innodb_thread_concurrency：设置 InnoDB 的最大并发线程数。
innodb_flush_method：设置 InnoDB 的刷新方法。
innodb_file_per_table：每个表使用单独的表空间文件。

其他优化参数

key_buffer_size：设置 MyISAM 索引缓存的大小。
read_buffer_size 和 read_rnd_buffer_size：设置读缓冲区的大小。
bulk_insert_buffer_size：设置批量插入缓冲区的大小。
myisam_sort_buffer_size：设置 MyISAM 排序缓冲区的大小。
myisam_max_sort_file_size：设置 MyISAM 最大排序文件大小。
myisam_repair_threads：设置 MyISAM 修复线程数。

网络设置

max_allowed_packet：设置最大允许的包大小。
net_buffer_length：设置网络缓冲区的长度。

连接超时设置

wait_timeout 和 interactive_timeout：设置连接超时。

日志文件大小

log_bin_trust_function_creators：允许创建函数和触发器。
expire_logs_days：设置日志文件的过期天数。
max_binlog_size：设置二进制日志文件的最大大小。

复制设置（如果使用复制）

server-id：服务器 ID。
log_bin：二进制日志文件的位置。
binlog_format：二进制日志格式。
relay_log：中继日志文件的位置。
log_slave_updates：记录从服务器的更新。

线程模型

MySQL 使用的是“一连接一线程”模型，即每个客户端连接都会分配一个独立的线程来处理该连接的所有请求。这种模型简单直观，但在高并发环境下可能会带来一些性能和资源管理上的挑战。

一连接一线程模型

每个连接一个线程：每当一个客户端连接到 MySQL 服务器时，服务器会为该连接创建一个独立的线程。这个线程负责处理该连接的所有 SQL 请求，直到连接关闭。
线程开销：线程的创建和销毁是有开销的，特别是在高并发环境中，频繁的线程创建和销毁会影响性能。
资源消耗：每个线程都会消耗一定的系统资源（如内存、CPU 时间），大量的并发连接可能会导致资源耗尽。

线程缓存

为了减少线程创建和销毁的开销，MySQL 提供了线程缓存机制，通过 thread_cache_size 参数来控制线程缓存的大小。当一个连接断开时，MySQL 会将该连接的线程放入线程缓存中，以便下次有新的连接请求时可以重用这个线程，而不需要重新创建线程。

线程池插件

MySQL Enterprise Edition 提供了线程池插件，可以更高效地管理线程，减少线程创建和销毁的开销。线程池插件允许多个连接共享一组线程，从而提高并发处理能力。

线程池的优点

减少线程开销：通过共享线程池，减少了线程创建和销毁的开销。
提高并发处理能力：线程池可以更高效地管理并发连接，提高系统的整体性能。
更好的资源管理：线程池可以更好地管理系统资源，避免资源耗尽的问题。

示例：启用线程池插件

在 MySQL 配置文件（my.cnf 或 my.ini）中启用线程池插件：

1
2
3
4
5
6
7
8


[mysqld]
# 启用线程池插件
plugin-load-add=thread_pool.so

# 配置线程池参数
thread_pool_size=8
thread_pool_stall_limit=6
thread_pool_max_threads=1000

收录于合集・Mysql调优 9

Mysql慢查询