Zenn
MIXI DEVELOPERS NOTE
🫗

MySQL 8で、テーブルキャッシュを無効にしてクエリ時間の検証をする方法（マサカリコメント募集）

gecko655
2024/11/01に公開
MySQL
Database
キャッシュ
MySQL 8
tech

はじめに

この記事ではマサカリコメントを募集しています。
良い方法をご存じの方はコメントください。

この記事内では、クエリ文内に [xxxx] のようなプレースホルダを使っています。
実際のクエリ文では、 [xxxx] の部分を適切な値に置き換えています。

やりたいこと

MySQL8 を利用しているサーバーアプリケーションにおいて、新機能開発によって既存のテーブルに対してこれまで投げたことない DB クエリを投げるような実装を行ないたいため、そのクエリがスロークエリにならないかどうかをあらかじめ検証することにしました。

本番の同テーブルは億レベルのレコード数があり、このデータを本番からダンプしてきて、別の検証用 DB にデータをロードして、検証したいクエリを投げてみました。

しかし、本番 DB と開発 DB で同じクエリを投げても、クエリ実行にかかる時間が全然違い、本番 DB では時間がかかるのに開発 DB ではすぐに結果が返ってくることがわかりました。

本番 DB では、1回目のクエリ実行時には大幅に時間がかかり、全く同じクエリを2回実行すると2回目はすぐに結果返ってきます。

> select [カラム] from [テーブル名] where user_id = [ユーザーID] and [条件];

# 結果略
1 row in set (48.57 sec)

select [カラム] from [テーブル名] where user_id = [ユーザーID] and [条件];

# 結果略
1 row in set (0.06 sec)

また、クエリする [ユーザーID] 部分を他のユーザーIDに変更すると、ユーザーID ごとに最初の1回目のクエリだけ時間がかかります。

一方、開発 DB では、1回目のクエリ実行からすぐに結果が返ってきます。

> select [カラム] from [テーブル名] where user_id = [ユーザーID] and [条件];

# 結果略
1 row in set (0.06 sec)

どう見ても何らかのキャッシュが効いている感じがします。
これでは本番 DB でのクエリ時間を1度も開発 DB で再現できず、検証ができません。

本番で時間がかかっている原因を EXPLAIN ANALYZE で見てみると、 index lookup に全体の99%くらいの時間がかかっているようでした。

