PolarDB：FAQ Indeks Kolom dalam Memori

Bagaimana cara menggunakan fitur IMCI PolarDB for MySQL?

Bagaimana cara memeriksa status indeks penyimpanan kolom?

Bagaimana cara memastikan bahwa pernyataan SQL menggunakan indeks penyimpanan kolom 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 cara memecahkan masalah mengapa rencana eksekusi SQL tidak menggunakan indeks kolom / mengapa kueri tidak menggunakan penyimpanan kolom / mengapa rencana eksekusi SQL tidak berubah setelah penambahan node penyimpanan kolom / bagaimana menentukan apakah indeks kolom mendukung pernyataan SQL tertentu / mengapa fitur 'Automatic Row/Column Store Query Routing' tidak berfungsi?

Setelah menambahkan node penyimpanan kolom, Anda harus menambahkan indeks penyimpanan kolom ke tabel yang dikueri oleh pernyataan SQL tersebut. Pernyataan SQL hanya akan menggunakan indeks penyimpanan kolom untuk kueri jika biaya eksekusi melebihi ambang batas tertentu. Selain itu, pernyataan SQL harus diteruskan ke node penyimpanan kolom agar dapat menggunakan fitur IMCI untuk percepatan kueri. Jika suatu pernyataan SQL tidak dapat menggunakan indeks penyimpanan kolom untuk kueri, ikuti langkah-langkah berikut untuk memecahkan masalahnya:

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

   Anda dapat menggunakan fitur SQL Explorer untuk memastikan apakah pernyataan SQL 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 nilai imci_ap_threshold ke node penyimpanan kolom. Anda juga dapat menambahkan hint /*FORCE_IMCI_NODES*/ sebelum kata kunci SELECT dalam pernyataan SQL untuk memaksa pernyataan tersebut diteruskan ke node penyimpanan kolom. Contoh:

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

   Untuk informasi selengkapnya, lihat Konfigurasikan distribusi permintaan otomatis antara node penyimpanan baris dan penyimpanan kolom.

   Catatan

   Anda juga dapat membuat titik akhir baru untuk terhubung langsung ke node penyimpanan kolom. Hal ini memastikan bahwa pernyataan SQL selalu diteruskan ke node penyimpanan kolom untuk dieksekusi.

2. Periksa apakah biaya eksekusi kueri lebih tinggi daripada ambang batas yang ditentukan.

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

   Setelah memastikan bahwa pernyataan SQL diteruskan ke node penyimpanan kolom, jika rencana eksekusi masih tidak menggunakan indeks penyimpanan kolom saat menjalankan EXPLAIN, periksa apakah perkiraan biayanya terlalu rendah. Anda dapat melakukan kueri terhadap variabel Last_query_cost untuk mengambil perkiraan biaya eksekusi pernyataan SQL sebelumnya:

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

   Jika perkiraan biaya eksekusi lebih rendah daripada nilai cost_threshold_for_imci yang telah ditetapkan, pertimbangkan untuk menyesuaikan nilai cost_threshold_for_imci . Misalnya, gunakan hint untuk menyesuaikan ambang batas 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 diperlukan oleh pernyataan SQL dicakup oleh indeks penyimpanan kolom.

   Gunakan prosedur tersimpan bawaan dbms_imci.check_columnar_index() untuk memeriksa apakah indeks penyimpanan kolom telah dibuat untuk tabel dalam pernyataan SQL. Contoh:

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

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

4. Periksa fitur SQL yang tidak didukung.

   Merujuk ke daftar batasan untuk memastikan apakah fitur SQL tertentu mendukung indeks penyimpanan kolom.

Jika semua pemeriksaan di atas berhasil, pernyataan SQL tersebut seharusnya menggunakan indeks penyimpanan kolom untuk kueri.

Dapatkah node penyimpanan kolom menggunakan indeks penyimpanan baris?

Node read-only penyimpanan kolom adalah node read-only biasa yang mencakup fitur IMCI. Oleh karena itu, node penyimpanan kolom dapat menggunakan indeks penyimpanan baris maupun penyimpanan kolom. Pengoptimal memilih indeks 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 menambahkan indeks penyimpanan kolom yang sesuai untuk pernyataan SQL?

