mysql5.7性能调优

2022-06-03 本文已影响0人周识儒ice

一千个DBA就有一千种配置方式!

大家一定记得不要轻易去看网上，要看只看官网！网上很多博客都是错的，连参数都列错了，5.7很多参数和5.6是完全不一样的。

可能你从未看到过这样的一篇集中火力式的把mysql参数列了这么全的文章，很有兴曾参与过超3万并发的18～19年的数轮520、618、双11、双12保卫战。因此这一篇是汇集了最精华和实战的内容把mysql所有的参数列在这边供大家参考。并且以（64c cpu，128gb内存）的mysql cpu和内存来进行了一轮配置。而此文的内存相关参数部分可以延展至256gb～512gb。

另外有一点，建议在mysql的服务器上使用ssd。除非并发数永远控制在500-1000内那就没必要使用ssd，普通高速磁盘就可以了。

你会发觉这篇文章是一篇宝藏，这些参数都能够自己动手试验一篇基本在外面是可以吊打mysql面试官了。

client域：

character_set_client
推荐设置：utf8mb4

作用：
字符集设定，如果前台有连social mobile application一类包括wechat，并且允许有使用emoji表情的，请开启成utf8mb4
如果不配的后果：
mysql不支持前端app存表情等字符

配置实例：
character_set_client=utf8mb4

mysqld域：

1.server-id

推荐设置：
如果没有做任何主从复制，此值可以不设。

作用：
遇有主从复制，必设该值，每个参与主从复制的mysql实例的server-id不能重复，必须为阿拉伯数字。
如果不配的后果：
如果你用的是主从复制，这个id不设那么整个mysql的主从复制会失几。

配置实例：
server-id=1

2.port

推荐设置：
3306

作用：
mysql实例端口
如果不配的后果：
默认为3306

配置实例：
port=3306

3.bind_address

推荐设置：
0.0.0.0

作用：
除非有特殊需要，我们会限制只允许mysql实例被某一个ip方问，不支持多个，生产上都为：0.0.0.0然后使用防火墙策略来控制。
如果不配的后果：
默认不允许远程登录

配置实例：
bind_address=0.0.0.0

4.autocommit

推荐设置：
1

作用：
生产上开启成1，如果你开启的是0会有一个这样的情况：
a运行一条insert语句，并未作commit;b去做查询此时b是查询不到的。这种操作一般用于在写store procedure时用到。
如果不配的后果：
如果在系统的my.cnf层面把它设成了0，如果在使用时（99%情况是用的1）时，你想要用root在生产运行时把它设成set autocommit = 1都开启不了。而如果你在一开始就没它设置成1，那么当碰到某些特殊场景特别是写store procedure时需要把它设成0时，你是可以手动临时把某一个session给开在0的。

配置实例：
autocommit = 1

5.character_set_server

推荐设置：
utf8mb4

作用：
字符集设定，如果前台有连social mobile application一类包括wechat，并且允许有使用emoji表情的，请开启成utf8mb4
如果不配的后果：
mysql不支持前端app存表情等字符

配置实例：
character_set_server=utf8mb4

6.skip_name_resolve

推荐设置：
1

作用：
生产上建议开启成1，这样mysql server不会对客户端连接使用反向dns解析，否则客户端连上后有时在遇有生产高速运行时直接timeout，如果设成了1带来的问题就是你不能在mysql中使用主机名来对客户端权限进行划分，而是需要使用ip。
如果要做成即允许mysql里允许使用主机名来分配客户端连接权限，又要做到不要让mysql去做dns解析，可以在mysql所在主机端的/etc/hosts文件中写上客户端的主机名，因为当客户端连接连上来时，mysql反向查找客户端连接时的域名解析的步骤是：首先查找 /etc/hosts 文件，搜索域名和IP的对应关系。但是这样做也有一个问题，那就是如果你有多个客户端多个mysql主从关系，哪到你要把mysql做成一个dns解析器吗？因此推荐设成1

如果不配的后果：
mysql server每一次会对客户端连接使用反向dns解析，经常会出现客户端连上后有timeout现象。

配置实例：
skip_name_resolve=1

7.max_connections

推荐设置：
20,000

