MySQL 인프라 및 로그 시스템 예시 분석

1. MySQL 인프라

MySQL은 두 부분으로 나눌 수 있습니다: 서버 계층과 스토리지 엔진 계층

서버 계층에는 MySQL을 다루는 커넥터, 쿼리 캐시, 분석기, 최적화 프로그램, 실행 프로그램 등이 포함됩니다. 대부분의 핵심 서비스 기능과 모든 내장 기능(예: 날짜, 시간, 수학 및 암호화 기능 등), 저장 프로시저, 트리거,

Storage 엔진은 데이터 저장 및 검색을 담당합니다. 플러그인 아키텍처 모델을 기반으로 하는 여러 스토리지 엔진(예: InnoDB, MyISAM 및 Memory)을 지원할 수 있습니다. InnoDB는 현재 가장 일반적으로 사용되는 스토리지 엔진이며 MySQL 버전 5.5.5부터 기본 스토리지 엔진이 되었습니다. 메모리 엔진 실행을 지정하는 방법은 SQL 문에서 engin=memory를 사용하는 것입니다. 서로 다른 스토리지 엔진이 서버 계층을 공유합니다. 1. 커넥터는 클라이언트와의 연결 설정, 권한 획득, 유지 관리를 담당합니다. 연결하다. 연결 명령은 일반적으로 다음과 같습니다.

mysql -h$ip -P$port -u$user -p

연결 명령의 mysql은 서버와 연결을 설정하는 데 사용되는 클라이언트 도구입니다. TCP 핸드셰이크가 완료된 후 커넥터는 ID 인증을 시작합니다. 사용자 이름이나 비밀번호가 올바르지 않으면 "사용자에 대한 액세스가 거부되었습니다" 오류가 발생하고 클라이언트 프로그램은 사용자 이름과 비밀번호를 확인한 후 실행을 종료합니다. 비밀번호를 입력하면 귀하가 가진 권한이 권한 테이블에서 검색됩니다. 이후 이 연결의 권한 판단 논리는 이때 읽은 권한에 따라 달라집니다

즉, 사용자가 성공적으로 연결을 설정한 후에는 관리자 계정을 사용하여 사용자의 권한을 수정하더라도 해당 권한에는 영향을 미치지 않습니다. 기존 연결의 권한. 수정이 완료되면 새로 생성된 연결만 새 권한 설정을 사용합니다

연결이 완료된 후 후속 조치가 없으면 연결은 유휴 상태가 됩니다. show processlist 명령에서 확인할 수 있습니다.

명령이 Sleep이면 이 연결이 유휴 연결임을 의미합니다. 클라이언트가 너무 오랫동안 비활성 상태이면 커넥터가 자동으로 연결을 끊습니다. 이 시간은 wait_timeout 매개변수에 의해 제어됩니다. 기본값은 8시간입니다

• 연결이 끊어진 후 클라이언트가 다시 요청을 보내면 다음과 같은 오류 메시지가 표시됩니다. 쿼리하는 동안 MySQL 서버에 대한 연결이 끊겼습니다. 이때 클라이언트가 계속해서 요청을 보내는 경우에는 다시 연결한 후 요청을 실행해야 합니다. 긴 연결이란 데이터베이스에 연결한 후 동일한 연결을 사용하는 것을 의미합니다. 짧은 연결이란 매번 몇 번의 쿼리를 실행한 후 연결을 끊고 다음 쿼리를 위해 다시 연결하는 것을 말합니다

연결을 설정하는 과정은 일반적으로 더 복잡하므로 최대한 긴 연결을 사용하는 것이 좋습니다

• 하지만 모두 사용하십시오. 오랜 연결 후에 MySQL이 차지하는 메모리가 매우 빠르게 증가하는 이유는 MySQL이 실행 중에 일시적으로 사용하는 메모리를 연결 개체에서 관리하기 때문입니다. 이러한 리소스는 연결이 끊어지면 해제됩니다. 따라서 긴 연결이 누적되면 너무 많은 메모리를 차지하여 시스템(OOM)에 의해 강제 종료될 수 있습니다. 이 현상으로 판단하면 다음 두 가지 해결 방법을 통해 이 문제를 해결할 수 있습니다.

