MySQLバージョンアップによるInnoDB性能劣化可能性事件簿

一般論ですが、どんな基盤ソフトでもCPUスケールを上げようとすれば、何らかの排他制御を細かく行うことになるのでCPUのパイプライン処理にブレーキをかけるアトミックな処理が増えて、バージョンが上がるとある程度はシングルスレッドの処理は重くなっていきます。前エントリのような言語の高度化により遅くなる事情もあります。（中には、Redisのように並列を捨てて排他処理を完全排除する潔い逆振りプロダクトもありますが。）

とはいえ、「これは（条件付きとはいえ）急に遅くなりすぎだろ！」と私も思うバージョン（回避策はある＆一開発者の一存ではどうにもできない）があるので遡って何点か挙げて注意喚起したいと思います。

これらはある程度限られた条件で発生するので世間では怪奇現象扱いされている可能性もあります。

何故こんなことになるのかというと、基盤となるmysqld側の変更に上手くついていけなくなってるか、性能上メリットデメリットが両方ある変更で、HDDにはデメリットが大きいけどSSDではそうでもないので敢行してしまったものとか、あまりにハイスペックなハードでしか性能チェックをしていなくて…（そういう過去の過ちを可能な限り直すのが私の仕事ですが直せない箇所もある…）

というわけで、今後も直らないかも知れない地雷ポイントを利便性のために晒してしまおうという主旨です。更新が複数バージョンに跨ってると上手くリリースノートに反映されないんですよ。最初(1)の件で最近議論になりましたが、ほらブログに書けばいいじゃないか、みたいなこと言われたので…

