PolarDB:Percepatan kueri berbasis IMCI otomatis (AutoIndex)

Ikhtisar

Fitur indeks kolom in-memory (IMCI) dari PolarDB for MySQL dirancang khusus untuk kueri kompleks pada set data besar dalam skenario OLAP. Dengan IMCI, PolarDB for MySQL dapat menyediakan kemampuan terintegrasi untuk pemrosesan transaksi real-time dan analisis data real-time, menjadikannya solusi database HTAP satu atap.

Dengan metode tradisional, untuk mempercepat tabel yang terlibat dalam kueri lambat, Anda harus membuat indeks kolom secara manual dengan menambahkan COLUMNAR=1 ke bidang COMMENT tabel menggunakan CREATE TABLE atau ALTER TABLE. Metode manual ini tidak efisien ketika Anda memiliki banyak templat SQL dengan logika kompleks.

Fitur Percepatan kueri berbasis IMCI otomatis (AutoIndex) mengotomatiskan proses ini dengan menambahkan indeks kolom ke tabel berdasarkan kueri lambat.

Latar belakang teknis

AutoIndex adalah teknik untuk menyetel performa SQL dengan menambahkan indeks secara otomatis. Untuk indeks row-store, konsep ini telah banyak diteliti dan diimplementasikan secara komersial di sistem seperti Oracle dan SQL Server. Sistem-sistem tersebut biasanya menganalisis predikat, klausa ORDER BY, atau operasi JOIN untuk mengidentifikasi potensi indeks sekunder. Mereka kemudian menghitung ulang biaya kueri dengan asumsi indeks telah dibuat dan membuat indeks yang secara signifikan mengurangi biaya tersebut.

Untuk indeks kolom, AutoIndex telah diadopsi oleh sistem seperti Redshift, Snowflake, Databricks, dan Heatwave. Teknik otomatis umum meliputi:

• Automatic loading and unloading: Secara otomatis membuat indeks kolom untuk mempercepat kueri lambat berdasarkan riwayat eksekusi kueri. Indeks kolom yang jarang digunakan atau memakan banyak resource secara otomatis dihapus untuk mengurangi konsumsi storage.

• Automatic encoding: Secara otomatis memilih algoritma encoding optimal untuk setiap kolom berdasarkan distribusi datanya guna lebih mengompresi storage space.

• Automatic selection of distribution keys: Menyeimbangkan distribusi data dalam skenario Pemrosesan Paralel Masif (MPP) untuk mengurangi kesenjangan data dan mengoptimalkan pengacakan data pada operasi GROUP BY dan JOIN terdistribusi.

• Automatic selection of sort keys: Mempercepat penyaringan atau pengurutan kueri dengan menggunakan predikat umum atau kolom yang terlibat dalam join dan pengurutan.

Pembuatan indeks kolom secara otomatis dalam IMCI memiliki biaya lebih rendah dan lebih toleran terhadap kegagalan dibandingkan pembuatan indeks row-store karena alasan berikut:

• Dampak lebih rendah terhadap write: Jumlah indeks row-store yang berlebihan dapat secara signifikan menurunkan performa write pada node primary. Sebaliknya, IMCI tidak mematerialisasi data column-store pada node primary. Ketika indeks kolom baru ditambahkan, operasi write disinkronkan ke node read-only IMCI melalui Redo log, sehingga dampaknya terhadap performa write minimal.

• Rasio kompresi lebih tinggi: Indeks kolom biasanya mencapai rasio kompresi 3x hingga 5x, sehingga overhead storage akibat penambahan indeks baru relatif kecil.

• Dampak terbatas terhadap operasi bisnis: Sistem menggunakan struktur data bebas kunci yang efisien untuk mengumpulkan informasi jejak SQL dan menggunakan DDL nonblocking untuk meminimalkan gangguan terhadap operasi bisnis.

Cara kerja

SELECT * FROM information_schema.imci_autoindex

SELECT *
FROM information_schema.imci_autoindex autoindex, information_schema.sql_sharing share
WHERE autoindex.sql_id = share.sql_id
  AND share.type = 'SQL'

Hasil pengujian

Berikut ini adalah contoh berdasarkan suite pengujian TPC-H 100 GB.

• Persiapan lingkungan: Eksekusi ke-22 kueri pengujian TPC-H sekali setiap menit. Aktifkan HTAP Optimization (Transactional/Analytical Processing Splitting) pada titik akhir kluster. Atur derajat paralel row-store menjadi 8 (terutama untuk mempersingkat durasi pengujian).

• Hasil pengujian: Seperti ditunjukkan pada tabel di bawah, performa kueri pada putaran berikutnya meningkat secara signifikan. Menjelang akhir putaran pertama (sekitar kueri ke-12), AutoIndex telah membuat indeks kolom untuk sebagian besar tabel, dan pengoptimal secara otomatis mengarahkan kueri tersebut ke node read-only IMCI.

