王张开

Appearance

优化哲学 ​

优化有风险！！！优化需谨慎！！！

优化有风险！！！优化需谨慎！！！

优化有风险！！！优化需谨慎！！！

1832670103863296000.png

硬件优化 ​

  • 主机：

    • IBM小型机
    • 服务器，浪潮、联想、华为、HP、DELL
    • 云产品：ECS、数据库RDS

  • 内存：建议2~3倍的CPU核心数量。

  • 磁盘选择：

    • SATA-3
    • SSD

  • 网络

操作系统优化 ​

swap ​

在MySQL中，尽量不使用swap。

在cento6中，内存使用达到40%时，就会使用swap；在centos7中，内存使用达到70%才会使用swap。

我们可以将这个值调整100%才使用swap：

bash
[root@db01 ~]# cat /proc/sys/vm/swappiness    100 - 30 = 70%
30
# 临时修改
[root@db01 ~]# echo 0 >/proc/sys/vm/swappiness

# 永久修改
[root@db01 ~]# vim /etc/sysctl.conf
vm.swappiness = 0
[root@db01 ~]# sysctl -p

这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。 当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。

IO调度 ​

摘自：

Linux I/O调度器（Linux I/O Scheduler）Linux内核中的一个组成部分，用户可以通过调整这个调度器来优化系统性能， Linux I/O调度器（Linux I/O Scheduler）是LinuxI/O体系的一个组件，它介于通用块层和块设备驱动程序之间。

目前 Linux 上有如下几种 I/O 调度算法

  • noop(No Operation) - 通常用于内存存储的设备。
  • cfq(Completely Fair Scheduler ) – 完全公平调度器。进程平均使用IO带宽。
  • Deadline – 针对延迟的调度器，每一个 I/O，都有一个最晚执行时间。
  • Anticipatory – 启发式调度，类似 Deadline 算法，但是引入预测机制提高性能。

centos6默认使用cfg；而一般在centos7中，对于SSD硬盘环境使用noop算法。

查看默认的IO调度算法：

bash
[root@db01 ~]# cat /sys/block/sda/queue/scheduler 
noop [deadline] cfq 
[root@db01 ~]# dmesg | grep -i scheduler
[    1.430969] io scheduler noop registered
[    1.430973] io scheduler deadline registered (default)
[    1.431009] io scheduler cfq registered
[    1.431013] io scheduler mq-deadline registered
[    1.431016] io scheduler kyber registered

而MySQL也建议使用deadline。

应用端优化 ​

  • 开发过程规范，标准
    • 表结构规范：约束类型、注释、数据类型
  • 减少烂SQL
  • 尽量走索引
  • 优化复杂逻辑；切割大事物。
  • 避免业务逻辑问题，带来的锁争用。

这个块需要DBA深入业务中，和开发/业务人员进行配置。

参数优化 ​

sql
drop database if exists pp;
create database pp charset utf8mb4 collate utf8mb4_bin;
use pp;
create table pr_1000w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
delimiter //
create  procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into pr_1000w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;
call rand_data(10000000);
commit;

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='pp' --query="select * from pp.pr_1000w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose

max_connections(*****) ​

MySQL的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。

bash
db01 [(none)]>select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
1 row in set (0.00 sec)

db01 [(none)]>show processlist;
+----+------+------------------+------+------------------+--------+---------------------------------------------------------------+------------------+
| Id | User | Host             | db   | Command          | Time   | State                                                         | Info             |
+----+------+------------------+------+------------------+--------+---------------------------------------------------------------+------------------+
| 14 | rs   | 10.0.0.206:53694 | NULL | Binlog Dump GTID | 104957 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 15 | rs   | 10.0.0.207:60452 | NULL | Binlog Dump GTID | 104957 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 16 | rs   | 10.0.0.205:34614 | NULL | Binlog Dump GTID | 103895 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 17 | mha  | 10.0.0.207:60454 | NULL | Binlog Dump      |  97100 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 26 | root | db01:53804       | NULL | Sleep            |  84647 |                                                               | NULL             |
| 30 | root | localhost        | NULL | Query            |      0 | starting                                                      | show processlist |
+----+------+------------------+------+------------------+--------+---------------------------------------------------------------+------------------+
6 rows in set (0.00 sec)