作用：
最大连接数，以微品会：前端3万的tps并发，假设redis命中失效50%（这是灾难），那么后端mysql单个主或从开启连接数为：20,000，我们公司在前端并发曾达到过6万，80%被waf、vanish、缓存挡掉，落在db上的qps最高一次为20,000连接，再按照mysql官方，max_connections值受系统os最大打开连接数限制，因此我们需要做以下2步操作：

1）在 /etc/security/limits.conf 底部增加2行
mysql hard nofile 65535
mysql soft nofile 65535

2）在/usr/lib/systemd/system/mysqld.service（视如何安装mysql所决定，用编译安装和yum安装会产生path路径不同。）文件最后添加：
LimitNOFILE=65535
LimitNPROC=65535
$ systemctl daemon-reload
$ systemctl restart mysqld.service
如不生效重服务器。

如果不配的后果：
默认只有150

配置实例：
max_connections = 20,000

8.max_connect_errors

推荐设置：
生产上设10
开发测试上使用默认-100

作用：
生产上开启成10次，开发测试上使用默认即不设。
max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。如果需要设置此数值，手动添加。当此值设置为10时，意味着如果某一客户端尝试连接此MySQL服务器，但是失败（如密码错误等等）10次，则MySQL会无条件强制阻止此客户端连接。相关的登录错误信息会记录到performance_schema.host_cache表中。如果希望重置此计数器的值，则必须重启MySQL服务器或者执行
Mysql> FLUSH HOSTS;
1 Mysql> FLUSH HOSTS;
当这一客户端成功连接一次MySQL服务器后，针对此客户端的max_connect_errors会清零。可以在防火墙上做策略限制某些ip的远程连接。

如果不配的后果：
默认为100

配置实例：
max_connect_errors =10

9.innodb_flush_log_at_trx_commit

推荐设置：
2

作用：
(核心交易系统设置为1，默认为1，其他2或者0)，
0代表：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
1代表：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认（因此会保留每一份redo日志）
2代表：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
除非你用的是小型机或者是超大规模mysql集群一类如：游戏行业，那么需要保留每一秒的事务，否则请设成2，要不然会严重影响系统性能。这个参数是5.6所没有的。

如果不配的后果：
默认为1，影响系统写性能。

配置实例：
innodb_flush_log_at_trx_commit=2

10.transaction_isolation

推荐设置：
READ-COMMITTED

作用：
此参数直接决定了mysql的性能，oracle中的事务默认级别就是read-commited，而mysql的默认级别是:repeatable-read，它利用自身独有的Gap Lock解决了"幻读"。但也因为Gap Lock的缘故，相比于READ-COMMITTED级别的Record Lock，REPEATABLE-READ的事务并发插入性能受到很大的限制。离级别的选择取决于实际的业务需求（安全与性能的权衡），如果不是金融、电信等事务级别要求很高的业务，完全可以设置成transaction_isolation=READ-COMMITTED。

Repeatable-read: 这是MySQL的InnoDB引擎默认的隔离级别，它阻止查询的任何行被其他事务更改。因此，阻塞不可重复读，而不是幻读。也就是说在可重复读中，可能会出现幻读。重复读使用一种中等严格的锁定策略，以便事务中的所有查询都能看到来自相同快照(即事务启动时的数据)的数据。当拥有该级别的事务执行 UPDATE ... WHERE, DELETE ... WHERE, SELECT ... FOR UPDATE和LOCK IN SHARE MODE操作时，其他事务可能需要等待。
Read-Committed-推荐: 事务无法看到来自其他事务的未提交数据，但可以看到当前事务启动后另一个事务提交的数据。当拥有这种级别的事务执行 UPDATE ... WHERE or DELETE ... WHERE操作时，其他事务可能需要等待。但是该事务可以执行 SELECT ... FOR UPDATE, and LOCK IN SHARE MODE操作，其他事务不需要等待。
串行化（SERIALIZABLE）-极力不推荐，串行化隔离级别是最高的隔离级别，它使用了最保守的锁策略。它阻止任何其他事务插入或更改此事务读取的数据，直到该事务完成。简单的来说，就是一个事务一个事务的来执行，显然性能会很低。在这种隔离级别下，一个事务中的相同查询可以反复执行，每次查询结果是一样的。从当前事务开始执行，任何更改另一个事务提交的数据的尝试都会导致当前事务等待（阻塞）。这是SQL标准指定的默认隔离级别（注意不是MySQL）。在实践中，这种严格程度是很少需要的。
读未提交（READ-UNCOMMITTED）-它是最低的隔离级别，虽然性能最高，但也不推荐
它会读取到其他事务修改尚未提交的数据，使用此隔离级别就需要非常小心，认识到这种级别下的查询结果可能不一致或不可复制，这取决于其他事务同时在做什么。通常，具有此隔离级别的事务只执行查询，而不执行插入、更新或删除操作。

