Gambaran umum manajemen rencana kueri Aurora PostgreSQL - Amazon Aurora
Pernyataan SQL yang didukungBatasanTerminologiVersi yang tersediaMengaktifkan manajemen rencana kueriMeningkatkan manajemen rencana kueriMenonaktifkan manajemen rencana kueri Aurora PostgreSQL

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Gambaran umum manajemen rencana kueri Aurora PostgreSQL

Manajemen rencana kueri Aurora PostgreSQL dirancang untuk memastikan stabilitas rencana terlepas dari perubahan pada basis data yang dapat menyebabkan regresi rencana kueri. Regresi rencana kueri terjadi ketika pengoptimal memilih rencana yang kurang optimal untuk pernyataan SQL yang diberikan setelah perubahan sistem atau basis data. Perubahan pada statistik, pembatasan, pengaturan lingkungan, pengikatan parameter kueri, dan peningkatan ke mesin basis data PostgreSQL semuanya dapat menyebabkan regresi rencana.

Dengan manajemen rencana kueri Aurora PostgreSQL, Anda dapat mengendalikan bagaimana dan kapan rencana eksekusi kueri akan berubah. Manfaat manajemen rencana kueri Aurora PostgreSQL meliputi yang berikut ini.

  • Meningkatkan stabilitas rencana dengan memaksa pengoptimal untuk memilih dari sejumlah kecil rencana yang sudah diketahui bagus.

  • Mengoptimalkan rencana secara terpusat lalu mendistribusikan rencana terbaik secara global.

  • Mengidentifikasi indeks yang tidak digunakan dan menilai dampak dari membuat atau menghapus indeks.

  • Secara otomatis mendeteksi rencana biaya minimum baru yang ditemukan oleh pengoptimal.

  • Mencoba fitur-fitur pengoptimal baru dengan risiko lebih rendah karena Anda dapat memilih untuk hanya menyetujui perubahan rencana yang meningkatkan performa.

Anda dapat menggunakan alat yang disediakan oleh manajemen rencana kueri secara proaktif untuk menentukan rencana terbaik untuk kueri tertentu. Atau, Anda dapat menggunakan manajemen rencana kueri untuk bereaksi terhadap perubahan keadaan dan menghindari regresi rencana. Untuk informasi selengkapnya, lihat Praktik terbaik untuk manajemen rencana kueri Aurora PostgreSQL.

Pernyataan SQL yang didukung

Manajemen rencana kueri mendukung jenis pernyataan SQL berikut.

  • Pernyataan SELECT, INSERT, UPDATE, atau DELETE, terlepas dari kompleksitasnya.

  • Pernyataan yang disiapkan. Untuk informasi selengkapnya, lihat PREPARE dalam dokumentasi PostgreSQL.

  • Pernyataan dinamis, termasuk yang dijalankan dalam mode segera. Untuk informasi selengkapnya, lihat Dynamic SQL dan EXECUTE IMMEDIATE dalam dokumentasi PostgreSQL.

  • Perintah dan pernyataan SQL tersemat. Untuk informasi selengkapnya, lihat Embedded SQL Commands dalam dokumentasi PostgreSQL.

  • Pernyataan di dalam fungsi bernama. Untuk informasi selengkapnya, lihat CREATE FUNCTION dalam dokumentasi PostgreSQL.

  • Pernyataan yang berisi tabel temp.

  • Pernyataan di dalam prosedur dan DO-block.

Anda dapat menggunakan manajemen rencana kueri dengan EXPLAIN dalam mode manual untuk menangkap rencana tanpa benar-benar menjalankannya. Untuk informasi selengkapnya, lihat Menganalisis rencana yang dipilih pengoptimisasi. Untuk mempelajari selengkapnya tentang mode manajemen rencana kueri (manual, otomatis), lihat Mengambil rencana eksekusi Aurora PostgreSQL.

Manajemen rencana kueri Aurora PostgreSQL mendukung semua fitur bahasa PostgreSQL, termasuk tabel yang dipartisi, pewarisan, keamanan tingkat baris, dan ekspresi tabel umum rekursif (). CTEs Untuk mempelajari selengkapnya tentang fitur bahasa PostgreSQL ini, lihat Table Partitioning, Row Security Policies, dan WITH Queries (Common Table Expressions) serta topik lainnya dalam dokumentasi PostgreSQL.

Untuk informasi tentang berbagai versi fitur manajemen rencana kueri Aurora PostgreSQL, lihat Aurora PostgreSQL versi ekstensi apg_plan_mgmt dalam Catatan Rilis untuk Aurora PostgreSQL.

