mysql运营_MySQL日常运营重要事项存在问题总结

一、数据库备份及恢复

(1)物理备份

MySQL开启两个线程分别对datafile和redo log进行copy，当数据文件 copy 完成后，上全局读锁，阻塞写操作并获取 binlog 位点，备份非 innodb 表数据，待 redo log copy 完成后，释放全局读锁并结束复制。FTWRL在执行过程中会关闭表，如果在执行 FTWRL 的过程中有大查询，会导致FTWRL等待，进而导致DML堵塞的时间变长，如果并发不断增大，则很有可能引起实例可用连接数用完，严重时还会导致实例 hang 住。这个在读写分离场景将备份放到slave节点的时候，是有机会见到的问题。

注：MySQL-8.0推出的clone plugin同样需要获取备份锁。

(2)逻辑备份

逻辑备份是通过 SQL 语句的形式对数据进行备份，其过程简单描述为：执行全局读锁(FTWRL)，备份非innodb 数据文件，获取全局 binlog 位点，设置事务隔离级别为 RR 并开启快照读(执行此语句会立即创建该事务对应的 read view，之后所有读是基于些 read view 的快照读，此时创建的 read view 和获取的 binlog 位点相对应)，释放锁并备份 innodb 数据，待 innodb 表数据备份完成后即完成备份。

从物理备份以及逻辑备份的过程不难看出：实例数据量过大会导致备份时间较长，并在备份的过程中占用大量的 IO、CPU、内存等资源。

高并发场景，特别是MySQL存在非innodb或系统表备份时，有可能导致锁等待队列过程，导致备份节点挂起。

备库进行备份，MySQL有可能阻塞redo回放，导致备库出现较大延迟，读写分离场景有可能导致slave挂起。

二、数据库读写性能

写性能方面

MySQL 在事务提交时，先写 redo log，再写 binlog ,最后提交事务。一次事务提交涉及多个 IO (如 redo log, binlog, 刷脏等)，binlog 是串行化提交，顺序写 binlog，虽然引入了 binlog 的 group commit ，但在个人的使用经验看，这也容易引起flush binlog stage, sync binlog stage, commit trx stage的瓶颈，影响整体性能。

另外，从现网业务压测发现，在主从同步场景下，通常MySQL单节点的tps极限能力是8000左右(异步且并行复制)，一旦到达该水平，则会出现严重的同步延迟，对读写分离业务场景并不友好。

2.读性能方面

mysql slave基于binlog逻辑复制，无法深入到存储层做更多性能优化，遇到大事务回放会产生高延迟。同时逻辑复制读写分离场景下业务用户读数据、系统的回放线程写数据，这个在innodb上无法做到感知和区别的，读写冲突会重演主库的读写的严重性，性能瓶颈明显。

三、主备数据同步

MySQL 主备之间使用逻辑日志 binlog 进行数据同步，主库事务完成后将产生的 binlog 发送给备库，备库 IO thread 将收到的 binlog 写入到磁盘，然后由 SQL thread 执行或由分发线程分发到 worker thread 后执行，从而保证主备之间的数据一致。但是基于 binlog 的逻辑复制存在着以下问题：

(1)主备数据延迟， 即 binlog 执行性能所引起的主备延迟问题。binlog 在执行过程中需要完整的 B+ 树遍历过程，并在执行的过程中产生 redo log & binlog，当主库压力大到一定程度时，备库执行性能由于并发的原因会跟不上主库的执行速度，最终产生延迟问题。

(2)数据不一致问题， binlog 内部 bug 会导致主备之间的数据不一致性，如 replace into 导致的主备 auto_increment 不一致性，repository 所导致的主备数据不一致问题, insert ... select 所导致的主备不一致问题等。

(3)大事务问题，binlog 在事务完成后才被发送到备库，备库则执行和主库相同的逻辑，执行需要的时间和主库基本相同或更多(row 模式无主建更新等)，所以没有办法解决大事务所带来的延迟问题。

虽然 MySQL-5.6 及后续版本有并行复制功能，如基于 database、logical clock 或者 MySQL-8.0中writeset 并行复制来提升备库执行性能，但都没有彻底解决由于大事务或主库压力过大所导致的复制延迟问题。

PG主备同步基于redo log物理复制(也可以逻辑复制)，同步redo直接操作数据页，不存在查询 B+ 树定位数据的过程，回放速度快，不存在mysql binlog 的束缚，在写性能方面由于逻辑binlog所带来的性能瓶颈不复存在，取而代之的是内核中相关锁资源的优化，主备延迟在1.5w的tps依然可以保留在毫秒级别。

五、数据库崩溃恢复

MySQL 崩溃恢复过程简单描述为：从上一次 checkpoint开始执行 redo log，扫描最后一个 binlog 文件，收集此文件中 xid 事务集合，根据回滚段重构读写事务链表trx_list_init_at_db_start，根据事务的状态，对于未开始的事务执行回滚操作，对于处于 prepared 的事务，如果收集的 xid 集合中包含事务的 xid ，则提交事务，否则回滚事务；

当写压力较大时，崩溃恢复的过程持续时间较长，导致 RTO 较大，影响可用性；另外，备理备份也需要 apply redo log ，当 redo log 的量较大时，可能影响 RTO & RPO。