MySQL のインフラストラクチャとロギング システムについて話しましょう

この記事では、MySQL の関連知識を紹介し、MySQL インフラストラクチャとログ システムについて詳しく説明します。お役に立てば幸いです。

1. MySQL インフラストラクチャ

## MySQL は、サーバー層とストレージ エンジン層の 2 つの部分に分けることができます。

サーバー層には、コネクタ、クエリ キャッシュ、アナライザー、オプティマイザー、エグゼキューターなどが含まれており、MySQL のコア サービス機能のほとんどと、すべての組み込み関数 (日付、時刻、数学的関数、暗号化など) をカバーします。関数など)、ストアド プロシージャ、トリガー、ビューなど、すべてのクロスストレージ エンジン関数がこの層に実装されます。

ストレージ エンジンは、データの保存と取得を担当します。そのアーキテクチャ モデルはプラグインであり、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 command

コマンドはスリープで、この接続がアイドル接続であることを示します

クライアントが長時間移動しない場合、コネクタは自動的に切断してください。この時間はパラメータ wait_timeout によって制御されます。デフォルト値は 8 時間です。

接続が切断された後にクライアントが再度リクエストを送信すると、次のエラー メッセージが表示されます: クエリ中に MySQL サーバーへの接続が失われました。このとき、再接続してリクエストを実行する必要があります。データベースでは、接続が長いとは、接続が成功した後、クライアントがリクエストを継続すると、常に同じ接続が使用されることを意味します。短い接続とは、毎回いくつかのクエリを実行した後に接続を切断し、次のクエリのために接続を再確立することを指します。接続を確立するプロセスは通常より複雑であるため、できるだけ長い接続を使用することをお勧めします。

##しかし、長い接続をすべて使用した後、MySQL が占有するメモリが急速に増加する場合があります。これは、MySQL が実行中に一時的に使用するメモリが接続オブジェクトで管理されるためです。これらのリソースは、接続が切断されると解放されます。したがって、長い接続が蓄積されると、メモリを占有しすぎてシステムによって強制終了される可能性があります (OOM)。現象から判断すると、MySQL が異常に再起動します。

この問題は、次の 2 つの解決策で解決できます:

1. 長い接続は定期的に切断してください。一定期間使用した後、またはメモリを占有する大規模なクエリが実行されたとプログラムが判断した後、接続を切断し、クエリ後に再接続します。以降のバージョンでは、比較的大規模な操作を初めて実行した後、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语句做解析

分析器会先做词法分析。输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么

select * from T where ID=10；

MySQL从输入的select这个关键字识别出来，这是一个查询语句。它也要把字符串T识别成表名T，把字符串ID识别成列ID

做完了这些识别以后，就要做语法分析。根据词法分析的结果，语法分析器会根据语法规则，判断这个SQL语句是否满足MySQL语法。如果语法不对，就会收到"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功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog

为什么会有两份日志？

MySQL には最初から InnoDB エンジンがなかったからです。 MySQL に付属するエンジンは MyISAM ですが、MyISAM にはクラッシュセーフ機能がなく、binlog ログはアーカイブにのみ使用できます。 InnoDB は、プラグインの形式で MySQL に導入されます。binlog のみに依存するとクラッシュ セーフ機能がないため、InnoDB は REDO ログを使用してクラッシュ セーフ機能を実装します。

binlog ログ フォーマット:

binlog には 3 つの形式があります: STATEMENT、ROW、MIXED

1)、STATEMENT モード

SQL ステートメントはバイナリログに記録されます。利点は、データの変更を各行に記録する必要がないため、binlog ログの量が減り、IO が節約され、パフォーマンスが向上することです。欠点は、場合によってはマスターとスレーブ内のデータが不整合になることです (sleep() 関数、last_insert_id()、ユーザー定義関数 (udf) などが問題を引き起こします)

2 )、ROW モード