db01 [(none)]>计数器，从MySQL服务开启后，就开始计数，最大的并发连接数^C
db01 [(none)]>show status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 7     |
+----------------------+-------+
1 row in set (0.01 sec)

修改：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
Max_connections=1024

建议：

  • 开启数据库时,我们可以临时设置一个比较大的测试值。
  • 观察show status like 'Max_used_connections';变化。
  • 如果max_used_connections跟max_connections相同，那么就是max_connections设置过低或者超过服务器的负载上限了，而低于10%则设置过大。

back_log(***) ​

MySQL能暂存的连接数量，当主要MySQL线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果MySQL的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，如果等待连接的数量超过back_log值，将不被授予连接资源。

说白了，这个参数就是允许有多少个人在排队。

back_log值指出在MySQL暂时停止相应新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它。

bash
db01 [(none)]>select @@back_log;
+------------+
| @@back_log |
+------------+
|         80 |
+------------+
1 row in set (0.00 sec)

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
back_log=1024

wait_timeout和interactive_timeout(****) ​

wait_timeout：指的是MySQL在关闭一个非交互(sleep)的连接之前所要等待的秒数。

interactive_timeout：指的是MySQL在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行MySQL管理，使用的既是交互的连接，这时候，如果没有操作的时间超过了interactive_timeout设置的时间就会自动的断开，默认的是28800(8小时)，可调优为7200。

bash
db01 [(none)]>select @@wait_timeout, @@wait_timeout / 3600 as "小时";
+----------------+--------+
| @@wait_timeout | 小时   |
+----------------+--------+
|          28800 | 8.0000 |
+----------------+--------+
1 row in set (0.00 sec)

db01 [(none)]>select @@interactive_timeout, @@interactive_timeout / 3600 as "小时";
+-----------------------+--------+
| @@interactive_timeout | 小时   |
+-----------------------+--------+
|                 28800 | 8.0000 |
+-----------------------+--------+
1 row in set (0.00 sec)

建议：

  • wait_timeout：如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用；如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低；

修改：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
wait_timeout=60   			# 60秒
interactive_timeout=1200    # 20分钟

长连接的应用，为了避免反复的回收和分配资源，降低额外的开销。 一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。 另外还可以使用额外的参数弥补。

key_buffer_size(****) ​

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。

  • 此参数与myisam表的索引有关。
  • 临时表的创建有关（多表链接、子查询中、union）
    • 有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃。
    • 临时表有两种创建方式
      • 内存：key_buffer_size
      • 磁盘：5.6（ibdata1）;5.7（ibtmp1）

查看：

bash
db01 [(none)]>select @@key_buffer_size, @@key_buffer_size/1024/1204 as "MB";
+-------------------+------------+
| @@key_buffer_size | MB         |
+-------------------+------------+
|           8388608 | 6.80398671 |
+-------------------+------------+
1 row in set (0.00 sec)

设置依据：

bash
db01 [(none)]>show status like "key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 16    |
| Key_reads         | 5     |
+-------------------+-------+
2 rows in set (0.00 sec)

一共有16个索引读取请求，有5个请求在内存中没有找到，直接从硬盘中读取索引，通常将该参数控制在 5%以内。

注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知：

bash
db01 [(none)]>show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

可以通过简单的公式：Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables)和Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)计算其利用率。

或者各自的计算一个时间段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。一般利用率控制在5%-10%以内。

另外，上面的示例仅作为参考，比如在备份时也可能产生大量的临时表，这种情况下就要理性的分析了。

配置：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
key_buffer_size=64M   # 微调看效果，一般都是以8M为单位调整

query_cache_size(***) ​

这个参数仅作为了解，因为MySQL5.7中默认是关闭的，而到了8.0中直接就取消了该参数，所以，我们研究的意义不大了，仅作了解即可。因为现在更多的是使用专门缓存软件来做，比如Redis。

查询缓存简称QC，使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写），将直接从缓冲区中读取结果。

它是SQL层的缓存。

