Mysqlオンライン回収undo表空間実戦

6139 ワード
mysql undo truncate mysql 5.7
1 Mysql5.6
1.1関連パラメータ
MySQL 5.6パラメータinnodb_を追加undo_directory、innodb_undo_logsとinnodb_undo_tablespacesの3つのパラメータは,undo logをibdata 1から取り出して単独で格納することができる.
  • innodb_undo_directory:undo表領域を個別に格納ディレクトリを指定し、デフォルトは.(すなわちdatadir)は、相対パスまたは絶対パスを設定できます.このパラメータインスタンスは初期化後は直接変更できませんが、ライブラリを停止し、プロファイルを変更し、undo表領域ファイルを移動することで変更できます.デフォルトパラメータ:
    mysql> show variables like '%undo%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_undo_directory   | .     |
| innodb_undo_logs        | 128   |
| innodb_undo_tablespaces | 0     |
+-------------------------+-------+
  • innodb_undo_tablespaces:個別に格納されているundo表領域の数を指定します.たとえば、3に設定されている場合、undo表領域はundo 001、undo 002、undo 003で、各ファイルの初期サイズはデフォルトで10 Mです.このパラメータは、以下で説明するため、3以上に設定することを推奨します.このパラメータインスタンスの初期化後は変更不可
    • インスタンス初期化はinnodb_を変更することですundo_tablespaces: 
    mysql_install_db ...... --innodb_undo_tablespaces

$ ls
...
undo001  undo002  undo003
  • innodb_rollback_segments:デフォルト128.ロールバック・セグメントごとに1024個のオンライン・トランザクションを同時にサポートできます.これらのロールバックセグメントは、各undoテーブル空間に平均的に分布します.この変数は動的に調整できますが、物理的なロールバックセグメントは減少しません.使用するロールバックセグメントの数を制御します.

    • 1.2使用
    インスタンスを初期化する前にinnodb_を設定するだけですundo_tablespacesパラメータ(推奨値が3以上)では、undo logを個別のundoテーブルスペースに設定できます.undo logをより速いデバイスに配置する必要がある場合はinnodb_を設定できます.undo_Directoryパラメータですが、一般的にはそうしません.今SSDが非常に普及しているからです.innodb_undo_logsはデフォルトで128に変更できます.
    undo logはibdataの外に格納できます.しかし、この特性は依然として鶏の肋骨です.
  • まず、installインスタンスのときに独立したUndo tablespaceを指定する必要があります.installが完了した後は変更できません.
  • Undo tablepsaceのspace idは1から開始する必要があり、undo tablespaceを追加または削除することはできません.

    • 1.3大取引テスト
    mysql> create table test.tbl( id int primary key auto_increment, name varchar(200));
Query OK, 0 rows affected (0.03 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.tbl(name) values(repeat('1',00));
Query OK, 1 row affected (0.00 sec)

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

...

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 2097152 rows affected (24.84 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (7.90 sec)

    undologが膨らみ始めているのを観察!トランザクションcommit後もスペースが回収されていません.
    $ du -sh undo*
10M    undo001
69M    undo002
10M    undo003

    2 Mysql5.7
    5.7オンラインtruncate undo tablespaceを導入
    2.1関連パラメータ
    必要条件:
  • innodb_undo_tablespaces:少なくとも2つあり、1つはクリーンアップ時に別のパラメータインスタンスを使用することができ、このパラメータインスタンスは初期化後に
    • を変更することはできない.
  • innodb_rollback_segments:ロールバックセグメントの数は、システム表領域に割り当てられ、32個が一時表領域に保持されます.だからundoテーブルスペースを使いたいなら、この値は少なくとも33です.たとえば、2つのundoテーブルスペースを使用すると、この値は35になります.複数のundoテーブルスペースを設定すると、システムテーブルスペースのロールバックセグメントが非アクティブになります.

    • 起動パラメータ:
  • innodb_undo_log_truncate =on
  • innodb_max_undo_log_size:この値を超える表領域はtruncateとしてマークされ、動的パラメータのデフォルトは1 G
    • です.
  • innodb_purge_rseg_truncate_frequency:purge操作が何回呼び出された後にrollback segmentsを解放するかを指定します.undoテーブル空間のrollback segmentsが解放されると、undoテーブル空間はtruncateになります.このことから,このパラメータが小さいほどundoテーブル空間がtruncateを試みる頻度が高いことが分かる.

    • 2.2クリーンアッププロセス
  • undo表領域サイズがinnodb_max_undo_log_sizeを超えると、この表領域をクリーンアップする必要があるとマークされます.タグは循環して行われ、1つの表領域が繰り返しクリーンアップされないようにします.
  • タグ表領域内のロールバック・セグメントは非アクティブになり、実行中のトランザクションは実行を待機します.
  • purge
    • を開始する
  • undo表領域のすべてのロールバック・セグメントを解放した後、truncateを実行してundo表領域を初期サイズに切断し、初期サイズはinnodb_page_sizeは、デフォルト16 KBのサイズ対応表領域が10 MB
    • であることを決定する
  • ロールバック・セグメントを再アクティブ化し、新しいトランザクション
    • に割り当てる
    2.3性能提案
    truncate表領域でパフォーマンスに影響を及ぼさない最も簡単な方法は、元に戻す表領域の数を増やすことです.
    2.4大取引テスト
    8つのundoテーブルスペースを構成し、innodb_purge_rseg_truncate_frequency=10 
    mysqld --initialize ... --innodb_undo_tablespaces=8

    テストの開始
    mysql> show global variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | ON         |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 8          |
+--------------------------+------------+

mysql> select @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                     10 |
+----------------------------------------+

select @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                   10485760 |
+----------------------------+

mysql> create table test.tbl( id int primary key auto_increment, name varchar(200));
Query OK, 0 rows affected (0.03 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.tbl(name) values(repeat('1',00));
Query OK, 1 row affected (0.00 sec)

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

...

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 2097152 rows affected (24.84 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (7.90 sec)

    undo表空間状況、100 MB+に膨張後回収に成功
    $ du -sh undo*
10M    undo001
10M    undo002
10M    undo003
10M    undo004
10M    undo005
10M    undo006
125M   undo007
10M    undo008

$ du -sh undo*
10M    undo001
10M    undo002
10M    undo003
10M    undo004
10M    undo005
10M    undo006
10M    undo007
10M    undo008

    3 Reference
    https://dev.mysql.com/doc/ref...
    [BOJ]8980号:宅配便
    MySQLテーブル関連のいくつかの一般的な方法