Mengoptimalkan penggunaan memori tinggi dalam instance

Dokumen ini mencakup cara mengidentifikasi penggunaan memori yang tinggi untuk instance Cloud SQL dan memberikan rekomendasi tentang cara memecahkan masalah terkait memori.

Untuk mempelajari cara mengonfigurasi penggunaan memori untuk instance Cloud SQL, lihat Praktik terbaik untuk mengelola penggunaan memori.

Mengidentifikasi penggunaan memori yang tinggi

Menggunakan Metrics Explorer untuk mengidentifikasi penggunaan memori

Anda dapat meninjau penggunaan memori instance dengan metrik database/memory/components.usage di Metrics Explorer.

Menggunakan Query insights untuk menganalisis rencana penjelasan untuk kueri yang memakai resource tinggi

Query insights membantu Anda mendeteksi, mendiagnosis, dan mencegah masalah performa kueri untuk database Cloud SQL. Query insights memberi Anda daftar kueri yang berjalan lama beserta rencana penjelasannya (dokumentasi PostgreSQL). Tinjau rencana penjelasan dan identifikasi bagian kueri yang memiliki metode pemindaian penggunaan memori yang tinggi. Terlepas dari waktu proses kueri, insight kueri memberi Anda rencana penjelasan untuk seluruh kueri. Identifikasi kueri kompleks yang memerlukan lebih banyak waktu sehingga Anda tahu kueri mana yang memblokir memori untuk durasi yang lebih lama.

Metode pemindaian PostgreSQL umum yang menggunakan memori tinggi meliputi:

Pemindaian heap bitmap
Pengurutan cepat
Hash join atau Hash

Penggunaan memori tinggi dan log yang relevan untuk instance berkemampuan Gemini

Jika Anda mengaktifkan Gemini, bukannya kegagalan kehabisan memori (OOM) yang menyebabkan periode nonaktif database, koneksi yang mengeksekusi kueri dengan penggunaan memori tinggi akan dihentikan sehingga mencegah periode nonaktif database. Untuk mengidentifikasi kueri default, Anda dapat memeriksa log database untuk entri berikut:

  (...timestamp….) db=postgres, user=customer FATAL: terminating connection due to administrator command

Log database Cloud SQL untuk PostgreSQL berikut ditampilkan dan menampilkan kueri penggunaan memori tinggi yang dihentikan untuk mencegah OOM. Kueri ini adalah versi kueri asli yang dinormalkan:

  db=postgres,user=customer LOG:  postgres process with PID 1734 for the query "SELECT COUNT(product) AS item_count FROM test_table WHERE product_type = $1 AND product LIKE $2 AND c6_2 IN ($3,$4,$5,$6,$7)" has been cancelled.

Notifikasi juga ditampilkan di halaman Instance Cloud SQL untuk peristiwa berikut:

Penggunaan memori instance selama 24 jam terakhir.
Daftar kueri yang dinormalkan, yang telah dibatalkan dalam 24 jam terakhir.
Link ke dokumentasi Google tentang mengoptimalkan penggunaan memori.

Penggunaan Memori Tinggi - Rekomendasi

Rekomendasi berikut membahas masalah umum terkait memori. Jika instance terus menggunakan memori dalam jumlah besar, kemungkinan besar instance akan mengalami masalah out of memory. Jika permintaan memori dari PostgreSQL atau proses lainnya menyebabkan sistem kehabisan memori, Anda akan melihat pesan kernel Out of Memory di log PostgreSQL dan instance PostgreSQL pada akhirnya dihentikan. Contoh:

Out of Memory: Killed process 12345 (postgres)

Instance paling umum saat Anda melihat masalah OOM adalah saat nilai work_mem lebih tinggi dengan jumlah koneksi aktif yang tinggi. Oleh karena itu, jika Anda sering mendapatkan OOM atau menghindari OOM dalam instance Cloud SQL untuk PostgreSQL, Anda harus mempertimbangkan untuk mengikuti rekomendasi berikut:

Setel work_mem

Kueri yang menggunakan pengurutan cepat lebih cepat daripada kueri yang menggunakan pengurutan penggabungan eksternal. Namun, aktivitas pertama dapat menyebabkan kehabisan memori. Untuk mengatasi masalah ini, setel nilai work_mem dengan cukup wajar sehingga dapat menyeimbangkan keduanya, yaitu operasi pengurutan yang terjadi dalam memori dan disk. Anda juga dapat mempertimbangkan untuk menyetel work_mem pada tingkat sesi, bukan menyetelnya untuk seluruh instance.
Memantau sesi aktif

Setiap koneksi menggunakan sejumlah memori tertentu. Gunakan kueri berikut untuk memeriksa jumlah koneksi aktif:
```
  SELECT
    state,
    usename,
    count(1)
  FROM
    pg_stat_activity
  WHERE
    pid <> pg_backend_pid()
  GROUP BY
    state,
    usename
  ORDER BY
    1;
```
Jika Anda memiliki sesi aktif dalam jumlah besar, analisis penyebab utama untuk jumlah sesi aktif yang tinggi; misalnya, kunci transaksi.
Setel shared_buffers

Jika shared_buffers disetel ke nilai yang lebih tinggi, pertimbangkan untuk mengurangi nilai shared_buffers agar memori dapat digunakan untuk operasi lain, seperti work_mem, atau untuk membuat koneksi baru.

Rasio cache ditemukan

PostgreSQL umumnya mencoba menyimpan data yang paling sering Anda akses di cache. Ketika data diminta oleh klien, jika telah di-cache di buffer bersama, data tersebut akan langsung diberikan ke klien. Hal ini disebut cache ditemukan. Jika data tidak ada di buffer bersama, data akan diambil terlebih dahulu ke buffer bersama dari disk, lalu diberikan ke klien. Hal ini disebut cache tidak ditemukan. Rasio cache ditemukan mengukur jumlah permintaan konten yang telah ditangani oleh cache dibandingkan dengan permintaan yang diterima. Jalankan kueri berikut untuk memeriksa rasio cache ditemukan untuk permintaan tabel di instance PostgreSQL:
```
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
  pg_statio_user_tables;
```
Jalankan kueri berikut untuk memeriksa rasio cache ditemukan untuk permintaan indeks di instance PostgreSQL:
```
  SELECT
    sum(idx_blks_read) as idx_read,
    sum(idx_blks_hit)  as idx_hit,
    (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
  FROM
    pg_statio_user_indexes;
```
Pada umumnya, 95 hingga 99% rasio cache ditemukan dianggap sebagai nilai yang baik.
Mengaktifkan halaman besar Cloud SQL untuk PostgreSQL secara default telah mengaktifkan huge_pages untuk pengelolaan memori yang lebih baik. Kami merekomendasikan Anda untuk mengaktifkannya. Untuk mempelajari huge_pages lebih lanjut, lihat dokumentasi PostreSQL.
Setel max_locks_per_transaction

Nilai max_locks_per_transaction menunjukkan jumlah objek database yang dapat dikunci secara bersamaan. Dalam sebagian besar kasus, nilai default 64 sudah cukup. Namun, jika Anda menangani set data yang besar, Anda mungkin akan mendapatkan OOM. Pertimbangkan untuk meningkatkan nilai max_locks_per_transaction dengan cukup tinggi untuk menghindari OOM.

Catatan: Penguncian tingkat objek terjadi di memori pada transaksi yang sedang berlangsung, meningkatkan nilai max_prepared_transactions secara tidak wajar dapat menyebabkan instance database meminta lebih banyak memori bersama,

Nilai max_locks_per_transaction harus berupa objek max_locks_per_transaction * (max_connections + max_prepared_transactions). Artinya, jika Anda memiliki 300 ribu objek, dan jika nilai max_connections adalah 200, maka max_locks_per_transaction harus 1500.
Setel max_pred_locks_per_transaction

Transaksi mungkin gagal jika Anda memiliki klien yang menyentuh banyak tabel yang berbeda dalam satu transaksi serialisasi. Dalam skenario tersebut, pertimbangkan untuk meningkatkan max_pred_locks_per_transaction ke nilai yang cukup tinggi. Seperti max_locks_per_transaction, max_pred_locks_per_transaction juga menggunakan memori bersama sehingga jangan menetapkan nilai tinggi yang tidak wajar.
Jika penggunaan memori masih tinggi dan Anda merasa kueri tersebut adalah traffic yang sah, pertimbangkan untuk meningkatkan jumlah resource memori dalam instance Anda untuk menghindari error atau periode nonaktif database.