…全部書いてやります。(ぇ

影響度が高い新しい方から挙げます。古い方は皆さんもう忘れたか諦めたかですよね？…よね？ また、新しいものほど根が浅いので近い将来直ってる可能性はあります。将来のバージョン使う時はちゃんと確認しましょう。

(1) fallocate() log sync事件(Linux版のみ) (8.0.22〜) 回避策は8.0.23〜 ※近いうち直ると思います。

<条件>

これは、ストレージが遅いとデメリットの方が大きいかも知れない変更ですが、8.0.23だと回避策があり、それを使うと逆に速くなる（！）のですが、面倒で使わないと遅いまま。という類のものです。条件にヒットすると、file_per_tableでINSERTが沢山あって単調増加の処理が重くなります。データ取り込みとか。

まぁ、それでも前々エントリのlog_sys改善で相殺して気づかない人も多いかもですが。

そういう事情なので、ユーザーの手間を考えない開発陣はこのまま放置する方針みたい…

<仕組>

fallocate()してファイル拡張の処理を軽くする（メリットも大きい）のですが、リカバリを考えるとファイル拡張のログをトランザクションログに残さないと危険で、トランザクションログはWALの原則があるので、そのログがflushされるの待ってfallocate()します。fallocate利用自体はON/OFFできるのですが、ログの整合性を考えファイル拡張のログは常に出力される。つまりファイル拡張時は必ずlog flush syncが起きます。

というわけで、すべてのレコードINSERTにlog flush syncする可能性が発生します。(commitよりも細かい単位。innodb_flush_log_at_trx_commit設定も現状関係ない。)

現状、その可能性を最小化するのは拡張の頻度を減らすことのみです。8.0.23で従来NDBのものだったテーブル単位のAUTOEXTEND_SIZEパラメータを受け付けるようになったようで、それを大きくして設定して頻度を減らせば逆に速くなる可能性のほうが大きいです。

※単語の意味は違うけど、innodb_flush_log_at_trx_commit に従うようにすると混乱は少なくなるとも思うのだが。現状無視されます。そうだとしても、回避策のほうが結果速くなるから性能観点では下記回避策を推奨。

<回避策>

8.0.23以降で、ディスクスペースに余裕があって（INSERTが多い処理なのだから普通あるだろ）、テーブルがある程度正規化されていて常識的な個数の場合は、テーブル１個あたりファイルサイズが最低64Mになっても困らないですよね？

何も考えずに全てのfile_per_tableの通常InnoDBテーブルを最大値

ALTER TABLE xxx AUTOEXTEND_SIZE=64M;

してしまうこと(TABLESPACE使ってる場合はそっちも？)と、既存の通常InnoDBテーブルのCREATE TABLE文に（一時テーブルはlog発生しないので関係ない）、下位互換性も考慮して、

/*!80023 AUTOEXTEND_SIZE=64M */

を足しておきましょう。

というのは如何でしょうか？バージョン違いとかレプリケーションの整合性とかは自分で考えてください。

(※追記： log flush sync 自体は不要かも。ファイルサイズは増加しかしないし、既に増加してたら無視すればいいので。中身に関するログとの前後関係さえちゃんとしてれば。）

(※追記２: 何か意図があって追加されたものなのですが、他で代用可能で、且つ現状何の役にも立ってないので、近いうち戻ると思います。回避策はチューニングとして依然有効です。)

どんどん遡ります。

(2) 新 Doublewrite Buffer 事件 (8.0.20〜)

<条件>

素、若しくはRAID1、のHDDで、Doublewrite Bufferが有効な場合に8.0.20以降でデメリットが勝ると遅くなったかも知れません。

<仕組>

並列性や、SSDなどの特性も考慮してDoublewrite周りが完全改修されました。が、デフォルトが細かい単位で書き出しを区切るようになった(SSDなどではそのほうが速くて安全)ので、シークが遅い素HDDなどのデバイスでは遅くなる場合があります。

<回避策>

innodb_doublewrite_batch_size (8.0.20〜) を大きく設定します。従来値相当は 120 ですが調整してもいいかも。遅くなってない人は設定しないほうが良いかも知れません。

 

以降は、5.7に端を発するものです。知らないほうがいいかも？

(3) Adaptive Hash Index 事件 (5.7.8〜) ※これ以前の5.7.1〜5.7.7はもっと酷い。GA以前だから割愛。

<条件>

CHAR/VARCHAR などのcharactersetベースのキー値の二次索引を利用している場合に Adaptive Hash Index の効きが5.6よりも悪くて、恩恵を受けていた一部のバッチ処理等が遅くなるかも知れない。今更ですが。

<仕組>

5.7以降では、文字列データはUnicodeベースになったために、charactersetベースのデータの比較などはmysqld側を呼び出して行うようになりました。(乱暴な説明だが、大体合ってる。)

本来、Adaptive Hash Index 内部ではバイト単位比較を行っており、その中でも Adaptive Hash Index を利用するかどうかの判断が細かく行われていたのが、CHAR/VARCHAR等の文字列データキー値では部分一致での判断ができなくなってこの部分が飛ばされてしまいます。

※（補足）Adaptive Hash Index は全ての二次索引レコードへのショートカットを提供する役割がありますが、構造上並列性が低く、OLTPな処理ではメリットが薄く敬遠されます。しかし、そもそも並列性の低いバッチ処理（二次索引があるテーブル同士の"INSERT SELECT"文とか）では結構加速がかかる場合があるみたいです。

<回避策>

collationとか関係ない英数字のコードがキー値ならば、BINARY/VARBINARYに変えてしまえば5.6と同様にAdaptive Hash Indexが利用されます。

 

(4) PSI_MEMORY 疑惑 (5.7.1〜)

<条件>

5.7以降、PERFORMANCE_SCHEMA でメモリ確保開放の統計も取るようになりました。これが重くて、performance_schema=OFF にしてる人も多いと思いますが…

…実は、OFFにしてもまだ重いのです。

この件について8.0では、メモリの確保開放が整理されてかなり頻度が減っているので(5.7よりは)影響はかなり小さいと思います。さらに新しい8.0では(8.0.23？)もっと改善してるかもしれません。

現状ほぼ、5.7固有の問題かも知れないです。

※これがある御蔭で、5.7よりも8.0の方が遅い部分は目立たなくなってるのかも……

……いや、聞かなかったことにしてください…

<仕組>

performance_schemaがONかOFFかチェックするコードが重いんだと思います。（!!!）

<回避策>

ビルド時に "-DDISABLE_PSI_MEMORY=ON" とすればメモリ確保開放周りのそのコードもカットされて軽くなります。まぁ、当然performance_schema=ONにしてもメモリ関連は見られなくなりますが。

※8.0以降ではメモリ確保周りは大きく変わってる（バリエーションが増えてる）ので"-DDISABLE_PSI_MEMORY=ON"しないほうが安全かも？(怪情報)

え、ビルドできない？

大量に購入頂いている大口のお客様なら、かかりつけの者がいると思うので、「5.7の"-DDISABLE_PSI_MEMORY=ON"のバイナリが欲しい」と言えば作ってもらえるかも？（いや、作ってもらえないかも。無責任には断定できないですが。）

矢張り、いずれにせよ、一度ビルドして速くなるか試してからをお勧めしておきます。いやお願いします。やたら反響が大きいと怒られるかもなので…

 

以上です

というわけで、私の力が及ばないモノを挙げてみました。他にも細かいモヤモヤポイントもありますが、書かなかったことは諦めてないもので危険な劣化ではないもの、又は調査中ということで、またいずれ。