SQLトレース機能の使用方法 - PolarDB - Alibaba Cloud ドキュメントセンター

PolarDB for MySQLは、実行プランや実行統計 (スキャンされた行数や実行時間を含む) など、SQL文の実行情報を追跡するSQLトレース機能を提供します。この機能は、SQL文の実行計画の変更によって引き起こされるデータベースのパフォーマンスの変化を検出し、現在のクラスターでメモリを占有している上位のSQL文の統計を収集するのに役立ちます。

前提条件

PolarDBクラスターでは、次のいずれかのバージョンを使用します。

リビジョンバージョンが8.0.1.1.30以降のPolarDB for MySQL 8.0.1。
リビジョンバージョンが8.0.2.2.12以降のPolarDB for MySQL 8.0.2。

クラスターのバージョンを表示する方法については、「エンジンバージョンの照会」をご参照ください。

制限

SQLトレース機能は、CREATE USER、DROP USER、GRANTなどのアカウントに関連する操作を追跡しません。

Parameters

パラメーター	説明
loose_sql_trace_type	SQLトレース機能を使用して追跡するトレースタスクの種類。有効な値： OFF (デフォルト): SQL文は追跡されません。 DEMAND: 指定されたSQL文が追跡されます。 ALL: すべてのSQL文が追跡されます。 SLOW_QUERY: 遅いクエリが追跡されます。説明 SLOW_QUERYは、リビジョンバージョンが8.0.1.1.34以降のPolarDB for MySQL 8.0.1を実行するクラスターでのみサポートされます。
loose_sql_sharing_size	SQL共有 (SQLトレースの基本コンポーネント) の最大メモリサイズ。有効な値: 8388608〜1073741824。単位：バイトデフォルト値: 134217728
loose_sql_trace_plan_expire_time	SQLトレース機能によってトレースされる実行プランの有効期限。実行中のSQL文が実行計画を生成したが、指定された期間内に実行計画に基づいて後続のSQL文が実行されなかった場合、実行計画は期限切れと見なされ、削除されます。有効な値: 0 ~ 18446744073709551615 単位は秒です。デフォルト値: 604800

使用法

追跡するSQL文を追加します。
次のいずれかの方法を使用して、追跡する必要があるSQL文を追加し、SQLトレース機能を使用してSQL文の実行を追跡できます。
- すべてのSQL文を追跡するには、loose_sql_trace_typeパラメーターをALLに設定します。
- loose_sql_trace_typeパラメーターをDEMANDに設定し、dbms_sql.add_traceストアドプロシージャを使用してトレースするSQL文を追加します。
SQLトレース機能によってトレースされるSQLステートメントに関する情報を取得します。
information_schema.sql_sharingテーブルにアクセスして、SQLトレース機能によって追跡されるSQL文と上位のSQL文の実行情報を表示できます。サンプル構文:
- 指定したSQL文の実行情報と実行プランを取得します。
```
SELECT * FROM information_schema.sql_sharing WHERE sql_id = polar_sql_id('select * from t');
```
- 合計実行時間、平均実行時間、およびスキャンされた行の合計数で上位10個のSQL文を取得します。
```
SELECT * FROM information_schema.sql_sharing WHERE TYPE='sql' ORDER BY SUM_EXEC_TIME DESC LIMIT 10;
SELECT * FROM information_schema.sql_sharing WHERE TYPE='sql' ORDER BY SUM_EXEC_TIME/EXECUTIONS DESC LIMIT 10;
SELECT * FROM information_schema.sql_sharing WHERE TYPE='sq1' ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10; 
```

その他の操作

dbms_sql.add_traceストアドプロシージャを使用して追加されたSQL文を追跡する必要がなくなった場合は、dbms_sql.delete_traceストアドプロシージャを使用して特定のSQL文に基づいてテンプレート化SQL文を削除するか、dbms_sql.dele_trace_by_sqlidストアドプロシージャを使用してテンプレート化SQL IDにします。
dbms_sql.reset_trace_statsストアドプロシージャを使用して、information_schema.sql_sharingテーブルのすべての統計をリセットできます。
dbms_sql.flush_traceストアドプロシージャを使用して、information_schema.sql_sharingテーブル内のすべての統計情報をクリアできます。 dbms_sql.add_traceストアドプロシージャを使用して追加されたSQL文の実行に関する統計を再収集する場合は、テーブル内のすべての統計をクリアした後、dbms_sql.reload_traceを使用してテンプレート化sql文をmysql_sharingテーブルからinformation_schema.sql_sharingテーブルにリロードできます。

パフォーマンステスト

SQLトレース機能は、多くのロックフリー設計を使用して、高い同時実行性と多数のSQLテンプレートが必要なさまざまなシナリオでデータベースのパフォーマンスを確保します。

次のテストでは、同じシナリオでsql_trace_typeパラメーターがOFFおよびALLに設定されている場合のクラスター内のデータベースのパフォーマンスを比較します。

Sysbenchテストでは、2,000の表を使用します。各テーブルは10,000行のデータを含む。 4コアと8 GBのメモリのクラスターと8コアと32 GBのメモリのクラスターがテストに使用されます。

次の図は、さまざまなシナリオでの4コアと8 GBのメモリのクラスターのパフォーマンス比較を示しています。
- db-ps-mode=oltp_read_onlyを無効にする
- db-ps-mode=auto oltp_read_write
次の図は、さまざまなシナリオでの8コアと32 GBのメモリのクラスターのパフォーマンス比較を示しています。
- db-ps-mode=oltp_read_onlyを無効にする
- db-ps-mode=auto oltp_read_write

結論

上記のテストデータは、oltp_read_onlyおよびoltp_read_writeシナリオで、SQLトレース機能がデータベースのパフォーマンスを3% 未満低下させることを示しています。