MySQL 常用调优参数学习笔记

show variables like '%sync_binlog%';

show variables like '%innodb_flush_log_at_trx_commit%';

show variables like '%binlog_cache_size%';

show variables like '%innodb_log_buffer_size%';

show variables like '%binlog_group_commit_sync_delay%';

show variables like '%innodb_change_buffer_max_size%';

show slave status

在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒

“同步延迟”。与数据同步有关的时间点主要包括以下三个：主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;备库 B 执行完成这个事务，我们把这个时刻记为 T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1

• seconds_behind_master 的计算方法是这样的：每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；
• 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master

在网络正常的时候，日志从主库传给备库所需的时间是很短的，即 T2-T1 的值是非常小的。也就是说，网络正常情况下，主备延迟的主要来源是备库接收完 binlog 和执行完这个事务之间的时间差。所以说，主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢

延迟的原因

• 有些部署条件下，备库所在机器的性能要比主库所在的机器性能差
• 即备库的压力大：一般的想法是，主库既然提供了写能力，那么备库可以提供一些读能力。或者一些运营后台需要的分析语句，不能影响正常业务，所以只能在备库上跑。
  由于主库直接影响业务，大家使用起来会比较克制，反而忽视了备库的压力控制。结果就是，备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟；我们公司都是使用备库做数据查询，我们一般可以这么处理
  • 一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力
  • 通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力
• 大事务：因为主库上必须等事务执行完成才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟；不要一次性地用 delete 语句删除太多数据。其实，这就是一个典型的大事务场景
• 就是大表 DDL
• 备库的并行复制能力

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表

使用 show processlist 命令查看当前MySQL执行的情况

通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可

如果你用的是 MySQL 5.7 版本，可以通过 sys.innodb_lock_waits 表查到谁占有写锁

mysql> select * from t sys.innodb_lock_waits where locked_table='`test`.`t`'\G

show variables like 'transaction_isolation'

查看当前数据库的隔离级别

为什么建议你尽量不要使用长事务

• 每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值
• 回滚日志总一直保留吧，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除
• 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间
• 除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库
• 建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务
• 查找持续时间超过 60s 的事务

  
  select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

怎么避免长事务？

1. 作为业务负责人要尽量避免长事务的产生:
(1)培训业务开发人员，避免长事务
(2)在code review中仔细检查数据库的相关配置
(3)让测试人员建立长事务的相关用例
2. 作为数据库负责人
(1)要求业务组上生产之前必须将数据库的相关配置、表结构设计以及SQL语句提交到DBA部门审核，由DBA把关，从源头杜绝长事务
(2)定期到各业务组进行数据库相关知识的培训
(3)建立长事务监控指标，发现之后及时报警

事务开启语法

start transaction WITH CONSISTENT SNAPSHOT 才是会直接创建read-view ，也就是快照，意思是从这个语句开始，创建一个持续整个事务的一致性快照

start transaction 和 begin语义上是一样的

MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

innodb_print_all_deadlocks=ON 这样就会记录死锁日志到 log_error文件中

Show engine innodb status

show variables like '%auto_increment_offset%';
show variables like '%auto_increment_increment%';

分别用来表示自增的初始值和步长，默认值都是 1；

在一些场景下，使用的就不全是默认值。比如，双 M 的主备结构里要求双写的时候，我们就可能会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突

show variables like '%Innodb_rows_read%';

可以看看 InnoDB 扫描了多少行

初始共享表空间文件有多大 

 show variables like %innodb_file_per_table%';