Gunakan fitur paging cache untuk meningkatkan kinerja query halaman dalam yang mendalam - AnalyticDB

AnalyticDB for MySQL menyediakan fitur paging cache. Anda dapat menggunakan fitur ini untuk meningkatkan efisiensi query halaman yang melibatkan sejumlah besar data di dalam klausa LIMIT, OFFSET, dan ORDER BY. Fitur ini membantu menyelesaikan masalah kinerja dan hambatan sumber daya dari query halaman dalam yang mendalam. Topik ini menjelaskan cara menggunakan fitur paging cache untuk mengoptimalkan kinerja query halaman.

Prasyarat

Sebuah kluster AnalyticDB for MySQL versi V3.2.3 atau lebih baru telah dibuat.

Catatan

Untuk melihat dan memperbarui versi minor dari sebuah kluster AnalyticDB for MySQL, masuk ke Konsol AnalyticDB for MySQL dan pergi ke bagian Configuration Information pada halaman Cluster Information.

Ikhtisar

Masalah Kinerja yang Disebabkan oleh Query Halaman Dalam yang Mendalam

Pengguna di platform e-commerce sering ingin mengurutkan produk berdasarkan volume penjualan atau skor komprehensif untuk memprioritaskan produk berkualitas. Aplikasi biasanya menampilkan hasil dengan metode paginasi untuk mempercepat respons halaman dan mencegah masalah kinerja akibat memuat sejumlah besar data sekaligus.

Metode umum dalam database adalah mengurutkan data berdasarkan kolom dan melakukan query halaman menggunakan klausa LIMIT dan OFFSET. Klausa LIMIT menentukan jumlah entri data yang ditampilkan per halaman, sedangkan klausa OFFSET menentukan offset awal data per halaman. Sebagai contoh, untuk menampilkan 100 entri data per halaman, jalankan pernyataan berikut untuk halaman pertama: SELECT * FROM t_order ORDER BY id LIMIT 0, 100. Untuk halaman 10.001, jalankan pernyataan berikut: SELECT * FROM t_order ORDER BY id LIMIT 1.000.000, 100.

Saat melakukan query halaman pada sejumlah besar data, query halaman dalam yang mendalam dapat secara signifikan menurunkan kinerja database karena overhead pengurutan global dan operasi pencarian yang sering. Masalah ini menjadi lebih kompleks di AnalyticDB for MySQL. Untuk mengurangi jumlah data yang dipindahkan antara node, setiap node penyimpanan melakukan perhitungan topN, dan node Gather menggabungkan hasil eksekusi semua node penyimpanan untuk mendapatkan set hasil akhir.

Untuk memastikan bahwa set hasil akhir benar, sistem harus menetapkan pernyataan SQL yang telah ditulis ulang ke setiap node penyimpanan untuk dieksekusi. Sebagai contoh, untuk menanyakan entri data pada halaman 10.001, setiap node penyimpanan menerima pernyataan SQL berikut: SELECT * FROM t_order ORDER BY id LIMIT 0, 1.000.100. Akibatnya, node Gather harus mengurutkan 1.000.100 × Jumlah node penyimpanan entri data untuk mendapatkan 100 entri.

Jumlah data yang diurutkan meningkat secara linear dengan kedalaman paginasi, dan kinerja menurun secara drastis. Ini memberikan beban yang signifikan pada sumber daya memori dan CPU serta meningkatkan risiko kesalahan kehabisan memori (OOM). Inilah masalah kinerja yang disebabkan oleh query halaman dalam yang mendalam.

Untuk menyelesaikan masalah kinerja yang disebabkan oleh query halaman dalam yang mendalam, AnalyticDB for MySQL menyediakan fitur paging cache. Saat pertama kali Anda memulai query halaman, sistem menanyakan data dari database dan menyimpan hasil query di tabel cache sementara. Saat Anda memulai query halaman berikutnya dengan pola SQL yang sama, sistem membaca data dari tabel cache sementara untuk mencegah operasi pengurutan berulang. Ini secara efektif menyelesaikan masalah kinerja yang disebabkan oleh query halaman dalam yang mendalam dan mencegah kesalahan OOM yang disebabkan oleh klausa ORDER BY. AnalyticDB for MySQL secara otomatis membersihkan data cache yang tidak lagi diperlukan berdasarkan kebijakan eviksi untuk memastikan bahwa sumber daya digunakan dengan baik.