Batasan manajemen rencana kueri

Rilis manajemen rencana kueri Aurora PostgreSQL saat ini memiliki batasan berikut.

  • Rencana tidak ditangkap untuk pernyataan yang mereferensikan relasi sistem – Pernyataan yang mereferensikan relasi sistem, seperti pg_class, tidak ditangkap. Hal ini berlaku berdasarkan desain untuk mencegah sejumlah besar rencana yang dihasilkan sistem yang digunakan secara internal agar tidak ditangkap. Hal ini juga berlaku untuk tabel sistem dalam tampilan.

  • Kelas instans DB yang lebih besar mungkin diperlukan untuk cluster Aurora PostgreSQL DB Anda — Bergantung pada beban kerja, manajemen rencana kueri mungkin memerlukan kelas instans DB yang memiliki lebih dari 2 v. CPUs Jumlah max_worker_processes dibatasi oleh ukuran kelas instans DB. Jumlah max_worker_processes yang disediakan oleh kelas instans DB 2-vCPU (db.t3.medium, misalnya) mungkin tidak cukup untuk beban kerja tertentu. Kami menyarankan Anda memilih kelas instans DB dengan lebih dari 2 v CPUs untuk klaster Aurora PostgreSQL DB Anda jika Anda menggunakan manajemen rencana kueri.

    Ketika kelas instans DB tidak dapat mendukung beban kerja, manajemen rencana kueri memunculkan pesan kesalahan seperti berikut ini. 

    WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.

    Dalam hal ini, Anda harus menaikkan skala klaster DB Aurora PostgreSQL Anda ke ukuran kelas instans DB dengan lebih banyak memori. Untuk informasi selengkapnya, lihat Mesin DB yang didukung untuk kelas instans DB.

  • Rencana yang sudah disimpan dalam sesi tidak terpengaruh – Manajemen rencana kueri menyediakan cara untuk memengaruhi rencana kueri tanpa mengubah kode aplikasi. Namun, ketika rencana generik sudah disimpan dalam sesi yang ada dan jika Anda ingin mengubah rencana kueri, maka Anda harus terlebih dahulu mengatur plan_cache_mode ke force_custom_plan dalam grup parameter klaster DB.

  • queryid di apg_plan_mgmt.dba_plans dan pg_stat_statements dapat menyimpang ketika:

    • Objek dihapus dan dibuat ulang setelah disimpan dalam apg_plan_mgmt.dba_plans.

    • Tabel apg_plan_mgmt.plans diimpor dari klaster lain.

Untuk informasi tentang berbagai versi fitur manajemen rencana kueri Aurora PostgreSQL, lihat Aurora PostgreSQL versi ekstensi apg_plan_mgmt dalam Catatan Rilis untuk Aurora PostgreSQL.

Terminologi manajemen rencana kueri

Istilah berikut digunakan di seluruh topik ini:

pernyataan terkelola

Pernyataan SQL yang ditangkap oleh pengoptimal dalam manajemen rencana kueri. Pernyataan terkelola memiliki satu atau beberapa rencana eksekusi kueri yang disimpan dalam tampilan apg_plan_mgmt.dba_plans.

acuan dasar rencana

Kumpulan rencana yang disetujui untuk pernyataan terkelola tertentu. Artinya, semua rencana untuk pernyataan terkelola yang telah “Disetujui” untuk kolom status-nya dalam tampilan dba_plan.

riwayat rencana

Kumpulan semua rencana yang ditangkap untuk suatu pernyataan terkelola tertentu. Riwayat rencana berisi semua rencana yang ditangkap untuk pernyataan tersebut, terlepas dari statusnya.

regresi rencana kueri

Kasus ketika pengoptimal memilih rencana yang kurang optimal dibandingkan dengan sebelum perubahan tertentu dilakukan pada lingkungan basis data, seperti versi PostgreSQL baru atau perubahan statistik.

Versi manajemen rencana kueri Aurora PostgreSQL

Manajemen rencana kueri didukung oleh semua rilis Aurora PostgreSQL yang tersedia saat ini. Untuk informasi selengkapnya, lihat Daftar pembaruan Amazon Aurora PostgreSQL dalam Catatan Rilis untuk Aurora PostgreSQL.

Fungsionalitas manajemen rencana kueri ditambahkan ke klaster DB Aurora PostgreSQL Anda saat Anda menginstal ekstensi apg_plan_mgmt. Berbagai versi Aurora PostgreSQL mendukung versi ekstensi apg_plan_mgmt yang berbeda-beda. Kami menyarankan agar Anda meningkatkan ekstensi manajemen rencana kueri ke rilis terbaru untuk versi Aurora PostgreSQL Anda.

