この記事は検索エンジンプロダクトを一緒に開発してた同窓会 Advent Calendar 2023の20日目の記事です。

はじめに

slow queryの調査と聞くと、slow query logの調査が一番に頭に浮かぶのではないでしょうか?

しかし、その隠れた前提としてインフラは問題ないということがあると思います。今回はインフラ視点から原因を調査していきます。

前提

Amazon RDS for MySQL 8.0.20(他のdbでも同じようなことができると思いますので、参考にしてください)

手っ取り早く解決するには?

slow queryになるSQLを実行したあとに、

SHOW WARNINGS;

をして、出てきたwarningを片っ端から調べて修正していく方法があります。

こちら解決までは速いですが、あくまでpatch的なやり方になるので、後の作業者が困る可能性が高くなります。設定の見直しをしたほうが後々を考えると安心です。

インフラ視点から調査する

mysqlのサーバーシステムの変数がいくつあるかご存知でしょうか?250個以上あります。InnoDBのパラメータを含めると、さらに増え500を超えていきます。
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
https://dev.mysql.com/doc/refman/8.0/ja/innodb-parameters.html

数が多いですよね、1つ1つの変数の意味を理解して設定できる人はどのくらいいるのでしょうか?versionがあがるたびに変数が増えたり減ったりするので、理解するのは難しいです。またdefault値も変わるので、versionがあがるたびに変数の設定を見直す必要があります。

途中参加したプロダクトだと、mysqlのサーバーシステムの変数が初期から変わっていなく、古のファイルとして受け継げれていることがままあります。最初に設定した人がきっとカリカリにチューニングしてくれたのでしょう。しかし、defaultの変数はversionがあがるたびに変わるので、最新のdefault値のほうが良いということはあります。

一番良いのは、あまり設定しないことです。

日本語対応や、監視用の設定、サーバーの設定など最低限必要な設定はあるのですが、それ以外はdefault値のままで良いと思います。
特にRDSの場合は、インスタンスサイズに合わせて変動する値もあったりするので(例: innodb_buffer_pool_size {DBInstanceClassMemory*3/4})

設定が終わったら
slow queryになるSQLを実行したあとに、

SHOW WARNINGS;

をして何も問題がないか確認しましょう。または--show-warningsをオプションにつけて起動してください。

メモリがそもそも足りなくて、swapが発生しているとかがあった場合は、メモリを増やすなどのインフラの対応が必要になります。

最後に

上記が終了したあとに、slow query logを調査をすると良いと思います。
以上、インフラ視点でslow queryを調査する方法でした。参考になれば幸いです。