工作流程：

  • MySQL会对每个查询语句进行hash，得到的一个hash值，我们称这个hash值为SQL_ID。
  • 在执行完查询结果后，会将存储引擎返回的结果加SQL_ID存储到缓存中。
  • 后续再有查询语句，同样进行hash，然后如果有相同hash结果的语句，就直接从缓存中返回结果；否则就执行后续的SQL流程去存储引擎中找结果了。

之所以这个功能关闭，我个人理解查询缓存是个无底洞， 比如查询语句都不怎么重复，那么缓存命中率就太低了，所以没必要开这个功能。

查看：

bash
db01 [(none)]>show status like "%Qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 11789   |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

db01 [(none)]> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

各参数：

  • Qcache_free_blocks：缓存中相邻内存块的个数。 如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。 注：当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks。
  • Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。
  • Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
  • Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
  • Qcache_lowmem_prunes：多少条Query因为内存不足而被清除出QueryCache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉你属于哪种情况）。
  • Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
  • Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量。
  • Qcache_total_blocks：当前Query Cache 中的block 数量。
  • query_cache_limit：超过此大小的查询将不缓存。
  • query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
  • query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
  • query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：
    • 如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。
    • 如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
    • 如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

Qcache_hits命中率计算（命中的除以总和）：

Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits) 
    90/         10000             0             90

如果出现hits比例过低，其实就可以关闭查询缓存了。

其他：

  • Qcache_free_blocks 来判断碎片。
  • Qcache_free_memory + Qcache_lowmem_prunes 来判断内存够不够。
  • Qcache_hits 多少次命中 Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)。

配置：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
query_cache_size=128M
query_cache_type=1

max_connect_errors(***) ​

max_connect_errors是MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，MySQL服务器将禁止host的连接请求，直到MySQL服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值，该参数与性能并无太大关系。 配置：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
max_connect_errors=2000

sort_buffer_size(***) ​

sort_buffer_size为每个需要(order by、group by、distinct、union)进行排序的线程分配指定大小的一个缓冲区，来加速查询。

但是这个值并不是越大越好，如果需要排序的线程过多，再加上连接的客户端过多(高并发)的场景，可能会耗尽系统内存资源。

如，设置2M，如果有500个连接，那么将会消耗500*sort_buffer_size(2M)约等于1G内存。

配置：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
sort_buffer_size=1M   # 通常设置1M或者512kb

max_allowed_packet(*****) ​

MySQL 服务端接受的数据包大小。

有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，必须设置1024的倍数。

配置：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
max_allowed_packet=32M

join_buffer_size(***) ​

join_buffer_size用于联表查询中，指定缓存大小。它跟sort_buffer_size一样，它会为每个线程分配内存，所以这个值也不能太大。

当然了，对于联表查询，我们通常的优化手段是优化SQL语句和在on字段增加索引。

配置：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
join_buffer_size=1M

thread_cache_size(*****) ​

服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量，当断开连接时，那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限)，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。

相当于搞个线程池提前准备一些线程，然后反复利用，降低创建和销毁的资源消耗。

官方建议设置规则：

  • 1GB内存，设置为8。
  • 2GB内存，设置为16。
  • 3GB内存，设置为32。
  • .....

查看：

bash
db01 [(none)]>select @@thread_cache_size;
+---------------------+
| @@thread_cache_size |
+---------------------+
|                   9 |
+---------------------+
1 row in set (0.00 sec)

db01 [(none)]>show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 2     |
| Threads_connected | 5     |
| Threads_created   | 7     |
| Threads_running   | 5     |
+-------------------+-------+
4 rows in set (0.00 sec)

各参数：

  • Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
  • Threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
  • Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗cpu sys资源，可以适当增加配置文件中thread_cache_size值。
  • Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

配置：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
thread_cache_size=32   # 根据内存分配

补充：

Threads_created ：一般在架构设计阶段，会设置一个测试值，做压力测试。 结合zabbix监控，看一段时间内此状态的变化。 如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK。 如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源（内存）够用的情况下来增加。

innodb_buffer_pool_size(*****) ​

这个参数也是非常重要的参数，在InnoDB存储引擎中，innodb_buffer_pool主要缓冲数据和索引。

对于单独的MySQL数据服务器来说，一般innodb_buffer_pool_size值最大可以是物理内存的80%甚至90%，但建议设置为物理内存的70%~80%。

配置：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=2048M

innodb_flush_log_at_trx_commit(******) ​