Fitur paging cache cocok untuk skenario berikut:

Ekspor Sejumlah Besar Data
Saat mengekspor sejumlah besar data, klien mungkin menjadi tidak stabil. Dalam hal ini, sistem melakukan query halaman untuk menarik hasil dalam batch. Jika Anda menggunakan klausa LIMIT dan OFFSET, lingkungan terdistribusi tidak menjamin bahwa data diproses dalam urutan tertentu. Setiap query pada data halaman yang sama dapat mengembalikan hasil yang berbeda. Oleh karena itu, Anda harus menggunakan klausa ORDER BY untuk mencegah data duplikat atau hilang. Fitur paging cache memungkinkan Anda menghilangkan klausa ORDER BY yang tidak perlu. Ini secara signifikan meningkatkan kinerja query dan mengurangi risiko kesalahan OOM.
Query Halaman Data Lengkap
Hasil query lengkap disimpan dalam ruang penyimpanan panas AnalyticDB for MySQL. Ini memastikan ketersediaan data dan secara signifikan meningkatkan kecepatan query. Anda dapat menggunakan fitur paging cache untuk mendapatkan dan menampilkan informasi per halaman.
Kontrol Konkurensi untuk Laporan Bisnis
Jika beberapa query dilakukan secara bersamaan pada laporan yang sama, permintaan independen meningkatkan beban kerja pada kluster dan mengakibatkan ketidaksesuaian data. Anda dapat menggunakan fitur paging cache untuk melakukan satu query dalam jendela waktu untuk meningkatkan kinerja query dan stabilitas kluster.

Penggunaan

Konfigurasikan database cache

Sebelum menggunakan fitur paging cache untuk menyimpan hasil query, kami sarankan Anda menentukan database untuk menyimpan tabel cache sementara untuk query halaman. Jika Anda tidak menentukan database, tabel cache sementara akan disimpan di database internal yang terhubung. Saat Anda mengaktifkan fitur paging cache, tabel cache sementara akan dibuat secara otomatis.

Catatan

Anda tidak dapat menentukan database eksternal sebagai database cache.

Sebagai contoh, Anda dapat menentukan database paging_cache sebagai database cache. Anda juga dapat menentukan database lainnya.

SET ADB_CONFIG PAGING_CACHE_SCHEMA=paging_cache;

Aktifkan fitur paging cache untuk query halaman

Jika beberapa query halaman memiliki pola SQL yang sama, Anda dapat menambahkan petunjuk ke pernyataan SQL untuk meningkatkan kinerja. Saat pertama kali Anda memulai query halaman yang berisi petunjuk, sistem membuat tabel cache sementara untuk menyimpan hasil query halaman. Saat Anda memulai query halaman berikutnya dengan pola SQL yang sama, Anda dapat menambahkan petunjuk yang sama ke pernyataan SQL. Dengan cara ini, sistem membaca data dari tabel cache sementara tanpa perlu akses database berulang.

Batasan

Setelah Anda menghilangkan klausa LIMIT dan OFFSET, jumlah entri data yang ingin Anda lakukan query halaman harus kurang dari 100 juta.

Catatan

Jika jumlah entri data yang akan diquery melebihi 100 juta, Submit a ticket untuk menyesuaikan batas atas jumlah entri data.

Metode untuk mengaktifkan fitur paging cache

Anda dapat menggunakan salah satu petunjuk berikut untuk mengaktifkan fitur paging cache:

