转载

MySQL常用参数列表

收集一些常用的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状态的时间