Manfaat

Fitur IMCI menawarkan manfaat signifikan dalam hal kompatibilitas, performa, dan efisiensi biaya:

• Fully MySQL compatible: Mendukung semua tipe data MySQL dan protokol MySQL.

• Excellent HTAP performance: Memberikan peningkatan performa satu hingga dua orde besaran untuk workload analitis.

• Cost-effective hybrid storage: Storage hibrida menjamin konsistensi transaksional, sedangkan indeks kolom memberikan performa unggul dan biaya lebih rendah untuk skenario kueri tertentu.

• One-click automatic acceleration: Meningkatkan performa secara otomatis hanya dengan satu klik, tanpa perlu konfigurasi atau tuning kompleks.

• Reduced operational overhead: Secara otomatis membuat indeks kolom untuk kueri lambat, meminimalkan upaya optimasi manual.

• Lower IT costs: Menghemat sumber daya memori dan storage dengan hanya membuat indeks kolom untuk kueri lambat, bukan untuk seluruh database dan tabel.

Versi yang didukung

• Kluster Enterprise Edition harus memenuhi kondisi berikut:

  • Series: Cluster Edition.

  • Versi mesin database:

    • MySQL 8.0.1, dengan revisi 8.0.1.1.45.2 atau lebih baru.

    • MySQL 8.0.2, dengan revisi 8.0.2.2.27 atau lebih baru.

• Kluster Standard Edition harus memenuhi kondisi berikut:

  • CPU architecture: X86.

  • Versi mesin database: MySQL 8.0.1, dengan revisi 8.0.1.1.45.2 atau lebih baru.

Catatan penggunaan

• Kluster Multi-master Cluster (Limitless) Edition dan Serverless tidak mendukung Percepatan kueri berbasis IMCI otomatis.

• Setelah Anda mengaktifkan Percepatan kueri berbasis IMCI otomatis:

  • Node read-only IMCI tambahan dikenai aturan billing yang sama seperti node komputasi biasa.

  • Sistem secara otomatis membuat indeks kolom in-memory berdasarkan kueri SQL lambat Anda. Data IMCI ini mengonsumsi storage space.

  • Sistem menggunakan fitur SQL Trace untuk mencatat riwayat eksekusi SQL lambat. Proses ini biasanya menimbulkan overhead performa tidak lebih dari 3% pada node database.

• Fitur Percepatan kueri berbasis IMCI otomatis menggunakan DDL nonblocking untuk menambahkan indeks kolom. Fitur DDL nonblocking memastikan bahwa transaksi baru tetap dapat mengakses tabel target meskipun kunci MDL-X tidak dapat diperoleh, sehingga menjaga stabilitas aplikasi Anda. Untuk informasi lebih lanjut, lihat nonblocking DDL.

Aktifkan percepatan kueri berbasis IMCI otomatis

1. Masuk ke PolarDB console. Di bilah navigasi kiri, klik Clusters, pilih Region tempat kluster berada, lalu klik ID kluster target untuk membuka halaman detail kluster.

2. Pada halaman Basic Information, temukan bidang Automatic IMCI-based Query Acceleration dan klik Enable.

3. Langkah berikutnya bergantung pada apakah kluster Anda sudah memiliki node read-only IMCI:

   • Jika kluster Anda sudah memiliki node read-only IMCI, klik OK pada kotak dialog Enable Automatic IMCI-based Query Acceleration untuk mengaktifkan fitur tersebut.

   • Jika kluster Anda belum memiliki node read-only IMCI, klik OK pada kotak dialog Enable Automatic IMCI-based Query Acceleration. Anda akan diarahkan ke halaman untuk menambahkan node read-only IMCI.

     Catatan

     • Anda dapat menambahkan node read-only IMCI segera setelah mengklik OK, atau Anda dapat secara manual menambahkan node read-only IMCI nanti.

     • Setelah mengaktifkan fitur ini, kluster Anda harus memiliki setidaknya satu node read-only IMCI. Jika tidak, meskipun status Automatic IMCI-based Query Acceleration menunjukkan Enable, percepatan kueri tidak akan aktif.

     • Jika Anda mengaktifkan fitur ini tetapi tidak menambahkan node read-only IMCI, sistem akan menggunakan SQL Trace untuk mencatat riwayat eksekusi kueri SQL lambat tetapi tidak membuat indeks kolom. Akibatnya, tidak ada percepatan yang diberikan.

Nonaktifkan percepatan kueri berbasis IMCI otomatis

1. Masuk ke PolarDB console, klik Clusters di bilah navigasi kiri, pilih Region tempat kluster berada, lalu klik ID kluster target untuk membuka halaman detail kluster.

2. Pada halaman Basic Information, temukan bidang Automatic IMCI-based Query Acceleration dan klik Close.

3. Pada kotak dialog Disable Automatic IMCI-based Query Acceleration, klik OK untuk menonaktifkan fitur tersebut.