Analisis kinerja kueri berbasis IMCI - PolarDB

PolarDB for MySQL menyediakan fitur analisis kinerja kueri berbasis Indeks Kolom dalam Memori (IMCI). Fitur ini mengumpulkan informasi eksekusi rinci dari pernyataan SQL, seperti durasi eksekusi, dan mengembalikan informasi tersebut bersama dengan rencana kueri yang diperoleh menggunakan pernyataan EXPLAIN. Hal ini memungkinkan Anda mendapatkan wawasan tentang detail eksekusi pernyataan SQL serta membantu menganalisis kueri lambat.

Prasyarat

Kluster Anda harus menjalankan PolarDB for MySQL versi 8.0.1.1.42 atau lebih baru. Untuk informasi tentang cara memeriksa versi kluster, lihat Kueri Versi Mesin.

Catatan penggunaan

Anda harus menyetel parameter imci_analyze_query ke ON untuk mengaktifkan fitur analisis kinerja kueri berbasis IMCI.

Parameter

Tingkat

Deskripsi

imci_analyze_query

Sesi

Menentukan apakah akan mengaktifkan fitur analisis kinerja kueri berbasis IMCI. Nilai valid:

OFF (default)
ON

Contoh

Contoh kueri sederhana

Berikut adalah contoh pernyataan SQL sederhana yang dijalankan pada skema Transaction Processing Performance Council Benchmark H (TPC-H).

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

Jalankan pernyataan EXPLAIN untuk mendapatkan informasi terperinci tentang rencana eksekusi kueri, termasuk biaya eksekusi yang diperkirakan dari setiap operator dan jumlah baris yang diproses oleh setiap operator.

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

Contoh hasil:

+----+------------------------+----------+--------+----------+---------------------------------------------------------------+
| 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     |                                                               |
+----+------------------------+----------+--------+----------+---------------------------------------------------------------+

Aktifkan fitur analisis kinerja kueri berbasis IMCI.
```
SET imci_analyze_query = ON;
```

Jalankan pernyataan SQL.

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

Contoh hasil:

+------------+----------+
| 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)

Data profil kinerja disimpan dalam tabel information_schema.imci_sql_profiling. Setelah menyetel parameter imci_analyze_query ke ON, tabel ini akan menyimpan data profil kinerja untuk pernyataan SQL terbaru yang dieksekusi menggunakan IMCI. Anda dapat mengambil data profil kinerja pernyataan SQL dengan menanyakan tabel ini.

Kueri data profil kinerja pernyataan SQL.

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

Catatan

Petunjuk /*ROUTE_TO_LAST_USED*/ menginstruksikan PolarProxy untuk merutekan kueri ke node tempat pernyataan SQL terbaru dieksekusi menggunakan IMCI. Jika petunjuk /*ROUTE_TO_LAST_USED*/ tidak digunakan, kueri mungkin dirutekan ke node yang salah, sehingga hasil kueri mungkin tidak diperoleh atau salah.

Contoh hasil:

+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+
| 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 |                                                                                                        |
+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+

Dalam keluaran, kolom Extra Info menampilkan kondisi join untuk operator Hash Join dan kunci grup untuk operator Hash Groupby. Kolom Extra Info pada baris pertama menampilkan penggunaan sumber daya CPU dan memori.

(Opsional) Lakukan operasi seperti agregasi pada tabel information_schema.imci_sql_profiling. Sebagai contoh, Anda dapat menanyakan durasi eksekusi pernyataan SQL.

/*ROUTE_TO_LAST_USED*/SELECT SUM(`Execution Time(s)`) AS TOTAL_TIME FROM information_schema.imci_sql_profiling;

Contoh hasil:

+----------------------+
| TOTAL_TIME           |
+----------------------+
| 0.010000000000000002 |
+----------------------+
1 row in set (0.00 sec)

Hasilnya menunjukkan bahwa total durasi eksekusi pernyataan SQL adalah 10 ms.

Contoh kueri kompleks

Berikut adalah contoh pernyataan SQL kompleks yang dijalankan pada dataset TPC-H SF100.

SELECT
    c_name,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE 
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name
ORDER BY
    c_name
LIMIT 10;

Jalankan pernyataan EXPLAIN untuk mendapatkan informasi terperinci tentang rencana eksekusi kueri.

EXPLAIN SELECT
    c_name,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE 
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name
ORDER BY
    c_name
LIMIT 10;

Contoh hasil:

