はじめに
数日前、ストアドプロシージャを使って10Wのデータを書き込むというMySQLのテスト実験をしていたところ、1時間以上かかることがわかりました。以前、InnoDBの原理について学び、それを分析してチューニングできると思いました。
環境
- Docker
- Linux
- 機械式ハードドライブ
- MySQL 5.7
- SQL
--
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
-- 挿入手順
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
-- 実行結果
mysql> call idata();
Query OK, 1 row affected (1 hour 12 min 46.07 sec)
分析
データが挿入されるとInnoDBは何をしますか？
データがデータベースに書き込まれると、binlogとredologが書き込まれます。
- データを改行します；
- redologへの書き込み
- ビンログへの書き込み
- commit(redolog
テーブルには一意なセカンダリインデックスがないため、書き込み時にChange Bufferを使用することができます。そして、パフォーマンスのボトルネックはログの書き込みに集中します。
ログ書き込みの設定
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
2 rows in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)
パラメータからわかるように、どちらのロギング設定も最も安全で、IOが最も遅くなります。
innodb_flush_log_at_trx_commit 値を1とすると、ログバッファはログファイルに書き込まれ、トランザクションがコミットする度にディスクにフラッシュされます。これはデフォルト値でもあります。これは最も安全な設定ですが、トランザクションごとにディスクI/Oが必要となるため、最も遅い設定でもあります。
sync_binlogは、自動コミットがオンの場合はステートメント毎に1回、それ以外の場合はトランザクション毎に1回、バイナリログを書き込みます。デフォルト値は0で、積極的な同期は行わず、オペレーティングシステム自身がファイルの内容をディスクにフラッシュすることに依存します。1に設定するのが最も安全で、各ステートメントやトランザクションの後に1回バイナリログを同期させ、クラッシュした場合でも最大で1つのステートメントやトランザクションを失います。
チューニング実験1
- 上記の2つの設定をオンにして、0に設定します；
- 新しいテーブルt2を作成します。
- 新しい手順
- ストアドプロシージャの実行
- データの監視
実験的ステートメント
set global sync_binlog=0;
set globle innodb_flush_log_at_trx_commit=0;
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
-- 挿入手順
delimiter ;;
create procedure idata3()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata3();
実験結果
mysql> call idata3();
Query OK, 1 row affected (8.68 sec)
** わずか 8 秒で 10w のデータを書き込みます。
チューニング実験2
原理的に分析すると、上記は実際にはパフォーマンス向上のためにデータ・セキュ リティを犠牲にしていることになります。自動コミットでは、idata3() プロシージャは挿入されるたびにコミットします。これは多くのディスクIOを誘発するもので、毎回、REDOログとbinlogの2回ディスクに書き込む必要があるため、書き込み性能は常に悪くなります。
そのため、コンスタント・コミット・トランザクションは、データを大量に書き込む際の障害操作となります。ストアドプロシージャを改良して、すべてのコミットを単一のコミットにまとめるようにしましょう。
- idata4プロシージャを書いてください。
- 空のテーブルt2
- idata4() の実行
- 実行結果の観察
CREATE DEFINER=`root`@`%` PROCEDURE `idata4`()
begin
declare i int;
START TRANSACTION; -- トランザクションを有効にする
set i=1;
while(i<=100000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
COMMIT; -- コミットトランザクション
end
実験結果
set global sync_binlog=1;
set globle innodb_flush_log_at_trx_commit=1;
mysql> call idata4();
Query OK, 0 rows affected (1.21 sec) // 異なるホストでのテスト
実装は非常に優れています。しかし、ビジネスの観点からすると、それはすでに複数のトランザクションを1つのトランザクションにまとめたものです。
まとめ
InnoDBのChange Bufferは、TPSを提供するために非常に重要な設計です。しかし、Change Bufferの使用中にディスクIOを開始するための他のメカニズムに注意を払わなければ、Change Bufferを最大限に活用することは困難です。
頻繁なトランザクションに加えて、ユニークなセカンダリインデックスも変更バッファを無意味にする可能性があります。