catatan

Untuk catatan rilis untuk setiap versi ekstensi apg_plan_mgmt, lihat Aurora PostgreSQL versi ekstensi apg_plan_mgmt dalam Catatan Rilis untuk Aurora PostgreSQL.

Anda dapat mengidentifikasi versi yang berjalan di klaster Anda dengan menghubungkan ke instans menggunakan psql lalu menggunakan perintah meta \dx untuk menampilkan daftar ekstensi seperti yang ditunjukkan berikut.

labdb=> \dx
                       List of installed extensions
     Name      | Version |    Schema     |                            Description
---------------+---------+---------------+-------------------------------------------------------------------
 apg_plan_mgmt | 1.0     | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
 plpgsql       | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

Output menunjukkan bahwa klaster ini menggunakan ekstensi versi 1.0. Hanya versi apg_plan_mgmt tertentu yang tersedia untuk versi Aurora PostgreSQL tertentu. Dalam beberapa kasus, Anda mungkin perlu meningkatkan klaster DB Aurora PostgreSQL ke rilis minor baru atau menerapkan patch sehingga Anda dapat meningkatkan ke manajemen rencana kueri versi terbaru. apg_plan_mgmt versi 1.0 yang ditampilkan dalam output berasal dari klaster DB Aurora PostgreSQL versi 10.17, yang tidak memiliki apg_plan_mgmt versi lebih baru. Dalam hal ini, klaster DB Aurora PostgreSQL harus ditingkatkan ke versi PostgreSQL yang lebih baru.

Untuk informasi selengkapnya tentang meningkatkan klaster DB Aurora PostgreSQL Anda ke PostgreSQL versi baru, lihat Pembaruan mesin database untuk Amazon Aurora Postgre SQL.

Untuk mempelajari cara meningkatkan ekstensi apg_plan_mgmt, lihat Meningkatkan manajemen rencana kueri Aurora PostgreSQL.

Mengaktifkan manajemen rencana kueri Aurora PostgreSQL

Penyiapan manajemen rencana kueri untuk klaster DB Aurora PostgreSQL Anda memerlukan penginstalan ekstensi dan perubahan beberapa pengaturan parameter klaster DB. Anda memerlukan izin rds_superuser untuk menginstal ekstensi apg_plan_mgmt dan mengaktifkan fitur untuk klaster DB Aurora PostgreSQL.

Saat ekstensi ini diinstal, sebuah peran baru, apg_plan_mgmt, akan dibuat. Peran ini memungkinkan pengguna basis data melihat, mengelola, dan memelihara rencana kueri. Sebagai administrator dengan hak akses rds_superuser, pastikan untuk memberikan peran apg_plan_mgmt kepada pengguna basis data sesuai kebutuhan.

Hanya pengguna dengan peran rds_superuser yang dapat menyelesaikan prosedur berikut. rds_superuser diperlukan untuk membuat ekstensi apg_plan_mgmt dan peran apg_plan_mgmt. Pengguna harus diberi peran apg_plan_mgmt untuk mengelola ekstensi apg_plan_mgmt.

Mengaktifkan manajemen rencana kueri untuk klaster DB Aurora PostgreSQL Anda