+----+----------------------------------+----------+-----------+------------+---------------------------------------------------------------+
| ID | Operator                         | Name     | E-Rows    | E-Cost     | Extra Info                                                    |
+----+----------------------------------+----------+-----------+------------+---------------------------------------------------------------+
|  1 | Select Statement                 |          |           |            | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
|  2 | └─Limit                          |          | 10        | 7935739.96 | Offset=0 Limit=10                                             |
|  3 |   └─Sort                         |          | 10        | 7935739.96 | Sort Key: c_name ASC                                          |
|  4 |     └─Hash Groupby               |          | 1503700   | 7933273.99 | Group Key: customer.C_NAME                                    |
|  5 |       └─Hash Right Semi Join     |          | 54010545  | 7865930.26 | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY            |
|  6 |         ├─Table Scan             | lineitem | 59785766  | 24001.52   | Cond: (L_PARTKEY > 18000000)                                  |
|  7 |         └─Hash Join              |          | 538776190 | 5488090.33 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY            |
|  8 |           ├─Hash Join            |          | 181006430 | 668535.99  | Join Cond: customer.C_CUSTKEY = orders.O_CUSTKEY              |
|  9 |           │ ├─Table Scan         | customer | 15000000  | 600.00     |                                                               |
| 10 |           │ └─Table Scan         | orders   | 150000000 | 6000.00    |                                                               |
| 11 |           └─Table Scan           | lineitem | 600037902 | 24001.52   |                                                               |
+----+----------------------------------+----------+-----------+------------+---------------------------------------------------------------+

Biaya eksekusi pernyataan SQL adalah 7.935.739. Operator Hash Join dengan ID 7 mengonsumsi sejumlah besar sumber daya CPU. Biaya eksekusinya adalah 5.488.090, yang mencakup sekitar 70% dari total biaya. Untuk mengidentifikasi masalah kinerja pernyataan SQL, Anda dapat mengaktifkan fitur analisis kinerja kueri berbasis IMCI, menjalankan pernyataan SQL, dan melihat informasi kinerja pernyataan SQL.

Aktifkan fitur analisis kinerja kueri berbasis IMCI.
```
SET imci_analyze_query = ON;
```

Jalankan pernyataan SQL.

SELECT
    c_name,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE 
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name
ORDER BY
    c_name
LIMIT 10;

Contoh hasil:

+--------------------+-----------------+
| 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 rows in set (21.37 sec)

Kueri data profil kinerja pernyataan SQL.

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

Catatan

Contoh hasil:

+----+----------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
| ID | Operator                         | Name     | A-Rows    | A-Cost     | Execution Time(s) | Extra Info                                         |
+----+----------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
|  1 | Select Statement                 |          |         0 | 8336856.67 |                 0 |                                                    |
|  2 | └─Limit                          |          |        10 | 8336856.67 |             0.002 | Offset=0 Limit=10                                  |
|  3 |   └─Sort                         |          |         0 | 8336856.67 |             2.275 | Sort Key: c_name ASC                               |
|  4 |     └─Hash Groupby               |          |   9813586 | 8320763.22 |           160.083 | Group Key: customer.C_NAME                         |
|  5 |       └─Hash Right Semi Join     |          | 239598134 | 7994854.23 |            98.174 | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
|  6 |         ├─Table Scan             | lineitem |  60013756 |   24001.52 |              3.28 | Cond: (L_PARTKEY > 18000000)                       |
|  7 |         └─Hash Join              |          | 600037902 | 5156677.35 |           301.503 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
|  8 |           ├─Hash Join            |          | 150000000 |  629777.96 |            97.201 | Join Cond: customer.C_CUSTKEY = orders.O_CUSTKEY   |
|  9 |           │ ├─Table Scan         | customer |  15000000 |        600 |             3.321 |                                                    |
| 10 |           │ └─Table Scan         | orders   | 150000000 |       6000 |             0.241 |                                                    |
| 11 |           └─Table Scan           | lineitem | 600037902 |   24001.52 |             0.661 |                                                    |
+----+----------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+

Hasilnya menunjukkan bahwa operator Hash Join dengan ID 7 mengonsumsi sekitar separuh durasi eksekusi. Hal ini disebabkan oleh ukuran besar tabel lineitem dan hasil dari operator Hash Join dengan ID 8. Akibatnya, sejumlah besar data terlibat dalam operasi join. Dalam operasi hash join, overhead pembuatan tabel hash jauh lebih besar daripada pencarian hash. Dalam kasus ini, Anda hanya perlu mengurangi ukuran hasil operator Hash Join dengan ID 8 untuk secara efektif mengurangi durasi kueri.