# は、各 SQL ステートメントのコンテキスト情報を記録せず、どのデータが変更されたか、およびその変更内容のみを記録します。また、特定の状況下でストアド プロシージャや関数、トリガーの呼び出しやトリガーが正しくコピーされないという問題は発生しません。欠点は、大量のログが生成されることです。特にテーブルを変更する場合、ログが急増します。

3)、MIXED モード

上記 2 つのモードは通常、レプリケーションの場合は STATEMENT モードを使用して binlog を保存します。STATEMENT モードでコピーできない操作の場合は、ROW モードを使用して binlog を保存します。MySQL は実行された SQL ステートメントに従ってログの保存方法を選択します。

3、REDO ログと binlog ログの違い

1.redo ログは InnoDB エンジンに固有であり、binlog は MySQL のサーバー層によって実装されており、すべてのユーザーが使用できます。エンジン

2.redo ログは、特定のデータにどのような変更が加えられたかを記録する物理ログです。ビンログは、このステートメントの元のロジックを記録する論理ログです。たとえば、c に 1 を追加します。 ID=2

3 の行のフィールド REDO ログはループで書き込まれます はい、スペースは必ず使い果たされます、binlog は追加で書き込めます binlog ファイルが一定のサイズに達すると、次のログに切り替わり、前のログは上書きされません

4. 2 段階の送信

実行プログラムと InnoDB エンジンの内部プロセスこの更新ステートメントを実行するとき:

1. 実行プログラムは最初にエンジンを見つけ、行 ID=2 を取得します。 ID が主キーであり、エンジンはツリー検索を直接使用してこの行を見つけます。 ID=2 の行のデータが既にメモリ内にある場合は、そのデータが直接実行プログラムに返されますが、それ以外の場合は、ディスクからメモリに読み取られてから返される必要があります。エグゼキューターはエンジンに行データを取得し、この値に 1 を加算して新しいデータ行を取得し、エンジン インターフェイスを呼び出してこの新しいデータ行

3 を書き込みます。エンジンはこの新しい行を更新します。データをメモリに読み込んで更新します 操作はREDOログに記録されますが、この時点でREDOログは準備状態になっています。次に、実行が完了し、いつでもトランザクションを送信できることを実行者に通知します (

4)。実行者は、この操作のバイナリログを生成し、そのバイナリログをディスクに書き込みます

5。 executor がエンジンのコミット トランザクション インターフェイスを呼び出し、エンジンは書き込まれたばかりの REDO ログを送信済みの状態に変更し、更新が完了します。

update ステートメントの実行フローチャートは次のとおりです。図は InnoDB 内で実行されることを示し、黒いボックスはエグゼキューターで実行されることを示します。

# で実行されると、REDO ログの書き込みが 2 つのステップに分割されます。準備とコミット。これは 2 段階のコミットです。

REDO ログと Binlog は 2 つの独立したロジックであるため、2 段階の送信が必要ない場合は、最初に REDO ログを書き込んでから binlog を書き込むか、最初に binlog を書き込み、次に REDO ログを書き込みます

1.最初に REDO ログを書き込み、次に binlog を書き込みます。 REDO ログは書き込まれたが、バイナリログはまだ書き込まれていないときに、MySQL プロセスが異常に再起動した場合。 REDO ログが書き込まれた後は、システムがクラッシュしてもデータを回復できるため、回復後のこの行の c の値は 1 になります。ただし、バイナリログが完了する前にクラッシュしたため、このステートメントはこの時点ではバイナリログには記録されませんでした。バイナリログに記録されたこの行の c の値は 0

2 です。最初にバイナリログを書き込み、次にバイナリログを書き込みます。やり直しログ。 binlog の書き込み後にクラッシュが発生した場合、REDO ログはまだ書き込まれていないため、クラッシュ回復後のトランザクションは無効になるため、この行の c の値は 0 になります。ただし、binlog には、c を 0 から 1 に変更するログがすでに記録されています。したがって、後で binlog を復元すると、トランザクションがもう 1 つ出てきて、復元された行の c の値は 1