1. .긴 연결. 프로그램 내부 판단에 따라 많은 양의 메모리를 차지하는 쿼리가 실행될 경우 일정 시간이 지나면 연결이 끊어집니다. 다시 쿼리해야 하는 경우 연결을 다시 설정해야 합니다

2. MySQL5.7 이상 버전을 사용하는 경우 상대적으로 큰 작업을 실행할 때마다 mysql_reset_connection을 실행하여 연결 리소스를 다시 초기화할 수 있습니다. 재진술: 이 프로세스에는 재접속이나 권한 확인이 필요하지 않지만 연결 상태는 초기 생성 상태로 복원됩니다

2. 쿼리 캐시

연결이 설정된 후 select 문을 실행할 수 있습니다. MySQL은 쿼리 요청을 받은 후 먼저 쿼리 캐시로 이동하여 이전에 이 명령문이 실행되었는지 확인합니다. 이전에 실행된 명령문과 그 결과는 키-값 쌍으로 메모리에 직접 캐시될 수 있습니다. 키는 쿼리문이고, 값은 쿼리 결과입니다. 캐시에 키가 있으면 해당 값이 클라이언트에 직접 반환됩니다. 명령문이 쿼리 캐시에 없으면 후속 실행 단계가 계속됩니다. 실행이 완료된 후 실행 결과는 쿼리 캐시에 저장됩니다. 쿼리가 캐시에 도달하면 MySQL은 후속 복잡한 작업을 수행하지 않고 결과를 직접 반환할 수 있습니다. 그러나 대부분의 경우 쿼리 캐시가 매우 자주 실패하므로 쿼리 캐시를 사용하지 않는 것이 좋습니다. 이 테이블의 쿼리 캐시가 지워집니다. 업데이트 압력이 심한 데이터베이스의 경우 쿼리 캐시 적중률이 매우 낮습니다

可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于确定要是查询缓存的语句，可以用SQL_CACHE显示指定，如下面这条语句一样：

select SQL_CACHE * from T where ID=10；

MySQL8.0版本直接将查询缓存的整块功能删掉了

3、分析器

如果没有命中查询缓存，就要开始真正执行语句了。MySQL首先要对SQL语句做解析

分析器会先做词法分析。MySQL需要识别一条由多个字符串和空格组成的SQL语句，将其中的每个字符串识别并理解其代表的意义

select * from T where ID=10；

当输入select关键字时，MySQL会识别它为一个查询语句。它也要把字符串T识别成表名T，把字符串ID识别成列ID

做完了这些识别以后，就要做语法分析。基于词法分析的结果，语法分析器会依据MySQL语法规则来检查这个SQL语句是否符合规范。如果语法不对，就会收到"You have an error in your SQL syntax"的错误提示

4、优化器

经过了分析器，在开始执行之前，还要先经过优化器的处理

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联的时候，决定各个表的连接顺序

5、执行器

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段，开始执行语句

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

比如在表T中，ID字段没有索引，那么执行器的执行流程是这样的：

1.调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这个行存在结果集中

2.调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行

3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

在慢查询日志中可以查看到一个名为rows_examined的字段，其表示执行该语句所扫描的行数。这个值就是在执行器每次调用引擎获取数据行的时候累加的

在有些场景下，执行器调用一次，在引起内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的

二、日志系统

表T的创建语句如下，这个表有一个主键ID和一个整型字段c：

create table T(ID int primary key, c int);

如果要将ID=2这一行的值加1，SQL语句如下：

update T set c=c+1 where ID=2;

1、redo log（重做日志）

在MySQL中，如果每次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。MySQL里常说的WAL技术，全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

当有一条记录需要更新的时候，InnoDB引擎就会把记录写到redo log里面，并更新buffer pool的page，这个时候更新就算完成了