Agar pernyataan SQL dapat menggunakan indeks penyimpanan kolom untuk kueri, semua kolom yang digunakan dalam pernyataan tersebut harus dicakup oleh indeks penyimpanan kolom. Jika kolom yang terlibat dalam pernyataan SQL tidak tercakup, tambahkan ke indeks penyimpanan kolom menggunakan pernyataan CREATE TABLE atau ALTER TABLE. PolarDB for MySQL menyediakan serangkaian prosedur tersimpan bawaan untuk membantu operasi ini.

dbms_imci.columnar_advise('<query_string>');

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

Kueri paralel single-node diaktifkan secara default. Gunakan EXPLAIN untuk melihat rencana eksekusi. Bidang max_dop menunjukkan tingkat paralelisme aktual yang digunakan. Untuk menyesuaikan tingkat paralelisme untuk pernyataan SQL tertentu, atur parameter imci_max_dop pada tingkat sesi sebelum mengeksekusi pernyataan SQL tersebut. Contoh:

set imci_max_dop=8; explain select xxxx

Apa yang harus saya lakukan jika node penyimpanan kolom memiliki penggunaan CPU atau memori yang tinggi? Bagaimana cara mengonfigurasi pemantauannya?

• Secara default, satu pernyataan SQL dieksekusi secara konkuren dan dapat menggunakan seluruh sumber daya CPU yang tersedia. Saat beberapa pernyataan SQL dieksekusi secara bersamaan, penjadwal internal dalam database menjadwalkan pernyataan SQL tersebut dan secara dinamis mengurangi batas CPU dan memori untuk setiap pernyataan. Oleh karena itu, node penyimpanan kolom cenderung memiliki rata-rata penggunaan CPU dan memori yang lebih tinggi dibandingkan node lainnya. Anda dapat menyesuaikan parameter imci_max_dop untuk mengontrol tingkat paralelisme maksimum untuk satu pernyataan SQL, yang membatasi jumlah core CPU yang dapat digunakan oleh satu pernyataan SQL.

• Kami menyarankan agar Anda menetapkan ambang batas pemantauan CPU pada 70% penggunaan CPU dan ambang batas pemantauan memori pada 90% penggunaan memori.

• PolarDB for MySQL memungkinkan node dengan spesifikasi berbeda dalam satu kluster yang sama. Anda dapat melakukan peningkatan dan penurunan spesifikasi node penyimpanan kolom secara independen. Kami menyarankan agar node penyimpanan kolom memiliki minimal 8 core dan memori 16 GB.

Apakah fitur IMCI didukung di PolarDB for MySQL 5.6 dan 5.7?

PolarDB for MySQL 5.6 dan 5.7 tidak mendukung fitur IMCI. PolarDB for MySQL 8.0 mendukung fitur IMCI.

Apa saja batasan fitur IMCI? Apakah kompatibel dengan MySQL? Apakah mendukung indeks teks penuh?

Fitur IMCI sepenuhnya kompatibel dengan MySQL. Namun, beberapa fitur kueri yang kurang umum, seperti ekspresi spasial-temporal tertentu, indeks teks penuh, dan beberapa bentuk subkueri berkorelasi, belum sepenuhnya didukung. Pernyataan SQL yang menggunakan fitur-fitur tersebut tidak dapat menggunakan indeks penyimpanan kolom dan akan kembali menggunakan indeks penyimpanan baris untuk kueri. Untuk informasi selengkapnya mengenai batasan fitur IMCI, lihat Batasan.

Dapatkah pernyataan INSERT INTO SELECT dan CREATE TABLE AS SELECT menggunakan indeks penyimpanan kolom?

Pernyataan INSERT dan CREATE hanya dapat dieksekusi pada node primary (RW), sedangkan kueri IMCI dijalankan pada node read-only. Oleh karena itu, untuk menggunakan indeks penyimpanan kolom pada pernyataan INSERT INTO SELECT atau CREATE TABLE AS SELECT, Anda harus menggunakan fitur IMCI ETL. Untuk informasi selengkapnya, lihat Gunakan indeks penyimpanan kolom untuk mempercepat ETL.

Apakah fitur IMCI gratis? Dapatkah saya menggunakannya pada node read-only biasa?

