IMCIベースのクエリのパフォーマンスを分析する - PolarDB

PolarDB for MySQLは、インメモリ列インデックス (IMCI) ベースのクエリパフォーマンス分析機能を提供します。この機能は、実行時間などの詳細なSQL文の実行情報を収集し、EXPLAIN文を使用して取得したクエリプランとともに返します。これにより、SQL文の実行の詳細に関する洞察を得ることができ、低速クエリの分析に役立ちます。

前提条件

クラスターはPolarDB for MySQL 8.0.1.1.42以降を実行します。クラスターのバージョン情報を確認する方法については、「エンジンバージョンの照会」をご参照ください。

使用上の注意

IMCIベースのクエリパフォーマンス分析機能を有効にするには、imci_analyze_queryパラメーターをONに設定する必要があります。

パラメーター

レベル

説明

imci_analyze_query

セッション

IMCIベースのクエリパフォーマンス分析機能を有効にするかどうかを指定します。有効な値：

OFF (デフォルト)
オン

例

単純なクエリの例

次の例では、トランザクション処理パフォーマンスカウンシルベンチマークH (TPC-H) スキーマで単純なSQLステートメントを実行します。サンプルSQL文:

SELECT l_shipmode, COUNT(*) FROM lineitem, orders WHERE l_orderkey = o_orderkey GROUP BY l_shipmode;

EXPLAINステートメントを実行して、各演算子の推定実行コストや各演算子が処理する行数など、クエリの実行計画に関する詳細情報を取得します。

EXPLAIN SELECT l_shipmode, COUNT(*) FROM lineitem, orders WHERE l_orderkey = o_orderkey GROUP BY l_shipmode;

サンプル結果:

+----+------------------------+----------+--------+----------+---------------------------------------------------------------+
| ID | Operator               | Name     | E-Rows | E-Cost   | Extra Info                                                    |
+----+------------------------+----------+--------+----------+---------------------------------------------------------------+
|  1 | Select Statement       |          |        |          | IMCI Execution Plan (max_dop = 1, max_query_mem = unlimited)  |
|  2 | └─Hash Groupby         |          | 6      | 51218.50 | Group Key: lineitem.l_shipmode                                |
|  3 |   └─Compute Scalar     |          | 1869   | 50191.00 |                                                               |
|  4 |     └─Hash Join        |          | 1869   | 31000.00 | Join Cond: lineitem.l_orderkey = orders.o_orderkey            |
|  5 |       ├─Table Scan     | lineitem | 2000   | 0.00     |                                                               |
|  6 |       └─Table Scan     | orders   | 2000   | 0.00     |                                                               |
+----+------------------------+----------+--------+----------+---------------------------------------------------------------+

IMCIベースのクエリパフォーマンス分析機能を有効にします。
```
SET imci_analyze_query = ON;
```
SQL文を実行します。
```
SELECT l_shipmode, COUNT(*) FROM lineitem, orders WHERE l_orderkey = o_orderkey GROUP BY l_shipmode;
```
サンプル結果:
```
+------------+----------+
| l_shipmode | COUNT(*) |
+------------+----------+
| REG AIR    |      283 |
| SHIP       |      269 |
| FOB        |      299 |
| RAIL       |      289 |
| TRUCK      |      314 |
| MAIL       |      274 |
| AIR        |      272 |
+------------+----------+
7 rows in set (0.05 sec)
```
パフォーマンスプロファイリングデータは、information_schema.imci_sql_profilingテーブルに格納されます。 imci_analyze_queryパラメーターをONに設定すると、IMCIを使用して実行された最新のSQL文のパフォーマンスプロファイリングデータがテーブルに格納されます。テーブルをクエリすることで、SQL文のパフォーマンスプロファイリングデータを取得できます。

SQL文のパフォーマンスプロファイリングデータを照会します。

/* ROUTE_TO_LAST_USED */SELECT * FROM information_schema.imci_sql_profiling;

説明

/* ROUTE_TO_LAST_USED */ ヒントは、IMCIを使用して最新のSQL文が実行されたノードにクエリをルーティングするようにPolarProxyに指示します。 /* ROUTE_TO_LAST_USED */ hintを使用しない場合、クエリが正しくないノードにルーティングされ、クエリ結果が取得されないか、正しくない可能性があります。

サンプル結果:

+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+
| ID | Operator               | Name     | A-Rows | A-Cost   | Execution Time(s) | Extra Info                                                                                             |
+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+
|  1 | Select Statement       |          |      0 | 52609.51 |                 0 | IMCI Execution Plan (max_dop = 1, real_dop = 1, max_query_mem = unlimited, real_query_mem = unlimited) |
|  2 | └─Hash Groupby         |          |      7 |  52609.5 |             0.002 | Group Key: lineitem.l_shipmode                                                                         |
|  3 |   └─Compute Scalar     |          |   2000 |    51501 |                 0 |                                                                                                        |
|  4 |     └─Hash Join        |          |   2000 |    31000 |             0.007 | Join Cond: lineitem.l_orderkey = orders.o_orderkey                                                     |
|  5 |       ├─Table Scan     | lineitem |   2000 |        0 |             0.001 |                                                                                                        |
|  6 |       └─Table Scan     | orders   |   2000 |        0 |                 0 |                                                                                                        |
+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+

出力の [Extra Info] 列には、Hash join演算子の結合条件とHash Groupby演算子のグループキーが表示されます。 1行目の [Extra Info] 列には、CPUおよびメモリリソースの使用率が表示されます。

(オプション) information_schema.imci_sql_profilingテーブルで集計などの操作を実行します。たとえば、SQL文の実行期間を照会できます。
```
/* ROUTE_TO_LAST_USED */SELECT SUM('実行時間') AS TOTAL_TIME FROM information_schema.imci_sql_profiling;
```
サンプル結果:
```
+----------------------+
| TOTAL_TIME           |
+----------------------+
| 0.010000000000000002 |
+----------------------+
1 row in set (0.00 sec)
```
結果は、SQL文の合計実行時間が10 msであることを示しています。

複雑なクエリの例

次の例では、複雑なSQLステートメントを実行して、TPC-H SF100データセットを照会します。例：

SELECT
    c_name,
    sum(l_quantity)
から
    顧客、
    注文、
    lineitem
どこ
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE 
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
グループ化
    c_name
注文によって
    c_name
LIMIT 10;

EXPLAINステートメントを実行して、クエリの実行計画に関する詳細情報を取得します。

EXPLAIN SELECT
    c_name,
    sum(l_quantity)
から
    顧客、
    注文、
    lineitem
どこ
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE 
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
グループ化
    c_name
注文によって
    c_name
LIMIT 10;

サンプル結果:

+ ----------------------------------------------------- +
| ID | オペレーター | 名前 | E-Rows | E-Cost | Extra Info |
---- ---------------------------------- -------- ------------------------------------------ --------------------------------------------------------------- +
| 1 | Selectステートメント | | | | IMCI実行計画 (max_dop = 32、max_query_mem=無制限) |
| 2 |] Limit | | 10 | 7935739.96 | オフセット=0 Limit=10 |
| 3 | └ ─ ソート | | 10 | 7935739.96 | ソートキー: c_name ASC |
| 4 | └ ─ Hash Groupby | | 1503700 | 7933273.99 | グループキー: customer.C_NAME |
| 5 | └ ─ ハッシュ右半参加 | | 54010545 | 7865930.26 | 参加コンド: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
| 6 | テーブルスキャン | lineitem | 59785766 | 24001.52 | Cond :( L_PARTKEY > 18000000) |
| 7 | └ ─ Hash Join | | 538776190 | 5488090.33 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
| 8 | アハッシュ・ジョイン | | 181006430 | 668535.99 | ジョン・コンド: customer.C_CUSTKEY = orders.O_CUSTKEY |
| 9 | お客様 | 15000000 | 600.00 | | |
| 10 | テーブルスキャン | 注文 | 150000000 | 6000.00 | |
| 11 | └ ─ テーブルスキャン | lineitem | 600037902 | 24001.52 | |
---- ---------------------------------- -------- ------------------------------------------- --------------------------------------------------------------- +

SQL文の実行コストが7935739です。 IDが7のHash Join演算子は、大量のCPUリソースを消費します。実行コストは5488090で、総コストの約70% を占めます。 SQL文のパフォーマンスの問題を特定するには、IMCIベースのクエリパフォーマンス分析機能を有効にし、SQL文を実行してから、SQL文のパフォーマンス情報を表示します。

IMCIベースのクエリパフォーマンス分析機能を有効にします。
```
SET imci_analyze_query = ON;
```

SQL文を実行します。

SELECT
    c_name,
    sum(l_quantity)