paging_id=<paging_id>
Parameter paging_id menentukan tabel cache yang dibuat untuk sekumpulan query halaman dengan pola SQL yang sama tetapi melibatkan nilai yang berbeda dalam klausa LIMIT dan OFFSET. Klien harus menghasilkan ID unik untuk mengidentifikasi secara unik tabel cache untuk sekumpulan query halaman.
- Jika parameter paging_id yang ditentukan tidak ada, tabel cache dibuat.
- Jika parameter paging_id yang diquery ada dan pola SQL yang terlibat dalam query sesuai dengan pola SQL yang sesuai dengan parameter paging_id, query mengenai cache.
- Jika parameter paging_id yang diquery ada tetapi pola SQL yang terlibat dalam query tidak sesuai dengan pola SQL yang sesuai dengan parameter paging_id, terjadi kesalahan. Anda dapat memeriksa apakah parameter paging_id sudah digunakan. Untuk informasi lebih lanjut, lihat bagian "Tanyakan Informasi tentang Tabel Cache" dari topik ini.
Catatan
Parameter paging_id harus memenuhi konvensi penamaan berikut: Nama parameter harus 1 hingga 127 karakter panjangnya dan dapat berisi huruf, angka, dan garis bawah (_). Nama parameter harus dimulai dengan huruf atau garis bawah (_). Nama parameter tidak boleh berisi tanda kutip tunggal ('), tanda kutip ganda ("), tanda seru (!), atau spasi. Nama parameter tidak boleh menjadi kata cadangan SQL reserved word.
Sebagai contoh, Anda dapat mengatur ID paging ke paging123 untuk hasil dari sekumpulan query halaman yang menggunakan fitur paging cache.
```
/*paging_id=paging123*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
```
paging_cache_enabled=true
Metode ini tidak memerlukan modifikasi petunjuk yang sering. Server menggunakan pola SQL yang menghilangkan klausa LIMIT dan OFFSET untuk menghasilkan ID paging guna mengidentifikasi sekumpulan query halaman.
Ketergantungan pada pencocokan pola SQL membatasi fleksibilitas metode ini. Jika tidak ada tabel cache untuk query halaman dengan pola SQL yang sama tanpa klausa LIMIT dan OFFSET, tabel cache dibuat. Jika ada tabel cache untuk query halaman dengan pola SQL yang sama tanpa klausa LIMIT dan OFFSET, tabel cache digunakan untuk menanyakan data.
Pernyataan contoh:
```
/*paging_cache_enabled=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
```

Jika tabel cache gagal dibuat, Anda harus membersihkan data cache dari query halaman dan memulai ulang query halaman untuk membuat tabel cache.

Tanyakan informasi tentang tabel cache

Anda dapat menanyakan informasi tentang semua tabel cache dari query halaman dalam kluster saat ini, seperti ID paging, ukuran cache, dan status cache.

SELECT * FROM INFORMATION_SCHEMA.KEPLER_PAGING_CACHE_STATUS_MERGED;

Tentukan jumlah maksimum tabel cache

Anda dapat menentukan jumlah maksimum tabel cache dalam kluster saat ini. Nilai default: 100.

SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=100;

Jika Anda mencoba membuat tabel cache saat total jumlah tabel cache melebihi batas, terjadi kesalahan. Pesan kesalahan contoh:

Jumlah paging cache melebihi batas. Harap bersihkan cache yang tidak digunakan atau tingkatkan parameter terkait menggunakan SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=xxx.

Bersihkan data cache yang tidak lagi diperlukan atau tingkatkan jumlah maksimum tabel cache berdasarkan pesan kesalahan.

Tentukan periode validitas untuk tabel cache

Anda dapat menentukan periode validitas untuk tabel cache. Unit: detik. Setelah periode validitas berakhir, tabel cache menjadi tidak valid. Saat Anda memulai query halaman berikutnya dengan pola SQL yang sama, sistem melakukan akses database dan memperbarui tabel cache. Dalam banyak kasus, Anda dapat menentukan periode validitas untuk tabel cache dalam skenario kontrol konkurensi laporan.

Sebagai contoh, Anda dapat menggunakan petunjuk /*paging_cache_enabled=true, paging_cache_validity_interval=300*/ untuk memungkinkan tabel cache tetap valid selama 300 detik setelah pembuatan. Pernyataan contoh:

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

Bersihkan data cache dari query halaman

Saat menggunakan fitur paging cache untuk menyimpan hasil query halaman, data cache disimpan di ruang penyimpanan panas AnalyticDB for MySQL. Jika data cache tertentu tidak lagi diperlukan, Anda dapat membersihkan data cache untuk meningkatkan penyimpanan yang tersedia.

Pembersihan manual

Data Cache dari Query Halaman yang Ditentukan oleh Pola SQL
Gunakan petunjuk /*paging_cache_enabled=true, invalidate_paging_cache=true*/ untuk membersihkan data cache dari query halaman yang ditentukan oleh pola SQL.
Pernyataan contoh:
```
/*paging_cache_enabled=true,invalidate_paging_cache=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;
```
Data Cache dari Query Halaman yang Ditentukan oleh Parameter paging_id
Bersihkan data cache dari query halaman yang ditentukan oleh parameter paging_id.Pernyataan contoh:
Contoh pernyataan:
```
CLEAN_PAGING_CACHE paging123;
```
Catatan
Untuk informasi tentang cara mendapatkan nilai parameter paging_id, lihat bagian "Tanyakan Informasi tentang Tabel Cache" dari topik ini.

Pembersihan otomatis

Anda dapat menentukan waktu kedaluwarsa cache untuk membersihkan data cache dari query halaman yang tidak diakses dalam rentang waktu tertentu. Nilai default: 600. Unit: detik. Secara default, data cache yang tidak diakses dalam 10 menit secara otomatis dibersihkan.

SET ADB_CONFIG PAGING_CACHE_EXPIRATION_TIME=600;

Nonaktifkan fitur paging cache untuk kluster

Setelah Anda menonaktifkan fitur paging cache untuk kluster, semua petunjuk terkait fitur ini menjadi tidak valid, dan query halaman diproses menggunakan metode query asli.

SET ADB_CONFIG PAGING_CACHE_ENABLE=false;

Setelah Anda menonaktifkan fitur paging cache, Anda dapat menjalankan pernyataan SHOW ADB_CONFIG KEY=PAGING_CACHE_ENABLE; untuk memeriksa apakah konfigurasi berlaku.

Kesalahan umum dan pemecahan masalah

Persiapan paging cache gagal, dan cache tidak tersedia

Pesan Kesalahan:

Persiapan paging cache gagal, dan cache tidak tersedia. Harap gunakan /*paging_cache_enabled=true,invalidate_paging_cache=true*/ untuk membersihkan cache yang tidak tersedia atau atur pagingId tertentu dengan /*paging_id=xxx*/ untuk menghasilkan cache baru. Perhatikan bahwa data cache lama dan baru mungkin tidak konsisten.

Penyebab: Saat menggunakan fitur paging cache untuk menanyakan data, pengecualian mungkin terjadi, seperti restart node dan penskalaan. Jika query halaman mengenai tabel cache yang gagal dibuat, server tidak melakukan akses database atau secara otomatis membuat ulang tabel cache. Server melempar kesalahan.

Solusi: Untuk memastikan konsistensi data, lakukan operasi berikut: Dalam skenario ekspor data, kami sarankan Anda membersihkan data yang diekspor dan data cache yang tidak tersedia, kemudian memulai ulang query halaman untuk membuat ulang tabel cache. Dalam skenario lain, kami sarankan Anda membersihkan data cache yang tidak tersedia atau menentukan nilai baru untuk parameter paging_id, kemudian memulai ulang query halaman untuk membuat ulang tabel cache.

Perbandingan kinerja

Gunakan dataset TPC-H 100 GB untuk mengevaluasi efek optimasi fitur paging cache untuk query halaman dalam skenario ekspor data.

Uji ini melibatkan 1 juta entri data yang diekspor. Setiap halaman berisi 100.000 entri. Jalankan pernyataan berikut untuk melakukan query halaman pada halaman pertama:

-- Query halaman umum tanpa menggunakan fitur paging cache
SELECT * FROM lineitem ORDER BY l_orderkey,l_linenumber LIMIT 0,100000;

-- Query halaman dengan menggunakan fitur paging cache (ORDER BY dihilangkan dalam skenario ekspor data)
/*paging_cache_enabled=true*/ SELECT * FROM lineitem LIMIT 0,100000;

Hasil Uji:

Permintaan query dijalankan dalam mode konkurensi tunggal. Selama proses ekspor data, waktu respons rata-rata dari query halaman umum adalah 54.391 ms. Setelah fitur paging cache diaktifkan, waktu respons rata-rata adalah 525 ms. Kinerja meningkat sekitar 103 kali. Pemanfaatan CPU dan penggunaan memori secara signifikan berkurang.

Fitur paging cache secara signifikan mengurangi waktu respons dari query halaman selama proses ekspor data dan secara efektif mengurangi konsumsi sumber daya CPU dan memori.