Langkah-langkah berikut mengaktifkan manajemen rencana kueri untuk semua pernyataan SQL yang dikirimkan ke klaster DB Aurora PostgreSQL. Hal ini dikenal sebagai mode otomatis. Untuk mempelajari selengkapnya tentang perbedaan di antara mode, lihat Mengambil rencana eksekusi Aurora PostgreSQL.

  1. Buka konsol Amazon RDS di https://console.aws.amazon.com/rds/.

  2. Buat grup parameter klaster DB kustom untuk klaster DB Aurora PostgreSQL Anda. Anda perlu mengubah parameter tertentu untuk mengaktifkan manajemen rencana kueri dan mengatur perilakunya. Untuk informasi selengkapnya, lihat Membuat grup parameter DB di Aurora.

  3. Buka grup parameter klaster DB kustom dan atur parameter rds.enable_plan_management ke 1, seperti yang ditunjukkan pada gambar berikut ini.

    Gambar grup parameter cluster DB.

    Untuk informasi selengkapnya, lihat Memodifikasi parameter dalam grup parameter cluster DB di Amazon Aurora.

  4. Buat grup parameter DB kustom yang dapat Anda gunakan untuk mengatur parameter rencana kueri di tingkat instans. Untuk informasi selengkapnya, lihat Membuat grup parameter cluster DB di Amazon Aurora.

  5. Ubah instans penulis klaster DB Aurora PostgreSQL untuk menggunakan grup parameter DB kustom. Untuk informasi selengkapnya, lihat Memodifikasi instans DB dalam klaster DB.

  6. Ubah klaster DB Aurora PostgreSQL untuk menggunakan grup parameter klaster DB kustom. Untuk informasi selengkapnya, lihat Memodifikasi klaster DB dengan menggunakan konsol, CLI, dan API.

  7. Boot ulang instans DB Anda untuk mengaktifkan pengaturan grup parameter kustom.

  8. Hubungkan ke titik akhir instans DB untuk klaster DB Aurora PostgreSQL Anda menggunakan psql atau pgAdmin. Contoh berikut menggunakan akun postgres default untuk peran rds_superuser.

    psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db

  9. Buat ekstensi apg_plan_mgmt untuk instans DB Anda, seperti yang ditunjukkan berikut ini.

    labdb=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
    Tip

    Instal ekstensi apg_plan_mgmt dalam basis data templat untuk aplikasi Anda. Basis data templat default diberi nama template1. Untuk mempelajari selengkapnya, lihat Template Databases dalam dokumentasi PostgreSQL.

  10. Ubah parameter apg_plan_mgmt.capture_plan_baselines menjadi automatic. Pengaturan ini akan menyebabkan pengoptimal menghasilkan rencana untuk setiap pernyataan SQL yang direncanakan atau dijalankan dua kali atau lebih.

    catatan

    Manajemen rencana kueri juga memiliki mode manual yang dapat Anda gunakan untuk pernyataan SQL tertentu. Untuk mempelajari selengkapnya, lihat Mengambil rencana eksekusi Aurora PostgreSQL.

  11. Ubah nilai parameter apg_plan_mgmt.use_plan_baselines menjadi "on". Parameter ini menyebabkan pengoptimal memilih rencana untuk pernyataan dari acuan dasar rencananya. Untuk mempelajari selengkapnya, lihat Menggunakan rencana terkelola Aurora PostgreSQL.

    catatan

    Anda dapat mengubah nilai salah satu parameter dinamis ini untuk sesi tanpa perlu mem-boot ulang instans.

Ketika pengaturan manajemen rencana kueri Anda selesai, pastikan untuk memberikan peran apg_plan_mgmt kepada setiap pengguna basis data yang perlu melihat, mengelola, atau memelihara rencana kueri.

Meningkatkan manajemen rencana kueri Aurora PostgreSQL

Kami menyarankan agar Anda meningkatkan ekstensi manajemen rencana kueri ke rilis terbaru untuk versi Aurora PostgreSQL Anda.

  1. Hubungkan ke instans penulis klaster DB Aurora PostgreSQL Anda sebagai pengguna yang memiliki hak akses rds_superuser. Jika Anda menyimpan nama default saat menyiapkan instans, Anda akan terhubung sebagai postgres. Contoh ini menunjukkan cara menggunakan psql, tetapi Anda juga dapat menggunakan pgAdmin jika mau.

    psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

  2. Jalankan kueri berikut untuk meningkatkan ekstensi.

    ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';

  3. Gunakan fungsi apg_plan_mgmt.validate_plans untuk memperbarui hash semua rencana. Pengoptimal akan memvalidasi semua rencana yang Disetujui, Tidak Disetujui, dan Ditolak untuk memastikan bahwa rencana tersebut masih layak untuk ekstensi versi baru. 

    SELECT apg_plan_mgmt.validate_plans('update_plan_hash');

    Untuk mempelajari selengkapnya tentang penggunaan fungsi ini, lihat Memvalidasi rencana.

  4. Gunakan fungsi apg_plan_mgmt.reload untuk me-refresh rencana apa pun di memori bersama dengan rencana yang divalidasi dari tampilan dba_plans. 

    SELECT apg_plan_mgmt.reload();

Untuk mempelajari selengkapnya tentang semua fungsi yang tersedia untuk manajemen rencana kueri, lihat Referensi fungsi untuk manajemen rencana kueri Aurora PostgreSQL.

Menonaktifkan manajemen rencana kueri Aurora PostgreSQL

Anda dapat menonaktifkan manajemen rencana kueri setiap saat dengan menonaktifkan apg_plan_mgmt.use_plan_baselines dan apg_plan_mgmt.capture_plan_baselines. 

labdb=> SET apg_plan_mgmt.use_plan_baselines = off;

labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;