收集一些常用的 MySQL 参数, 方便日后使用.
全局参数
通用配置
max_connections
MySQL 能创建的最大连接数, 如果数据库的并发量比较大, 建议调高此值, 以增加并行连接数量, 当然连接数越多, 由于 MySQL 会为每个连接创建连接缓冲区, 连接数越多会消耗更多内存
open_files_limit
MySQL 打开的文件描述符限制, 默认最小 1024
port
= 3306 守护进程监听端口
socket
= /tmp/mysql.sock 本地 sock 文件
basedir
= /opt/mysql57 mysql 安装目录
datadir
= /opt/mysql57/var mysql 数据目录
back_log
= 600 参数值指定到来的 TCP/IP 连接的侦听队列的大小
max_connect_errors
= 6000 允许每个客户端最多尝试登录次数
skip-name-resolve
跳过域名解析, 只使用 ip
default-storage-engine
= InnoDB 默认存储引擎
缓冲区参数
key_buffer_size
key_buffer_size 指定索引缓冲区的大小, 它决定索引处理的速度, 尤其是索引读的速度.
query_cache_size
使用查询缓冲, MySQL 将查询结果存放在缓冲区中, 今后对于同样的 SELECT 语句 (区分大小写), 将直接从缓冲区中读取结果.
record_buffer_size
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区. 如果你做很多顺序扫描, 你可能想要增加该值.
wait_timeout
指定一个请求的最大连接时间, 对于 4GB 左右内存的服务器可以设置为 5-10.
innodb 引擎相关配置
sync_binlog
二进制日志文件 binlog 的刷新写入方式, 这个参数不仅影响到 Binlog 日志对 MySQL 所带来的性能损耗, 而且还影响到 MySQL 中数据的完整性. 参数设置说明如下:
1) sync_binlog=0,
当事务提交之后, MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘, 而让文件系统自行决定什么时候来做同步, 或者 cache 满了之后才同步到磁盘. 如果没刷新到磁盘前系统宕机, 则会丢失最后的 binlog 内容, 但是此参数性能最佳
2) sync_binlog=n,
当每进行 n 次事务提交之后, MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘.
innodb_flush_logs_at_trx_commit
InnoDB 引擎事务日志文件的刷新写入方式, 这个参数对 InnoDB 引擎的写入性能来说非常重要, 有以下 3 种设置:
1) =1 时, 在每个事务提交时, 日志缓冲被写到日志文件, 对日志文件做到磁盘操作的刷新. 最安全的方式, 但是速度最慢.
2) =2 时, 在每个事务提交时, 日志缓冲被写到文件, 但不对日志文件做到磁盘操作的刷新. 只有操作系统崩溃或掉电才会删除最后一秒的事务, 不然不会丢失事务.
3) =0 时, 日志缓冲每秒一次地被写到日志文件, 并且对日志文件做到磁盘操作的刷新. 任何 mysqld 进程的崩溃会删除崩溃前最后一秒的事务
innodb_flush_method
这个参数控制着 innodb 数据文件及 redo og 的打开, 刷写模式, 有以下 3 种设置:
1) 默认是 fdatasync, 调用 fsync() 去刷数据文件与 redo log 的 buffer
2) 为 O_DSYNC 时, innodb 会使用 O_SYNC 方式打开和刷写 redo log, 使用 fsync() 刷写数据文件, 通常比较慢.
3) 为 O_DIRECT 时, innodb 使用 O_DIRECT 打开数据文件, 使用 fsync() 刷写数据文件跟 redo log, 在 Linux 上使用 Direct IO, 可以显著提高速度, 特别是在 RAID 系统上, 避免额外的数据复制和 double buffering(mysql buffering 和 OS buffering).
任何数据库, 只要涉及到持久化, 就与上面这三个方面的参数有极大关系, 包括 NOSQL 与内存数据库, 有时 NOSQL 与内存数据库之所以比 MYSQL 快, 与这方面的设置也有很大关系
其实, 在大部分场景下, 如果某个产品宣称自己的写读 tps 超过其他存储 n 倍, 一般来说都是从 k-v 这个角度入手进行优化的, 主要入手的点是树的数据结构优化和锁的细化, 一般都能在一些特定的场景获得 5-10 倍的性能提升.
innodb_buffer_pool_size
这是 Innodb 最重要的一个配置参数, 这个参数控制 Innodb 本身的缓大小, 也影响到, 多少数据能在缓存中. 建议该参数的配置在物理内存的 70%-80%之间.
innodb_io_capacity
这个参数控制 Innodb checkpoint 时的 IO 能力, 一般可以按一块 SAS 15000 转的磁盘 200 个计算, 6 块盘的 SAS 做的 Raid10 这个值可以配到 600 即可. 如果是普通的 SATA 一块盘只能按 100 算.(innodb-plugin, Percona 有这个参数)
innodb_log_file_size
在高写入负载尤其是数据集很大的时候, 这个值非常重要, 值越高性能越好, 不过可能会增加数据恢复的时候. 我设置为 128M.
日志文件:
错误日志
log-error="filename.log"
可以通过如下 sql 语句查询 error 日志配置路径:
mysql> SHOW VARIABLES LIKE 'log_error%';
查询日志
参数配置:
- log-output=FILE
- general-log=1
- general_log_file="filename.log"
通过 sql 语句查询日志路径:
mysql> SHOW VARIABLES LIKE 'general_log%';
慢查询日志
参数配置
- slow-query-log = 1
- slow-query-log-file = /usr/loval/var/mysql/mysql-slow.log
- long_query_time = 1 #设置满请求时间
- log-queries-not-using-indexes
通过 sql 语句查询慢查询日志配置:
mysql> SHOW VARIABLES LIKE 'slow_query_log%';
二进制日志
log-bin=mysql-bin 表示启用二进制日志记录, 服务器记录了所有改变数据语句的二进制日志, 用于备份和复制.
binlog_format=mixed 设置 binlog 格式, 可选如下三种:
Row, 日志中会记录成每一行数据被修改的形式, 缺点是会产生大量的日志
Statement, 每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中, 如果 sql 语句包含函数可能会导致主从数据库的数据不一致, 比如调用 NOW() 函数获取时间
Mixed,MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式, 也就是在 statement 和 row 之间选择一种.
操作系统优化
网络优化
修改 / etc/sysctl.conf 文件
1. 增加 SYN 队列长度: net.ipv4.tcp_max_syn_backlog = 65535
2. socket 资源回收优化
net.ipv4.tcp_tw_recycle = 1 处理 TIME-WAIT 状态的 Socket 快速回收
net.ipv4.tcp_tw_reuse = 1 允许将处于 TIME-WAIT 状态的 Socket 重新用于新的连接
net.ipv4.tcp_fin_timeout = 30 处于 FIN_WAIT-2 状态的时间
打开文件数限制
通过 ulimit -a 查看限制, 修改 / etc/security/limits.conf, 增加打开文件句柄限制
* soft nofile 65536
* hard nofile 65535
参考文档
影响 MySQL 之 IO 性能最重要的几个参数 https://blog.csdn.net/jiao_fuyou/article/details/15810229
mysql 参考配置以及参数解释 https://www.jianshu.com/p/8cef22b6917f
Linux 内核 tcp 调优方案 https://blog.csdn.net/hytfly/article/details/53426630
来源: https://www.thinksaas.cn/group/topic/839749/