PolarDB:Kueri paralel untuk data dingin

Cara kerja

ePQ menetapkan tugas pemindaian independen kepada Parallel Worker. Setiap pekerja membaca sebagian data CSV dalam tabel OSS secara konkuren, lalu hasilnya diagregasi. Tingkat paralelisme dikendalikan oleh jumlah pekerja dan jumlah node yang terlibat.

Kueri paralel pada data dingin hanya mendukung format CSV.

Versi yang didukung

Semua mode memerlukan Enterprise Edition.

Prasyarat

Sebelum memulai, pastikan Anda telah:

• Tersambung ke kluster. Lihat Connect to a cluster

• Mengaktifkan pengarsipan data dingin pada kluster. Lihat Enable cold data archiving

• Mengaktifkan fitur ePQ. Lihat Enable elastic parallel query

• Data dingin dalam format CSV

• loose_csv_max_oss_threads diatur ke 2 atau lebih tinggi

loose_csv_max_oss_threads mengontrol jumlah maksimum thread OSS yang berjalan secara paralel pada satu node. Nilai yang valid: 1–100. Default: 1. Pada nilai default 1, kueri paralel dinonaktifkan. Setiap thread OSS mengonsumsi memori sebesar 128 MB, jadi atur parameter ini berdasarkan memori kluster yang tersedia.

Jalankan kueri paralel pada data dingin

Contoh di bawah menggunakan set data TPC-H tetapi tidak memenuhi semua persyaratan pengujian benchmark TPC-H resmi. Hasilnya tidak dapat dibandingkan dengan hasil benchmark TPC-H yang dipublikasikan. Untuk detailnya, lihat TPC-H dataset.

Contoh-contoh berikut menggunakan tabel bernama lineitem yang diarsipkan ke OSS dalam format CSV:

mysql> show create table lineitem;
*************************** 1. row ***************************
       Table: lineitem
Create Table: CREATE TABLE `lineitem` (
  `l_orderkey` int(11) NOT NULL,
  `l_partkey` int(11) NOT NULL,
  `l_suppkey` int(11) NOT NULL,
  `l_linenumber` int(11) NOT NULL,
  `l_quantity` decimal(10,2) NOT NULL,
  `l_extendedprice` decimal(10,2) NOT NULL,
  `l_discount` decimal(10,2) NOT NULL,
  `l_tax` decimal(10,2) NOT NULL,
  `l_returnflag` char(1) NOT NULL,
  `l_linestatus` char(1) NOT NULL,
  `l_shipDATE` date NOT NULL,
  `l_commitDATE` date NOT NULL,
  `l_receiptDATE` date NOT NULL,
  `l_shipinstruct` char(25) NOT NULL,
  `l_shipmode` char(10) NOT NULL,
  `l_comment` varchar(44) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 /*!99990 800020204 NULL_MARKER='NULL' */ CONNECTION='default_oss_server'

1 row in set (0.00 sec)

Untuk mengaktifkan kueri paralel, tambahkan petunjuk /*+ PARALLEL(N) */ ke kueri Anda, di mana N adalah jumlah pekerja per node. Untuk memverifikasi apakah kueri paralel aktif, jalankan EXPLAIN dan periksa kolom Extra:

Total paralelisme = pekerja per node × jumlah node.

mysql> explain SELECT
    ->     sum(l_extendedprice * l_discount) AS revenue
    -> FROM
    ->     lineitem
    -> WHERE
    ->     l_shipdate >= date '1994-01-01'
    ->     AND l_shipdate < date '1994-01-01' + interval '1' year
    ->     AND l_discount between 0.05 - 0.01 AND 0.05 + 0.01
    ->     AND l_quantity < 24;

+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | lineitem | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 61560489 |     0.41 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+

1 row in set, 1 warning (1.23 sec)

mysql> explain SELECT /*+ PARALLEL(4) */
    ->     sum(l_extendedprice * l_discount) AS revenue
    -> FROM
    ->     lineitem
    -> WHERE
    ->     l_shipdate >= date '1994-01-01'
    ->     AND l_shipdate < date '1994-01-01' + interval '1' year
    ->     AND l_discount between 0.05 - 0.01 AND 0.05 + 0.01
    ->     AND l_quantity < 24;
+----+-------------+-------------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------+
|  1 | SIMPLE      | <gather1.1> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |        4 |   100.00 | NULL                                   |
|  1 | SIMPLE      | lineitem    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15390122 |     0.41 | Parallel scan (4 workers); Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------+

2 rows in set, 1 warning (2.17 sec)

mysql> explain SELECT /*+ PARALLEL(4) */
    ->     sum(l_extendedprice * l_discount) AS revenue
    -> FROM
    ->     lineitem
    -> WHERE
    ->     l_shipdate >= date '1994-01-01'
    ->     AND l_shipdate < date '1994-01-01' + interval '1' year
    ->     AND l_discount between 0.05 - 0.01 AND 0.05 + 0.01
    ->     AND l_quantity < 24;
+----+-------------+-------------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                                 |
+----+-------------+-------------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------------------+
|  1 | SIMPLE      | <gather1.1> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |        1 |   100.00 | NULL                                                  |
|  1 | SIMPLE      | lineitem    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59986051 |     0.41 | Parallel scan (8 workers); MPP (2 nodes); Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------------------+

2 rows in set, 1 warning (0.00 sec)

Pemecahan masalah

Jika EXPLAIN tidak menampilkan Parallel scan pada kolom Extra, kueri paralel tidak diaktifkan. Periksa hal-hal berikut: