共计 3844 个字符,预计需要花费 10 分钟才能阅读完成。
十二、MySQL 中进步性能的办法
短连贯风暴
连贯到数据库后,执行很少的 SQL 语句就断开,下次须要的时候再重连。
- 先解决掉那些占着连贯然而不工作的线程。
能够通过 kill connection 被动踢掉不工作的线程。这个行为跟当时设置wait_timeout
的成果是一样的。设置wait_timeout
参数示意的是,一个线程闲暇wait_timeout
这么多秒之后,就会被 MySQL 间接断开连接。 - 缩小连贯过程的耗费。
有的业务代码会在短时间内先大量申请数据库连贯做备用,如果当初数据库确认是被连贯行为打挂了,那么一种可能的做法,是让数据库跳过权限验证阶段。
慢查问性能问题
-
索引没有设计好
这种场景个别就是通过紧急创立索引来解决。比拟现实的是可能在备库先执行。假如你当初的服务是一主一备,主库 A、备库 B,这个计划的大抵流程是这样的:- 在备库 B 上执行 set sql_log_bin=off,也就是不写 binlog,而后执行 alter table 语句加上索引;
- 执行主备切换;
- 这时候主库是 B,备库是 A。在 A 上执行 set sql_log_bin=off,而后执行 alter table 语句加上索引。
** 或者思考 gh-ost。**
- SQL 语句没写好
SQL 语句没写,导致语句没有应用上索引。 - MySQL 选错了索引
应急计划就是给这个语句加上 force index。
如何防止上述问题:
- 上线前,在测试环境,把慢查问日志(slow log)关上,并且把
long_query_time
设置成 0,确保每个语句都会被记录入慢查问日志; - 在测试表里插入模仿线上的数据,做一遍回归测试;
- 察看慢查问日志里每类语句的输入,特地注意
Rows_examined
字段是否与预期统一。(咱们在后面文章中曾经屡次用到过Rows_examined
办法了,置信你曾经入手尝试过了。如果还有不明确的,欢送给我留言,咱们一起探讨)。
QPS 突增问题
有时候因为业务忽然呈现顶峰,或者应用程序 bug,导致某个语句的 QPS 忽然暴涨,也可能导致 MySQL 压力过大,影响服务。最现实的状况是让业务把这个性能下掉,服务天然就会复原。
- 一种是由全新业务的 bug 导致的。假如你的 DB 运维是比拟标准的,也就是说白名单是一个个加的。这种状况下,如果你可能确定业务方会下掉这个性能,只是工夫上没那么快,那么就能够从数据库端间接把白名单去掉。
- 如果这个新性能应用的是独自的数据库用户,能够用管理员账号把这个用户删掉,而后断开现有连贯。这样,这个新性能的连贯不胜利,由它引发的 QPS 就会变成 0。
- 如果这个新增的性能跟主体性能是部署在一起的,那么咱们只能通过解决语句来限度。这时,咱们能够应用下面提到的查问重写性能,把压力最大的 SQL 语句间接重写成 ”select 1″ 返回。
十三、MySQL 是怎么保证数据不丢的?
binlog 的写入机制
事务执行过程中,先把日志写到 binlog cache,事务提交的时候,再把 binlog cache 写到 binlog 文件中。
一个事务的 binlog 是不能被拆开的,因而不管这个事务多大,也要确保一次性写入。这就波及到了 binlog cache 的保留问题。
零碎给 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 管制的:
- sync_binlog=0 的时候,示意每次提交事务都只 write,不 fsync;
- sync_binlog=1 的时候,示意每次提交事务都会执行 fsync;
- sync_binlog=N(N>1) 的时候,示意每次提交事务都 write,但累积 N 个事务后才 fsync。4.
redo log 的写入机制
事务在执行过程中,生成的 redo log 是要先写到 redo log buffer 的。
redo log 存在三种状态,这三种状态别离是:
- 存在 redo log buffer 中,物理上是在 MySQL 过程内存中,就是图中的红色局部;
- 写到磁盘 (write),然而没有长久化(fsync),物理上是在文件系统的 page cache 外面,也就是图中的黄色局部;
- 长久化到磁盘,对应的是 hard disk,也就是图中的绿色局部。
日志写到 redo log buffer 是很快的,wirte 到 page cache 也差不多,然而长久化到磁盘的速度就慢多了。为了管制 redo log 的写入策略,InnoDB 提供了 innodb_flush_log_at_trx_commit
参数,它有三种可能取值:
- 设置为 0 的时候,示意每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
- 设置为 1 的时候,示意每次事务提交时都将 redo log 间接长久化到磁盘;
- 设置为 2 的时候,示意每次事务提交时都只是把 redo log 写到 page cache。
InnoDB 有一个后盾线程,每隔 1 秒,就会把 redo log buffer 中的日志,调用 write 写到文件系统的 page cache,而后调用 fsync 长久化到磁盘。
留神,事务执行两头过程的 redo log 也是间接写在 redo log buffer 中的,这些 redo log 也会被后盾线程一起长久化到磁盘。也就是说,一个没有提交的事务的 redo log,也是可能曾经长久化到磁盘的。
实际上,除了后盾线程每秒一次的轮询操作外,还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。
- 一种是,redo log buffer 占用的空间行将达到
innodb_log_buffer_size
一半的时候,后盾线程会被动写盘。留神,因为这个事务并没有提交,所以这个写盘动作只是 write,而没有调用 fsync,也就是只留在了文件系统的 page cache。 - 另一种是,并行的事务提交的时候,顺带将这个事务的 redo log buffer 长久化到磁盘。假如一个事务 A 执行到一半,曾经写了一些 redo log 到 buffer 中,这时候有另外一个线程的事务 B 提交,如果
innodb_flush_log_at_trx_commit
设置的是 1,那么依照这个参数的逻辑,事务 B 要把 redo log buffer 里的日志全副长久化到磁盘。这时候,就会带上事务 A 在 redo log buffer 里的日志一起长久化到磁盘。
两阶段提交的时候,时序上 redo log 先 prepare,再写 binlog,最初再把 redo log commit。如果把 innodb_flush_log_at_trx_commit
设置成 1,那么 redo log 在 prepare 阶段就要长久化一次,因为有一个解体复原逻辑是要依赖于 prepare 的 redo log,再加上 binlog 来复原的。每秒一次后盾轮询刷盘,再加上解体复原这个逻辑,InnoDB 就认为 redo log 在 commit 的时候就不须要 fsync 了,只会 write 到文件系统的 page cache 中就够了。
通常咱们说 MySQL 的 “ 双 1″ 配置,指的就是 sync_binlog
和innodb_flush_log_at_trx_commit
都设置成 1。也就是说,一个事务残缺提交前,须要期待两次刷盘,一次是 redo log(prepare 阶段),一次是 binlog。
组提交(group commit)机制
日志逻辑序列号(log sequence number,LSN)。LSN 是枯燥递增的,用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redolog,LSN 的值就会加上 length。
组提交就是 write 完之后,fsync 晚一点调用,一次提交很多事务。
在并发更新场景下,第一个事务写完 redo log buffer 当前,接下来这个 fsync 越晚调用,组员可能越多,节约 IOPS 的成果就越好。
MySQL 呈现了 IO 性能瓶颈怎么办?
针对这个问题,能够思考以下三种办法:
- 设置
binlog_group_commit_sync_delay
和binlog_group_commit_sync_no_delay_count
参数,缩小 binlog 的写盘次数。这个办法是基于“额定的成心期待”来实现的,因而可能会减少语句的响应工夫,但没有失落数据的危险。 - 将
sync_binlog
设置为大于 1 的值(比拟常见是 100~1000)。这样做的危险是,主机掉电时会丢 binlog 日志。 - 将
innodb_flush_log_at_trx_commit
设置为 2。这样做的危险是,主机掉电的时候会丢数据。
不倡议把 innodb_flush_log_at_trx_commit
设置成 0。因为把这个参数设置成 0,示意 redo log 只保留在内存中,这样的话 MySQL 自身异样重启也会丢数据,危险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的,所以将这个参数设置成 2 跟设置成 0 其实性能差不多,但这样做 MySQL 异样重启时就不会丢数据了,相比之下危险会更小。
十四、MySQL 的主备统一