Gunakan Paging Cache untuk meningkatkan kinerja kueri paginasi mendalam - AnalyticDB

Fitur Paging Cache pada AnalyticDB for MySQL menggunakan mekanisme caching untuk meningkatkan efisiensi kueri terpaginasi skala besar yang menggunakan LIMIT, OFFSET, dan ORDER BY, serta mengatasi masalah kinerja dan bottleneck sumber daya pada paginasi mendalam. Topik ini menjelaskan cara menggunakan fitur Paging Cache dalam kueri terpaginasi.

Prasyarat

Versi minor kluster Anda harus 3.2.3 atau lebih baru.

Catatan

Untuk melihat dan memperbarui versi minor, buka bagian Configuration Information pada halaman Cluster Information di AnalyticDB for MySQL console.

Apa itu Paging cache?

Mengapa Paginasi Mendalam Menyebabkan Masalah Kinerja?

Di platform e-commerce, pengguna sering mengurutkan produk berdasarkan volume penjualan atau peringkat keseluruhan untuk melihat barang berkualitas tinggi terlebih dahulu. Untuk meningkatkan kecepatan respons halaman dan menghindari masalah kinerja akibat memuat data dalam jumlah besar sekaligus, aplikasi biasanya menampilkan hasil menggunakan paginasi.

Di database, metode umum untuk paginasi adalah mengurutkan berdasarkan kolom tertentu lalu menggunakan LIMIT dan OFFSET. LIMIT menentukan jumlah baris per halaman, sedangkan OFFSET menentukan offset awal. Misalnya, untuk menampilkan 100 catatan per halaman, pernyataan SQL untuk mengkueri halaman pertama adalah: SELECT * FROM t_order ORDER BY id LIMIT 0, 100. Untuk halaman yang jauh lebih dalam, seperti halaman 10.001 (melewati 1.000.000 catatan pertama), pernyataan SQL-nya adalah: SELECT * FROM t_order ORDER BY id LIMIT 1000000, 100.

Saat mengkueri halaman mendalam dalam dataset besar, beban operasi pengurutan global dan pencarian tabel berulang dapat menyebabkan penurunan tajam pada kinerja database. Masalah ini lebih kompleks di gudang data terdistribusi seperti AnalyticDB for MySQL. Untuk mengurangi volume data yang dipindahkan antar node, setiap node penyimpanan terlebih dahulu melakukan perhitungan Top-N lokal. Satu node penyimpanan kemudian menggabungkan dan mengurutkan hasil dari setiap node penyimpanan untuk mengembalikan set hasil akhir.

Untuk memastikan hasil akhir akurat, pernyataan SQL yang ditulis ulang harus dikirim ke setiap node penyimpanan. Misalnya, untuk mengkueri halaman 10.001, setiap node penyimpanan menerima kueri: SELECT * FROM t_order ORDER BY id LIMIT 0, 1000100. Meskipun hanya diperlukan 100 baris, satu node harus melakukan pengurutan global pada 1.000.100 × (jumlah node penyimpanan) baris.

Jumlah data yang harus diurutkan bertambah secara linear seiring dengan kedalaman paginasi, menyebabkan penurunan kinerja yang tajam. Hal ini memberikan beban berat pada sumber daya memori dan CPU serta meningkatkan risiko error kehabisan memori (OOM), yang dikenal sebagai masalah "paginasi mendalam".

Paging Cache adalah strategi caching di AnalyticDB for MySQL yang mengoptimalkan kinerja paginasi mendalam. Saat Anda menjalankan kueri terpaginasi untuk pertama kalinya, sistem mengambil data dari database dan menyimpan hasilnya di tabel temporary. Kueri dasar (base queries) berikutnya—yang identik dengan kueri asli kecuali parameter LIMIT dan OFFSET—dapat membaca langsung dari tabel cache. Proses ini menghindari operasi pengurutan berulang. Pendekatan ini tidak hanya secara efektif mengatasi masalah kinerja kueri paginasi mendalam tetapi juga mencegah error kehabisan memori (OOM) yang disebabkan oleh ORDER BY. Selain itu, AnalyticDB for MySQL secara otomatis membersihkan cache Paging yang tidak digunakan berdasarkan kebijakan penggantian untuk memastikan pemanfaatan sumber daya yang efisien.

Fitur ini berlaku untuk skenario berikut.

Ekspor Data Skala Besar
Saat mengekspor data dalam jumlah besar, mengambil terlalu banyak data sekaligus dapat menyebabkan client menjadi tidak stabil. Untuk mencegah hal ini, Anda dapat menggunakan kueri terpaginasi untuk mengambil hasil secara batch. Namun, di lingkungan terdistribusi, penggunaan LIMIT dan OFFSET tidak menjamin bahwa data diproses dalam urutan deterministik. Hal ini dapat menyebabkan halaman yang sama mengembalikan hasil berbeda pada setiap kueri. Oleh karena itu, Anda perlu secara eksplisit menambahkan klausa ORDER BY untuk memastikan tidak ada data yang duplikat atau terlewat selama ekspor. Dengan menggunakan Paging Cache, Anda dapat menghapus klausa ORDER BY jika tidak diperlukan oleh logika bisnis Anda. Hal ini secara signifikan meningkatkan kinerja kueri dan sangat mengurangi risiko error kehabisan memori (OOM).
Kueri Terpaginasi Dataset Lengkap
Set hasil lengkap dari suatu kueri di-cache di penyimpanan hot AnalyticDB for MySQL, sehingga data langsung tersedia dan kecepatan kueri meningkat pesat. Anda dapat dengan cepat mengambil dan menampilkan informasi yang diperlukan melalui paginasi.
Kontrol Konkurensi untuk Laporan Bisnis
Saat beberapa pengguna mengkueri laporan yang sama secara konkuren, setiap permintaan biasanya memicu kueri independen. Hal ini meningkatkan beban kluster dan dapat menyebabkan masalah konsistensi data. Dengan Paging Cache, Anda dapat menggabungkan permintaan tersebut menjadi satu kueri global dalam rentang waktu tertentu, sehingga secara signifikan meningkatkan kinerja kueri dan stabilitas kluster.

Penggunaan

Konfigurasikan database cache

Sebelum menggunakan Paging Cache untuk menyimpan hasil kueri, tentukan database untuk menyimpan tabel temporary kueri terpaginasi. Jika tidak ada database yang ditentukan, tabel temporary akan disimpan di database internal koneksi saat ini. Tabel temporary dibuat secara otomatis saat Paging Cache diaktifkan.

Catatan

Database cache tidak boleh merupakan database eksternal.

Contoh berikut menetapkan database paging_cache sebagai database cache. Anda juga dapat menentukan database lainnya.

SET ADB_CONFIG PAGING_CACHE_SCHEMA=paging_cache;

Aktifkan Paging cache dalam kueri terpaginasi

Tambahkan petunjuk (hint) ke pernyataan kueri untuk mengaktifkan Paging Cache dan meningkatkan kinerja kueri terpaginasi. Saat Anda memulai kueri terpaginasi untuk pertama kalinya dengan hint, tabel temporary dibuat untuk menyimpan hasil kueri terpaginasi. Untuk kueri dasar berikutnya, Anda dapat menambahkan hint yang sama ke pernyataan kueri agar membaca data langsung dari tabel cache tanpa mengakses database lagi.

Batasan

Saat klausa LIMIT dan OFFSET dihapus, kueri terpaginasi harus mengembalikan kurang dari 100 juta baris data.

Catatan

Jika jumlah baris yang akan dikueri melebihi 100 juta, kirim tiket untuk menghubungi dukungan teknis guna menaikkan batas baris tersebut.

Metode

Gunakan salah satu hint berikut untuk mengaktifkan Paging Cache pada kueri.

