PolarDB：FAQ tentang Indeks Kolom dalam Memori

Bagaimana cara menggunakan fitur IMCI dari PolarDB for MySQL?

Bagaimana cara melihat status IMCI?

Bagaimana cara memastikan IMCI digunakan dan melihat rencana eksekusinya?

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

Bagaimana mengatasi masalah ketika rencana eksekusi pernyataan SQL tidak menggunakan indeks kolom / kueri tidak menggunakan penyimpanan kolom / rencana eksekusi SQL tidak berubah setelah penambahan node penyimpanan kolom / tidak jelas apakah indeks kolom mendukung pernyataan SQL tertentu / fitur "Row Store/Column Store Automatic Stream Diversion" tidak berfungsi?

Pernyataan SQL dapat menggunakan IMCI untuk akselerasi hanya jika beberapa kondisi terpenuhi. IMCI harus tersedia untuk tabel yang dikueri, pernyataan harus diteruskan ke node penyimpanan kolom, dan biaya eksekusinya harus melebihi ambang batas tertentu. Jika pernyataan SQL tidak menggunakan IMCI, periksa hal-hal berikut:

1. Pastikan pernyataan SQL telah diteruskan ke node penyimpanan kolom.

   Gunakan fitur SQL Explorer untuk memastikan bahwa pernyataan SQL telah diteruskan ke node penyimpanan kolom.

   Jika Anda menggunakan titik akhir kluster dan mengaktifkan fitur distribusi permintaan otomatis, proksi database secara otomatis meneruskan pernyataan SQL dengan perkiraan biaya kueri yang melebihi imci_ap_threshold ke node penyimpanan kolom. Anda juga dapat menambahkan /*FORCE_IMCI_NODES*/ sebelum kata kunci SELECT dalam pernyataan SQL untuk memaksa pernyataan tersebut diteruskan ke node penyimpanan kolom. Berikut contohnya:

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

   Untuk informasi selengkapnya, lihat Konfigurasikan distribusi permintaan otomatis.

   Catatan

   Buat titik akhir baru untuk terhubung langsung ke node penyimpanan kolom. Ini memastikan bahwa pernyataan SQL selalu diteruskan ke node penyimpanan kolom untuk dieksekusi.

2. Periksa apakah biaya eksekusi kueri berada di atas ambang batas.

   Pada node penyimpanan kolom, pengoptimal memperkirakan biaya pernyataan SQL. Jika perkiraan biaya lebih tinggi daripada ambang batas cost_threshold_for_imci, kueri akan menggunakan IMCI. Jika tidak, kueri akan menggunakan indeks penyimpanan baris yang ada.

   Setelah memastikan bahwa pernyataan SQL telah diteruskan ke node penyimpanan kolom, jalankan EXPLAIN untuk melihat rencana eksekusi. Jika rencana tersebut masih tidak menggunakan IMCI, bandingkan perkiraan biaya eksekusi dengan ambang batas yang telah ditetapkan. Hal ini membantu menentukan apakah perkiraan biaya terlalu rendah sehingga tidak memicu penggunaan IMCI. Anda dapat mengkueri variabel Last_query_cost untuk mengambil perkiraan biaya eksekusi dari pernyataan SQL sebelumnya:

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

   Jika perkiraan biaya eksekusi pernyataan SQL kurang dari ambang batas cost_threshold_for_imci yang telah ditetapkan, pertimbangkan untuk menyesuaikan nilai cost_threshold_for_imci . Misalnya, Anda dapat menggunakan petunjuk (hint) 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 indeks kolom mencakup semua kolom yang diperlukan oleh pernyataan SQL.

   Gunakan prosedur tersimpan bawaan dbms_imci.check_columnar_index() untuk memeriksa apakah IMCI telah dibuat untuk suatu tabel dalam pernyataan SQL. Berikut contohnya:

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

   Jika pernyataan SQL tidak sepenuhnya dicakup oleh IMCI, prosedur tersimpan ini akan mengembalikan tabel dan kolom yang tidak tercakup. Jika pernyataan tersebut sepenuhnya tercakup, prosedur tersimpan akan mengembalikan set hasil kosong.

4. Periksa fitur SQL yang tidak didukung.

   Periksa daftar batasan untuk memastikan apakah fitur SQL tertentu didukung oleh IMCI.