在实际环境中，应当根据是否允许出现脏读（dirty reads），不可重复读（non-repeatable reads）和幻读（phantom reads ）现象而选择相应的隔离级别。例如在大数据中，少量的数据不一致不会影响到最后的决策，这种情况下可以使用较低的隔离级别以提交性能和并发性。

如果不配的后果：
默认就是repeatable-read

配置实例：
transaction_isolation = READ-COMMITTED

11.explicit_defaults_for_timestamp

推荐设置：
1

作用：
mysql5.7默认对于timestamp字段会显示“系统当前日期”，就算你在插表时这个timestamp字段留空，它在select出来时也会显示系统日期。因此，这个值的影响范围是你在建表时导致的。
系统默认这个值是0，在0的情况下，你要让该表的timestamp字段在为null时不显示系统默认时间，你的建表必须为：create table order(o_id int ,updateed_time timestamp null default null) ;
explicit_defaults_for_timestamp 变量会直接影响表结构，也就是说explicit_defaults_for_timestamp的作用时间是在表定义的时候；你的update | insert 想通过它去改变行为已经太晚了！
因此，我推荐把这个值设为1.

如果不配的后果：
默认为0

配置实例：
explicit_defaults_for_timestamp = 1

12.join_buffer_size

推荐设置：
16M

作用：
系统默认大小为：512k，mac下默认大小为：256k，针对128GB，1万并发的mysql我推荐给到的值为：8~16M
对于JOIN KEY 有索引和二级索引，JOIN KEY 无索引mysql会使用到join_buffer_size，一般建议设置一个很小的 GLOBAL 值，完了在 SESSION 或者 QUERY 的基础上来做一个合适的调整。如果你拍脑袋给也个4g，我们有1000个并发，就是用掉了4T的内存。。。4T啊。。。你以为你是小型机。适当的去改变它确实可以带来一定的提速，但并不是说很多值越大越好，为什么我们设置成4m呢？我们假设我们的mysql所在的vm是128gb，一根这样的join（如果被用到）是4M，1万个也不过用掉40G,而根据官方说法，total加在一起产生的join_buffer_size不要超过你所在系统的50%.默认512k肯定是小了点，我们可以适当放宽，比如说：2M，在实际使用场景时我们发觉有这样的高频操作（要看高频出现的有意义的sql的执行计划，并确认该计划的：执行cost如： "query_cost": "1003179606.87"，它产生的cost为：0.93个G,如果它真的很高频出现在调优sql到无法调优的程度，我们会去做set session join_buffer_size = 1024 * 1024 * 1024;这样的操作。而不是在一开始的my.cnf中去分配一个暴大的值，我们这边基于128gb，1万connection的并发来说，你给个16M不算小也不算多，我推荐给到8~16M间（这是指在一开始）。

如果不配的后果：
默认的为256k

配置实例：
join_buffer_size = 16M

13)tmp_table_size
推荐设置：

67108864

作用：

如果是128gb内存的服务器，我建议是在my.cnf中设成64M

通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。默认系统为32M，如果当你的临时表越来越多加在一起超过了这个值，那么mysql会在系统磁盘上创建，这个值不是越多越好，也没有一个合适的值。一开始的建议为>64M，然后在运行时我们通过以下公式来做临时调优，

show global status like 'created_tmp%';

把得到的结果中的：(Created_tmp_disk_tables / Created_tmp_tables) * 100% 如果<=25%为最佳值。注意了，在生产时热设定时一定要用类似以下算法：

set global tmp_table_size=6410241024而不是set global tmp_table_size=64M。

如果不配的后果：

默认为32M

配置实例：

tmp_table_size = 67108864

14)tmpdir
这块参数可以让运维给到，放到大空间里就行了，没什么太敏感的。