buffer pool是物理页的缓存，对InnoDB的任何修改操作都会首先在buffer pool的page上进行，然后这样的页面将被标记为脏页并被放到专门的flush list上，后续将由专门的刷脏线程阶段性的将这些页面写入磁盘

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，从头开始写，写到末尾就又回到开头循环写

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。check point是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件

write pos和check point之间空着的部分，可以用来记录新的操作。如果write pos追上check point，这时候不能再执行新的更新，需要停下来擦掉一些记录，把check point推进一下

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe

2、binlog（归档日志）

MySQL整体来看就有两块：一块是Server层，主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。InnoDB引擎拥有一种特定的日志称为redo log，而Server层也有其自己的日志，被称为binlog

为什么会有两份日志？

처음에는 MySQL에 InnoDB 엔진이 없었거든요. MySQL과 함께 제공되는 엔진은 MyISAM이지만 MyISAM에는 충돌 방지 기능이 없으며 binlog 로그는 보관에만 사용할 수 있습니다. InnoDB는 플러그인 형태로 MySQL에 도입되었습니다. binlog에만 의존하면 충돌 방지 기능이 없으므로 InnoDB는 충돌 방지 기능을 달성하기 위해 리두 로그를 사용합니다. 다음과 같은 세 가지 binlog 형식이 있습니다. ROW, MIXED

1), STATEMENT 모드

SQL 문의 원본 텍스트가 binlog에 기록됩니다. 각 행마다 데이터 변경 사항을 기록할 필요가 없으므로 binlog 로그 양이 줄어들고 IO가 절약되며 성능이 향상된다는 장점이 있습니다. 단점은 경우에 따라 마스터-슬레이브의 데이터가 일치하지 않는다는 것입니다(예: sleep() 함수, last_insert_id() 및 사용자 정의 함수(udf) 등으로 인해 문제가 발생함)

2) , ROW 모드

각 SQL 문장의 문맥 정보를 기록하지 않고 어떤 데이터가 수정되었는지, 수정된 상태만 기록하면 됩니다. 그리고 특정 상황에서는 저장 프로시저나 함수 또는 트리거의 호출과 트리거가 올바르게 복사되지 않는 문제가 발생하지 않습니다. 단점은 특히 테이블 변경 작업을 수행할 때 많은 양의 로그가 생성되어 로그가 급격히 증가한다는 것입니다.

3) MIXED 모드

위 두 모드를 혼합하여 사용하는 일반 복제 STATEMENT 모드를 사용하여 binlog를 저장하며, STATEMENT 모드에서 복사할 수 없는 작업은 ROW 모드를 사용하여 binlog를 저장합니다. MySQL은 실행된 SQL 문에 따라 로그 저장 방법을 선택합니다

3.

1. Redo 로그는 InnoDB 엔진에 고유하며 Binlog는 MySQL의 서버 계층에서 구현되며 모든 엔진에서 사용할 수 있습니다.

2. 특정 데이터, binlog는 명령문의 원래 버전을 기록하는 논리 로그입니다. 예를 들어 ID=23인 줄의 c 필드에 1을 추가합니다. 공간은 항상 소모됩니다. binlog는 추가로 기록될 수 있으며, binlog 파일은 특정 크기에 도달한 후 다음 파일로 전환됩니다. 첫째, 이전 로그를 덮어쓰지 않습니다.

4. 단계 커밋

이 업데이트 문을 실행할 때 실행기 및 InnoDB 엔진의 내부 프로세스:

1 실행기는 먼저 엔진을 찾고 ID=2 이 줄을 가져옵니다. ID는 기본 키이며 엔진은 이 행을 찾기 위해 트리 검색을 직접 사용합니다. ID=2인 행의 데이터가 이미 메모리에 있으면 실행기로 직접 반환됩니다. 그렇지 않으면 먼저 디스크에서 메모리로 읽어온 다음 실행기가 반환됩니다. 엔진에서 제공한 행 데이터, 이 값에 1을 추가하여 새 데이터 행을 가져온 다음 엔진 인터페이스를 호출하여 새 데이터 행을 씁니다.

