PolarDB:FAQ IMCI

Bagaimana cara menggunakan fitur IMCI dari PolarDB for MySQL?

Bagaimana cara memeriksa status IMCI

Konfirmasi penggunaan IMCI dan lihat rencana eksekusi

*************************** 1. row ***************************
IMCI Execution Plan (max_dop = 8, max_query_mem = 3435134976):
Project | Exprs: temp_table3.lineitem.L_ORDERKEY, temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT), temp_table3.orders.O_ORDERDATE, temp_table3.orders.O_SHIPPRIORITY
  Sort | Exprs: temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT) DESC,temp_table3.orders.O_ORDERDATE ASC
    HashGroupby | OutputTable(3): temp_table3 | Grouping: lineitem.L_ORDERKEY orders.O_ORDERDATE orders.O_SHIPPRIORITY | Output Grouping: lineitem.L_ORDERKEY, orders.O_ORDERDATE, orders.O_SHIPPRIORITY | Aggrs: SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT)
      HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_ORDERKEY = lineitem.L_ORDERKEY
        HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_CUSTKEY = customer.C_CUSTKEY
          CTableScan | InputTable(0): orders | Pred: (orders.O_ORDERDATE < 03/24/1995 00:00:00.000000)
          CTableScan | InputTable(1): customer | Pred: (customer.C_MKTSEGMENT = "BUILDING")
        CTableScan | InputTable(2): lineitem | Pred: (lineitem.L_SHIPDATE > 03/24/1995 00:00:00.000000)
1 row in set (0.04 sec)

Penyelesaian masalah kueri yang tidak menggunakan IMCI

IMCI hanya mempercepat kueri jika beberapa kondisi terpenuhi: IMCI ada pada tabel yang dikueri, perkiraan biaya kueri melebihi ambang batas tertentu, dan kueri diarahkan ke node read-only IMCI. Jika kueri tidak menggunakan IMCI, ikuti langkah troubleshooting berikut:

1. Verifikasi bahwa kueri diarahkan ke node read-only IMCI.

   Gunakan fitur SQL Explorer and Audit untuk memverifikasi bahwa kueri diarahkan ke node read-only IMCI.

   Jika Anda menggunakan titik akhir kluster dengan pemisahan trafik otomatis diaktifkan, PolarProxy secara otomatis mengarahkan kueri yang memiliki perkiraan biaya lebih tinggi daripada nilai imci_ap_threshold ke node read-only IMCI. Anda juga dapat memaksa pengarahan kueri ke node read-only IMCI dengan menambahkan petunjuk /*FORCE_IMCI_NODES*/ sebelum kata kunci SELECT. Contoh:

   /*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;

   Untuk informasi lebih lanjut, lihat Konfigurasikan pemisahan trafik otomatis.

   Catatan

   Untuk memastikan kueri selalu diarahkan ke node read-only IMCI, buat titik akhir baru yang terhubung langsung ke node tersebut.

2. Periksa apakah biaya kueri melebihi ambang batas.

   Pada node read-only IMCI, pengoptimal memperkirakan biaya kueri. Jika perkiraan biaya melebihi nilai parameter cost_threshold_for_imci, kueri akan menggunakan IMCI. Jika tidak, kueri akan menggunakan row index standar.

   Setelah memastikan kueri diarahkan ke node read-only IMCI, jika rencana eksekusi tetap tidak menunjukkan penggunaan IMCI, kemungkinan biaya kueri yang diperkirakan terlalu rendah. Periksa biaya perkiraan kueri terakhir yang dieksekusi dengan melihat variabel Last_query_cost_for_imci:

   EXPLAIN SELECT * FROM t1;
   SHOW STATUS LIKE 'Last_query_cost_for_imci';

   Jika perkiraan biaya eksekusi pernyataan SQL kurang dari cost_threshold_for_imci yang telah ditetapkan, Anda dapat mempertimbangkan untuk menyesuaikan cost_threshold_for_imci . Misalnya, gunakan petunjuk untuk menyesuaikan ambang batas yang telah ditetapkan untuk satu pernyataan SQL:

   /*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

3. Periksa apakah semua kolom yang digunakan dalam kueri telah dicakup oleh IMCI.

   Gunakan prosedur tersimpan bawaan dbms_imci.check_columnar_index() untuk memeriksa apakah IMCI telah dibuat pada tabel dalam suatu kueri. Contoh:

   CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');

   Jika kueri merujuk kolom yang tidak dicakup oleh IMCI, prosedur tersimpan tersebut akan mengembalikan daftar tabel dan kolom yang tidak tercakup. Jika semua kolom telah tercakup, prosedur tersebut akan mengembalikan set hasil kosong.

4. Periksa fitur SQL yang tidak didukung.

   Tinjau Batasan untuk memastikan semua fitur dalam kueri Anda didukung oleh IMCI.

Jika semua pemeriksaan ini berhasil, kueri seharusnya menggunakan IMCI.

Apakah node read-only IMCI dapat menggunakan row index?

Ya. Node read-only IMCI merupakan node read-only standar dengan fitur IMCI yang diaktifkan. Dengan demikian, node tersebut dapat memanfaatkan IMCI maupun row index standar. Pengoptimal memilih indeks yang akan digunakan berdasarkan nilai cost_threshold_for_imci.

SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

SELECT /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ COUNT(*) FROM t1 WHERE t1.a > 1;

Bagaimana cara membuat IMCI yang sesuai untuk suatu kueri

Agar kueri dapat menggunakan IMCI, semua kolom yang dirujuk kueri harus dicakup oleh IMCI. Jika beberapa kolom tidak tercakup, Anda dapat menambahkannya ke IMCI dengan menggunakan pernyataan CREATE TABLE atau ALTER TABLE. PolarDB for MySQL menyediakan beberapa prosedur tersimpan bawaan untuk membantu proses ini.

dbms_imci.columnar_advise('<query_string>');

PolarDB for MySQL IMCIApakah mendukung kueri paralel single-node? Jika ya, bagaimana cara menyesuaikan tingkat paralelisme untuk kueri SQL tertentu?

Kueri paralel pada single node diaktifkan secara default. Jalankan EXPLAIN untuk melihat rencana eksekusi, di mana bidang max_dop menunjukkan tingkat paralelisme aktual yang digunakan. Untuk menyesuaikan tingkat paralelisme pada kueri tertentu, atur parameter imci_max_dop pada tingkat sesi sebelum mengeksekusi kueri. Contohnya:

set imci_max_dop=8; explain select xxxx

Penggunaan sumber daya tinggi dan pemantauan

• Secara default, IMCI dikonfigurasi untuk mengeksekusi satu kueri secara paralel dengan memanfaatkan seluruh sumber daya CPU yang tersedia. Ketika beberapa kueri dijalankan secara bersamaan, penjadwal internal database mengelola sumber daya tersebut dengan secara dinamis mengurangi batas CPU dan memori untuk setiap kueri. Akibatnya, rata-rata penggunaan CPU dan memori pada node read-only IMCI umumnya lebih tinggi dibandingkan node lainnya. Anda dapat mengontrol tingkat paralelisme maksimum (jumlah maksimum core CPU) untuk satu kueri dengan menyesuaikan parameter imci_max_dop.

• Kami merekomendasikan mengatur ambang batas peringatan pemantauan menjadi 70% untuk penggunaan CPU dan 90% untuk penggunaan memori.

• PolarDB for MySQL memungkinkan Anda menggunakan spesifikasi berbeda untuk node yang berbeda dalam satu kluster yang sama. Anda dapat melakukan penskalaan naik atau turun node read-only IMCI secara independen. Kami merekomendasikan agar node read-only IMCI memiliki minimal CPU 8 core dan memori 16 GB.

PolarDB for MySQL 5.6/5.7: Apakah IMCI didukung?

Tidak. Fitur IMCI tidak didukung di PolarDB for MySQL 5.6 atau 5.7. Fitur ini hanya didukung di PolarDB for MySQL versi 8.0 dan yang lebih baru.

Batasan IMCI dan kompatibilitas MySQL

IMCI sepenuhnya kompatibel dengan sintaksis MySQL. Namun, beberapa fitur kueri yang kurang umum belum didukung sepenuhnya, seperti ekspresi tipe data spasial tertentu, full-text index, dan beberapa bentuk subkueri berkorelasi. Kueri yang menggunakan fitur-fitur tersebut tidak dapat dipercepat oleh IMCI dan secara otomatis akan kembali menggunakan row index standar. Untuk daftar lengkap batasan, lihat Batasan.

Menggunakan IMCI dengan INSERT/CREATE AS SELECT

IMCI hanya dapat digunakan untuk kueri pada node read-only, sedangkan pernyataan INSERT dan CREATE hanya dapat dieksekusi pada node primary. Oleh karena itu, untuk mempercepat bagian SELECT dalam pernyataan INSERT INTO SELECT atau CREATE TABLE AS SELECT, Anda harus menggunakan fitur ETL IMCI. Untuk informasi lebih lanjut, lihat Gunakan IMCI untuk mempercepat ETL.

Harga dan biaya

Fitur IMCI tidak dikenai biaya. Namun, untuk menggunakannya, Anda harus menambahkan node read-only khusus dengan IMCI yang diaktifkan. Anda akan dikenai biaya untuk node read-only baru tersebut serta penyimpanan tambahan yang dikonsumsi oleh IMCI.

Node read-only standar tidak mendukung IMCI.

Kebutuhan penyimpanan

IMCI menyimpan data dalam format kolom, memungkinkan rasio kompresi tinggi. Dibandingkan dengan penyimpanan berbasis baris, IMCI dapat mencapai rasio kompresi 3:1 hingga 10:1, biasanya menghasilkan jejak penyimpanan tambahan hanya sebesar 10% hingga 30% dari ukuran tabel asli.

Lihat penggunaan penyimpanan IMCI

• Untuk versi kluster PolarDB for MySQL 8.0.1.1.32 dan sebelumnya, lakukan kueri terhadap tabel sistem imci_columns di information_schema untuk melihat ruang penyimpanan dan rasio kompresi tabel dengan IMCI. Misalnya, untuk memeriksa ruang penyimpanan dan rasio kompresi untuk tabel bernama test di database test, jalankan pernyataan SQL berikut:

  SELECT
    SCHEMA_NAME, TABLE_NAME,
    SUM(EXTENT_SIZE * TOTAL_EXTENT_COUNT) AS TOTAL_SIZE,
    SUM(EXTENT_SIZE * USED_EXTENT_COUNT) AS USED_SIZE,
    SUM(EXTENT_SIZE * FREE_EXTENT_COUNT) AS FREE_SIZE,
    SUM(RAW_DATA_SIZE) / SUM(FILE_SIZE) AS COMPRESS
  FROM
    information_schema.imci_columns
  WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

• Untuk versi kluster PolarDB for MySQL 8.0.1.1.33 dan yang lebih baru, lakukan kueri terhadap tabel sistem imci_data_files di information_schema untuk melihat ruang penyimpanan dan tabel sistem imci_columns untuk melihat rasio kompresi. Misalnya, untuk memeriksa ruang penyimpanan dan rasio kompresi untuk tabel bernama test di database test, jalankan pernyataan SQL berikut:

  • Lihat ruang penyimpanan yang digunakan oleh tabel test dengan IMCI:

    SELECT
        SCHEMA_NAME, TABLE_NAME,
        SUM(EXTENT_SIZE * TOTAL_EXTENT_COUNT) AS TOTAL_SIZE,
        SUM(EXTENT_SIZE * USED_EXTENT_COUNT) AS USED_SIZE,
        SUM(EXTENT_SIZE * FREE_EXTENT_COUNT) AS FREE_SIZE
    FROM
        INFORMATION_SCHEMA.IMCI_DATA_FILES
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

  • Lihat rasio kompresi kolom:

    SELECT
        SCHEMA_NAME, TABLE_NAME,
         SUM(RAW_DATA_SIZE) / SUM(CMP_DATA_SIZE) AS COMPRESS_RATIO
    FROM
        INFORMATION_SCHEMA.IMCI_COLUMNS
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

Tabel berikut menjelaskan parameter dalam pernyataan SQL di atas.

Instant DDL dengan IMCI

• Pada versi PolarDB for MySQL sebelum 8.0.1.1.42 dan 8.0.2.2.23, penambahan kolom ke tabel dengan IMCI tingkat tabel tidak menggunakan logika instant DDL karena operasi tersebut memerlukan perubahan struktur IMCI dan pembangunan ulang data indeks.

• Pada PolarDB for MySQL versi 8.0.1.1.42 dan yang lebih baru, serta 8.0.2.2.23 dan yang lebih baru, instant DDL didukung untuk tabel dengan IMCI tingkat tabel. Fitur ini tidak kompatibel dengan mode rebuild pada versi sebelumnya. Anda harus mengatur parameter imci_enable_add_column_instant_ddl ke OFF dan memastikan tabel memiliki primary key.

Lihat atau hapus IMCI yang dibuat otomatis

SELECT * FROM  information_schema.imci_autoindex_executed;

Proses penghapusan IMCI yang dibuat oleh autoindex sama dengan proses penghapusan IMCI yang dibuat secara manual:

ALTER TABLE t1 comment 'columnar=0';

Performa ALTER TABLE dengan IMCI

Menambahkan atau menghapus kolom biasanya melibatkan pembangunan ulang data tabel. Jika tabel memiliki IMCI, data IMCI juga harus dibangun ulang. Proses pembangunan ulang ini menulis ke log Redo. Karena IMCI sering kali mencakup banyak kolom, jumlah data log Redo yang dihasilkan sebanding dengan ukuran data tabel asli. Hal ini mengakibatkan volume I/O yang lebih tinggi dibandingkan dengan pembangunan ulang tabel tanpa IMCI, sehingga operasi membutuhkan waktu lebih lama.

Dampak terhadap performa write

Membuat IMCI memiliki dampak minimal terhadap performa write, biasanya kurang dari 5%. Pengujian Sysbench menggunakan oltp_insert workload menunjukkan penurunan performa sekitar 3% setelah IMCI dibuat.

Tingkat isolasi transaksi yang didukung

IMCI mendukung tingkat isolasi transaksi READ_COMMITTED dan REPEATABLE_READ.

Akselerasi kueri fuzzy

Ya. IMCI memberikan akselerasi luar biasa untuk kasus penggunaan kueri fuzzy dan mendukung operasi seperti LIKE PRUMER, NGRAM LIKE, dan SMID LIKE. Selain itu, fitur full-text index pada kolom juga memberikan akselerasi untuk kasus penggunaan kueri fuzzy.