https://www.cnblogs.com/Neeo/articles/13883976.html#innodb_flush_method

innodb_flush_log_at_trx_commit主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2，默认值是1：

sql
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.01 sec)

1832670105352273920.png

根据上图：

  • 当innodb_flush_log_at_trx_commit=0时，master thread每秒将redo log buffer中的日志往os buffer写入，os buffer再刷写(fsync)到磁盘的redo文件中。该模式速度最快，但安全性较差，因为MySQL宕机的话， 会丢失上一秒所有的事务。
  • 当innodb_flush_log_at_trx_commit=1时，每次事物的提交都会执行redo log buffer▶os buffer▶disk的过程。该模式最安全，但速度最慢，当MySQL宕机时最多可能丢失一个事务的redo log。
  • 当innodb_flush_log_at_trx_commit=2时，每次事务的提交都会从redo log buffer往os buffer写入，但os buffer会每秒完成一次fsync操作。该模式速度适中，只有在操作系统崩溃或者断电时，上一秒的redo log在os buffer中没来得及写入磁盘，导致丢失。

配置：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_flush_log_at_trx_commit=1

innodb_thread_concurrency (***) ​

此参数用来设置InnoDB存储引擎的线程并发数量，默认值为0表示不限制。

在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：

  • 如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0。
  • 如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128。并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置innodb_thread_concurrency参数为80，以避免影响性能。
  • 如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定mysqld进程到专有的虚拟CPU。但是需要注意的是，这种绑定，在mysqld进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
  • 在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
  • 定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。

设置依据：

  • 通过top命令和show processlist;命令甚至可以结合show status like 'threads_%';命令来进行微调。

方法：

  • 看top ，观察每个cpu的各自的负载情况。我们期望各个cpu的使用率比较平均，不至于出现某个一核有难八核围观这种情况。
  • 发现不平均，先设置参数为cpu个数，然后不断增加(一倍)这个数值。
  • 一直观察top状态，直到达到比较均匀时，说明已经到位了。

配置：

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_thread_concurrency=8

innodb_log_buffer_size(****) ​

此参数决定redo log buffer的大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。当然，这个参数也要根据具体的并发事务数来决定。

bash
db01 [(none)]>select @@innodb_log_buffer_size,@@innodb_log_buffer_size/1024/1024;
+--------------------------+------------------------------------+
| @@innodb_log_buffer_size | @@innodb_log_buffer_size/1024/1024 |
+--------------------------+------------------------------------+
|                 16777216 |                        16.00000000 |
+--------------------------+------------------------------------+
1 row in set (0.00 sec)

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_log_buffer_size=128M

innodb_log_file_size(****) ​

这个参数就是设置redo log文件（ib_logfile0，ib_logfile1）的大小了，以M为单位，适当的提高该值可以提高性能。

配置：

bash
db01 [(none)]>select @@innodb_log_file_size,@@innodb_log_file_size/1024/1024;
+------------------------+----------------------------------+
| @@innodb_log_file_size | @@innodb_log_file_size/1024/1024 |
+------------------------+----------------------------------+
|               50331648 |                      48.00000000 |
+------------------------+----------------------------------+
1 row in set (0.00 sec)

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_log_file_size=100M

当然，这个值也不能设置太大，不然MySQL在自动故障恢复时，会延长耗时。

innodb_log_files_in_group(****) ​

多个redo log文件（ib_logfile0，ib_logfile1）可以以组的形式共同组成一个逻辑上的redo log文件，MySQL将会以循环的方式将redo log buffer中的日志块刷写到该文件中。

那么这个参数就是决定有几个文件组成一组，默认是2文件。以M为单位。

bash
db01 [(none)]>select @@innodb_log_files_in_group;
+-----------------------------+
| @@innodb_log_files_in_group |
+-----------------------------+
|                           2 |
+-----------------------------+
1 row in set (0.00 sec)

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_log_file_size=100M	
innodb_log_files_in_group=3

推荐设置为3。

read_buffer_size(**) ​

MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享，这意味着这个值不能设置太大。

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
read_buffer_size=1M

read_rnd_buffer_size(**) ​

MySQL的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。 注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键进行回表查询，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
 read_rnd_buffer_size=1M

