mysql5.7 常用参数
max_connections
命令行格式 | --max-connections=# |
---|---|
系统变量 | max_connections |
范围 | global |
动态的 | 是的 |
类型 | 整数 |
默认值 | 151 |
最小值 | 1 |
最大值 | 100000 |
允许的最大同时客户端连接数。最大有效值是open_files_limit-810的有效值和为max_connections实际设置的值中的较小值。
open_files_limit
命令行格式 | --open-files-limit=# |
---|---|
系统变量 | open_files_limit |
范围 | global |
动态的 | 不 |
类型 | 整数 |
默认值 | 5000, with possible adjustment |
最小值 | 0 |
最大值 | platform dependent |
有效值计算公式
-
10 + max_connections + (table_open_cache * 2)
-
max_connections * 5
-
如果该限制为正但不是 Infinity,则为操作系统限制。
-
如果操作系统限制为 Infinity:
open_files_limit
如果在启动时指定,则为 value,否则为 5000。
在 Linux 上, 的值open_files_limit
不能超过ulimit -n。
table_open_cache
命令行格式 | --table-open-cache=# |
---|---|
系统变量 | table_open_cache |
范围 | global |
动态的 | 是的 |
类型 | 整数 |
默认值 | 2000 |
最小值 | 1 |
最大值 | 524288 |
计算公式为: open_files_limit
- 10 -
max_connections
/ 2
thread_cache_size
命令行格式 | --thread-cache-size=# |
---|---|
系统变量 | thread_cache_size |
范围 | global |
动态的 | 是的 |
类型 | 整数 |
默认值 | -1 (表示自动调整大小;不要分配此文字值) |
最小值 | 0 |
最大值 | 16384 |
服务器应缓存多少线程以供重用。当客户端断开连接时,如果客户端的线程少于缓存中的线程,则将客户端的线程放入缓存中 thread_cache_size
。如果可能,通过重用从缓存中获取的线程来满足对线程的请求,并且只有当缓存为空时才会创建新线程。如果您有很多新连接,可以增加此变量以提高性能。通常,如果您有良好的线程实现,这不会提供显着的性能改进。但是,如果您的服务器每秒看到数百个连接,您通常应该设置 thread_cache_size
足够高,以至于大多数新连接都使用缓存线程。默认值基于以下公式,上限为 100: 8 + (max_connections / 100)
back_log
命令行格式 | --back-log=# |
---|---|
系统变量 | back_log |
范围 | global |
动态的 | 不 |
类型 | 整数 |
默认值 | -1 (表示自动调整大小;不要分配此文字值) |
最小值 | 1 |
最大值 | 65535 |
back_log
不能设置为高于操作系统限制。
默认值基于以下公式,上限为 900:50 + (max_connections / 5)
max_connect_errors
命令行格式 | --max-connect-errors=# |
---|---|
系统变量 | max_connect_errors |
范围 | global |
动态的 | 是的 |
类型 | 整数 |
默认值 | 100 |
最小值 | 1 |
最大值(64 位平台) | 18446744073709551615 |
最大值(32 位平台) | 4294967295 |
在出现max_connect_错误后,来自主机的连续连接请求在没有成功连接的情况下被中断,服务器会阻止该主机进行进一步的连接。如果在上一次连接中断后,在少于max_connect_errors的尝试次数内成功建立了与主机的连接,则主机的错误计数将清除为零。要解除阻止主机,请刷新主机缓存
max_user_connections
命令行格式 | --max-user-connections=# |
---|---|
系统变量 | max_user_connections |
范围 | global, session |
动态的 | 是的 |
类型 | 整数 |
默认值 | 0 |
最小值 | 0 |
最大值 | 4294967295 |
任何给定 MySQL 用户帐户允许的最大同时连接数。值 0(默认值)表示 “没有限制。”
wait_timeout
命令行格式 | --wait-timeout=# |
---|---|
系统变量 | wait_timeout |
范围 | global, session |
动态的 | 是的 |
类型 | 整数 |
默认值 | 28800 |
最小值 | 1 |
最大值 (Windows) | 2147483 |
最大值(其他) | 31536000 |
服务器在关闭非交互式连接之前等待其活动的秒数。
connect_timeout
命令行格式 | --connect-timeout=# |
---|---|
系统变量 | connect_timeout |
范围 | global |
动态的 | 是的 |
类型 | 整数 |
默认值 | 10 |
最小值 | 2 |
最大值 | 31536000 |
mysqld 服务器在响应Bad handshake
之前等待连接数据包 的秒数。默认值为 10 秒。
如果客户端经常遇到以下错误,增加connect_timeout值可能会有所帮助。 Lost connection to MySQL server at '
XXX
', system error: errno
innodb_open_files
命令行格式 | --innodb-open-files=# |
---|---|
系统变量 | innodb_open_files |
范围 | global |
动态的 | 不 |
类型 | 整数 |
默认值 | -1 (表示自动调整大小;不要分配此文字值) |
最小值 | 10 |
最大值 | 2147483647 |
指定InnoDB一次可以打开的最大文件数。最小值为10。如果innodb_file_per_table被禁用,默认值为300;否则,默认值为300或table_open_cache设置,以较高者为准。
query_cache_limit
命令行格式 | --query-cache-limit=# |
---|---|
已弃用 | 5.7.20 |
系统变量 | query_cache_limit |
范围 | global |
动态的 | 是的 |
类型 | 整数 |
默认值 | 1048576 |
最小值 | 0 |
最大值(64 位平台) | 18446744073709551615 |
最大值(32 位平台) | 4294967295 |
不要缓存大于此字节数的结果。默认值为 1MB。
query_cache_size
命令行格式 | --query-cache-size=# |
---|---|
已弃用 | 5.7.20 |
系统变量 | query_cache_size |
范围 | 全球的 |
动态的 | 是的 |
类型 | 整数 |
默认值 | 1048576 |
最小值 | 0 |
最大值(64 位平台) | 18446744073709551615 |
最大值(32 位平台) | 4294967295 |
分配给缓存查询结果的内存量。默认情况下,查询缓存处于禁用状态。这是使用默认值1M实现的,查询缓存类型的默认值为0。(如果将大小设置为0,为了显著减少开销,还应使用query_cache_type=0启动服务器。
允许值为1024的倍数;其他值向下舍入到最接近的倍数。对于query_cache_size的非零值,即使query_cache_type=0,也会分配许多字节的内存。有关更多信息,请参阅第8.10.3.3节“查询缓存配置”。
查询缓存需要大约40KB的最小大小来分配其结构。(具体大小取决于系统架构。)如果将query_cache_size的值设置得太小,则会出现警告.
sort_buffer_size
命令行格式 | --sort-buffer-size=# |
---|---|
系统变量 | sort_buffer_size |
范围 | global, session |
动态的 | 是的 |
类型 | 整数 |
默认值 | 262144 |
最小值 | 32768 |
最大值 (Windows) | 4294967295 |
最大值(其他,64 位平台) | 18446744073709551615 |
最大值(其他,32 位平台) | 4294967295 |
每个必须执行排序的会话都会分配一个这样大小的缓冲区。sort_buffer_size
不特定于任何存储引擎,通常用于优化。sort_buffer_size值至少必须足够大,以容纳排序缓冲区中的15个元组。此外,增加“max_sort_length
”的值可能需要增加“ sort_buffer_size
”的值。
如果您 Sort_merge_passes
在输出中看到每秒很多SHOW GLOBAL STATUS
,您可以考虑增加该 sort_buffer_size
值以加快ORDER BY
或GROUP BY
无法通过查询优化或改进的索引来改进的操作。
优化器试图计算出需要多少空间,但可以分配更多空间,直到达到极限。在全局范围内将其设置为大于所需值会减慢大多数排序查询的速度。最好将其作为会话设置来增加,并且仅适用于需要更大大小的会话。在Linux上,有256KB和2MB的阈值,其中较大的值可能会显著减慢内存分配,因此您应该考虑保持低于这些值中的一个。尝试为你的工作量找到最佳价值。
排序缓冲区大小的最大允许设置为4GB−1。64位平台允许使用较大的值(64位窗口除外,对于64位窗口,较大的值被截断为4GB)−1)带有警告)。
read_only
命令行格式 | --read-only[={OFF|ON}] |
---|---|
系统变量 | read_only |
范围 | global |
动态的 | 是的 |
类型 | 布尔值 |
默认值 | OFF |
如果read_only
启用系统变量,则服务器不允许客户端更新,除非来自具有 SUPER
特权的用户。默认情况下禁用此变量。
服务器还支持 super_read_only
系统变量(默认禁用),它具有以下效果:
-
如果
super_read_only
启用,服务器将禁止客户端更新,即使是拥有SUPER
权限的用户也是如此。 -
设置
super_read_only
为ON
隐式read_only
强制ON
. -
设置
read_only
为OFF
隐式super_read_only
强制OFF
.
即使read_only
启用,服务器也允许这些操作:
-
如果服务器是副本,则由复制线程执行的更新。
read_only
在复制设置中,在副本服务器上启用以确保副本仅接受来自源服务器而不是来自客户端的更新 可能很有用。 -
使用
ANALYZE TABLE
orOPTIMIZE TABLE
语句。只读模式的目的是防止更改表结构或内容。分析和优化不属于此类更改。这意味着,例如,可以使用mysqlcheck --all-databases --analyze对只读副本执行一致性检查。 -
对
TEMPORARY
表的操作。 -
插入日志表(
mysql.general_log
和mysql.slow_log
);请参阅 第 5.4.1 节,“选择常规查询日志和慢查询日志输出目标”。 -
从 MySQL 5.7.16 开始,更新 Performance Schema 表,例如
UPDATE
orTRUNCATE TABLE
操作。
复制源服务器上的更改read_only
不会复制到副本服务器。可以在副本上设置该值,而与源上的设置无关。
以下条件适用于启用尝试 read_only
(包括因启用而导致的隐式尝试 super_read_only
):
-
LOCK TABLES
如果您有任何显式锁(使用 获取)或有待处理的事务 ,则尝试失败并发生错误。 -
当其他客户端有任何正在进行的语句、活动
LOCK TABLES WRITE
或正在进行的提交时,尝试阻塞,直到锁被释放并且语句和事务结束。虽然启用的尝试处于read_only
挂起状态,但其他客户端对表锁或开始事务的请求也会阻塞,直到read_only
设置完成。 -
如果存在持有元数据锁的活动事务,则尝试阻塞,直到这些事务结束。
-
read_only
可以在您持有全局读锁(通过 获取FLUSH TABLES WITH READ LOCK
)时启用,因为这不涉及表锁。
read_rnd_buffer_size
命令行格式 | --read-rnd-buffer-size=# |
---|---|
系统变量 | read_rnd_buffer_size |
范围 | global, session |
动态的 | 是的 |
类型 | 整数 |
默认值 | 262144 |
最小值 | 1 |
最大值 | 2147483647 |
此变量用于从MyISAM
表中读取,并且对于任何存储引擎,用于多范围读取优化。
net_buffer_length
命令行格式 | --net-buffer-length=# |
---|---|
系统变量 | net_buffer_length |
范围 | global, session |
动态的 | 是的 |
类型 | 整数 |
默认值 | 16384 |
最小值 | 1024 |
最大值 | 1048576 |
块大小 | 1024 |
每个客户端线程都与一个连接缓冲区和结果缓冲区相关联。两者都以 给出的大小开始, net_buffer_length
但根据需要动态放大到 max_allowed_packet
字节。结果缓冲区缩小到 net_buffer_length
每个 SQL 语句之后。
此变量通常不应更改,但如果您的内存很少,您可以将其设置为客户端发送的语句的预期长度。如果语句超过此长度,则连接缓冲区会自动扩大。net_buffer_length
可设置 的最大值 为 1MB。
此变量的会话值是只读的。
thread_stack
命令行格式 | --thread-stack=# |
---|---|
系统变量 | thread_stack |
范围 | global |
动态的 | 不 |
类型 | 整数 |
默认值(64 位平台) | 262144 |
默认值(32 位平台) | 196608 |
最小值 | 131072 |
最大值(64 位平台) | 18446744073709547520 |
最大值(32 位平台) | 4294967295 |
块大小 | 1024 |
每个线程的堆栈大小。默认值对于正常操作来说足够大。如果线程堆栈大小太小,则会限制服务器可以处理的 SQL 语句的复杂性、存储过程的递归深度以及其他消耗内存的操作。
skip_name_resolve
命令行格式 | --skip-name-resolve[={OFF|ON}] |
---|---|
系统变量 | skip_name_resolve |
范围 | global |
动态的 | 不 |
类型 | 布尔值 |
默认值 | OFF |
检查客户端连接时是否解析主机名。如果这个变量是OFF
, mysqld在检查客户端连接时解析主机名。如果是ON
, mysqld只使用 IP 地址;在这种情况下,Host
授权表中的所有列值都必须是 IP 地址。
sync_binlog
Command-Line Format | --sync-binlog=# |
---|---|
System Variable | sync_binlog |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 1 |
Minimum Value | 0 |
Maximum Value | 4294967295 |
控制 MySQL 服务器将二进制日志同步到磁盘的频率。
-
sync_binlog=0
:禁用MySQL服务器将二进制日志同步到磁盘。相反,MySQL 服务器依靠操作系统不时将二进制日志刷新到磁盘,就像它对任何其他文件所做的那样。此设置提供最佳性能,但在电源故障或操作系统崩溃的情况下,服务器可能已提交尚未同步到二进制日志的事务。 -
sync_binlog=1
:在提交事务之前启用二进制日志到磁盘的同步。这是最安全的设置,但由于磁盘写入次数增加,可能会对性能产生负面影响。在电源故障或操作系统崩溃的情况下,二进制日志中丢失的事务仅处于准备状态。这允许自动恢复例程回滚事务,从而保证二进制日志中不会丢失任何事务。 -
sync_binlog=
,其中N
N
是 0 或 1 以外的值:二进制日志在N
收集二进制日志提交组后同步到磁盘。在电源故障或操作系统崩溃的情况下,服务器可能已提交尚未刷新到二进制日志的事务。由于磁盘写入次数增加,此设置可能会对性能产生负面影响。较高的值可提高性能,但会增加数据丢失的风险。
为了在与事务一起使用的复制设置中获得最大可能的持久性和一致性InnoDB
,请使用以下设置:
许多操作系统和一些磁盘硬件会欺骗刷新到磁盘的操作。他们可能会告诉 mysqld刷新已经发生,即使它没有发生。在这种情况下,即使使用推荐的设置也无法保证事务的持久性,在最坏的情况下,断电可能会损坏InnoDB
数据。在 SCSI 磁盘控制器或磁盘本身中使用电池供电的磁盘缓存可以加快文件刷新速度,并使操作更安全。您还可以尝试禁用硬件缓存中的磁盘写入缓存。
innodb_flush_log_at_timeout
Command-Line Format | --innodb-flush-log-at-timeout=# |
---|---|
System Variable | innodb_flush_log_at_timeout |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 1 |
Minimum Value | 1 |
Maximum Value | 2700 |
Unit | seconds |
每N
秒写入并刷新日志 。 innodb_flush_log_at_timeout
允许增加刷新之间的超时时间,以减少刷新并避免影响二进制日志组提交的性能。默认设置为 innodb_flush_log_at_timeout
每秒一次。
innodb_flush_log_at_trx_commit
Command-Line Format | --innodb-flush-log-at-trx-commit=# |
---|---|
System Variable | innodb_flush_log_at_trx_commit |
Scope | Global |
Dynamic | Yes |
Type | Enumeration |
Default Value | 1 |
Valid Values |
|
控制提交操作的严格ACID合规性 与重新安排和批量完成提交相关 I/O 操作时可能实现的更高性能 之间的平衡 。您可以通过更改默认值来获得更好的性能,但是您可能会在崩溃中丢失事务。
-
完全符合 ACID 需要默认设置 1。日志在每次提交事务时写入并刷新到磁盘。
-
设置为 0 时,日志每秒写入并刷新到磁盘一次。尚未刷新日志的事务可能会在崩溃中丢失。
-
设置为 2 时,日志在每次事务提交后写入并每秒刷新到磁盘一次。尚未刷新日志的事务可能会在崩溃中丢失。
-
对于设置 0 和 2,不能 100% 保证每秒刷新一次。由于 DDL 更改和其他
InnoDB
导致日志独立于innodb_flush_log_at_trx_commit
设置刷新的内部活动,刷新可能更频繁地发生,有时由于调度问题而不太频繁。如果每秒刷新一次日志,则在崩溃中可能会丢失最多一秒的事务。如果日志刷新频率高于或低于每秒一次,则可能丢失的事务量会相应变化。 -
日志刷新频率由 控制
innodb_flush_log_at_timeout
,它允许您将日志刷新频率设置为N
秒(其中N
是1 ... 2700
,默认值为 1)。但是,任何意外的mysqld进程退出都可以擦除长达N
数秒的事务。 -
DDL 更改和其他内部
InnoDB
活动独立于设置刷新日志innodb_flush_log_at_trx_commit
。 -
InnoDB
无论设置如何, 崩溃恢复innodb_flush_log_at_trx_commit
都有效 。事务要么被完全应用,要么被完全擦除。
为了在与事务一起使用的复制设置中保持持久性和一致性InnoDB
:
-
如果启用了二进制日志记录,请设置
sync_binlog=1
.
许多操作系统和一些磁盘硬件会欺骗刷新到磁盘的操作。他们可能会告诉 mysqld刷新已经发生,即使它没有发生。在这种情况下,即使使用推荐的设置也无法保证事务的持久性,在最坏的情况下,断电可能会损坏 InnoDB
数据。在 SCSI 磁盘控制器或磁盘本身中使用电池供电的磁盘缓存可以加快文件刷新速度,并使操作更安全。您还可以尝试禁用硬件缓存中的磁盘写入缓存。
expire_logs_days
Command-Line Format | --expire-logs-days=# |
---|---|
System Variable | expire_logs_days |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 99 |
Unit | days |
参考文档
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html