MySQLで非インデックス系ソートのためのパフォーマンスチューニング
1. ファイルソートについて
DBにおけるソート操作は大きく分けて2つ、インデックスソート（Index Scan）とファイルソート（Filesort） があります。通常、ソートが必要なクエリに対してはB-Treeインデックスを適切に付与することで、ソート処理そのものを省略し、クエリを高速化できます。
一方で、インデックスの付与でパフォーマンス改善されないクエリ操作としてORDER BYやGROUP BYなどがあります。背景として、複合インデックスであったり、WHERE時にソート結果がインデックスに引き継がれないなど様々な要因があり、このようなインデックスを利用できない・しない操作をファイルソートと呼びます。
1.2 ファイルソートの仕組み
ファイルソートの仕組みについて簡単に解説します。
ソート発生時にファイルソートが選択された場合、InnoDBストレージエンジンでは、スレッド(コネクション)単位でsort_buffer_sizeで指定した分のメモリ領域を確保します (Sort Buffer)。例えばsort_buffer_sizeを16MiB指定している場合、スレッド単位で16MiBを確保します。
InnoDBはこの確保したメモリ領域内でまずはソートを実施します。ここで、データサイズがメモリ領域を超過する場合、ディスク領域を使って一時ファイルを作成し領域を拡張します。一時ファイルに分割されたデータは実行後、Sort Mergeというプロセスを経て統合され、クエリ結果を返します。このように、ファイルソートの中でもディスク領域を利用した方法を、ディスクソート (Disksort) と呼びます
1.3 スロークエリと sort_buffer_size
ディスクソートはスロークエリの原因になります。内部のメモリ領域を使うのではなく、外部のディスクにファイルを書き出すという処理を行うので、I/O負荷 (I/Oバウンド)が高くなるためです。
物理要因ではsort_buffer_sizeが不足していることを意味しています。シンプルなパフォーマンスチューニングでは、メモリ領域を増やして終わりなのですが、本記事では、現実的なトレードオフを意識したパフォーマンスチューニングを行うための具体的なメソッドを考えていきます。
2. 原因を調査する
パフォーマンスチューニングを進める上で、実際の原因がディスクソートにあるのかを確かめる必要があります。本記事では以下の手順を実践します。
2.1 ファイルソート発生率
まずは、全クエリ中でファイルソートが発生する割合を調査します。
SELECT
(
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Sort_scan') +
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Sort_range')
) /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Questions')
AS sort_ratio;
ファイルソートの総回数は Sort_scan( テーブル全体を読み込んでソートした回数) と Sort_range (範囲検索の結果をソートした回数) を足したものに相当します。その値を、全クエリの実行回数 (Questions) で割ることで、全体に対する、ファイルソートのクエリ回数の発生率(%) を計算できます。
2.2 ディスクソート発生頻度
次に全体に対するディスクソートの発生頻度を算出します。
SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Sort_merge_passes') /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Uptime')
AS sort_merge_passes_per_second;
);
方法として、Sort_merge_passes( メモリ不足によりディスクを使用した総回数 )を、Uptime ( サーバ起動してからの経過時間 )で割ります。
これより、1秒あたりに発生したディスクソート回数(ディスクソート発生頻度) (回/s)...①を求めます。
補足: Created_tmp_filesは調査で使わない
Created_tmp_filesという変数で、過去に一時ファイルが作成された回数をカウントしています。しかし、sort_buffer_sizeはスレッド単位で確保されるBufferのため、Created_tmp_filesもスレッド単位でカウントされる値であり、統計的な計算で利用することが困難のため、Sort_merge_passesを利用するという意図があります。
2.3 ディスクソート依存率
最後に、ファイルソートが、どの程度の割合でディスクソートになっているかを計算します。
SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Sort_merge_passes') /
(NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Sort_scan') +
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Sort_range'), 0)) AS disk_sort_ratio;
2.1と2.1の計算を組み合わせます。ディスクソートの発生回数はSort_merge_passesでわかりました。また、ファイルソートの発生回数は Sort_scan + Sort_rangeで計算できました。
これらから、ディスクソートの発生回数 (Sort_merge_passes) をファイルソートの発生回数は(Sort_scan + Sort_range)で割ることで、ディスクソート依存率 (%) を求めることができます。
3. 結果から考察する
2で調査した結果からチューニング対象を考察します。
3.1 ファイルソート発生率が高い
ファイルソート発生率50%を超えるような高い割合の場合、インデックスの付与がうまくいっていない可能性が高いということになります。そのためこのケースでのチューニングは、sort_buffer_sizeを増やすのではなく、インデックスが付与できるテーブルを検討する方が重要度が高いことがわかります。もちろんそういったテーブルがない場合はこの限りではありません。
3.2 ディスクソート発生頻度が高い
実はディスクソート発生頻度が高いだけでは、一概にディスクソートの改善が必要であるとは言えません。
例えば、ディスクソート発生頻度が0.5(回/s)としましょう。2秒に1回はディスクソートが発生することになるので、一見かなりのI/O負荷になっていそうに見えます。ここで、ファイルソート発生率が10%だったとしたらどうでしょうか？大規模なテーブルにおいて、絶え間なくソートクエリが走る中で、10回に1回しかファイルソートが発生していないため、この中でディスクソートが起こる割合は、相対的にかなり小さくなります。これはインデックスが効いているという証拠なので、もしかしたらパフォーマンスチューニングは不要だと判断できるかもしれません。
3.3 ディスクソート依存率が高い
最後はディスクソート依存率が高い場合です。例えばディスクソート率が20%だった場合を考えましょう。この場合、5回に1回はメモリ不足が起きていることになります。この場合になり、初めてsort_buffer_sizeの見直しが必要になります。
具体的には、作成される一時ファイルが平均どの程度のファイルサイズかを調べることで、その分を上乗せすることで、必要な分のメモリ拡充を実施できます。
考察を見ていかがでしたでしょうか？パフォーマンスチューニングをする際に、「sort_buffer_sizeを不足分」ではなく、そもそも見直しが必要なのか精査する重要性がお分かりいただけたと思います。
4. チューニング時の考慮事項
実際にsort_buffer_sizeの拡充が必要となった際は、DBインスタンスが確保するメモリ量を超過しないか、という点に留意が必要です。(以下参考)
例えば、Amazon RDS for MySQLでは概ね以下のように最大メモリ使用量を計算できます。
MaxUsageMemory = Global_Buffer_Size + Thread_Buffer_Size * max_connections
ここでsort_buffer_sizeはThread_Buffer_Sizeの一つの値になります。そのためメモリを拡充した場合、メモリ追加分(MiB/KiB) * max_connectionsがMaxUsageMemoryに上乗せされることになるため、最終的な値がインスタンスメモリを超えないことを確認する必要があります。(ここを超えるとメモリ不足やコネクション枯渇など、アプリケーションに重大な影響が発生します。)
Discussion