paging_id=<paging_id>
Parameter paging_id digunakan untuk mengidentifikasi sekelompok kueri terpaginasi terkait. Client harus menghasilkan ID unik untuk mengidentifikasi cache secara unik bagi sekelompok kueri terpaginasi.
- Jika paging_id yang ditentukan tidak ada, cache akan dibuat.
- Kueri mencocokkan cache ketika paging_id-nya ada dan kueri saat ini konsisten dengan kueri dasar di cache.
- Terjadi error jika paging_id yang ditentukan sudah ada, tetapi kueri saat ini tidak konsisten dengan kueri dasar di cache. Anda dapat memeriksa apakah paging_id sedang digunakan dengan mengkueri cache yang ada.
Catatan
paging_id harus dimulai dengan huruf atau garis bawah (_), hanya berisi huruf, angka, dan garis bawah (_), serta memiliki panjang 1 hingga 127 karakter. Nilai ini tidak boleh mengandung tanda kutip, tanda seru (!), atau spasi, dan tidak boleh merupakan kata tercadang SQL.
Contoh berikut menunjukkan bahwa paging123 adalah ID hasil kueri terpaginasi yang menggunakan fitur Paging Cache.
```
/*paging_id=paging123*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
```
paging_cache_enabled=true
Dengan metode ini, Anda tidak perlu sering memodifikasi Hint. Server secara otomatis menghapus klausa LIMIT dan OFFSET dari pernyataan kueri dan menghasilkan paging_id untuk mengidentifikasi apakah kueri termasuk dalam kelompok kueri terpaginasi yang sama.
Karena metode ini bergantung pada pencocokan otomatis, fleksibilitasnya terbatas. Jika cache untuk kueri dasar belum ada, cache baru akan dibuat. Jika cache untuk kueri dasar sudah ada, kueri akan langsung mengakses cache tersebut.
Contoh:
```
/*paging_cache_enabled=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
```

Jika pembuatan cache gagal, Anda harus terlebih dahulu membersihkan cache kueri terpaginasi lalu memulai ulang kueri terpaginasi untuk menghasilkan data cache baru.

Kueri cache yang ada

Ambil informasi tentang semua cache terpaginasi di kluster saat ini, termasuk namun tidak terbatas pada paging_id, ukuran cache, dan status cache.

SELECT * FROM INFORMATION_SCHEMA.KEPLER_PAGING_CACHE_STATUS_MERGED;

Tetapkan jumlah maksimum cache

Jumlah maksimum cache. Nilai default adalah 100.

SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=100;

Jika Anda mencoba membuat cache baru setelah batas tercapai, sistem akan mengembalikan error. Pesan error-nya sebagai berikut:

Paging cache count exceeds the limit. Please clean up unused caches or increase the related parameter using SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=xxx.

Ikuti petunjuk dalam pesan error untuk membersihkan cache yang tidak digunakan atau menaikkan jumlah maksimum cache.

Tetapkan periode validitas cache

Anda dapat menetapkan periode validitas untuk cache. Setelah periode yang ditentukan berakhir, cache kedaluwarsa. Kueri dasar berikutnya akan mengakses database lagi untuk mengambil data dan memperbarui tabel cache. Periode validitas cache diukur dalam detik (s) dan biasanya digunakan untuk kontrol konkurensi dalam skenario pelaporan.

Misalnya, paging_cache_validity_interval=300 menunjukkan bahwa cache berlaku selama 300 detik. Artinya, cache kedaluwarsa 300 detik setelah dibuat. Berikut contohnya:

/*paging_cache_enabled=true, paging_cache_validity_interval=300*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

Bersihkan cache kueri terpaginasi

Saat Anda menggunakan Paging Cache untuk menyimpan hasil kueri terpaginasi, data cache disimpan di penyimpanan hot AnalyticDB for MySQL. Jika cache tidak lagi diperlukan, Anda dapat membersihkannya untuk membebaskan storage space.

Pembersihan manual

Bersihkan Cache Kueri Terpaginasi untuk Kueri Dasar Tertentu
Tetapkan paging_cache_enabled=true,invalidate_paging_cache=true untuk membersihkan hasil cache kueri terpaginasi yang sesuai dengan kueri dasar tersebut.
Contoh:
```
/*paging_cache_enabled=true,invalidate_paging_cache=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
```
Bersihkan Cache Kueri Terpaginasi untuk paging_id Tertentu
Tentukan paging_id untuk menghapus cache kueri terpaginasi yang diidentifikasi oleh paging_id ini.
Contoh:
```
CLEAN_PAGING_CACHE paging123;
```
Catatan
Anda dapat memperoleh paging_id dengan mengkueri cache yang ada.

Pembersihan otomatis

Anda dapat menentukan ambang batas kedaluwarsa cache untuk secara otomatis menghapus cache kueri terpaginasi yang tidak diakses dalam periode tertentu. Ambang batas kedaluwarsa default adalah 600 detik (s), artinya cache akan secara otomatis dibersihkan jika tidak diakses selama 10 menit.

SET ADB_CONFIG PAGING_CACHE_EXPIRATION_TIME=600;

Nonaktifkan Paging cache secara global

Setelah Paging Cache dinonaktifkan, semua hint terkait Paging Cache menjadi tidak berlaku, dan kueri terpaginasi diproses menggunakan jalur kueri asli.

SET ADB_CONFIG PAGING_CACHE_ENABLE=false;

Setelah menonaktifkan fitur Paging Cache, Anda dapat menjalankan perintah SHOW ADB_CONFIG KEY=PAGING_CACHE_ENABLE; untuk memeriksa apakah konfigurasi telah berlaku.

Pemecahan Masalah

Paging cache prepare failed

Pesan error lengkap:

Paging cache prepare failed, and cache is not available. Please use /*paging_cache_enabled=true,invalidate_paging_cache=true*/ to clean the unavailable cache or set a specific pagingId with /*paging_id=xxx*/ to gen a new cache. Note that the old and new cache data may be inconsistent.

Penyebab: Pengecualian seperti restart node atau scaling dapat terjadi selama kueri yang menggunakan Paging Cache. Jika kueri terpaginasi mencocokkan cache yang gagal dibuat, server secara default melemparkan pengecualian alih-alih mengkueri ulang database atau membuat cache baru secara otomatis.

Solusi: Jika Anda mengalami pengecualian ini, pembuatan ulang cache tidak menjamin konsistensi data. Untuk skenario ekspor data, bersihkan data yang diekspor dan cache yang tidak dapat digunakan, lalu buat ulang cache. Untuk skenario lain, bersihkan cache yang tidak dapat digunakan atau tentukan paging_id baru, lalu buat ulang cache.

Tolok ukur kinerja

Tolok ukur ini mengukur peningkatan kinerja yang diberikan oleh Paging Cache untuk kueri terpaginasi dalam skenario ekspor data menggunakan dataset TPC-H 100 GB.

Pengujian ini mengekspor 1 juta baris data, dengan 100.000 baris per halaman. SQL untuk halaman pertama adalah sebagai berikut:

-- Kueri terpaginasi standar, tanpa menggunakan fitur Paging Cache
SELECT * FROM lineitem ORDER BY l_orderkey,l_linenumber LIMIT 0,100000;

-- Kueri terpaginasi menggunakan fitur Paging Cache (dalam skenario ekspor data ini, klausa ORDER BY yang bukan kebutuhan bisnis dihapus)
/*paging_cache_enabled=true*/ SELECT * FROM lineitem LIMIT 0,100000;

Hasil pengujian:

Dengan konkurensi tunggal, waktu respons rata-rata untuk kueri terpaginasi standar selama proses ekspor adalah 54.391 ms. Setelah mengaktifkan Paging Cache, waktu respons rata-rata menjadi 525 ms. Kinerja meningkat sekitar 103 kali, dan penggunaan CPU serta memori berkurang secara signifikan.

Hasil ini menunjukkan bahwa penggunaan Paging Cache tidak hanya sangat mengurangi waktu respons rata-rata selama ekspor data tetapi juga secara efektif menurunkan beban CPU dan memori pada sistem.