View a markdown version of this page

Memverifikasi pernyataan mana yang menggunakan query paralel untuk Aurora MySQL - 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 MySQL

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 basis data, pengaturan, kueri SQL, atau bahkan klaster topologi dan pengaturan koneksi aplikasi untuk memanfaatkan fitur ini.

Untuk memeriksa apakah suatu kueri menggunakan kueri paralel, periksa rencana kueri (juga dikenal sebagai "rencana menjelaskan") dengan menjalankan pernyataan EXPLAIN. Sebagai contoh bagaimana pernyataan, klausa, dan ekspresi SQL memengaruhi output EXPLAIN untuk kueri paralel, lihat Konstruksi SQL untuk query paralel di Aurora MySQL.

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

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 MySQL versi 3, Anda mengaktifkan hash join pada tingkat sesi dengan mengeluarkan pernyataan berikut.

SET optimizer_switch='block_nested_loop=on';

Untuk Aurora MySQL versi 2.09 dan yang lebih tinggi, Anda mengatur parameter DB aurora_disable_hash_join atau parameter klaster 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 kueri join MySQL Aurora besar dengan hash join.

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. CPU-intensive Pemrosesan I/O-intensive dan untuk langkah-langkah tersebut didorong ke bawah 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 menginterpretasi output EXPLAIN untuk kueri paralel dan bagian pernyataan SQL yang dapat diterapkan oleh kueri paralel, lihat Konstruksi SQL untuk query paralel di Aurora MySQL.

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 TPC-H kumpulan data ini, terutama 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    |       |
+---------------+---------------+------+-----+---------+-------+

Lakukan eksperimen dengan beban kerja Anda untuk mengetahui apakah pernyataan SQL individual dapat memanfaatkan kueri 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.