Mengelola rencana eksekusi Aurora PostgreSQL

Manajemen rencana kueri menyediakan teknik dan fungsi untuk menambahkan, memelihara, dan meningkatkan rencana eksekusi.

Mengevaluasi performa rencana

Setelah pengoptimal mengambil rencana sebagai tidak disetujui, gunakan fungsi apg_plan_mgmt.evolve_plan_baselines untuk membandingkan rencana berdasarkan performanya yang sebenarnya. Bergantung pada hasil eksperimen performa Anda, Anda dapat mengubah status rencana dari tidak disetujui ke disetujui atau ditolak. Anda dapat memutuskan untuk menggunakan fungsi apg_plan_mgmt.evolve_plan_baselines untuk menonaktifkan rencana secara sementara jika tidak memenuhi persyaratan Anda.

Menyetujui rencana yang lebih baik

Contoh berikut menunjukkan cara mengubah status rencana terkelola menjadi disetujui menggunakan fungsi apg_plan_mgmt.evolve_plan_baselines.

SELECT apg_plan_mgmt.evolve_plan_baselines (
   sql_hash, 
   plan_hash, 
   min_speedup_factor := 1.0, 
   action := 'approve'
) 
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';

NOTICE:     rangequery (1,10000)
NOTICE:     Baseline   [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE:     Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE:     Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE:     Unapproved -> Approved
evolve_plan_baselines 
-----------------------
0
(1 row)     

Output menampilkan laporan performa untuk pernyataan rangequery dengan ikatan parameter 1 dan 10.000. Rencana baru yang belum disetujui (Baseline+1) lebih baik daripada rencana terbaik yang telah disetujui sebelumnya (Baseline). Untuk mengonfirmasi bahwa rencana yang baru sekarang berstatus Approved, periksa tampilan apg_plan_mgmt.dba_plans.

SELECT sql_hash, plan_hash, status, enabled, stmt_name 
FROM apg_plan_mgmt.dba_plans;

sql_hash  | plan_hash |  status  | enabled | stmt_name  
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t       | rangequery
1984047223 | 512284451 | Approved | t       | rangequery
(2 rows)

Rencana terkelola kini mencakup dua rencana yang disetujui yang merupakan acuan dasar rencana pernyataan. Anda juga dapat memanggil fungsi apg_plan_mgmt.set_plan_status untuk mengatur langsung bidang status rencana ke 'Approved', 'Rejected', 'Unapproved', atau 'Preferred'.

Menolak atau menonaktifkan rencana yang lebih lambat

Untuk menolak atau menonaktifkan rencana, teruskan 'reject' atau 'disable' sebagai parameter tindakan ke fungsi apg_plan_mgmt.evolve_plan_baselines. Contoh ini menonaktifkan setiap rencana Unapproved yang diambil yang lebih lambat setidaknya 10 persen dari rencana Approved terbaik untuk pernyataan.

SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash,  -- The managed statement ID
plan_hash, -- The plan ID
1.1,       -- number of times faster the plan must be 
'disable'  -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND   -- plan is Unapproved
origin = 'Automatic';       -- plan was auto-captured

Anda juga dapat secara langsung mengatur rencana ke ditolak atau dinonaktifkan. Untuk secara langsung mengatur bidang yang diaktifkan untuk rencana ke true atau false, panggil fungsi apg_plan_mgmt.set_plan_enabled. Untuk secara langsung mengatur bidang status rencana ke 'Approved', 'Rejected', 'Unapproved', atau 'Preferred', panggil fungsi apg_plan_mgmt.set_plan_status.

Memvalidasi rencana

Gunakan fungsi apg_plan_mgmt.validate_plans untuk menghapus atau menonaktifkan rencana yang tidak valid.

Rencana dapat menjadi tidak valid atau stale ketika objek yang diandalkan rencana tersebut dihapus, misalnya indeks atau tabel. Namun, rencana mungkin hanya menjadi tidak valid untuk sementara jika objek yang dihapus dibuat kembali. Jika rencana yang tidak valid dapat menjadi valid di lain waktu, mungkin Anda sebaiknya memilih untuk menonaktifkan rencana yang tidak valid atau tidak melakukan apa-apa daripada menghapusnya.

Untuk menemukan dan menghapus semua rencana yang tidak valid dan belum digunakan dalam kurun waktu seminggu, gunakan fungsi apg_plan_mgmt.validate_plans sebagai berikut.

SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete') 
FROM apg_plan_mgmt.dba_plans
WHERE last_used < (current_date - interval '7 days');

Untuk mengaktifkan atau menonaktifkan rencana secara langsung, gunakan fungsi apg_plan_mgmt.set_plan_enabled.

Memperbaiki rencana menggunakan pg_hint_plan

Pengoptimal kueri dirancang dengan baik untuk menemukan rencana yang optimal untuk semua pernyataan, dan pada sebagian besar kasus, pengoptimal menemukan rencana yang baik. Namun, terkadang Anda mungkin tahu bahwa ada rencana yang jauh lebih baik dari yang dihasilkan oleh pengoptimal. Dua cara yang disarankan agar pengoptimal menghasilkan rencana yang diinginkan adalah menggunakan ekstensi pg_hint_plan atau mengatur variabel Grand Unified Configuration (GUC) di PostgreSQL:

• Ekstensi pg_hint_plan – Menentukan "petunjuk" untuk memodifikasi cara perencana bekerja menggunakan ekstensi pg_hint_plan PostgreSQL. Untuk menginstal dan mempelajari selengkapnya tentang cara menggunakan ekstensi pg_hint_plan, lihat dokumentasi pg_hint_plan.

• Variabel GUC – Menulis ulang satu atau beberapa parameter model biaya atau parameter pengoptimal lainnya, seperti from_collapse_limit atau GEQO_threshold.

Saat Anda menggunakan salah satu teknik ini untuk memaksa pengoptimal kueri agar menggunakan suatu rencana, Anda juga dapat menggunakan manajemen rencana kueri untuk mengambil dan memberlakukan penggunaan rencana baru.

Anda dapat menggunakan ekstensi pg_hint_plan untuk mengubah urutan join, metode join, atau jalur akses untuk pernyataan SQL. Anda menggunakan komentar SQL dengan sintaksis pg_hint_plan khusus untuk memodifikasi cara pengoptimal membuat rencana. Misalnya, anggaplah pernyataan SQL yang bermasalah memiliki join dua arah.

SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;            

Kemudian, anggaplah bahwa pengoptimal memilih urutan join (t1, t2), tetapi Anda tahu bahwa urutan join (t2, t1) lebih cepat. Petunjuk berikut memaksa pengoptimal untuk menggunakan urutan join yang lebih cepat, (t2, t1). Sertakan EXPLAIN sehingga pengoptimal menghasilkan rencana untuk pernyataan SQL, tetapi tanpa menjalankan pernyataan ini. (Output tidak ditampilkan.)

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id; 

Langkah-langkah berikut menunjukkan cara menggunakan pg_hint_plan.

Untuk mengubah rencana yang dibuat oleh pengoptimal dan mengambil rencana menggunakan pg_hint_plan

1. Aktifkan mode pengambilan manual.

   SET apg_plan_mgmt.capture_plan_baselines = manual;

2. Tentukan petunjuk untuk pernyataan SQL yang ingin diamati.

   /*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
   FROM t1, t2 
   WHERE t1.id = t2.id; 

   Setelah proses ini berjalan, pengoptimal mengambil rencana dalam tampilan apg_plan_mgmt.dba_plans. Rencana yang diambil tidak mencakup sintaksis komentar pg_hint_plan khusus karena manajemen rencana kueri menormalisasi pernyataan dengan menghapus komentar awal.

3. Lihat rencana terkelola menggunakan tampilan apg_plan_mgmt.dba_plans.

   SELECT sql_hash, plan_hash, status, sql_text, plan_outline 
   FROM apg_plan_mgmt.dba_plans;

4. Atur status rencana menjadi Preferred. Tindakan ini memastikan bahwa pengoptimal memilih untuk menjalankannya dan tidak memilih dari rencana yang disetujui, saat rencana berbiaya minimum belum berstatus Approved atau Preferred.

   SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' ); 

5. Nonaktifkan pengambilan rencana manual dan berlakukan penggunaan rencana terkelola.

   SET apg_plan_mgmt.capture_plan_baselines = false;
   SET apg_plan_mgmt.use_plan_baselines = true;

   Sekarang, ketika pernyataan SQL asli berjalan, pengoptimal memilih rencana Approved atau Preferred. Jika rencana berbiaya minimum tidak berstatus Approved atau Preferred, pengoptimal akan memilih rencana Preferred.

Menghapus rencana

Rencana dihapus secara otomatis jika belum digunakan selama lebih dari sebulan, khususnya, 32 hari. Ini adalah pengaturan default untuk parameter apg_plan_mgmt.plan_retention_period. Anda dapat mengubah periode retensi rencana ke periode waktu yang lebih lama, atau ke periode waktu yang lebih pendek mulai dari nilai 1. Menentukan jumlah hari sejak rencana terakhir digunakan yang dihitung dengan mengurangi tanggal saat ini dengan tanggal last_used. Tanggal last_used adalah tanggal terbaru saat pengoptimal memilih rencana sebagai rencana berbiaya minimum atau saat rencana tersebut dijalankan. Tanggal disimpan untuk rencana dalam tampilan apg_plan_mgmt.dba_plans.

Kami menyarankan Anda menghapus rencana yang belum digunakan dalam waktu lama atau yang tidak berguna. Setiap rencana memiliki tanggal last_used yang diperbarui oleh pengoptimal setiap kali pengoptimal menjalankan rencana atau memilih rencana sebagai rencana berbiaya minimum untuk suatu pernyataan. Periksa tanggal last_used terakhir untuk mengidentifikasi rencana yang dapat Anda hapus dengan aman.

Kueri berikut menampilkan tabel tiga kolom dengan jumlah total rencana, rencana yang gagal dihapus, dan rencana yang berhasil dihapus. Kueri tersebut memiliki kueri bersarang yang merupakan contoh cara menggunakan fungsi apg_plan_mgmt.delete_plan untuk menghapus semua rencana yang belum dipilih sebagai rencana berbiaya minimum dalam 31 hari terakhir dan statusnya bukan Rejected

SELECT (SELECT COUNT(*) from apg_plan_mgmt.dba_plans) total_plans,
       COUNT(*) FILTER (WHERE result = -1) failed_to_delete,
       COUNT(*) FILTER (WHERE result = 0) successfully_deleted
       FROM (
            SELECT apg_plan_mgmt.delete_plan(sql_hash, plan_hash) as result
            FROM apg_plan_mgmt.dba_plans
            WHERE last_used < (current_date - interval '31 days')
            AND status <> 'Rejected'
            ) as dba_plans ;   

 total_plans | failed_to_delete | successfully_deleted
-------------+------------------+----------------------
           3 |                0 |                    2

Untuk informasi selengkapnya, lihat apg_plan_mgmt.delete_plan.

Untuk menghapus rencana yang tidak valid dan yang Anda perkirakan akan tetap tidak valid, gunakan fungsi apg_plan_mgmt.validate_plans. Fungsi ini memungkinkan Anda menghapus atau menonaktifkan rencana yang tidak valid. Untuk informasi selengkapnya, lihat Memvalidasi rencana.

penting

Jika Anda tidak menghapus rencana yang tidak berguna, Anda mungkin akan kehabisan memori bersama yang dialokasikan untuk manajemen rencana kueri. Untuk mengontrol berapa banyak memori yang tersedia untuk rencana terkelola, gunakan parameter apg_plan_mgmt.max_plans. Atur parameter ini di grup parameter DB kustom Anda lalu boot ulang instans DB Anda agar perubahan pada instans diterapkan. Untuk informasi selengkapnya, lihat parameter apg_plan_mgmt.max_plans.

Mengekspor dan mengimpor rencana

Anda dapat mengekspor rencana terkelola Anda dan mengimpornya ke dalam instans DB lain.

Untuk mengekspor rencana terkelola

Pengguna dapat menyalin subset tabel apg_plan_mgmt.plans ke tabel lain, lalu menyimpannya menggunakan perintah pg_dump. Berikut adalah contohnya.

CREATE TABLE plans_copy AS SELECT * 
FROM apg_plan_mgmt.plans [ WHERE predicates ] ;

% pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar

DROP TABLE apg_plan_mgmt.plans_copy;

Untuk mengimpor rencana terkelola

1. Salin file .tar dari rencana terkelola yang diekspor ke sistem tempat rencana tersebut akan dipulihkan.

2. Gunakan perintah pg_restore untuk menyalin file .tar ke dalam tabel baru.

   % pg_restore --dbname mytargetdatabase -Ft plans_copy.tar

3. Gabungkan tabel plans_copy dengan tabel apg_plan_mgmt.plans seperti yang ditunjukkan dalam contoh berikut.

   catatan

   Dalam beberapa kasus, Anda mungkin membuang dari satu versi ekstensi apg_plan_mgmt dan memulihkan ke versi yang berbeda. Dalam kasus ini, kolom di tabel rencana mungkin berbeda. Jika ya, tentukan nama kolom tersebut dengan eksplisit dan bukan menggunakan SELECT *.

   INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy
    ON CONFLICT ON CONSTRAINT plans_pkey
    DO UPDATE SET
    status = EXCLUDED.status,
    enabled = EXCLUDED.enabled,
    -- Save the most recent last_used date 
    --
    last_used = CASE WHEN EXCLUDED.last_used > plans.last_used 
    THEN EXCLUDED.last_used ELSE plans.last_used END, 
    -- Save statistics gathered by evolve_plan_baselines, if it ran:
    --
    estimated_startup_cost = EXCLUDED.estimated_startup_cost,
    estimated_total_cost = EXCLUDED.estimated_total_cost,
    planning_time_ms = EXCLUDED.planning_time_ms,
    execution_time_ms = EXCLUDED.execution_time_ms,
    total_time_benefit_ms = EXCLUDED.total_time_benefit_ms, 
    execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;

4. Muat ulang rencana terkelola ke dalam memori bersama dan hapus tabel rencana sementara.

   SELECT apg_plan_mgmt.reload(); -- refresh shared memory
   DROP TABLE plans_copy;