MySQL オンラインリカバリ UNDO テーブルスペース実戦記録

1 MySQL5.6

1.1 関連パラメータ

MySQL 5.6 では、innodb_undo_directory、innodb_undo_logs、innodb_undo_tablespaces の 3 つのパラメータが追加され、これにより、undo ログを ibdata1 から移動して個別に保存できるようになります。

innodb_undo_directory: UNDO テーブルスペースが個別に保存されるディレクトリを指定します。デフォルト値は . (つまり datadir) です。相対パスまたは絶対パスを設定できます。このパラメータインスタンスは初期化後に直接変更することはできませんが、データベースを停止し、構成ファイルを変更してから、UNDO 表領域ファイルを移動することで変更できます。

デフォルトのパラメータ:

mysql> '%undo%' のような変数を表示します。
+-------------------------+-------+
| 変数名 | 値 |
+-------------------------+-------+
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
+-------------------------+-------+

innodb_undo_tablespaces: 個別に保存される UNDO テーブルスペースの数を指定します。たとえば、3 に設定されている場合、UNDO テーブルスペースは undo001、undo002、undo003 となり、各ファイルの初期サイズはデフォルトで 10M になります。このパラメータを 3 以上に設定することをお勧めします。その理由については後述します。このパラメータインスタンスは初期化後に変更できません

インスタンスの初期化は、innodb_undo_tablespaces を変更することです。

mysql_install_db ...... --innodb_undo_tablespaces

$ ls
...
元に戻す001 元に戻す002 元に戻す003

innodb_rollback_segments: デフォルト値は 128 です。各ロールバックセグメントは、同時に 1024 のオンライントランザクションをサポートできます。これらのロールバックセグメントは、各 UNDO 表領域に均等に分散されます。この変数は動的に調整できますが、物理的なロールバックセグメントは削減されず、使用されるロールバックセグメントの数のみが制御されます。

1.2 使用方法

インスタンスを初期化する前に、innodb_undo_tablespaces パラメータ (3 以上を推奨) を設定して、UNDO ログを別の UNDO テーブルスペースに設定するだけです。より高速なデバイスに undo ログを配置する必要がある場合は、innodb_undo_directory パラメータを設定できますが、現在 SSD が非常に普及しているため、通常はこれを行いません。 innodb_undo_logs はデフォルトで 128 に設定できます。

UNDO ログは ibdata の外部に保存できます。しかし、この機能はまだ役に立ちません:

まず、インスタンスをインストールするときに、独立した UNDO 表領域を指定する必要があります。インストールが完了した後は変更できません。
UNDO 表領域のスペース ID は 1 から始まる必要があります。UNDO 表領域を追加または削除することはできません。

1.3 大規模トランザクションのテスト

mysql> テーブル test.tbl( id int primary key auto_increment, name varchar(200) ) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.03 秒)

mysql> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> test.tbl(name) に値を挿入します(repeat('1',00));
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> test.tbl(name) に挿入し、test.tbl から name を選択します。
クエリは正常、1 行が影響を受けました (0.00 秒)
記録: 1 重複: 0 警告: 0

...

mysql> test.tbl(name) に挿入し、test.tbl から name を選択します。
クエリは正常、2097152 行が影響を受けました (24.84 秒)
レコード: 2097152 重複: 0 警告: 0

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (7.90 秒)

アンドログが拡大し始めたのがわかります!トランザクションがコミットされた後、スペースは再利用されません。

$ du -sh 元に戻す*
10M 元に戻す001
69M 元に戻す002
10M 元に戻す003

2 MySQL5.7

5.7 オンラインでの UNDO 表領域の切り捨てを導入

2.1 関連パラメータ

必要条件:

innodb_undo_tablespaces: クリーンアップ中に一方が他方を使用できるように、少なくとも 2 つ。このパラメータは、インスタンスの初期化後に変更することはできません。
innodb_rollback_segments: ロールバックセグメントの数。システムテーブルスペースには常に 1 つのロールバックセグメントが割り当てられ、一時テーブルスペース用に 32 個が予約されています。したがって、UNDO 表領域を使用する場合は、この値は少なくとも 33 である必要があります。たとえば、2 つの UNDO 表領域が使用されている場合、この値は 35 になります。複数の UNDO 表領域を設定すると、システム表領域内のロールバックセグメントは非アクティブになります。

起動パラメータ:

innodb_undo_log_truncate=オン
innodb_max_undo_log_size: この値を超えるテーブルスペースは切り捨てとしてマークされます。動的パラメータのデフォルト値は 1G です。
innodb_purge_rseg_truncate_frequency: ロールバックセグメントを解放する前にパージ操作を呼び出す回数を指定します。 UNDO 表領域は、UNDO 表領域内のロールバックセグメントが解放された場合にのみ切り捨てられます。パラメータが小さいほど、UNDO 表領域の切り捨てが頻繁に試行されることがわかります。

2.2 洗浄工程

UNDO テーブルスペースのサイズが innodb_max_undo_log_size を超えると、テーブルスペースはクリーンアップ対象としてマークされます。テーブルスペースが繰り返し清掃されるのを避けるために、マーキングは繰り返されます。
マーク表領域内のロールバックセグメントは非アクティブになり、実行中のトランザクションは完了を待機します。
パージを開始する
UNDO テーブルスペース内のすべてのロールバックセグメントを解放した後、truncate を実行して UNDO テーブルスペースを初期サイズに切り捨てます。初期サイズは innodb_page_size によって決定されます。デフォルトサイズの 16 KB は 10 MB のテーブルスペースに相当します。
ロールバックセグメントを再アクティブ化して、新しいトランザクションに割り当てることができるようにします。

2.3 パフォーマンスに関する推奨事項

表領域を切り捨てるときにパフォーマンスの低下を回避する最も簡単な方法は、UNDO 表領域の数を増やすことです。

2.4 大規模トランザクションのテスト

8 つの UNDO テーブルスペース、innodb_purge_rseg_truncate_frequency=10 を設定します。

mysqld --initialize ... --innodb_undo_tablespaces=8

テストを開始

mysql> '%undo%' のようなグローバル変数を表示します。
+--------------------------+-------------+
| 変数名 | 値 |
+--------------------------+-------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | オン |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 8 |
+--------------------------+-------------+

mysql> @@innodb_purge_rseg_truncate_frequency を選択します。
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
| 10 |
+----------------------------------------+

@@innodb_max_undo_log_size を選択します。
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|10485760|
+----------------------------+

mysql> テーブル test.tbl( id int primary key auto_increment, name varchar(200) ) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.03 秒)

mysql> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> test.tbl(name) に値を挿入します(repeat('1',00));
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> test.tbl(name) に挿入し、test.tbl から name を選択します。
クエリは正常、1 行が影響を受けました (0.00 秒)
記録: 1 重複: 0 警告: 0

...

mysql> test.tbl(name) に挿入し、test.tbl から name を選択します。
クエリは正常、2097152 行が影響を受けました (24.84 秒)
レコード: 2097152 重複: 0 警告: 0

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (7.90 秒)

UNDO表領域は100MB以上に拡張した後、正常に回復されました。

$ du -sh 元に戻す*
10M 元に戻す001
10M 元に戻す002
10M 元に戻す003
10M 元に戻す004
10M 元に戻す005
10M 元に戻す006
125M 元に戻す007
10M 元に戻す008
$ du -sh 元に戻す*
10M 元に戻す001
10M 元に戻す002
10M 元に戻す003
10M 元に戻す004
10M 元に戻す005
10M 元に戻す006
10M 元に戻す007
10M 元に戻す008