15)max_allowed_packet
推荐设置：

134217728

作用：

如果你经常在应用层碰到了：Got a packet bigger than'max_allowed_packet' bytes，这时你可以使用

show variables like '%max_allowed_packet%';来查看这个值，这个值没有合适，一般如：用客户端导入数据的时候，遇到错误代码: 1153 - Got a packet bigger than 'max_allowed_packet' bytes 终止了数据导入。这样的场景下，当MySQL客户端或mysqld服务器收到大于max_allowed_packet字节的信息包时，将发出“信息包过大”错误，并关闭连接。对于某些客户端，如果通信信息包过大，在执行查询期间，可能会遇到“丢失与MySQL服务器的连接”错误。

客户端和服务器均有自己的max_allowed_packet变量，因此，如你打算处理大的信息包，必须增加客户端和服务器上的该变量。一般情况下，服务器默认max-allowed-packet为1MB,可以通过在交换机上抓包或者是图形化分析来抓返回结果判断。一般推荐在128gb内存下设置的置为128M.也可以在运行时动态调整：set global max_allowed_packet = 12810241024

如果不配的后果：

配置实例：

max_allowed_packet = 134217728

16)sql_mode
不需要去设置，使用默认的，这块和性能无关。我们的中台中的sql如果碰到有sql报错，因该是在测试环境上就已经报了，它的作用是用来约束你sql的写法的，如果是一个从头开始开发的应用，我们比如说约束好都是ansi sql写法，对于一个产品，不要去做这种画蛇添足的做法。

17)interactive_timeout
推荐设置：

600

作用：

单位为s，系统默认为：28800s即8小时。如果这2个值太大，你会发觉在mysql中有大量sleep的连接，这些连接又被称为：僵尸连接，僵尸连接一多你真正要用的时候就会抛：too many connection这样的错，因此对于长久不用的连接，我们一般要使用“踢出机制”，多久对于一个活动累的sql进行踢呢？我们说如果有一个长事务，它要执行1小时，我不知道这是不是属于正常？当然如果你设了太短，说1分钟就把它踢了，还真不一定踢的对，按照我们在oracle中设置的best practice我们都会把它放到10分钟。你有一条sql连着，10分钟不用，我就把它踢了，这也算正常。但是在高并发的场景下这个timeout会缩短至3-5分钟，这就是为什么我提倡我们的非报表即时类查询需要优化到sql的运行时间不超过300ms的原因，因为在高并发场景下，超过500ms的sql都已经很夸张了。保守点我觉得可以设成10分钏，在应用端由其通过jdbc连接数据库的，做的好的应用都会在jdbc里有一个autoconnect参数，这个autoconnect参数就要和mysql中的wait_timeout来做匹配了。同时在应用端要有相应的validate sql一类的操作来keep alived。不过我更推荐使用”连接池内连接的生存周期（idleConnectionTestPeriod）”来做设置，把这个置设成<mysql内的这两个值将会是最好，同时，idleConnectionTestPeriod会使用到异步的方式去做超时check。如c3p0中的：idleConnectionTestPeriod和testConnectionOnCheckin相当可靠

interactive_timeout：交互式连接超时时间(mysql工具、mysqldump等)

wait_timeout：非交互式连接超时时间，默认的连接mysql api程序,jdbc连接数据库等

interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。

show global variables like 'wait_timeout';

1 timeout 只是针对空闲会话有影响。

2 session级别的wait_timeout继承global级别的interactive_timeout的值。而global级别的session则不受interactive_timeout的影响。

3 交互式会话的timeout时间受global级别的interactive_timeout影响。因此要修改非交互模式下的timeout，必须同时修改interactive_timeout的值。

4 非交互模式下，wait_timeout参数继承global级别的wait_timeout。

如果不配的后果：

系统默认为28800

配置实例：

interactive_timeout = 600

18)wait_timeout
同interactive_timeout，两个值都设成一样。

19)read_buffer_size
推荐设置：

4194304

作用：

这个值其实轻易是用不到的，因为，它只对2种场景的full table scan产生影响而不是所有的full table scan，同时从mysql5.6以后开始没有数据块多块读的功能,与是否设置 read_buffer_size参数无关。应用场景：