3 엔진은 새 데이터 행을 메모리에 업데이트하고 기록합니다. 리두 로그에 대한 업데이트 작업입니다. 리두 로그가 준비 상태일 때입니다. 그런 다음 실행기가 완료되었음을 알리고 언제든지 트랜잭션을 제출할 수 있습니다4. 실행기는 이 작업의 binlog를 생성하고 디스크에 binlog를 기록합니다

5. 실행기는 엔진의 커밋 트랜잭션 인터페이스를 호출합니다. 그리고 엔진은 방금 작성한 redo를 작성합니다. 로그는 submit 상태로 바뀌고 업데이트가 완료됩니다. 그림의 라이트 박스는 InnoDB 내부에서 실행됨을 나타냅니다. 어두운 상자는 실행기에서 실행됨을 나타냅니다.

리두 로그 작성은 준비와 커밋의 두 단계로 나누어집니다. 이는 리두 로그와 binlog가 두 개의 독립적인 논리이기 때문입니다. 2단계 커밋은 필요하지 않습니다. 먼저 redo 로그를 작성한 다음 binlog를 작성하거나, 먼저 binlog를 작성한 다음 redo 로그를 작성하세요

1. redo 로그를 먼저 작성한 다음 binlog를 작성하세요. redo 로그는 작성되었으나 binlog는 아직 작성되지 않은 상태에서 MySQL 프로세스가 비정상적으로 재시작되는 경우. 리두 로그가 작성된 후에는 시스템이 충돌하더라도 데이터를 복구할 수 있으므로 복구 후 이 줄의 c 값은 1입니다. 그러나 binlog가 완료되기 전에 crash가 발생했기 때문에 현재 이 명령문은 binlog에 기록되지 않습니다. binlog에 기록되는 이 줄의 c 값은 binlog를 먼저 작성한 다음 redo 로그를 작성합니다. binlog가 작성된 후 충돌이 발생하면 redo 로그가 아직 작성되지 않았으므로 충돌 복구 후 트랜잭션이 유효하지 않으므로 이 줄의 c 값은 0입니다. 하지만 binlog에는 c가 0에서 1로 변경되는 로그가 이미 기록되어 있습니다. 따라서 binlog 복구를 사용하면 트랜잭션이 하나 더 생기고 최종 복구된 c 컬럼의 값은 1이 됩니다

two-phase commit을 사용하지 않으면 해당 로그 복구를 사용하는 라이브러리 상태와 데이터베이스 상태가 일치하지 않을 수 있습니다. . redo 로그와 binlog는 모두 트랜잭션의 커밋 상태를 나타내는 데 사용될 수 있으며, 2단계 커밋은 두 상태를 논리적으로 일관되게 유지하는 것입니다.

redo 로그는 충돌 방지 기능을 보장하는 데 사용됩니다. innodb_flush_log_at_trx_commit 매개변수를 1로 설정하면 각 트랜잭션의 redo 로그가 디스크에 직접 유지된다는 의미입니다. 이는 MySQL이 비정상적으로 다시 시작된 후에도 데이터가 손실되지 않음을 보장할 수 있다는 의미입니다. 각 트랜잭션의 binlog는 모두 디스크에 유지되므로 MySQL이 비정상적으로 다시 시작된 후에도 binlog가 손실되지 않습니다.

三、MySQL刷脏页

1、刷脏页的场景

当内存数据页跟磁盘数据页不一致的时候，我们称这个内存页为脏页。当内存数据被写入磁盘后，内存和磁盘上的数据页就会保持一致，这种状态被称为“干净页”

• 第一种场景是，InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写
  
  checkpoint位置从CP推进到CP’，就需要将两个点之间的日志对应的所有脏页都flush到磁盘上。之后，上图中从write pos到CP’之间就是可以再写入的redo log的区域

• 第二种场景是，系统内存不足。当内存空间不足以分配新的内存页时，系统会选择淘汰一些数据页来腾出内存空间以供其他数据页使用。如果淘汰的是脏页，就要先将脏页写到磁盘

这时候不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿redo log出来应用不就行了？

这里是从性能考虑的。如果刷脏页一定会写盘，就保证了每个数据页有两种状态：一种是内存里存在，内存里就肯定是正确的结果，直接返回；另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样的效率最高

• 第三种场景是，MySQL认为系统空闲的时候刷脏页，当然在系统忙的时候也要找时间刷一点脏页

• 第四种场景是，MySQL正常关闭的时候会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快

redo log写满了，要flush脏页，出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住

内存不够用了，要先将脏页写到磁盘，这种情况是常态。InnoDB用缓冲池管理内存，缓冲池中的内存页有三种状态：

• 第一种是还没有使用的

• 第二种是使用了并且是干净页

• 第三种是使用了并且是脏页

InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少

如果要读取的数据页不在内存中，则需要从缓冲池中请求一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页，即必须将脏页先刷到磁盘，变成干净页后才能复用

刷页虽然是常态，但是出现以下两种情况，都是会明显影响性能的：

• 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长

• 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的

2、InnoDB刷脏页的控制策略

首先，要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。参数为innodb_io_capacity，建议设置成磁盘的IOPS

考虑到脏页比例和redo log写入速度，InnoDB的刷盘速度得到优化。默认值为75％，参数innodb_max_dirty_pages_pct限制了脏页的比例。脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的，SQL语句如下：

mysql>  select VARIABLE_VALUE into @a from performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

四、日志相关问题

问题一：在两阶段提交的不同时刻，MySQL异常重启会出现什么现象

如果在图中时刻A的地方，也就是写入redo log处于prepare阶段之后、写binlog之前，发生了崩溃，由于此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog还没写，所以也不会传到备库

如果在图中时刻B的地方，也就是binlog写完，redo log还没commit前发生崩溃，那崩溃恢复的时候MySQL怎么处理？

崩溃恢复时的判断规则：

1）如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交

2）如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整

a.如果完整，则提交事务

b.否则，回滚事务

时刻B发生崩溃对应的就是2(a)的情况，崩溃恢复过程中事务会被提交

问题二：MySQL怎么知道binlog是完整的？

一个事务的binlog是有完整格式的：

• statement格式的binlog，最后会有COMMIT

• row格式的binlog，最后会有一个XID event

问题三：redo log和binlog是怎么关联起来的？

它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：

• 如果碰到既有prepare、又有commit的redo log，就直接提交

• 如果碰到只有prepare、而没有commit的redo log，就拿着XID去binlog找对应的事务

问题四：redo log一般设置多大？

如果是现在常见的几个TB的磁盘的话，redo log设置为4个文件、每个文件1GB

问题五：正常运行中的实例，数据写入后的最终落盘，是从redo log更新过来的还是从buffer pool更新过来的呢？

redo log并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在数据最终落盘是由redo log更新过去的情况

脏页是指在正常运行的实例中，当数据页被修改后，与存储在磁盘上的数据页不一致。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与redo log毫无关系

2.在崩溃恢复场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它对到内存，然后让redo log更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态

问题六：redo log buffer是什么？是先修改内存，还是先写redo log文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;insert into t1 ...insert into t2 ...commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没commit的时候就直接写到redo log文件里

所以，redo log buffer就是一块内存，用来先存redo日志的。也就是说，在执行第一个insert的时候，数据的内存被修改了，redo log buffer也写入了日志。在执行commit语句时，才真正将日志写入redo log文件

五、MySQL是怎么保证数据不丢的？

只要redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启后，数据可以恢复

1、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控制的：

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

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

• sync_binlog=N（N>1）的时候，表示每次提交事务都write，但累积N个事务后才fsync

因此，在出现IO瓶颈的场景中，将sync_binlog设置成一个比较大的值，可以提升性能，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志