2 フェーズ コミットを使用しないと、データベースの状態が異なる可能性があります。ログから回復されたライブラリのステータスが一貫していません。 REDO ログと binlog の両方を使用して、トランザクションのコミット ステータスを表すことができます。2 段階コミットは、2 つの状態の論理的な一貫性を維持するために使用されます。

REDO ログは、クラッシュ セーフ機能を確保するために使用されます。 innodb_flush_log_at_trx_commit パラメータが 1 に設定されている場合、各トランザクションの REDO ログがディスクに直接永続化され、MySQL が異常に再起動した後にデータが失われないことが保証されます。 1 に設定すると、すべてのトランザクションのバイナリログがディスクに永続化され、MySQL が異常に再起動した後でもバイナリログが失われないことを意味します。

三、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

InnoDB的刷盘速度就是考虑脏页比例和redo log写盘速度。参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。脏页比例是通过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更新过去的情况

1.如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与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也写入了日志。但是，真正把日志写到redo log文件，是在执行commit语句的时候做的

五、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 buffer的。redo log buffer里面的内容不是每次生成后都要直接持久化到磁盘，也有可能在事务还没提交的时候，redo log buffer中的部分日志被持久化到磁盘

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

这三张状态分别是：

• REDO ログ バッファ内、物理的には MySQL プロセス メモリ内に存在します (図の赤い部分です)
• はディスクに書き込まれますが、物理的には永続化されず、ファイル システム キャッシュ内、つまり図
• の黄色の部分はディスクに永続化されます。これはハードディスクに対応し、図

# の緑色の部分です。 # ログは REDO ログ バッファに書き込まれ、ページ キャッシュへの書き込みは非常に高速ですが、ディスクへの永続化はかなり遅くなります。

REDO ログの書き込み戦略を制御するために、InnoDB は innodb_flush_log_at_trx_commit パラメータを提供します。

0 に設定すると、トランザクションが送信されるたびに REDO ログが REDO ログ バッファにのみ残ることを意味します。 1 に設定すると、トランザクションが送信されるたびに REDO ログが REDO ログ バッファに残されることを意味します。Persist REDO Log Direct to disc
• 2 に設定すると、トランザクションが送信されるたびに、REDO ログがディスクに直接保存されることを意味します。 REDO ログはページ キャッシュにのみ書き込まれます
• InnoDB にはバックグラウンド スレッドがあり、1 秒ごとに、write を呼び出すことによって、REDO ログ バッファ内のログがファイル システムのページ キャッシュに書き込まれます。その後、fsync が呼び出されてディスクに永続化されます。トランザクション実行中の REDO ログも REDO ログ バッファに直接書き込まれ、これらの REDO ログもバックグラウンド スレッドによってディスクに永続化されます。つまり、コミットされていないトランザクションの REDO ログがディスクに永続化されている可能性があります。
##コミットされていないトランザクションの REDO ログがディスクに書き込まれるシナリオは 2 つあります

#1。 REDO ログ バッファが占めるスペースが innodb_log_buffer_size の半分に達しようとすると、バックグラウンド スレッドがディスクに積極的に書き込みます。トランザクションが送信されていないため、ディスク書き込みアクションは fsync を呼び出さずに書き込みのみになります。つまり、ファイル システムのページ キャッシュにのみ残ります

2。並列トランザクションが送信されると、このトランザクションは偶然に行われ、ログ バッファはディスクに保存されます。トランザクション A が実行途中で、REDO ログがバッファに書き込まれているとします。この時点で、別のスレッドからトランザクション B が送信されます。innodb_flush_log_at_trx_commit が 1 に設定されている場合、トランザクション B は、REDO ログ バッファ内のすべてのログを永続化します。ディスク。このとき、REDO ログ バッファ内のトランザクション A のログはディスクに永続化されます

2 段階コミット タイミングとしては、まず REDO ログが用意され、次に binlog が書き込まれ、最後に、REDO ログがコミットされます。 innodb_flush_log_at_trx_commit が 1 に設定されている場合、REDO ログは準備フェーズで一度永続化される必要があります。

