暂时性付出代价提高mysql性能

业务高峰性能时的紧急处理

  • 问题一：短连接风暴。

    • 数据库处理得慢一些，连接数就会暴涨。max_connections参数，用来控制一个MySQL实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。

    • 方法一：先处理掉占着连接却不工作的线程。

      • max_connections的计算，不是看谁在running，是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通过kill connection主动踢掉。这个行为跟事先设置wait_timeout的效果是一样的。设置wait_timeout参数表示的是，一个线程空闲wait_timeout这么多秒之后，就会被MySQL直接断开连接。
      • 注意：应该先断开那些非提交事物的连接，因为如果事物还未提交，断开会执行回滚。因此，如果是连接数过多，你可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。

    • 方法二：减少连接过程的消耗。

      • 有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables参数启动。这样，整个MySQL会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。（风险极高）

  • 问题二：慢查询性能问题

    • 索引没有设计好：

      • Online DDL，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行alter table 语句。
      • 比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库A、备库B，这个方案的大致流程是这样的：
        1. 在备库B上执行 set sql_log_bin=off，也就是不写binlog，然后执行alter table 语句加上索引；
        2. 执行主备切换；
        3. 这时候主库是B，备库是A。在A上执行 set sql_log_bin=off，然后执行alter table 语句加上索引。

    • sql语句没写好：

      • 检查sql语句，字符集是否一样，查询字段的数据类型和数据库存储的数据类型是否一样，有没有隐式使用了函数导致没有走索引。

    • mysql选错了索引：

      • 这时候，应急方案就是给这个语句加上force index。

mysql是怎么保证事物不丢的

  • binlog的写入机制：事物执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写道binlog文件中。
  • 一个事物的binlog是不能被拆开的，因此无论这个事物多大，也要确保一次性写入，系统给binlog cache分配了一片内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小，如果超过了，就要暂存到磁盘。
  • 事物提交的时候，执行器把binlog cache里的完整事物写入到binlog中，并清空binlog cache。

每个线程有自己的binlog cache，但是共用一份binlog。

  • 图中的write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。
  • 图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IOPS。

write 和fsync的时机，是由参数sync_binlog控制的：

  1. sync_binlog=0的时候，表示每次提交事务都只write，不fsync；

  2. sync_binlog=1的时候，表示每次提交事务都会执行fsync；

  3. sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。

    因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。

    但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

redo log的写入机制

  • 事务还没提交的时候，redo log buffer中的部分日志有没有可能被持久化到磁盘呢？

  • 确实会有，redo log可能存在在的三种状态。

    1. 存在redo log buffer中，物理上是在MySQL进程内存中，就是图中的红色部分；

    2. 写到磁盘(write)，但是没有持久化（fsync)，物理上是在文件系统的page cache里面，也就是图中的黄色部分；

    3. 持久化到磁盘，对应的是hard disk，也就是图中的绿色部分。

  • 日志写到redo log buffer是很快的，wirte到page cache也差不多，但是持久化到磁盘的速度就慢多了。为了控制redo log的写入策略，InnoDB提供了innodb_flush_log_at_trx_commit参数，它有三种可能取值：

    1. 设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中;
    2. 设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；
    3. 设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。
    • InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。
    • 注意，事务执行中间过程的redo log也是直接写在redo log buffer中的，这些redo log也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的redo log，也是可能已经持久化到磁盘的。

组提交机制

  • 日志逻辑序列号（log sequence number）LSN，LSN是单调递增的，用来对用redo log的一个个写入点，每次写入长度为length的redo log，LSN的值就会加上length。
  • 例子：如图，三个并发事物（trx1,trx2,trx3）在prepare阶段，都写完redo log buffer,持久化到磁盘的过程，对应的LSN是50，120，160

如图：

1. trx1是第一个到达的，会被选为这组的 leader；
2. 等trx1要开始写盘的时候，这个组里面已经有了三个事务，这时候LSN也变成了160；
3. trx1去写盘的时候，带的就是LSN=160，因此等trx1返回时，所有LSN小于等于160的redolog，都已经被持久化到磁盘；
4. 这时候trx2和trx3就可以直接返回了。
  • 所以，一次组提交里面，组员越多，节约磁盘IOPS的效果越好。

两阶段提交回顾

  1. 写binlog，先把binlog从binlog cache中写道磁盘上的binlog文件中
  2. 调用fsync持久化
    MySQL为了让组提交的效果更好，把redo log做fsync的时间拖到了步骤1之后。也就是说，上面的图变成了这样：

  • 这么一来，binlog也可以组提交了。在执行图中第4步把binlog fsync到磁盘时，如果有多个事务的binlog已经写完了，也是一起持久化的，这样也可以减少IOPS的消耗。
  • WAL机制是减少磁盘写，可是每次提交事务都要写redo log和binlog，这磁盘读写次数也没变少呀？
    • redo log 和 binlog都是顺序写，磁盘的顺序写比随机写速度要快；
    • 组提交机制，可以大幅度降低磁盘的IOPS消耗。

如果你的MySQL现在出现了性能瓶颈，而且瓶颈在IO上，可以通过哪些方法来提升性能呢？

  1. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count参数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
  2. 将sync_binlog 设置为大于1的值（比较常见是100~1000）。这样做的风险是，主机掉电时会丢binlog日志。
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jimmyhe/p/11116953.html