から
    顧客、
    注文、
    lineitem
どこ
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE 
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
グループ化
    c_name
注文によって
    c_name
LIMIT 10;

サンプル結果:

+ -------------------- + ----------------- +
| c_name | sum(l_quantity) |
+ -------------------- + ----------------- +
| Customer#000000001 | 172.00 |
| Customer#000000002 | 663.00 |
| Customer#000000004 | 174.00 |
| Customer#000000005 | 488.00 |
| Customer#000000007 | 1135.00 |
| Customer#000000008 | 440.00 |
| Customer#000000010 | 625.00 |
| Customer#000000011 | 143.00 |
| Customer#000000013 | 1032.00 |
| Customer#000000014 | 564.00 |
+ -------------------- + ----------------- +
セットの10行 (21.37秒)

SQL文のパフォーマンスプロファイリングデータを照会します。

/* ROUTE_TO_LAST_USED */SELECT * FROM information_schema.imci_sql_profiling;

説明

/* ROUTE_TO_LAST_USED */ ヒントは、IMCIを使用して最新のSQL文が実行されたノードにクエリをルーティングするようにPolarProxyに指示します。 /* ROUTE_TO_LAST_USED */ hintを使用しない場合、クエリが誤ったノードにルーティングされ、クエリ結果が取得されない可能性があります。

サンプル結果:

+ ------------------------------------------------------ ------------------- + ---------------------------------------------------- +
| ID | オペレータ | 名前 | A-行 | A-コスト | 実行時間 | 余分な情報 |
---- ---------------------------------- -------- ------------------------------------------- ------------------- ---------------------------------------------------- +
| 1 | Selectステートメント | | 0 | 8336856.67 | 0 | |
| 2 |] Limit | | 10 | 8336856.67 | 0.002 | オフセット=0 Limit=10 |
| 3 | └ ─ ソート | | 0 | 8336856.67 | 2.275 | ソートキー: c_name ASC |
| 4 | └ ─ Hash Groupby | | 9813586 | 8320763.22 | 160.083 | グループキー: customer.C_NAME |
| 5 | └ ─ ハッシュ右半参加 | | 239598134 | 7994854.23 | 98.174 | 参加コンド: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
| 6 | テーブルスキャン | lineitem | 60013756 | 24001.52 | 3.28 | (L_PARTKEY > 18000000) |
| 7 | └ ─ Hash Join | | 600037902 | 5156677.35 | 301.503 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
| 8 | アハッシュジョイン | | 150000000 | 629777.96 | 97.201 | ジョインコンド: customer.C_CUSTKEY = orders.O_CUSTKEY |
| 9 | 顾客 | 15000000 | 600 | 3.321 | | |
| 10 | テーブルスキャン | 注文 | 150000000 | 6000 | 0.241 | |
| 11 | └ ─ テーブルスキャン | lineitem | 600037902 | 24001.52 | 0.661 |
---- ---------------------------------- -------- ------------------------------------------- ------------------- ---------------------------------------------------- +

この結果は、IDが7のHash Join演算子が実行時間の約半分を消費することを示しています。これは、lineitemテーブルと、IDが8のHash Join演算子の結果が大きいためです。その結果、結合演算には大量のデータが含まれる。ハッシュ結合演算では、ハッシュテーブルを構築するオーバーヘッドは、ハッシュルックアップのオーバーヘッドよりもはるかに大きい。この場合、クエリ期間を効果的に短縮するには、IDが8のハッシュ結合演算子の結果サイズを縮小するだけです。

SQL文とクエリの実行プランに基づいて、オプティマイザがo_orderkey in (...) 条件を半結合サブクエリに変換し、半結合サブクエリをプッシュダウンすると、クエリのパフォーマンスが向上します。

コストベースのセミ結合プッシュダウンを有効にします。
```
SET imci_optimizer_switch = 'semijoin_pushdown=on';
```

EXPLAINステートメントを実行して、クエリの実行計画に関する詳細情報を取得します。

EXPLAIN SELECT
    c_name,
    sum(l_quantity)
から
    顧客、
    注文、
    lineitem
どこ
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE 
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
グループ化
    c_name
注文によって
    c_name
LIMIT 10;

サンプル結果:

+ ----------------------------------------------------- +
| ID | オペレーター | 名前 | E-Rows | E-Cost | Extra Info |
---- ---------------------------------------- -------- ------------------------------------------ --------------------------------------------------------------- +
| 1 | Selectステートメント | | | | IMCI実行計画 (max_dop = 32、max_query_mem=無制限) |
| 2 |] Limit | | 10 | 2800433.74 | オフセット=0 Limit=10 |
| 3 | └ ─ ソート | | 10 | 2800433.74 | ソートキー: c_name ASC |
| 4 | └ ─ Hash Groupby | | 14567321 | 2776544.58 | グループキー: customer.C_NAME |
| 5 | └ ─ Hash Join | | 57918330 | 2631846.75 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
| 6 | アッシュハッシュジョイン | | 14567321 | 1014041.92 | ジョイン: 注文。O_CUSTKEY = customer.C_CUSTKEY |
| 7 | ハッシュライトセミ参加 | | 12071937 | 906226.67 | 参加コンド: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
| 8 | │ │ ├─ テーブルスキャン | lineitem | 59785766 | 24001.52 | Cond :( L_PARTKEY > 18000000) |
| 9 | 表スキャン | 注文 | 150000000 | 6000.00 | |
| 10 | 顾客 | 15000000 | 600.00 | | | |
| 11 | └ ─ テーブルスキャン | lineitem | 600037902 | 24001.52 | |
---- ---------------------------------------- -------- ------------------------------------------- --------------------------------------------------------------- +

この結果は、コストベースの半結合プッシュダウン機能が有効化された後、総クエリコストが7935739から2800433に削減されることを示しています。

SQL文を再実行してください。

SELECT
    c_name,
    sum(l_quantity)
から
    顧客、
    注文、
    lineitem
どこ
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE 
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
グループ化
    c_name
注文によって
    c_name
LIMIT 10;

サンプル結果:

+ -------------------- + ----------------- +
| c_name | sum(l_quantity) |
+ -------------------- + ----------------- +
| Customer#000000001 | 172.00 |
| Customer#000000002 | 663.00 |
| Customer#000000004 | 174.00 |
| Customer#000000005 | 488.00 |
| Customer#000000007 | 1135.00 |
| Customer#000000008 | 440.00 |
| Customer#000000010 | 625.00 |
| Customer#000000011 | 143.00 |
| Customer#000000013 | 1032.00 |
| Customer#000000014 | 564.00 |
+ -------------------- + ----------------- +
セットの10行 (13.74秒)

結果は、実行期間が約40% 減少したことを示しています。

SQL文のパフォーマンスプロファイリングデータを照会します。

/* ROUTE_TO_LAST_USED */SELECT * FROM information_schema.imci_sql_profiling;

サンプル結果:

+ ------------------------------------------------------ ------------------- + ---------------------------------------------------- +
| ID | オペレータ | 名前 | A-行 | A-コスト | 実行時間 | 余分な情報 |
---- ---------------------------------------- -------- ------------------------------------------- ------------------- ---------------------------------------------------- +
| 1 | Selectステートメント | | 0 | 4318488.35 | 0 | |
| 2 |] Limit | | 10 | 4318488.35 | 0.002 | オフセット=0 Limit=10 |
| 3 | └ ─ ソート | | 0 | 4318488.34 | 3.076 | ソートキー: c_name ASC |
| 4 | └ ─ Hash Groupby | | 9813586 | 4302394.89 | 163.149 | グループキー: customer.C_NAME |
| 5 | └ ─ Hash Join | | 239598134 | 3976485.91 | 151.253 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
| 6 | アッシュハッシュジョイン | | 49393149 | 1321335.54 | 55.392 | ジョイン: 注文。O_CUSTKEY = customer.C_CUSTKEY |
| 7 | ハッシュライトセミ結合 | | | 49393149 | 954805.16 | 52.552 | 結合してください: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
| 8 | │ │ ├─ テーブルスキャン | lineitem | 60013756 | 24001.52 | 2.791 | Cond :( L_PARTKEY > 18000000) |
| 9 | 表スキャン | 注文 | 150000000 | 6000 | 0.152 | |
| 10 | 顾客 | 15000000 | 600 | 0.028 | | |
| 11 | └ ─ テーブルスキャン | lineitem | 600037902 | 24001.52 | 0.642 |
---- ---------------------------------------- -------- ------------------------------------------- ------------------- ---------------------------------------------------- +

この結果は、半結合サブクエリがプッシュダウンされた後、以前の大きなテーブル結合操作が削除され、クエリ期間が大幅に短縮されることを示しています。