bulk_insert_buffer_size(**) ​

批量插入数据缓存大小。

如果业务中，存在大量的插入操作的话，通过将该值调大以提高插入效率，默认为8M。

bash
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
  bulk_insert_buffer_size=1M

这个参数的可替代性太强了，比如我们可以使用专门的适用于大量插入操作的存储引擎或者第三方产品(ToKuDB、MyRocks、RocksDB、TiDB、MongoDB)来专门负责这类业务。

binary log(*****) ​

关于二进制日志的相关参数了：

bash
log-bin=/data/mysql-bin

# 为每个 session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点;
# 如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M
binlog_cache_size = 2M 

# 表示的是binlog能够使用的最大 cache 内存大小
max_binlog_cache_size = 8M

# 指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。
# 你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。
# 在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
max_binlog_size= 512M

# 定义了mysql清除过期日志的时间，默认值为0,表示永不过期。
expire_logs_days = 7
log-bin=/data/mysql-bin
binlog_format=row 

# 控制二进制日如何刷写到磁盘文件，1表示每次事务提交后。它和innodb_flush_log_at_trx_commit共同称为双一标准
sync_binlog=1
innodb_flush_log_at_trx_commit=1
set sql_log_bin=0;
show status like 'com_%';

innodb_flush_method(******) ​

https://www.cnblogs.com/Neeo/articles/13883976.html#innodb_flush_method

innodb_flush_method参数控制log buffer和buffer pool在刷写磁盘时是否经过os buffer。

通过select查看当前系统中使用的模式：

bash
mysql> select @@innodb_flush_method;
+-----------------------+
| @@innodb_flush_method |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set (0.00 sec)

If innodb_flush_method is set to NULL on a Unix-like system, the fsync option is used by default

1832670108103737344.png

在Linux系统中，常用三种刷写模式：

  • fsync：调用fsync函数刷写数据文件和redo log，两者都走os buffer.
  • O_DSYNC：数据文件走os buffer，redo log不走os buffer.
  • O_DIRECT：数据文件直接刷写到磁盘，不走os buffer；redo log走os buffer.

最高安全模式

我们偏向于安全！

bash
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_flush_method=O_DIRECT

最高性能

bash
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

关于参数，就介绍这里，因为MySQL它有500多个参数.....

bash
show variables\G

参数汇总 ​

bash
[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0

# 从下面开始
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock  
        
再次压力测试:
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='pp' --query="select * from pp.pr_1000w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose

锁优化 ​

如果要演示锁等待的话，记得关闭事务的自动提交功能。

查看当前数据库中有没有锁等待：

bash
db01 [(none)]>show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

查看哪个事务在等待，也就是被阻塞了：

bash
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
trx_id : 事务ID号
trx_state : 当前事务的状态
trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 当前被阻塞的操作(一般是要丢给开发的)

定位产生锁等待的源头：

bash
SELECT * FROM sys.innodb_lock_waits;     ## ====>被锁的和锁定它的之间关系

locked_table : 哪张表出现的等待 
waiting_trx_id: 等待的事务(与上个视图trx_id 对应)
waiting_pid   : 等待的线程号(与上个视图trx_mysql_thread_id)
blocking_trx_id : 锁源的事务ID 
blocking_pid    : 锁源的线程号

找到锁源的thread_id：

bash
SELECT * FROM performance_schema.threads WHERE processlist_id=15;

根据锁源定位产生锁的SQL语句：

bash
-- 当前在执行的语句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;
-- 执行语句的历史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;

得出结果,丢给开发
表信息 
被阻塞的
锁源SQL

主从复制优化 ​

bash
## 5.7 从库多线程MTS
基本要求:
5.7以上的版本
必须开启GTID 
binlog必须是row模式  

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON

slave-parallel-type=LOGICAL_CLOCK  # 逻辑时钟，实现基于事务级别的并发
slave-parallel-workers=16			# 并发回放的sql线程的的最大并发数

# relaylog日志相关
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

5.7 :
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
cpu核心数作为标准

CHANGE MASTER TO
  MASTER_HOST='10.0.0.204',
  MASTER_USER='rs',
  MASTER_PASSWORD='123',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1;
start slave;
```;
start slave;