MySQL の運用が楽になる percona-toolkit の便利機能を試してみた
メディア統括本部 サービスリライアビリティグループ（SRG）インターン生の鈴木友也（@oshiborii）です。
#SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
本記事は、MySQL の運用・監視を補助するツール群である percona-toolkit のうち、筆者が特に便利だと考えるツールを紹介します。本記事により、日々 MySQL の運用・監視を行う読者の方々の一助になれば幸いです。
pt-upgrade
pt-upgrade はバージョンの異なる MySQL サーバーに対して同じクエリを実行し、実行結果、実行速度、エラーの有無などの差分を記録してくれるツールです。
MySQL のメジャーバージョンを上げる場合、これまで実行できていたクエリが実行できなくなったり、オプティマイザの挙動が変わることにより特定のクエリの応答が急激に遅くなったりすることは皆さんご存知だと思います。このツールを使うことで、遅くなるクエリはあるか、あるとしたらどのようなクエリか、などの情報を事前に把握できます。
基本的な使い方は以下の通りです。
※ 以下、コマンド例の一部は公式サイトから抜粋しています
最も単純なケースでは、以下のような書き方になります。このツールは二つの MySQL サーバーの実行結果を比較するので、基本的にホストは二つ指定する必要があります。ただし、既に実行済みの結果との比較もサポートしており、その場合は指定するホストは一つです。
実行結果は以下のようになります。
※ 中の値はダミーです
Query class はフィンガープリントごとに割り当てられるIDのようなもので、フィンガープリントは SQL 文のうち、具体的な値を除いた部分のことです。以下の例だと、 がフィンガープリントになります。この例だと Query Class は 1 つですが、通常はいくつかの Query Class が出てくると思います。この結果を見て、必要ならインデックスなどをチューニングしてアップグレードに備えることができます。
基本的な使い方は以上ですが、以下のような注意点もあります。
- 本番環境では利用できない（正確にはできるが、するべきでない）
- 更新系のクエリは（基本的に）実行できない
1 に関しては説明不要だと思います。このツールを使いたい場合、ステージング環境などで本番環境と可能な限り同じデータを用意する必要があります。最近だと、AWS の Amazon Aurora にはクラスタのクローン機能があったりするため、そういった機能を使うのも一つの手です。2 に関してもほとんど同様で、更新系のクエリは結果を正確に比較することが難しいため、基本的には実行できません。ただし、 オプションをつければ更新系のクエリも実行できます。
pt-fingerprint
pt-fingerprint はクエリログからフィンガープリントを抽出するツールです。このツールを利用することで、スロークエリログにどのようなクエリが頻繁に出現するかの確認などが可能になります。
pt-fingerprint の基本的な使い方は以下の通りです。
最も単純なケースでは、以下のようになります。シンプルですね。
実行結果は以下のようになります。
ただ、このツールは単に SQL 文をフィンガープリントに一対一に変換するだけなので、上記のように重複したフィンガープリントを出力してしまいます。より便利に使うためには少し工夫が必要です。例えば、 スロークエリログに頻出のフィンガープリントを出現回数順にソートして表示したい、という場合は以下のようなコマンドになります。
これにより、以下のような実行結果が得られます。
ただ、実は同様のことは mysqldumpslow を使うことでも実現できます。
pt-fingerprint の良さはフィンガープリントを取得するというシンプルな機能だけを提供している点だと思うので、他のツールと組み合わせるときに便利だと思います。単にスロークエリの分析をしたい場合は mysqldumpslow の方が適していることが多いでしょう。
pt-query-digest
pt-query-digest も比較的よく使われる便利ツールだと思います。このツールはスロークエリログの統計情報をいい感じにまとめて出力してくれるツールです。最も遅いクエリの実行時間を調べたり、スロークエリが集中している実行時間の幅を把握したりに使えます。
基本的な使い方は以下の通りです。
最も簡単なケースではスロークエリログのファイルを指定するだけです。
実行結果は以下のようになります。
実行結果の最初の方には と書かれており、スロークエリ全体の統計情報が書かれています。 や はその名の通り合計の実行時間と最小の実行時間です。他の Attribute もどれもその名の通りで、非常に読みやすい結果になっています。説明が必要そうなところで言うと、 はスロークエリから返却された行数で、 は行を取得するために調べた行数です。上手くインデックスが効いていない場合は の結果が大きくなりがちです。今回はあえて全件取得のクエリを流しているので、どちらの値も大きいですね。
また、実行時間の分布も確認できます。今回だと 1s の所に完全に集中しており、この結果は上記の が 1s であり が 2s である結果と整合します。
全体結果に続いて、各クエリごとの統計情報も出力されています。
内容はほとんど変わりません。
pt-duplicate-key-checker
pt-duplicatekey-checker は重複あるいは冗長なインデックスを検出するツールです。具体的には、以下のようなインデックスを検出してくれます。
- 指定されるカラム名と順序が完全に一致する
- 指定されるカラム名と順序の左側が部分一致する
例えば、以下のようなテーブルを考えます。
このテーブルに、以下のようにインデックスを貼ったとします。
この場合、idx1 と idx2 の左側はカラム名とその順序が一致するため、冗長と判断され検出されます。一方で、以下のようにカラムの指定順序が異なる場合には冗長とは判断されません。複合インデックスの場合、カラムの指定順序により用途は大きく変わってしまうため、このような挙動は正しいと言えます。
idx1 と idx2 が完全に重複していた場合、出力は以下のようになります。出力には重複しているインデックスを DROP する ALTER 文が書かれており、どのインデックスが重複しているかの情報はコメントになっています。そのため、この出力自体を SQL ファイルとして実行可能です。
※ 出力に一部改変を加えているため、実際の出力はこれとは異なる可能性があります
pt-index-usage
pt-index-usage は未使用のインデックスを検出してくれるツールです。pt-index-usage は対象の MySQL データベース内のインデックスを全て収集し、それを入力として受け取るクエリログを EXPLAIN した時に出力されるインデックス情報と突きあわせることで、未使用のインデックスを検出します。
基本的な使い方は以下のようになります。
ローカルの MySQL に対して root ユーザーで実行する場合、以下のようになります。
このツールを利用することで、不要なインデックスを発見できます。不要なインデックスの存在はオプティマイザの間違った挙動を誘発するため、定期的にこのツールを実行して必要なインデックスのみを残すなどの運用ができると嬉しそうです。
実行結果は以下のようになります。pt-duplicate-key-checker の時と同様に出力結果には使われていないインデックスを DROP する ALTER 文が書かれています。
注意点として、スロークエリだけを見て使っていないインデックスを消してしまうと、インデックスを適切に使えているが故にスロークエリに出てこないクエリが遅くなってしまいます。実際にインデックスの削除を検討する場合は、 の値を小さくするか、 を ON にして一般クエリログを取ってきて、それをこのツールに流すなどの方法で、とにかく本番環境で流れているほぼ全てのクエリを取ってきてあげる必要があります。また、MySQL 8.0 を使っている場合、インデックスを削除してしまう前に、INVISIBLE 機能の利用も検討したい所です。
終わりに
SRG では一緒に働く仲間を募集しています。
ご興味ありましたらぜひこちらからご連絡ください。
SRG では最近出たホットなIT技術や書籍などについてワイワイ雑談するポッドキャストを運営しています。ぜひ、作業のお供に聞いていただければ幸いです。
