本エントリはMySQL Advent Calendar 2020 の13日目です。
今日はちょっとだけMySQL8.0にアップグレードしとくと良いよ、というネタです。
TRUNCATEよりもDROPとCREATEのほうが性能が良いか？
はい、MySQL5.7以前まではDROPと&CREATE のほうがTRUNCATE TABLEよりも高速なんです。
なぜならこんなバグが5.7以前にはあるからです。
要約すると、MySQL 5.7以前のInnoDBでは、TRUNCATE TABLEを実行した際、ファイルを再作成する以外にも、バッファプールのスキャンが発生するというものです。
ただし、適応型ハッシュインデックスがOFFの場合には、スキャンは生じません。
このバグ、MySQL8.0で改善されました。
どういうときに困るのか？
大きなバッファプールを持っていて、TRUNCATE対象のテーブル情報がそこに乗っているとき。
テーブルの再作成＋バッファープールのスキャンが走るので、テーブルがでかければでかいほど遅くなるってわけです。
この令和の時代、MySQL Serverでさすがにinnodb_buffer_pool_size=128MBのデフォルトサイズでいつまでも運用してる会社さんはそうそういないでしょう。
皆さん最低100GBのメモリくらいは積んでますよね？
ですから、皆さん全員困ってると思ってですね・・・おや、誰か来たようだ。
対応方法
- TRUNCATE TABLEをやめて、DROP TABLE＋CREATE TABLEにする。
- innodb_adaptive_hash_indexをOFFにして、適応型ハッシュインデックスを無効にする。
- MySQL8.0にアップグレードする。
バッファプールを小さくすることは他の処理への影響もあるのでやらないこと。（もちろん、OMMKillerが発生するような設定の場合は別）
innodb_adaptive_hash_indexをOFFにするのは動的に行えます。
なんでMySQL8.0では直ったの？
MySQL 8.0では、内部的にTRUNCATEテーブルがDROPとCREATEを連続して行うのと等価の操作に置き換えられ、バッファプールを読まなくなったからです。
https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html
In MySQL 5.7 and earlier, on a system with a large buffer pool and innodb_adaptive_hash_index enabled, a TRUNCATE TABLE operation could cause a temporary drop in system performance due to an LRU scan that occurred when removing the table's adaptive hash index entries (Bug #68184). The remapping of TRUNCATE TABLE to DROP TABLE and CREATE TABLE in MySQL 8.0 avoids the problematic LRU scan.
ところで適応型ハッシュインデックス（AHI）って何？
一部のインデックス値のアクセス頻度が高い場合、バッファプール上にハッシュテーブルを動的に作成し、主キーの検索を効率化させるInnoDBエンジンの機能です。
更には、セカンダリインデックスによる検索はその過程で主キーによる等価比較が行われますが、AHIは主キーの検索を効率化させているので、セカンダリインデックスによる検索が多いワークロードだと検索効率が上がる可能性も大いにあります。
一方で、これが複数の並列結合などの負荷が高いワークロードではロックが競合の原因となったり、LIKE 演算子と % ワイルドカードを使用したクエリは恩恵が得られないといったことがあり、5.6以降のアーキテクチャ改善によりこれがONになっているメリットは過去のバージョンと比べると徐々に減っています。
とはいえ、デフォルトはONであり、ONとなっていることで高速化している可能性もあるので、知らずに使っていた人もOFFにする場合は事前に検証が必要な機能です。
（これ、地味にパフォーマンスに影響する意外と重要な機能ですよね）
詳しくはドキュメントをご確認下さい。
AHIでおまけ
Perconaのピーター・ツァイツェフさんがAHI関連で面白い記事を2本載せているので参考に載せておきます。
一つは古いですが、MySQL5.7.11の段階で色々なデータで検証したものです。 www.percona.com データが非常に多くなった場合、sysbenchで検証した結果としては効率が落ちてはいるものの、MySQLのドキュメントにある通り、ワークロードに応じて使える場合もあるのでちゃんと確認してON/OFFしようねという内容になっています。
もう一つはAuroraでReaderノードでは無効なAHIをWriterノードで有効にしたら同じクエリがWriterでは高速だったという話です。 www.percona.com
今現在、Auroraでどのような設定になっているのか知らないのですが、innodb_adaptive_hash_indexがデフォルトでは無効だったらしく、かつReaderノードではAHIを有効化できなかったが、Writerノードでは変更できたので変えてみたら高速になったので注意してねとのこと。
(そういえば、AuroraがMySQL8.0をComing soon的な話を聞きましたが、クエリキャッシュはどうするんだろう？)
明日は？
明日はupdraftさんの
今日は、DBで日付にはDATE型？VARCHAR型どっちの日。 - 今日はなにの日。
です。
お楽しみに！