2、redo log的写入机制

在执行事务过程中所产生的 redo log 需要先写入 redo log 缓存。redo log buffer里面的内容不是每次生成后都要直接持久化到磁盘，也有可能在事务还没提交的时候，redo log buffer中的部分日志被持久化到磁盘

redo log可能存在三种状态，对应下图的三个颜色块

这三张状态分别是：

• 리두 로그 버퍼에 존재하며 물리적으로 MySQL 프로세스 메모리에 존재합니다. 그림에서 빨간색 부분은

• 디스크에 기록되지만 지속되지 않고 물리적으로 파일 시스템의 페이지 캐시에 존재합니다. 즉, 사진의 노란색 부분

• 은 하드디스크에 해당하는 디스크에 지속되며, 사진의 녹색 부분은

로그가 리두 로그 버퍼에 기록되고 페이지 캐시에 기록됩니다. 둘 다 빠르지만 내구성이 뛰어납니다. 디스크로 플러시하는 속도가 훨씬 느립니다.

리두 로그의 쓰기 전략을 제어하기 위해 InnoDB는 세 가지 가능한 값이 있는 innodb_flush_log_at_trx_commit 매개변수를 제공합니다.

• 0으로 설정하면 모든 트랜잭션이 제출됨을 의미합니다.

• 1로 설정하면 트랜잭션이 제출될 때마다 리두 로그가 디스크에 직접 유지된다는 의미입니다.

• 2로 설정하면 모든 트랜잭션이 제출됨을 의미합니다. InnoDB에는 1초마다 write를 호출하여 리두 로그 버퍼의 로그를 파일 시스템의 페이지 캐시에 쓴 다음 디스크에 대한 지속성을 위해 fsync를 호출합니다. . 트랜잭션 실행 도중의 리두 로그도 리두 로그 버퍼에 직접 기록되며, 이러한 리두 로그도 백그라운드 스레드에 의해 디스크에 유지됩니다. 즉, 트랜잭션이 아직 커밋되지 않은 경우에도 해당 리두 로그가 디스크에 유지되었을 수 있습니다

커밋되지 않은 트랜잭션의 리두 로그가 디스크에 기록되는 두 가지 시나리오가 있습니다

1.redo 로그 버퍼 점유된 공간이 innodb_log_buffer_size의 절반에 가까워지면 백그라운드 스레드가 디스크에 적극적으로 씁니다. 트랜잭션이 제출되지 않았으므로 디스크 쓰기 작업은 fsync를 호출하지 않고 쓰기만 수행됩니다. 이는 파일 시스템의 페이지 캐시에만 남아 있음을 의미합니다

2. 병렬 트랜잭션이 제출되면 이 트랜잭션의 리두 로그 버퍼가 생성됩니다. 디스크에 지속됩니다. 트랜잭션 A가 실행 중이고 일부 리두 로그를 버퍼에 기록했다고 가정합니다. 이때 다른 스레드의 트랜잭션 B가 제출됩니다. innodb_flush_log_at_trx_commit이 1로 설정되면 트랜잭션 B는 리두 로그 버퍼에 있는 모든 로그를 유지합니다. 디스크. 이 경우 트랜잭션 A와 관련된 Redo 로그 버퍼가 함께 디스크에 기록됩니다

2단계 커밋은 먼저 Redo 로그를 준비한 다음 binlog를 작성하고 마지막으로 Redo 로그를 작성합니다. 헌신적인. innodb_flush_log_at_trx_commit이 1로 설정되면 리두 로그는 준비 단계에서 한 번 유지됩니다

MySQL의 이중 1 구성은 sync_binlog와 innodb_flush_log_at_trx_commit가 모두 1로 설정됨을 의미합니다. 즉, 트랜잭션이 완전히 커밋되기 전에 다시 실행 로그(준비 단계)용과 binlog

3용으로 각각 하나씩 두 번의 디스크 플러시를 기다려야 합니다. 그룹 제출 메커니즘

