MySQLを止めずにレプリケーションをブーストする小技

先日は、MySQLユーザ会会 2020年7月に参加しました。

今回はWebや雑誌で連載の著者の方々が、執筆に至った経緯や、執筆時に心がけていることを語る回でした。 @kk2170 さんが「過去の自分に向けて書く」とおっしゃっていたのが、（そういう視点は自分の中になかったので）すごく響きました。

mysql.connpass.com

一刻も早くレプリケーション遅延を取り戻したい！そんな場合に使える小技を紹介します。

レプリケーションを止めずに有効化・無効化できるのみを取り上げています。元に戻すのにレプリケーションを止める必要性のある方法だと、またそこでレプリケーション遅延が発生しますからね…

CPU の governor を performance に変更する

LinuxにはCPUクロックの調整機能があり、CPU governor が ondemand 設定の場合、負荷に応じて、CPUのクロック数が上下します。クロック数を調整することで、消費電力を最適化します。

特に、(マルチスレッドスレーブを使わずに) シングルスレッドでレプリケーションを行っている場合、負荷が低いとみなされ、クロック数が十分上がらないケースがあります。シングルスレッドのレプリケーションではCPU 1コアしか使われないためです。

performance に変更することで、常に最高クロック状態を維持することができます。サーバの消費電力が上がってしまうのが注意点です。

# for CPU in /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor
> do
> echo -n performance > $CPU
> done

Durability を犠牲にする

InnoDB ログへの書き込み頻度を落とす

InnoDB ログ（REDOログ）への書き込み頻度を下げることで、DISK IOを少なくしパフォーマンスをブーストします。

mysql> SET GLOBAL innodb_flush_log_at_trx_commit=0;

mysql> SHOW GLOBAL VARIABLES like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
1 row in set (0.01 sec)

デフォルトは、innodb_flush_log_at_trx_commit=1であり、コミットする度に、InnoDB ログファイルへ書き込みが行われます。innodb_flush_log_at_trx_commit=0 では１秒おきに書き込みます。

InnoDB ログは mysqld がクラッシュしたり、サーバがダウンした際にデータが失われないようにする役割を担っています。innodb_flush_log_at_trx_commit=0 にすると障害時にデータが失われるリスクがあります。もし運悪く障害が発生してしまったら、レプリカを作り直すことになるでしょう。遅延が収まったら、有効に戻しましょう。

同様に、sync_binlog を無効化（0 を設定する）のも効果があります。

InnoDB ログそのものをOFFにする（MySQL 8.0.21 の新機能）

MySQL 8.0.21 からは InnoDB ログそのものを無効化することが出来るようになりました。innodb_flush_log_at_trx_commit では書き込み頻度を落とすことしか出来なかったのが、InnoDBログの存在自体を無効化できるようになりました。

ただし、innodb_flush_log_at_trx_commit=0 同様に、障害時にデータは失われてしまいます。

mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG ;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+

パフォーマンス比較

以下のグラフは、デフォルトの状態と、{innodb_flush_log_at_trx_commit,sync_binlog} = {0,0} と、InnoDB ログを無効化したパターンのレプリケーションのパフォーマンスを簡単に計測した結果です*1。「150万件のINSERTをどれぐらいの秒数でレプリカが食いきったか」を計測しています。

f:id:mita2db:20200718142154p:plain

InnoDB ログを無効化した場合が圧倒的に速い！！！

まとめ

レプリ遅延で困ったら、とりあえず、InnoDB ログをOFFれ

*1:マルチスレッドスレーブを有効にし、workerを4で計測

mita2 database life

主にMySQLに関するメモです