Jika semua pemeriksaan di atas telah dilalui, pernyataan SQL tersebut akan menggunakan IMCI untuk kueri.

Apakah node penyimpanan kolom dapat menggunakan indeks penyimpanan baris?

Node read-only penyimpanan kolom adalah node read-only standar dengan fitur IMCI. Node ini dapat menggunakan indeks penyimpanan baris, sama seperti node read-only standar, dan juga dapat menggunakan indeks penyimpanan kolom. Pengoptimal memilih indeks mana yang akan digunakan berdasarkan ambang batas 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 pernyataan SQL?

Pernyataan SQL hanya dapat menggunakan IMCI jika semua kolom dalam pernyataan tersebut dicakup oleh indeks. Jika kolom yang terlibat dalam pernyataan SQL tidak dicakup oleh IMCI, Anda dapat menambahkan IMCI menggunakan pernyataan CREATE TABLE atau ALTER TABLE. PolarDB for MySQL menyediakan serangkaian prosedur tersimpan bawaan untuk membantu proses ini.

dbms_imci.columnar_advise('<query_string>');

Mengapa penggunaan CPU atau memori tinggi pada node penyimpanan kolom? Bagaimana cara mengonfigurasi pemantauan?

• Secara default, satu pernyataan SQL dijalankan secara paralel dan dapat menggunakan semua core CPU yang tersedia. Saat beberapa pernyataan SQL dijalankan secara konkuren, penjadwal internal secara dinamis menurunkan batas CPU dan memori untuk setiap pernyataan. Oleh karena itu, rata-rata penggunaan CPU dan memori pada node penyimpanan kolom relatif tinggi dibandingkan node lainnya. Anda dapat menyesuaikan parameter imci_max_dop untuk mengontrol tingkat paralelisme maksimum untuk satu pernyataan SQL. Hal ini mengontrol jumlah maksimum core CPU yang dapat digunakan oleh satu pernyataan SQL.

• Tetapkan ambang batas pemantauan untuk penggunaan CPU sebesar 70% dan untuk penggunaan memori sebesar 90%.

• PolarDB for MySQL mendukung spesifikasi berbeda untuk node dalam kluster yang sama. Anda dapat melakukan peningkatan dan penurunan konfigurasi node penyimpanan kolom secara individual. Disarankan menggunakan node penyimpanan kolom dengan minimal 8 core dan memori 16 GB.

Apakah IMCI didukung pada PolarDB for MySQL 5.6 atau 5.7?

Fitur IMCI tidak didukung pada PolarDB for MySQL 5.6 atau 5.7. Fitur ini didukung pada PolarDB for MySQL 8.0.

Apa saja batasan IMCI? Apakah kompatibel dengan MySQL?

Penggunaan IMCI sepenuhnya kompatibel dengan MySQL. Namun, beberapa fitur kueri yang jarang digunakan belum sepenuhnya didukung, seperti ekspresi spasial-temporal tertentu, indeks teks penuh, dan beberapa bentuk subkueri berkorelasi. Pernyataan SQL yang menggunakan fitur-fitur tersebut tidak dapat menggunakan IMCI dan akan menggunakan indeks penyimpanan baris secara default. Untuk informasi selengkapnya mengenai batasan IMCI, lihat Batasan.

Apakah pernyataan `INSERT INTO SELECT` atau `CREATE TABLE AS SELECT` dapat menggunakan IMCI?

IMCI hanya dapat digunakan untuk kueri pada node read-only terpisah. Pernyataan INSERT dan CREATE hanya dapat dijalankan pada node primary (RW). Oleh karena itu, untuk menggunakan IMCI pada kueri INSERT INTO SELECT atau CREATE TABLE AS SELECT, Anda harus menggunakan fitur ekstrak, transformasi, dan muat (ETL) dari IMCI. Untuk informasi selengkapnya, lihat Gunakan IMCI untuk mempercepat ETL.

Apakah fitur IMCI gratis? Apakah node read-only standar mendukung IMCI?

Fitur IMCI itu sendiri gratis. Namun, penggunaan IMCI memerlukan penambahan node read-only terpisah dengan fitur IMCI yang diaktifkan. Node read-only baru ini, serta storage space tambahan untuk IMCI yang Anda buat, akan dikenai biaya.