로그 논리 시퀀스 번호 LSN은 다음과 같습니다. 단조 증가. 리두 로그의 쓰기 지점에 대응하기 위해 사용됩니다. 길이 길이의 리두 로그가 기록될 때마다 LSN 값이 길이에 추가됩니다. LSN은 InnoDB의 데이터 페이지에도 기록되어 리두 로그의 반복 실행과 데이터 페이지의 반복 업데이트를 방지합니다

위 그림은 준비 단계에서 세 개의 동시 트랜잭션을 보여줍니다. 리두 로그 버퍼가 있고 디스크 프로세스에 유지되며 해당 LSN은 각각 50, 120 및 160입니다

1. trx1이 가장 먼저 도착하고 이 그룹의 리더로 선택됩니다
2. 디스크에 쓰기를 시작하려고 하면 이 그룹은 이미 세 개의 트랜잭션으로 LSN도 160

3이 되었습니다. trx1이 디스크에 쓸 때 LSN=160이 되므로 trx1이 반환되면 LSN이 적은 모든 리두 로그가 발생합니다. 160 이상이 삭제되었습니다. 디스크에 대한 지속성

4 이때 trx2 및 trx3을 직접 반환할 수 있습니다

그룹 제출 시 그룹 구성원이 많을수록 디스크 IOPS 절약 효과가 더 좋습니다

In 하나의 fsync가 더 많은 그룹 구성원을 가져올 수 있도록 MySQL 시간 지연을 최적화했습니다

binlog를 디스크에 fsync하기 위해 위 그림의 4단계를 실행할 때 binlog가 그룹으로 제출될 수도 있습니다. 의 여러 트랜잭션이 기록되었으므로 함께 유지되므로 IOPS 소비도 줄일 수 있습니다

binlog 그룹 제출 효과를 향상시키려면 binlog_group_commit_sync_delay 및 binlog_group_commit_sync_no_delay_count

두 매개변수를 설정하여 이를 달성할 수 있습니다. 1. binlog_group_commit_sync_delay 매개변수는 fsync를 호출하기 전에 몇 마이크로초를 지연해야 하는지 나타냅니다.

2.binlog_group_commit_sync _no_delay_count 매개변수는 누적을 나타냅니다. 이 두 조건 중 하나가 충족되면 fsync가 호출되어야 합니다. WAL 메커니즘은 주로 두 가지 측면에서 이점을 얻습니다.

redo 로그와 binlog가 순차적으로 기록되고 디스크 순차 쓰기가 무작위 쓰기보다 빠릅니다.

그룹 제출 메커니즘은 디스크 주문 IOPS 소비를 크게 줄일 수 있습니다

4. 이제 MySQL에 성능 병목 현상이 발생하고 병목 현상이 IO에 있는 경우 성능을 향상하기 위해 어떤 방법을 사용할 수 있습니까?

1 binlog_group_commit_sync_delay(fsync를 호출하기 전에 지연할 마이크로초 수) 및 binlog_group_commit_sync_no_delay_count( 누적 횟수) 디스크에 대한 binlog 쓰기 횟수를 줄이려면 fsync) 매개변수만 호출하세요. 이 방법은 문의 응답 시간을 증가시킬 수 있지만 의도적으로 대기함으로써 이루어지기 때문에 데이터 손실의 위험이 없습니다

2. sync_binlog를 1보다 큰 값으로 설정하십시오(트랜잭션이 커밋될 때마다 쓰지만 fsync는 누적됩니다). N번 거래 후). 이렇게 하면 호스트 전원이 꺼질 때 binlog 로그가 손실될 위험이 있습니다

3. innodb_flush_log_at_trx_commit를 2로 설정합니다(트랜잭션이 커밋될 때마다 리두 로그만 페이지 캐시에 기록합니다). 이렇게 하면 호스트의 전원이 꺼지면 데이터가 손실될 위험이 있습니다

위 내용은 MySQL 인프라 및 로그 시스템 예시 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!