1）SELECT INTO … OUTFILE ‘fileName‘

2）When filesort is used, during merge buffers and when merged results are written to a temporary file, then writes are buffered

一般保留默认:64k，保守作法是设置在1～4M，不过它的应用场景很有限，对于互联网场景真的不太用，我推荐设成4M

如果不配的后果：

默认为64k

配置实例：

read_buffer_size = 4194304

20)read_rnd_buffer_size
推荐设置：

8388608

作用：

就是当数据块的读取需要满足一定的顺序的情况下，MySQL 就需要产生随机读取，进而使用到 read_rnd_buffer_size 参数所设置的内存缓冲区。它的默认为256k，最大可以设到2G，它会对order by关键字起作用，当order by的计划成本超出了sort_buffer_size后，mysql会产用随机读取并消耗额外的内容，很多外面的博客说它是只对myisam引擎起作用，但其实不是，该参数还真的覆盖到所有引擎，一般它的推荐设置在8-16M，我推荐8M，根据sql分析计划如果碰到高频的查询且order by的返回包体都很大，那么再在session级别去放。

如果不配的后果：

默认为256k

配置实例：

read_rnd_buffer_size = 8388608

21)sort_buffer_size
推荐设置：

4194304

作用：

每个会话执行排序操作所分配的内存大小。想要增大max_sort_length参数，需要增大sort_buffer_size参数。如果在SHOW GLOBAL STATUS输出结果中看到每秒输出的Sort_merge_passes状态参数很大，可以考虑增大sort_buffer_size这个值来提高ORDER BY 和 GROUP BY的处理速度。建议设置为1~4MB。当个别会话需要执行大的排序操作时，在会话级别增大这个参数。所谓会话级别，我举个例子，你拍脑袋一下，说我设个32M,你所它乘10,000请求，这得多大内存。另外，千万要注意，在mysql内存，当你的sort_buffer_size在超过2K时在底层使用的是mmap()的c函数去做内存分配的，而不是malloc()，做过c的都知道mmap()是一个矢量单位，因此它会付出性能的影响，能影响多少呢？单条sql影响值在30%。

如果不配的后果：

默认值为1M

配置实例：

sort_buffer_size =4194304

22)innodb_page_size
推荐设置：

8192

作用：

这个值可要小心，一般它在设置后就不能轻易改了，一般来说我们都认为，值越大越好，不是的，这个值它的原理是这样的：size越小，内存划分粒度越大，使用率越高，但是会有其他问题，就是限制了索引字段还有整行的大小。innodb引擎读取内存还有更新都是一页一页更新的，这个innodb_page_size决定了，一个基本页的大小。常用B+Tree索引，B+树是为磁盘及其他存储辅助设备而设计一种平衡查找树（不是二叉树）。B+树中，所有记录的节点按大小顺序存放在同一层的叶子节点中，各叶子节点用指针进行连接。MySQL将每个叶子节点的大小设置为一个页的整数倍，利用磁盘的预读机制，能有效减少磁盘I/O次数，提高查询效率。如果一个行数据，超过了一页的一半，那么一个页只能容纳一条记录，这样B+Tree在不理想的情况下就变成了双向链表。我们拿白话来说就是：你越大，空间利用率更高，但是越小呢越有助于性能但是这边一定一定有一个“但是”，但是小到一定的量反而性能不好，为什么呢？太大上面我已经举例了，太小。。。mysql的页间的check point太频繁。怎么样做才能达到一个合理值呢？

这个我们是在全真生产环境、全数据量下用测试工具去对4k,8k,16k三种场景压测得到的吞吐量即tps来做观察的，我这边可以给出一个推荐值，以单表超1000w条数据基于中台1.1的数据库结果（每个表都超1000w），我们在设置该值为：8K时，它的吞吐达到最优。

如果不配的后果：

32位下默认为8192

64位下默认为16384

配置实例：

innodb_page_size = 8192

23)innodb_buffer_pool_size
推荐设置：

72G

作用：

这个值和innodb_buffer_pool_instances相辅相成。在32位机器下，innodb_buffer_pool_instances一般为1，在64位机器上，这个值为8-64.

pool_instances其实为cpu核数，它的作用是：

1）对于缓冲池在数千兆字节范围内的系统，通过减少争用不同线程对缓存页面进行读写的争用，将缓冲池划分为多个单独的实例可以提高并发性。

