Memverifikasi pernyataan mana yang menggunakan query paralel untuk Aurora My SQL - Amazon Aurora

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Memverifikasi pernyataan mana yang menggunakan query paralel untuk Aurora My SQL

Dalam operasi biasa, Anda tidak perlu melakukan tindakan khusus apa pun untuk memanfaatkan kueri paralel. Setelah kueri memenuhi persyaratan penting untuk kueri paralel, pengoptimal kueri secara otomatis memutuskan apakah akan menggunakan kueri paralel untuk setiap kueri tertentu.

Jika menjalankan percobaan di lingkungan pengembangan atau pengujian, Anda mungkin menemukan bahwa kueri paralel tidak digunakan karena tabel Anda terlalu kecil dalam jumlah baris atau volume data keseluruhan. Data untuk tabel mungkin juga sepenuhnya berada di dalam buffer pool, terutama untuk tabel yang baru Anda buat untuk melakukan percobaan.

Saat Anda memantau atau menyesuaikan performa klaster, pastikan untuk memutuskan apakah kueri paralel digunakan dalam konteks yang sesuai. Anda dapat menyesuaikan skema database, pengaturan, SQL kueri, atau bahkan topologi cluster dan pengaturan koneksi aplikasi untuk memanfaatkan fitur ini.

Untuk memeriksa apakah kueri menggunakan query paralel, periksa rencana kueri (juga dikenal sebagai “jelaskan rencana”) dengan menjalankan EXPLAINpernyataan. Untuk contoh bagaimana SQL pernyataan, klausa, dan ekspresi mempengaruhi EXPLAIN output untuk query paralel, lihat. SQLkonstruksi untuk kueri paralel di Aurora My SQL

Contoh berikut menunjukkan perbedaan antara rencana kueri tradisional dan rencana kueri paralel. Rencana penjelasan ini berasal dari Query 3 dari benchmark TPC -H. Banyak kueri sampel di seluruh bagian ini menggunakan tabel dari kumpulan data TPC -H. Anda bisa mendapatkan definisi tabel, kueri, dan dbgen program yang menghasilkan data sampel dari situs web TPC-h. 

EXPLAIN SELECT l_orderkey,
  sum(l_extendedprice * (1 - l_discount)) AS revenue,
  o_orderdate,
  o_shippriority
FROM customer,
  orders,
  lineitem
WHERE c_mktsegment = 'AUTOMOBILE'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < date '1995-03-13'
AND l_shipdate > date '1995-03-13'
GROUP BY l_orderkey,
  o_orderdate,
  o_shippriority
ORDER BY revenue DESC,
  o_orderdate LIMIT 10;

Secara default, kueri tersebut mungkin memiliki rencana seperti berikut. Jika Anda tidak melihat hash join digunakan dalam rencana kueri, pastikan pengoptimalan diaktifkan terlebih dahulu. 

+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                              |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  1480234 |    10.00 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | orders   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 14875240 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | lineitem | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59270573 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+

Untuk Aurora My SQL versi 3, Anda mengaktifkan hash join di tingkat sesi dengan mengeluarkan pernyataan berikut.

SET optimizer_switch='block_nested_loop=on';

Untuk Aurora My SQL versi 2.09 dan yang lebih tinggi, Anda mengatur parameter aurora_disable_hash_join DB atau parameter cluster DB ke 0 (off). Jika aurora_disable_hash_join dinonaktifkan, nilai optimizer_switch berubah menjadi hash_join=on.

Setelah Anda mengaktifkan hash join, coba jalankan pernyataan EXPLAIN lagi. Untuk informasi tentang cara menggunakan hash join secara efektif, lihat Mengoptimalkan Aurora besar Kueri bergabung SQL saya dengan gabungan hash.

Dengan hash join diaktifkan tetapi kueri paralel dinonaktifkan, kueri mungkin memiliki rencana seperti berikut, yang menggunakan hash join, tetapi tidak menggunakan kueri paralel. 