Node read-only standar tidak mendukung IMCI.

Berapa banyak storage space tambahan yang dibutuhkan oleh IMCI?

Data IMCI diorganisasi berdasarkan kolom, yang memungkinkan rasio kompresi lebih tinggi. Dibandingkan dengan data penyimpanan baris, rasio kompresinya biasanya 3 hingga 10 kali lebih tinggi. Akibatnya, total storage space yang dibutuhkan meningkat sekitar 10% hingga 30%.

Bagaimana cara melihat storage space yang digunakan oleh IMCI?

• Untuk versi kluster PolarDB for MySQL 8.0.1.1.32 dan sebelumnya, Anda dapat mengkueri tabel sistem imci_columns di information_schema untuk melihat storage space dan rasio kompresi kolom dari tabel yang berisi IMCI. Sebagai contoh, untuk melihat storage space dan rasio kompresi dari tabel test dengan IMCI 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 seterusnya, Anda dapat mengkueri tabel sistem imci_data_files di information_schema untuk melihat storage space, dan tabel sistem imci_columns untuk melihat rasio kompresi kolom. Sebagai contoh, untuk melihat storage space dan rasio kompresi kolom dari tabel test dengan IMCI di database test, jalankan pernyataan SQL berikut:

  • Lihat storage space yang digunakan oleh tabel test dengan IMCI. 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
    FROM
        INFORMATION_SCHEMA.IMCI_DATA_FILES
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

  • Lihat rasio kompresi kolom. Jalankan pernyataan SQL berikut:

    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.

Mengapa DDL instan tidak berfungsi setelah penambahan IMCI?

• Untuk versi PolarDB for MySQL sebelum 8.0.1.1.42 dan 8.0.2.2.23, fitur penambahan kolom instan tidak berlaku untuk tabel dengan IMCI tingkat tabel. Hal ini karena operasi tersebut melibatkan perubahan struktur IMCI dan pembangunan ulang data indeks.

• PolarDB for MySQL 8.0.1.1.42 dan seterusnya, serta 8.0.2.2.23 dan seterusnya, mendukung DDL instan pada tabel dengan IMCI tingkat tabel. Fitur ini tidak kompatibel dengan mode rebuild versi lama. Tabel harus memiliki primary key, dan Anda harus mengatur parameter imci_enable_add_column_instant_ddl ke OFF.

Bagaimana cara melihat atau menghapus IMCI yang ditambahkan oleh Auto Index?

SELECT * FROM  information_schema.imci_autoindex_executed;

Anda dapat menghapus IMCI yang ditambahkan oleh Auto Index dengan cara yang sama seperti menghapus IMCI yang ditambahkan secara manual:

ALTER TABLE t1 comment 'columnar=0';

Mengapa `ALTER TABLE` membutuhkan waktu lebih lama untuk menambahkan atau menghapus kolom setelah IMCI ditambahkan?

Saat Anda menambahkan atau menghapus kolom, data tabel biasanya dibangun ulang. Jika tabel asli memiliki IMCI, data IMCI juga harus dibangun ulang. Proses pembangunan ulang data IMCI menulis ke log Redo. IMCI biasanya mencakup banyak kolom, sehingga ukuran log Redo untuk pembangunan ulang sebanding dengan ukuran data tabel asli. Dibandingkan dengan pembangunan ulang tabel tanpa IMCI, jumlah data I/O meningkat, yang memperpanjang durasi operasi.

Apakah penambahan IMCI memengaruhi performa write?

Dampak penambahan IMCI terhadap performa write umumnya berada dalam kisaran 5%. Saat diuji dengan test set oltp_insert workload Sysbench, performa write menurun sekitar 3% setelah IMCI ditambahkan.

Tingkat isolasi transaksi apa saja yang didukung oleh IMCI?

IMCI mendukung tingkat isolasi transaksi READ_COMMITTED dan REPEATABLE_READ.

Apakah IMCI mempercepat pencarian fuzzy?

Ya, benar. IMCI efektif dalam mempercepat pencarian fuzzy dan mendukung fitur seperti LIKE PRUNER, NGRAM LIKE, dan SMID LIKE. Fitur indeks teks penuh penyimpanan kolom yang akan datang juga akan mempercepat pencarian fuzzy.