2）使用散列函数将存储在缓冲池中或从缓冲池读取的每个页面随机分配给其中一个缓冲池实例。每个缓冲池管理自己的空闲列表，刷新列表， LRU和连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护。

innodb_buffer_pool_size的设置需要为pool_instance的整数倍。

网上很多说innodb_buffer_pool_size为系统的70%，这是错的！因为你真的设了70%你的swap空间会被挤压，你不要忘了你还有os，上面还可能有监控agent端。一旦swap空间被挤压后你的mysql反面严重拖慢读写。

此处强烈建议设成内存的20%-65%间（独立的mysql服务器），为什么有一个20%呢？对于<4gb的mysql用服务器来说按照20%系统内存来设置。由于我们是128gb的内存，此处我建议使用72G,如果内存超过128gb，一般我们会把pool instance设成16个，每个开启10g左右的buffer_pool_size，对于256gb内存的服务器来说我们可以这样设。

如果不配的后果：

默认为64

配置实例：

innodb_buffer_pool_size = 72G

24)innodb_buffer_pool_instances = 8
这个参数同innodb_buffer_pool_size一起讲解了。

25)innodb_buffer_pool_load_at_startup
推荐设置：

作用：

这两个参数几乎没人用一般dba也不曾听说过，它是什么意思呢？Mysql在第一次（重启）时，它的buffer_pool_size中是空的，随着mysql运行时间1-2小时后，它的buffer_pool_size里开始被塞入东西，它分为old block与new block，而此时mysql性能开始一点点读写效率上去了，那是因为在buffer_pool_size没有放入东西时，mysql很多读写发生在硬盘上，从硬盘到内存的加载过程是一个比较漫长和耗时的过程，因此我们往往会设一个startup=1以加快这个“预热”过程，它与参数shutdown配合使用，即相当于把上次使用的innot_db_buffer_pool里的东西在启动时先做一次加载，以加快mysql的性能。它会在innodb的数据目录中生成一个文件：ib_buffer_pool。高度注意：加入了startup和shutdown=1时，mysql的启动过程会比较慢，如果你上次的dump出的buffer_pool里的东西有50多g那么mysql启动时的加载过程会变得比较慢。这个值很多人使用默认的0（不开启），它的影响就是你在mysql重启后，一开始你的系统读写性能不如在你系统运行了2-4小时（视db读写而定）反而它的读写性能变好了。不设使用默认值（0）。

如果不配的后果：

不配的话系统默认为0

配置实例：

innodb_buffer_pool_load_at_startup = 0

26)innodb_buffer_pool_dump_at_shutdown
同上面的startup参数以及解说

27)innodb_lru_scan_depth
推荐设置：

2000

作用：

innodb_io_capactiy 在sas 15000转的下配置800就可以了，在ssd下面配置2000以上。

可使用默认配置。即不设。

如果不配的后果：

默认为200，db吞吐量上不去。

配置实例：

innodb_lru_scan_depth = 2000

28)innodb_lock_wait_timeout
推荐设置：

作用：

我们一般会碰到，mysql innodb_lock_wait_timeout这个错，这个错是慢sql导致，它代表的是慢sql的事务锁超过了mysql锁超时的设置了。默认这个值为：50s，这个值是可以动态改变的，我不建议去改这个值，因为一个sql能达50s这得多夸张？

动态改变命令如下：

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

SET GLOBAL innodb_lock_wait_timeout=500;

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

把它设成60s足够了。

如果不配的后果：

默认为50s

配置实例：

innodb_lock_wait_timeout = 60

29)innodb_io_capacity_max
推荐设置：

8000

作用：

这个值很重要，它对读无效，对写很有决定意义。

它会直接决定mysql的tps（吞吐性能），这边给出参考：sata/sas硬盘这个值在200. sas raid10: 2000，ssd硬盘：8000， fusion-io（闪存卡）：25,000-50,000

本调优基于的是ssd，此值设置为8000，笔者上一家公司互联网金融是把一整个mysql扔到了闪存卡里的，因此设置的值为：50,000.

需要根据paas或者是ias的vm的硬盘性号来定

如果不配的后果：

默认为200，系统吞吐上不去。

