Mysql可调优的参数分享

**导读**

> 作者：杨漆

> 16年关系型数据库管理，从oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB获得3个OCP、2个OCM；运维路上不平坦，跌过不少坑、熬过许多夜。把工作笔记整理出来分享给大伙儿，希望帮到大家少走弯路、少熬夜。

1、 back_log=500

在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。

当MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log。

如果等待连接的数量超过back_log，将不被授予连接资源。

将会报：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时。

back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令：

cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系统为1024。

查看mysql 当前系统默认back_log值，命令：

show variables like 'back_log'; 查看当前数量

1、  wait_timeout= 900（单位为秒）

MySQL客户端的数据库连接闲置最大时间值。

当MySQL连接闲置超过一定时间后将会被强行关闭。

MySQL默认的wait-timeout  值为8个小时，

可以通过命令show variables like 'wait_timeout'查看结果值。

interactive_timeout：服务器关闭交互式连接前等待活动的秒数。

wait_timeout:服务器关闭非交互连接之前等待活动的秒数。

这两个参数必须配合使用。否则单独设置wait_timeout无效

2、  max_connections=3000

max_connections是指MySQL服务器允许的最大连接数。

如果服务器的并发连接请求量比较大，可调高此值以增加并行连接数量。

当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

可以通过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

查看系统当前最大连接数：

show variables like 'max_connections';

4、 max_user_connections 每个数据库用户的最大连接

针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。

简单说是指同一个账号能够同时连接到mysql服务的最大连接数（设置为0表示不限制）。

目前默认值为：0不受限制。

查看max_user_connections值

show variables like 'max_user_connections';

5、 thread_concurrency=64

thread_concurrency的值的正确与否, 对mysql的性能影响很大。

在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。

thread_concurrency应设为CPU核数的2倍. 

1.若Server为一个双核的CPU, thread_concurrency  应设置为4; 

2. 若Server为两个双核的cpu, thread_concurrency应设置为8

查看系统当前thread_concurrency默认配置命令：

show variables like 'thread_concurrency';

6、 skip-name-resolve

禁止MySQL对外部连接进行DNS解析。

使用这一选项可以消除MySQL进行DNS解析的时间。

注意：如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

7、 skip-networking建议被注释掉，不要开启

开启该选项可以彻底关闭MySQL的TCP/IP连接方式。

如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项，否则将无法正常连接。

8、 default-storage-engine= InnoDB

设置创建数据库及表默认存储类型

show table status like ‘tablename’显示表的当前存储状态值

查看MySQL有哪些存储状态及默认存储状态

show engines; 

创建表并指定存储类型

CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;

修改表存储类型：

 Alter table tableName engine =engineName

备注：设置完后把以下几个开启： 

# Uncomment the following if you are using InnoDB tables

innodb_data_home_dir =/usr/local/mysql

#innodb_data_file_path = ibdata1:1024M;ibdata2:10M:autoextend（要注释掉，否则会创建一个新的把原来的替换的。）

innodb_log_group_home_dir = /usr/local/mysql

# You can set .._buffer_pool_size up to 50 - 80 %of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 1000M

innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 500M

innodb_log_buffer_size = 20M

innodb_flush_log_at_trx_commit = 0

innodb_lock_wait_timeout = 50

设置完后一定记得把MySQL安装目录地址下的ib_logfile0和ib_logfile1删除掉。否则重启MySQL起动失败

9、 全局缓存

key_buffer_size(默认值：402653184,即384M)

innodb_buffer_pool_size(默认值：134217728即：128M)

innodb_additional_mem_pool_size（默认值：8388608即：8M）

innodb_log_buffer_size(默认值：8388608即：8M)

query_cache_size(默认值：33554432即：32M)

通过命令如：show variables like '变量名';查看

10、 key_buffer_size=400M

用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM表性能影响最大的一个参数。

如果太大，系统将开始换页并且真的变慢了。

严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。

对于内存在4GB左右的服务器该参数可设置为256M或384M。

如何判断key_buffer_size的设置是否合理？

检查状态值Key_read_requests和Key_reads

key_reads / key_read_requests应该尽可能的低，

比如1:100，1:1000 ，1:10000。

其值可以用以下命令查得：show status like 'key_read%';

11、 Innodb_buffer_pool_size

主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。

另外InnoDB和 MyISAM 存储引擎不同。

MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。 

适当的增加这个参数的大小，可以有效的减少InnoDB类型的表的磁盘I/O

可以通过(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率。

并根据命中率来调整innodb_buffer_pool_size 参数大小进行优化。

值可以用以下命令查得：

show status like 'Innodb_buffer_pool_read%';

命中率越高越好

12. innodb_additional_mem_pool_size=20M

innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小。

当MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL会记录Warning信息到数据库的error log中，这时候就该调整这个参数大小。

查看当前系统mysql的error日志  cat  /var/lib/mysql/机器名.error 发现有很多waring警告。就要调大，比如20M

根据MySQL手册，对于2G内存的机器，推荐值是20M; 32G内存的，推荐100M

13、innodb_log_buffer_size=20M

InnoDB存储引擎的事务日志所使用的缓冲区。类似Binlog Buffer，InnoDB在写事务日志的时候，

为了提高性能，也是先将信息写入Innodb Log Buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件 (或者同步到磁盘)中。可以通过innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

InnoDB 将日志写入日志磁盘文件前的缓冲大小。

理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。

因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。 在 my.cnf中以数字格式设置。

默认是8MB,频繁的系统可适当增大至4MB～8MB。当然如上面介绍所说，这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB

14、 query_cache_size=40M

主要用来缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集。

Query Cache有一个致命的缺陷:当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。

 注意：当数据变化非常频繁的情况下，使用Query Cache可能会得不偿失.

Query Cache的使用需要多个参数配合，其中最为关键的是query_cache_size（用于缓存ResultSet的内存大小）和query_cache_type（在何种场景下使用Query Cache）

可以通过命令：

show status like 'Qcache_%';查看目前系统Query catch使用大小

15、 read_buffer_size=4M

MySql读入缓冲区大小。

对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。

read_buffer_size变量控制这一缓冲区的大小。

如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能.

16. sort_buffer_size = 4M

MySql执行排序使用的缓冲大小。

如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。

如果不能，增加sort_buffer_size变量的大小

17. read_rnd_buffer_size=8M

MySql的随机读缓冲区大小。

当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。

进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。

但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大

18、 tmp_table_size=16M

MySql的heap （堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。

如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。

还可以通过设置tmp_table_size选项来增加临时表的大小。

如果调高该值，MySql同时将增加heap表的大小，可达到提高联接查询速度的效果。

19、 thread_cache_size

默认值为8

根据物理内存设置规则如下：

1G —> 8

2G —> 16

3G —> 32    

4G —> 64

64G —> 512

mysql> show status like 'thread%'; 

+——————--——-——————+——-—————+

| Variable_name     | Value |

+——————--——-—————+——-—————+

| Threads_cached    | 401   |    <—当前被缓存的空闲线程的数量

| Threads_connected | 11     |  <—正在使用（处于连接状态）的线程

| Threads_created   | 271497 |  <—服务启动以来，创建了多少个线程

| Threads_running   | 10     |  <—正在忙的线程（正在查询数据，传输数据等等操作）

+——————--——————————+——--———-+

最好将thread_cache_size设置成与threads_connected一样

查看开机起来数据库被连接了多少次？

mysql> show status like '%connection%';

+———————--———————————+————————-+

| Variable_name        | Value  |

+———————--———————————+————————-+

| Connections          | 2808608  |          –>服务启动以来，历史连接数

| Max_used_connections | 37     |

+———————-————————————+————————-+

通过连接线程池的命中率来判断设置值是否合适？命中率超过90%以上,设定合理。

(Connections -  Threads_created) / Connections * 100 %