Berdasarkan pernyataan SQL dan rencana eksekusi kueri, kinerja kueri dapat ditingkatkan jika pengoptimal mengubah kondisi o_orderkey in (...) menjadi subquery semi-join dan mendorong turun subquery semi-join.

Aktifkan dorongan turun semi-join berbasis biaya.

SET imci_optimizer_switch = 'semijoin_pushdown=on';

Jalankan pernyataan EXPLAIN untuk mendapatkan informasi terperinci tentang rencana eksekusi kueri.

EXPLAIN SELECT
    c_name,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE 
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name
ORDER BY
    c_name
LIMIT 10;

Contoh hasil:

+----+----------------------------------------+----------+-----------+------------+---------------------------------------------------------------+
| ID | Operator                               | Name     | E-Rows    | E-Cost     | Extra Info                                                    |
+----+----------------------------------------+----------+-----------+------------+---------------------------------------------------------------+
|  1 | Select Statement                       |          |           |            | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
|  2 | └─Limit                                |          | 10        | 2800433.74 | Offset=0 Limit=10                                             |
|  3 |   └─Sort                               |          | 10        | 2800433.74 | Sort Key: c_name ASC                                          |
|  4 |     └─Hash Groupby                     |          | 14567321  | 2776544.58 | Group Key: customer.C_NAME                                    |
|  5 |       └─Hash Join                      |          | 57918330  | 2631846.75 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY            |
|  6 |         ├─Hash Join                    |          | 14567321  | 1014041.92 | Join Cond: orders.O_CUSTKEY = customer.C_CUSTKEY              |
|  7 |         │ ├─Hash Right Semi Join       |          | 12071937  | 906226.67  | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY            |
|  8 |         │ │ ├─Table Scan               | lineitem | 59785766  | 24001.52   | Cond: (L_PARTKEY > 18000000)                                  |
|  9 |         │ │ └─Table Scan               | orders   | 150000000 | 6000.00    |                                                               |
| 10 |         │ └─Table Scan                 | customer | 15000000  | 600.00     |                                                               |
| 11 |         └─Table Scan                   | lineitem | 600037902 | 24001.52   |                                                               |
+----+----------------------------------------+----------+-----------+------------+---------------------------------------------------------------+

Hasilnya menunjukkan bahwa total biaya kueri berkurang dari 7.935.739 menjadi 2.800.433 setelah fitur dorongan turun semi-join berbasis biaya diaktifkan.

Jalankan ulang pernyataan SQL.

SELECT
    c_name,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        WHERE 
            l_partkey > 18000000
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name
ORDER BY
    c_name
LIMIT 10;

Contoh hasil:

+--------------------+-----------------+
| 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 rows in set (13.74 sec)

Hasilnya menunjukkan bahwa durasi eksekusi berkurang sekitar 40%.

Kueri data profil kinerja pernyataan SQL.

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

Contoh hasil:

+----+----------------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
| ID | Operator                               | Name     | A-Rows    | A-Cost     | Execution Time(s) | Extra Info                                         |
+----+----------------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
|  1 | Select Statement                       |          |         0 | 4318488.35 |                 0 |                                                    |
|  2 | └─Limit                                |          |        10 | 4318488.35 |             0.002 | Offset=0 Limit=10                                  |
|  3 |   └─Sort                               |          |         0 | 4318488.34 |             3.076 | Sort Key: c_name ASC                               |
|  4 |     └─Hash Groupby                     |          |   9813586 | 4302394.89 |           163.149 | Group Key: customer.C_NAME                         |
|  5 |       └─Hash Join                      |          | 239598134 | 3976485.91 |           151.253 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
|  6 |         ├─Hash Join                    |          |  49393149 | 1321335.54 |            55.392 | Join Cond: orders.O_CUSTKEY = customer.C_CUSTKEY   |
|  7 |         │ ├─Hash Right Semi Join       |          |  49393149 |  954805.16 |            52.552 | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
|  8 |         │ │ ├─Table Scan               | lineitem |  60013756 |   24001.52 |             2.791 | Cond: (L_PARTKEY > 18000000)                       |
|  9 |         │ │ └─Table Scan               | orders   | 150000000 |       6000 |             0.152 |                                                    |
| 10 |         │ └─Table Scan                 | customer |  15000000 |        600 |             0.028 |                                                    |
| 11 |         └─Table Scan                   | lineitem | 600037902 |   24001.52 |             0.642 |                                                    |
+----+----------------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+

Hasilnya menunjukkan bahwa setelah subquery semi-join didorong turun, operasi join tabel besar sebelumnya dihilangkan, yang secara signifikan mengurangi durasi kueri.