配置实例：

innodb_io_capacity_max = 8000

30)innodb_io_capacity
它是io_capacity_max的一半，同样，它对读无效对写有决定意义。

配置实例：

innodb_io_capacity_max = 4000

31)innodb_flush_method
推荐设置：

O_DIRECT

作用：

推荐使用O_DIRECT。让我们一起来理解一下，它有3种模式：

1）fdatasync，上面最常提到的fsync(int fd)函数，该函数作用是flush时将与fd文件描述符所指文件有关的buffer刷写到磁盘，并且flush完元数据信息(比如修改日期、创建日期等)才算flush成功。它对磁盘的io读写会很频繁

2）O_DIRECT则表示我们的write操作是从mysql innodb buffer里直接向磁盘上写，它会充分利用缓存

3）_DIRECT模式的free内存下降比较慢，因为它是据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲，O_DIRECT在SQL吞吐能力上较好。

如果不配的后果：

它的默认值为fdatasync。

配置实例：

innodb_flush_method = O_DIRECT

32)innodb_file_format
推荐设置：

Barracuda

作用：

推荐使用Barracuda模式

它是启用表压缩用的，如：

CREATE TABLE test_1 (

x int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

建完后可以通过：show table status like 'test_1';来查看是否已经启用了表压缩了。

innodb_file_format有这么几种模式：

Antelope-羚羊模式，支持Redundant（冗余）、Compact（紧凑）模式
Barracuda-梭子鱼,是InnoDB Plugin支持的文件格式，在原来的基础上新增了两种数据表格式的支持：Dynamic 和 Compressed
因此我推荐使用：Barracude模式，因为它可以兼容其它数据模式。

它也可以在运行时动态改变：SET GLOBAL innodb_file_format_max = barracuda;

如果不配的后果：

它默认使用的是叫“联合模式”，即不是棱子鱼也不是羚羊。

配置实例：

innodb_file_format = Barracuda

33)innodb_file_format_max
这个参数必须和innodb_file_format参数一致，一定记住，要不然不生效。

34)innodb_log_group_home_dir = /redolog/
这个就不用解释了，太傻瓜了。这种路径的都可由运维决定，记得挂在大磁盘下。

35)innodb_undo_directory = /undolog/
这个就不用解释了，太傻瓜了。这种路径的都可由运维决定，记得挂在大磁盘下。

36)innodb_undo_logs = 128
推荐设置：

128

作用：

指定回滚段的个数（早期版本该参数名字是innodb_rollback_segments），默认128个。每个回滚段可同时支持1024个在线事务。这些回滚段会平均分布到各个undo表空间中。该变量可以动态调整，但是物理上的回滚段不会减少，只是会控制用到的回滚段的个数。现在SSD非常普及。innodb_undo_logs可以默认为128不变。

如果不配的后果：

默认就是128

配置实例：

innodb_undo_logs = 128

37)innodb_undo_tablespaces
推荐设置：

作用：

slave_skip_errors = ddl_exist_errors

但是有时我们的一些表（特别是不熟悉mysql的一些开发）真的是用的是mysql5.6旧版的建表语句，这个问题在平时单机模式下很难发现，一旦主从结构一上后，在5.7上真的是有一定机率（有10%-20%的机率）碰到ddl语句是旧版mysql而运行在mysql5.7上，这时在主从复制时会抛一个无法主从复制的错，那么这时我们需要抓数据，表已经建好了，这个影响不大、微乎其微，因此我们可以把它设成”忽略“。这个是本人的吐血经验，为什么要提这个梗。。。你们懂的。

如果不配的后果：

如果因为建表语句和mysql5.7有冲突时在单实例模式下mysql运行时不会发现，在主从复制时如果没有设跳过值，一旦发生，会影响主从复制，表现就是：主从复制失败。
配置实例：

slave_skip_errors = ddl_exist_errors

67)innodb_buffer_pool_dump_pct
推荐设置：

25~40
作用：

锦上添花的值，非必要，这边给出一些best practice:

通常来说我们会设成25%。对于大并发前提下我们会使用40这个值，这个值越大，mysql启动时间越长。它是你的innodb_buffer_pool_size的百分比！