> explain analyze select [カラム] from [テーブル名] where user_id = [ユーザーID] and [条件];
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                      |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: [カラム名]  (cost=30476.62 rows=32078) (actual time=48564.026..48564.027 rows=1 loops=1)
    -> Filter: ([条件] )  (cost=27268.81 rows=32078) (actual time=48162.512..48563.983 rows=486 loops=1)
        -> Index lookup on [テーブル名] using [INDEX名] (user_id=[ユーザーID])  (cost=27268.81 rows=96244) (actual time=0.384..48554.778 rows=56093 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (48.57 sec)
  • ↑ Index lookup on [テーブル名] using [INDEX名] の部分で 48秒かかっている。

同じ explain analyze を開発 DB で実行すると、 index lookup は一瞬で完了していることがわかります。

> explain analyze select [カラム] from [テーブル名] where user_id = [ユーザーID] and [条件];
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                      |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: [カラム名]  (cost=49865.08 rows=52485) (actual time=325.210..325.210 rows=1 loops=1)
    -> Filter: ([条件])  (cost=44616.54 rows=52485) (actual time=322.779..325.184 rows=364 loops=1)
        -> Index lookup on [テーブル名] using [INDEX名] (user_id=[ユーザーID])  (cost=44616.54 rows=157472) (actual time=0.359..313.503 rows=87191 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.33 sec)
  • ↑ Index lookup on [テーブル名] using [INDEX名] にかかった時間は313ms
  • Index lookup で発生した cost, 得られた rows は本番 DB のクエリよりも多い

どうにかして開発 DB で本番 DB と同様なクエリ時間のクエリが実行できるようにする方法を探すことにしました。

やってみてダメだったこと

MySQL 8 では MySQL5.x と違いクエリキャッシュが廃止されています。

したがって、 SQL_NO_CACHE オプションをつけてクエリキャッシュを使わないよう指定しても、実行時間は何も変わりませんでした。

また、以下のテーブルやインデックスのキャッシュをメモリから削除してくれそうな方法は、やってみたもののクエリが一瞬で返ってくる挙動は変わりませんでした。

  • クエリ発行前に FLUSH TABLES を実行する
  • 同一定義の INDEX を別名で再作成して利用する
    • ALTER TABLE [テーブル名] ADD INDEX [INDEX名]_test([元のINDEXと同じカラムリスト]) で INDEX を作ったうえで、 SELECT [カラム名] FROM [テーブル名] USE INDEX ([INDEX名]_test) ~~ でクエリする

やってみてうまくいったこと

なんかどうやってもキャッシュを無効化できないな…… となったので、最終手段として mysql プロセスの使えるメモリ量を減らしてみることにします。
これは MySQL の innodb_buffer_pool_size設定変数で変更できます。

  • innodb_buffer_pool_size はテーブルキャッシュ以外のデータも格納される領域 らしい ので、innodb_buffer_pool_size を0に設定することはできず、テーブルキャッシュを厳密には無効化できていないのですが、大きいテーブルの SELECT においてはテーブルキャッシュが全然使えなくなるはずです。
  • Amazon Aurora MySQL においては、パラメータグループで設定変更し、DB インスタンスを再起動することで設定を反映させることができます。

Before:

> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                         276.750000000000 |
+------------------------------------------+

After:
Aurora が指定してきた innodb_buffer_pool_size の下限値を設定することにしました。

> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           0.312500000000 |
+------------------------------------------+
  • 検証 DB では、本番と同じ db.r6g.12xlarge インスタンスタイプ (384GB)を使っており、 innodb_buffer_pool_size 変数の設定変更後はメモリが大幅に余りますが、対照実験のためインスタンスタイプは変えずに検証しています。

これで、開発 DB では常にテーブルキャッシュを使わない状態でクエリを叩けるようになり、実際にクエリを叩くと本番で1回目に投げたときと同じくらい時間がかかるようになりました。
また、同じクエリを何回投げても、本番で1回目に投げたときと同じくらい時間がかかるようになりました。

> select [カラム] from [テーブル名] where user_id = [ユーザーID] and [条件];

# 結果略
1 row in set (54.06 sec)
  
> select [カラム] from [テーブル名] where user_id = [ユーザーID] and [条件];

# 結果略
1 row in set (52.08 sec)

その後、検証 DB に別の INDEX ( INDEX_IMPROVED) を追加してクエリしたところ、こちらは一瞬で返ってきました。 この結果を以って、 INDEX_IMPROVED を使うように DB テーブル定義と実装を修正すれば、クエリの実行時間を改善できたと言って良さそうです。

> ALTER TABLE [テーブル名] ADD INDEX INDEX_IMPROVED([カラム名2])
> select [カラム] from [テーブル名] USE INDEX(INDEX_IMPROVED) where user_id = [ユーザーID] and [条件];

# 結果略
1 row in set (0.06 sec)
  • innodb_buffer_pool_size を減らすことで、バッファープールを使うテーブルキャッシュ以外の機能に影響が出る気もしますが、 innodb_buffer_pool_size を減らした状態でクエリの実行時間の改善を確認できたので、今回実施する必要があった検証はできたことにします。

その他、やってないけどうまくいくかもしれないこと

今回の検証では本番サービスで使用している MySQL8 互換 の AWS Aurora インスタンス(Aurora MySQL version 3)を使用しました。
Aurora は、インスタンス再起動によってテーブルキャッシュ（ページキャッシュ）が消失することがないため、再起動によってスロークエリを再現させることはできません。

Aurora では、各 DB インスタンスのページキャッシュはデータベースとは別のプロセスで管理されるため、ページキャッシュはデータベースとは無関係に存続できます。(ページキャッシュは Aurora MySQL では InnoDB バッファプール、Aurora PostgreSQL ではバッファキャッシュとも呼ばれます。)
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.Reliability.html

すなわち、 Aurora MySQL 以外の MySQL インスタンスを用意して同様の検証を行うことで、スロークエリの再現を確認することができたのかもしれません。（未確認）

  • 自前で "十分なスペックを積み" "適切なパラメータを設定した" MySQL インスタンスを用意するのは面倒ですし、 Aurora MySQL との設定差で別の問題を引き起こしかねないので、このような検証はしたくないですね…

まとめ

  • MySQL8 のテーブルキャッシュを消す・一時的に無効化するエレガントな方法は、私の調べた限りでは見つかりませんでした。
  • innodb_buffer_pool_size を小さくしてメモリを使わせないようにすることで、無理やりクエリ時間の検証をすることはできました。
    • （社内で飼っている文章生成AI bot Chat-M にこの方法を教えてもらいました。いつもありがとう Chat-M... 🤖 ）
  • もっといい方法を誰か知りませんか？
    • 色んな方法で "disable MySQL table cache" などで検索してみたものの、前述の "MySQL5.7 以前の Query cache を切る方法" しか見つかりませんでした。
GitHubで編集を提案
MIXI DEVELOPERS NOTE
MIXI DEVELOPERS NOTE

株式会社MIXIのZenn版エンジニアブログです。実務で得た知見や個人的に調べたことなど思い思いに発信します。

Discussion