+----+-------------+----------+...+-----------+-----------------------------------------------------------------+
| id | select_type | table    |...| rows      | Extra                                                           |
+----+-------------+----------+...+-----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | customer |...|   5798330 | Using where; Using index; Using temporary; Using filesort       |
|  1 | SIMPLE      | orders   |...| 154545408 | Using where; Using join buffer (Hash Join Outer table orders)   |
|  1 | SIMPLE      | lineitem |...| 606119300 | Using where; Using join buffer (Hash Join Outer table lineitem) |
+----+-------------+----------+...+-----------+-----------------------------------------------------------------+

Setelah kueri paralel diaktifkan, dua langkah dalam rencana kueri paralel, seperti ditunjukkan pada kolom Extra dalam output EXPLAIN. Pemrosesan intensif I/O dan CPU intensif untuk langkah-langkah tersebut didorong ke lapisan penyimpanan. 

+----+...+--------------------------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                                          |
+----+...+--------------------------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using index; Using temporary; Using filesort                                                                      |
|  1 |...| Using where; Using join buffer (Hash Join Outer table orders); Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra)   |
|  1 |...| Using where; Using join buffer (Hash Join Outer table lineitem); Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra) |
+----+...+--------------------------------------------------------------------------------------------------------------------------------+

Untuk informasi tentang cara menafsirkan EXPLAIN output untuk query paralel dan bagian SQL pernyataan yang dapat diterapkan oleh query paralel, lihatSQLkonstruksi untuk kueri paralel di Aurora My SQL.

Contoh output berikut menunjukkan hasil dari menjalankan kueri sebelumnya pada instans db.r4.2xlarge dengan IT buffer pool dingin. Kueri berjalan jauh lebih cepat saat menggunakan kueri paralel.

catatan

Karena waktu ditentukan oleh banyak faktor lingkungan, hasil Anda mungkin berbeda. Selalu lakukan uji performa Anda sendiri untuk mengonfirmasi temuan dengan lingkungan dan beban kerja Anda sendiri, dan sebagainya. 

-- Without parallel query
+------------+-------------+-------------+----------------+
| l_orderkey | revenue     | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
|   92511430 | 514726.4896 | 1995-03-06  |              0 |
.
.
|   28840519 | 454748.2485 | 1995-03-08  |              0 |
+------------+-------------+-------------+----------------+
10 rows in set (24 min 49.99 sec)

-- With parallel query
+------------+-------------+-------------+----------------+
| l_orderkey | revenue     | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
|   92511430 | 514726.4896 | 1995-03-06  |              0 |
.
.
|   28840519 | 454748.2485 | 1995-03-08  |              0 |
+------------+-------------+-------------+----------------+
10 rows in set (1 min 49.91 sec)

Banyak kueri sampel di seluruh bagian ini menggunakan tabel dari kumpulan data TPC -H ini, khususnya PART tabel, yang memiliki 20 juta baris dan definisi berikut. 

+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| p_partkey     | int(11)       | NO   | PRI | NULL    |       |
| p_name        | varchar(55)   | NO   |     | NULL    |       |
| p_mfgr        | char(25)      | NO   |     | NULL    |       |
| p_brand       | char(10)      | NO   |     | NULL    |       |
| p_type        | varchar(25)   | NO   |     | NULL    |       |
| p_size        | int(11)       | NO   |     | NULL    |       |
| p_container   | char(10)      | NO   |     | NULL    |       |
| p_retailprice | decimal(15,2) | NO   |     | NULL    |       |
| p_comment     | varchar(23)   | NO   |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+

Bereksperimenlah dengan beban kerja Anda untuk mengetahui apakah SQL pernyataan individual dapat memanfaatkan query paralel. Lalu gunakan teknik pemantauan berikut untuk membantu memverifikasi frekuensi penggunaan kueri paralel dalam beban kerja nyata dari waktu ke waktu. Untuk beban kerja nyata, faktor tambahan seperti batas keserentakan berlaku.