MySQL默认在InnoDB缓冲池（而不是整个缓冲池）中仅保留最频繁访问页的25%。请注意，这个变量是基于内存中的实际数据量，而不是缓冲池的大小。例如，如果有100GB的缓冲池，但只有10GB的数据，默认只有10GB的25%（即2.5GB）数据保存在内存中。

在多数使用场景下，合理的选择是：保留最有用的数据页，比加载所有的页(很多页可能在后续的工作中并没有访问到)在缓冲池中要更快。你可以更改innodb_buffer_pool_dump_pct变量的值。

如果不配的后果：

不配的话不生效。
配置实例：

innodb_buffer_pool_dump_pct=25

68)innodb_page_cleaners=8
这值一般会在主从延迟的情况下会去设，它的值最好是=innodb_buffer_pool_instance的值，它就是cpu的核数。

69)innodb_undo_log_truncate
推荐设置：

1
作用：

建议开启，设为1

innodb_undo_log_truncate参数设置为1，即开启在线回收（收缩）undo log日志文件，支持动态设置。

如果不配的后果：

不配的话是不生效的。
配置实例：

innodb_undo_log_truncate=1

70)innodb_max_undo_log_size
推荐设置：

推荐在默认值的2倍（默认为1GB）
作用：

推荐在默认值的2倍（默认为1GB），一般我们不会轻易去设它。

这个值和innodb_undo_tablespaces、innodb_undo_logs以及innodb_purge_rseg_truncate_frequency有关，这4个值是互相有牵连的。

1）innodb_undo_tablespaces必须为>=3

2）innodb_undo_logs必须开启

3）innodb_purge_rseg_truncate_frequence必须开启

如果不配的后果：

系统按照1GB来计算。
配置实例：

innodb_max_undo_log_size=2G

71)innodb_purge_rseg_truncate_frequency
推荐设置：

128
作用：

默认值在128，这个值不太会去碰。控制回收undo log的频率。指定purge操作被唤起多少次之后才释放rollback segments。当undo表空间里面的rollback segments被释放时，undo表空间才会被truncate。由此可见，该参数越小，undo表空间被尝试truncate的频率越高。
如果不配的后果：

系统默认按照：128去设定。
配置实例：

innodb_purge_rseg_truncate_frequency=128

72)binlog_gtid_simple_recovery
推荐设置：

建议开启
作用：

前提是你的mysql必须>5.7.6，否则要设为关闭。

这个参数控制了当mysql启动或重启时，mysql在搜寻GTIDs时是如何迭代使用binlog文件的。

这个选项设置为真，会提升mysql执行恢复的性能。因为这样mysql-server启动和binlog日志清理更快。该参数为真时，mysql-server只需打开最老的和最新的这2个binlog文件。

如果不配的后果：

默认为0
配置实例：

binlog_gtid_simple_recovery=1

73)log_timestamps
推荐设置：

system
作用：

推荐使用:system

这个参数主要是控制错误日志、慢查询日志等日志中的显示时间。但它不会影响查询日志和慢日志写到表 (mysql.general_log, mysql.slow_log) 中的显示时间，此参数是全局的，可以动态修改。

如果不配的后果：

默认值为:UTC
配置实例：

log_timestamps=system

74)transaction_write_set_extraction
推荐设置：

这个值不需要去设，因为你用的不是mysql8.0，在5.7.6版以后这个制不是很成熟，如果要开启一般会使用：XXHASH64.
作用：

这个值是基于group(并行）复制用的，推荐值为：XXHASH64，如果没有开启基于group（并行）的复制千万不要去设这个参数，设都不用去设，保持默认就可以了。

如果不配的后果：

默认为off状态，即不生效。
配置实例：

transaction_write_set_extraction = OFF

transaction_write_set_extraction = XXHASH64

transaction_write_set_extraction = MURMUR32

75)show_compatibility_56
推荐设置：

on
作用：

推荐打开。这个参数是兼容mysql5.6版的INFORMATION_SCHEMA.GLOBAL_STATUS相关功能的，它有利于从5.6到5.7的过渡时非mysql专职dba但是懂mysql的运维用的。
如果不配的后果：

默认是off。相当于严格模式。
配置实例：

show_compatibility_56=on

mysql5.7性能调优

client域：

mysqld域：

slave_skip_errors = ddl_exist_errors

猜你喜欢

热点阅读