ソートインデックスの作成 - Amazon Aurora
サポート対象エンジンバージョンContext待ち時間増加の考えられる原因アクション

ソートインデックスの作成

creating sort index スレッド状態は、データをソートするために内部テンポラリテーブルを使用する必要がある SELECT ステートメントを、スレッドが処理中であることを示します。

サポート対象エンジンバージョン

このスレッド状態情報は、以下のバージョンでサポートされています。

  • Aurora MySQL バージョン 2 から 2.09.2 まで

Context

creating sort index 状態は、ORDER BY および GROUP BY 句を持つクエリが既存のインデックスを使用して操作を実行できない場合に表示されます。この場合、MySQL はより高価な filesort オペレーションを実行する必要があります。通常、この操作は結果セットが大きすぎない場合にメモリ内で実行されます。それ以外の場合は、ディスク上にファイルを作成する必要があります。

待ち時間増加の考えられる原因

creating sort index の表示は、それ自体が問題を示しているわけではありません。パフォーマンスが低下し、頻繁に creating sort index の症例が表示される場合、最も可能性の高い原因は ORDER BY または GROUP BY 演算子を使った遅いクエリです。

アクション

一般的なガイドラインは、creating sort index 状態の増加と関連付けられる ORDER BY と GROUP BY 句を持つクエリを見つけることです。次に、インデックスを追加するか、ソートバッファサイズを大きくしても問題が解決するかどうかを確認します。

パフォーマンススキーマ がオンになっていない場合は、オンにします。

Performance Insights は、パフォーマンススキーマインストゥルメントがオンになっていない場合にのみ、スレッドの状態を報告します。パフォーマンススキーマインストゥルメントがオンの場合、Performance Insights は代わりに待機イベントをレポートします。パフォーマンススキーマインストゥルメントは、潜在的なパフォーマンスの問題を調査するための、追加のインサイトと優れたツールを提供します。したがって、パフォーマンススキーマをオンにすることをお勧めします。詳細については、「Aurora MySQL における Performance Insights のPerformance Schema の概要」を参照してください。

問題のあるクエリを特定する

creating sort index 状態の増加の原因となっている現在のクエリを特定するには、show processlist を実行して ORDER BY または GROUP BY を持つクエリがないか確認します。任意で、filesort を持つクエリのプロセスリスト ID に N がなっている場所で、explain for connection N を実行します。

これらの増加の原因となっている過去のクエリを特定するには、スロークエリログをオンにして、ORDER BY のクエリを検索します。遅いクエリでEXPLAINを実行し、「filesort を使用しています。」を探します。詳細については、「ファイルソートの使用に関する説明プランを調べる」を参照してください。

ファイルソートの使用に関する説明プランを調べる

creating sort index状態を引き起こすORDER BY と GROUP BY 句を持つステートメントを特定する。

次の例は、クエリで explain を実行する方法を解説しています。Extra 列は、このクエリが filesort を使用していることを示しています。

mysql> explain select * from mytable order by c1 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2064548
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.01 sec)

次の例は、c1 カラムにインデックスが作成された後、同じクエリで EXPLAIN を実行した結果を示しています。

mysql> alter table mytable add index (c1);
mysql> explain select * from mytable order by c1 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: index
possible_keys: NULL
          key: c1
      key_len: 1023
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

ソート順の最適化にインデックスを使用する方法については、MySQL ドキュメントの最適化による注文を参照してください。

ソートバッファサイズを増やす

特定のクエリで、ディスク上にファイルを作成した filesort プロセスが必要かどうかを確認するには、クエリの実行後、sort_merge_passes 可変値をチェックします。例を以下に示します。

mysql> show session status like 'sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.01 sec)

--- run query
mysql> select * from mytable order by u limit 10; 
--- run status again:

mysql> show session status like 'sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.01 sec)

sort_merge_passes の値が高い場合は、ソートバッファサイズを大きくすることを検討してください。この増加をセッションレベルで適用します。グローバルに増やすと、RAM MySQL の使用量が大幅に増加する可能性があるためです。次の例は、クエリを実行する前にソートバッファサイズを変更する方法を示しています。

mysql> set session sort_buffer_size=10*1024*1024;
Query OK, 0 rows affected (0.00 sec)
-- run query