MySQL の 2 つの 1 構成は、sync_binlog と innodb_flush_log_at_trx_commit の両方が 1 に設定されることを意味します。つまり、トランザクションが完全にコミットされる前に、REDO ログ (準備段階) 用と binlog 用の 2 回のディスク フラッシュを待つ必要があります。

#3. グループ送信メカニズム

ログ論理シーケンス番号 LSN は単調増加し、REDO ログの各書き込みポイントに対応するために使用されます。 length の長さの REDO ログが書き込まれるたびに、LSN の値が追加されます。長さ。データ ページが複数回実行されないように、LSN も InnoDB データ ページに書き込まれます。繰り返される REDO ログ

上の図は、準備フェーズでの 3 つの同時トランザクションを示しています。すべて書き込まれました REDO ログ バッファのプロセスが完了し、ディスクに永続化された後、対応する LSN は 50、120、および 160 になります。

1.trx1 が最初に到着し、このグループのリーダーとして選択されます

2. trx1 がディスクへの書き込みを開始するとき、このグループにはすでに 3 つのトランザクションがあり、この時点で LSN も 160 になります
3. trx1 がディスクへの書き込みを開始するとき、LSN = 160 になるため、trx1 が戻ると、LSN が 160 以下のすべての REDO ログがディスクに保存されています

4。この時点で、trx2 と trx3 は直接返すことができます

グループ送信では、グループ メンバーが多いほど、ディスク IOPS の節約効果が高くなります。

1 回の fsync でより多くのチーム メンバーを参加できるようにするために、MySQL は時間のかかる最適化を行いました

バイナリログはグループで送信することもできます。上図のステップ 4 を実行してバイナリログをディスクに fsync するときに、複数のトランザクションのバイナリログが書き込まれている場合は、それらのバイナリログもグループで送信されます。これにより、IOPS の消費も削減できます

binlog グループ送信の効果を改善したい場合は、2 つのパラメーター binlog_group_commit_sync_lay と binlog_group_commit_sync_no_lay_count

1 を設定することでこれを実現できます。 binlog_group_commit_sync_lay パラメータは、fsync を呼び出す前に遅延するマイクロ秒数を示します。

2 .binlog_group_commit_sync_no_delay_count パラメータは、fsync を呼び出す前に累積する回数を示します。

これら 2 つの条件のいずれかが満たされている限り、満たされた場合、fsync が呼び出されます

WAL メカニズムは主に 2 つの側面から恩恵を受けます:

• REDO ログと binlog は両方ともシーケンシャルに書き込まれます。ディスクへのシーケンシャル書き込みは、ランダム書き込みよりも高速です。
• グループ送信メカニズムにより、ディスク順序 IOPS 消費量を大幅に削減できます

4. MySQL にパフォーマンスのボトルネックがあり、そのボトルネックが IO にある場合、パフォーマンスを向上させるためにどのような方法を使用できますか

1. binlog_group_commit_sync_lay を設定します (fsync を呼び出すまでの遅延時間をマイクロ秒単位で指定します) ) および binlog_group_commit_sync_no_lay_count (fsync を呼び出す前に蓄積する回数) パラメーターを使用して、ディスクへの binlog 書き込みの回数を減らします。このメソッドは追加の意図的な待機に基づいて実装されているため、ステートメントの応答時間が長くなる可能性がありますが、データ損失のリスクはありません

2. sync_binlog を 1 より大きい値に設定します (同期するたびに書き込みます)。トランザクションはコミットされます)、ただし fsync は N 個のトランザクションが蓄積された後にのみ行われます)。これを実行すると、ホストの電源がオフになると binlog ログが失われるというリスクがあります。

3. innodb_flush_log_at_trx_commit を 2 に設定します (トランザクションが送信されるたびに、REDO ログのみがページ キャッシュに書き込まれます)。これを行うリスクは、ホストの電源が失われるとデータが失われることです。

[関連する推奨事項:

mysql ビデオ チュートリアル]

以上がMySQL のインフラストラクチャとロギング システムについて話しましょうの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。