Fitur IMCI sendiri tidak dikenai biaya tambahan. Namun, penggunaan fitur IMCI mengharuskan Anda menambahkan node read-only khusus dengan IMCI yang diaktifkan. Anda akan dikenai biaya untuk node read-only baru ini dan untuk penyimpanan tambahan yang digunakan oleh indeks penyimpanan kolom pada tabel Anda.

Node read-only dengan hot standby nonaktif tidak mendukung fitur Indeks Kolom IMCI.

Berapa banyak ruang penyimpanan tambahan yang digunakan oleh indeks penyimpanan kolom?

Indeks penyimpanan kolom mengorganisasi data berdasarkan kolom, memberikan rasio kompresi 3 hingga 10 kali lebih tinggi daripada penyimpanan baris, tetapi dengan biaya peningkatan penggunaan ruang sebesar 10% hingga 30%.

Bagaimana cara melihat ruang penyimpanan yang digunakan oleh indeks penyimpanan kolom?

• 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 kolom dari tabel yang memiliki indeks penyimpanan kolom. Sebagai contoh, untuk melihat ruang penyimpanan dan rasio kompresi tabel 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 seterusnya, lakukan kueri terhadap tabel sistem imci_data_files di information_schema untuk melihat ruang penyimpanan, dan tabel sistem imci_columns untuk melihat rasio kompresi kolom. Sebagai contoh, untuk melihat ruang penyimpanan dan rasio kompresi kolom tabel test di database test, jalankan pernyataan SQL berikut:

  • Lihat ruang penyimpanan yang digunakan oleh tabel test dengan indeks penyimpanan kolom. 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 berlaku setelah saya menambahkan indeks penyimpanan kolom?

• Pada versi PolarDB for MySQL sebelum 8.0.1.1.42 dan 8.0.2.2.23, logika penambahan kolom instan tidak berlaku untuk operasi yang menambahkan kolom ke tabel dengan IMCI tingkat tabel. Hal ini karena operasi tersebut melibatkan perubahan pada struktur IMCI dan memerlukan pembangunan ulang data indeks.

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

Bagaimana cara melihat atau menghapus indeks penyimpanan kolom yang ditambahkan oleh AutoIndex?

SELECT * FROM  information_schema.imci_autoindex_executed;

Metode untuk menghapus indeks penyimpanan kolom yang ditambahkan secara otomatis oleh AutoIndex sama dengan menghapus yang ditambahkan secara manual:

ALTER TABLE t1 comment 'columnar=0';

Mengapa proses penambahan atau penghapusan kolom menggunakan ALTER TABLE memakan waktu lebih lama setelah indeks penyimpanan kolom ditambahkan?

Saat menambahkan atau menghapus kolom, data tabel biasanya dibangun ulang. Jika tabel asli memiliki indeks penyimpanan kolom, data indeks tersebut juga harus dibangun ulang bersamaan dengan data tabel. Proses pembangunan ulang data indeks penyimpanan kolom memerlukan penulisan ke log Redo. Karena indeks penyimpanan kolom biasanya mencakup banyak kolom, jumlah data log Redo yang dihasilkan sebanding dengan ukuran data tabel asli. Hal ini meningkatkan volume data I/O dibandingkan dengan pembangunan ulang tabel tanpa indeks penyimpanan kolom, sehingga menghasilkan waktu eksekusi yang lebih lama.

Apakah penambahan indeks penyimpanan kolom memengaruhi performa write?

Dampak penambahan indeks penyimpanan kolom terhadap performa write umumnya berada dalam kisaran 5%. Dalam pengujian yang menggunakan test set oltp_insert workload Sysbench, performa write menurun sekitar 3% setelah indeks penyimpanan kolom ditambahkan.

Tingkat isolasi transaksi apa saja yang didukung oleh fitur IMCI?

Indeks penyimpanan kolom mendukung dua tingkat isolasi transaksi: READ_COMMITTED dan REPEATABLE_READ.

Apakah indeks penyimpanan kolom mempercepat pencarian fuzzy?

Ya. Indeks penyimpanan kolom sangat unggul dalam mempercepat skenario pencarian fuzzy. Indeks ini mendukung fitur seperti LIKE PRUNER, NGRAM LIKE, dan SMID LIKE. Fitur indeks teks penuh penyimpanan kolom juga memberikan percepatan untuk pencarian fuzzy.