IO:BufFileRead dan IO:BufFileWrite

Peristiwa IO:BufFileRead dan IO:BufFileWrite terjadi saat Aurora PostgreSQL membuat file sementara. Saat operasi membutuhkan lebih banyak memori daripada yang saat ini ditentukan oleh parameter memori kerja, operasi akan menulis data sementara ke penyimpanan persisten. Operasi ini terkadang disebut "spilling to disk".

Versi mesin yang didukung

Informasi peristiwa tunggu ini didukung untuk semua versi Aurora PostgreSQL.

Konteks

IO:BufFileRead dan IO:BufFileWrite berkaitan dengan area memori kerja dan area memori kerja pemeliharaan. Untuk informasi selengkapnya tentang area memori lokal ini, lihat Area memori kerja dan Area memori kerja pemeliharaan.

Nilai default untuk work_mem adalah 4 MB. Jika satu sesi melakukan operasi secara paralel, setiap pekerja yang menangani paralelisme menggunakan memori 4 MB. Untuk alasan ini, atur work_mem dengan hati-hati. Jika Anda meningkatkan nilai terlalu banyak, basis data yang menjalankan banyak sesi mungkin mengonsumsi terlalu banyak memori. Jika Anda menetapkan nilai terlalu rendah, Aurora PostgreSQL akan membuat file sementara di penyimpanan lokal. Disk I/O untuk file sementara ini dapat mengurangi performa.

Jika Anda mengamati urutan peristiwa berikut, basis data mungkin menghasilkan file sementara:

1. Penurunan ketersediaan secara tiba-tiba dan tajam

2. Pemulihan cepat untuk ruang kosong

Anda mungkin juga melihat pola "gergaji". Pola ini dapat menunjukkan bahwa basis data membuat file kecil secara terus-menerus.

Kemungkinan penyebab peningkatan peristiwa tunggu

Secara umum, peristiwa tunggu ini disebabkan oleh operasi yang mengonsumsi lebih banyak memori daripada yang dialokasikan oleh parameter work_mem atau maintenance_work_mem. Untuk mengompensasi, operasi menulis ke file sementara. Penyebab umum peristiwa IO:BufFileRead dan IO:BufFileWrite mencakup hal berikut:

Kueri yang membutuhkan lebih banyak memori daripada yang ada di area memori kerja

Kueri dengan karakteristik berikut menggunakan area memori kerja:

• Sambungan hash

• Klausa ORDER BY

• Klausa GROUP BY

• DISTINCT

• Fungsi jendela

• CREATE TABLE AS SELECT

• Penyegaran tampilan terwujud

Pernyataan yang membutuhkan lebih banyak memori daripada yang ada di area memori kerja pemeliharaan

Pernyataan berikut menggunakan area memori kerja pemeliharaan:

• CREATE INDEX

• CLUSTER

Tindakan

Kami merekomendasikan berbagai tindakan, tergantung pada penyebab peristiwa tunggu Anda.

Topik

• Mengidentifikasi masalah
• Memeriksa kueri gabungan
• Memeriksa kueri ORDER BY dan GROUP BY
• Mencegah penggunaan operasi DISTINCT
• Mempertimbangkan untuk menggunakan fungsi jendela alih-alih fungsi GROUP BY
• Menyelidiki tampilan terwujud dan pernyataan CTAS
• Menggunakan pg_repack saat Anda membuat indeks
• Meningkatkan maintenance_work_mem saat Anda mengelompokkan tabel
• Menyetel memori untuk mencegah IO:BufFileRead dan IO:BufFileWrite

Mengidentifikasi masalah

Asumsikan situasi saat Wawasan Performa tidak diaktifkan dan Anda menduga bahwa IO:BufFileRead dan IO:BufFileWrite terjadi lebih sering daripada biasanya. Lakukan hal berikut:

1. Periksa metrik FreeLocalStorage di Amazon CloudWatch.

2. Cari pola gergaji, yang merupakan serangkaian paku bergerigi.

Pola gergaji menunjukkan konsumsi cepat dan pelepasan penyimpanan, yang sering dikaitkan dengan file sementara. Jika Anda melihat pola ini, aktifkan Wawasan Performa. Saat menggunakan Wawasan Performa, Anda dapat mengidentifikasi waktu terjadinya peristiwa tunggu dan kueri yang terkait dengannya. Solusi Anda bergantung pada kueri spesifik yang menyebabkan peristiwa.

Sebagai alternatif, atur parameter log_temp_files. Parameter ini mencatat semua kueri yang menghasilkan lebih dari ambang batas KB file sementara. Jika nilainya 0, Aurora PostgreSQL mencatat semua file sementara. Jika nilainya 1024, Aurora PostgreSQL mencatat semua kueri yang menghasilkan file sementara berukuran lebih besar dari 1 MB. Untuk informasi selengkapnya tentang log_temp_files, lihat Pencatatan dan Pelaporan Kesalahan dalam dokumentasi PostgreSQL.

Memeriksa kueri gabungan

Aplikasi Anda mungkin menggunakan gabungan. Misalnya, kueri berikut menggabungkan empat tabel.

SELECT * 
       FROM order 
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = order.customer_id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

Kemungkinan penyebab lonjakan penggunaan file sementara adalah masalah dalam kueri itu sendiri. Misalnya, klausa yang rusak mungkin tidak memfilter gabungan dengan benar. Pertimbangkan gabungan inti kedua dalam contoh berikut.

SELECT * 
       FROM order
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = customer.id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

Kueri sebelumnya secara keliru menggabungkan customer.id ke customer.id, sehingga memberikan hasil perkalian Cartesian antara setiap pelanggan dan setiap pesanan. Jenis gabungan yang tak terduga ini menghasilkan file sementara yang besar. Tergantung pada ukuran tabel, kueri Cartesian bahkan dapat mengisi penyimpanan. Aplikasi Anda dapat memiliki gabungan Cartesian jika kondisi berikut terpenuhi:

• Anda melihat penurunan besar dan tajam dalam ketersediaan penyimpanan, yang diikuti oleh pemulihan cepat.

• Tidak ada indeks yang dibuat.

• Tidak ada pernyataan CREATE TABLE FROM SELECT yang dikeluarkan.

• Tidak ada tampilan terwujud yang disegarkan.

Untuk melihat apakah tabel sedang digabungkan menggunakan kunci yang tepat, periksa kueri dan petunjuk pemetaan relasional objek Anda. Perlu diperhatikan bahwa kueri tertentu dari aplikasi Anda tidak dipanggil sepanjang waktu, dan beberapa kueri dihasilkan secara dinamis.

Memeriksa kueri ORDER BY dan GROUP BY

Dalam beberapa kasus, klausa ORDER BY dapat menghasilkan file sementara yang berlebihan. Pertimbangkan panduan berikut:

• Hanya sertakan kolom dalam klausa ORDER BY saat perlu diurutkan. Pedoman ini sangat penting untuk kueri yang menampilkan ribuan baris dan menentukan banyak kolom dalam klausa ORDER BY.

• Pertimbangkan untuk membuat indeks guna mempercepat klausa ORDER BY saat klausa cocok dengan kolom yang memiliki urutan naik atau turun yang sama. Indeks sebagian lebih disukai karena lebih kecil. Indeks yang lebih kecil lebih cepat untuk dibaca dan dilewati.

• Jika Anda membuat indeks untuk kolom yang dapat menerima nilai kosong, pertimbangkan apakah Anda ingin nilai kosong disimpan di akhir atau di awal indeks.

  Jika memungkinkan, kurangi jumlah baris yang perlu diurutkan dengan memfilter set hasil. Jika Anda menggunakan pernyataan klausa atau subkueri WITH, perlu diperhatikan bahwa kueri dalam menghasilkan kumpulan hasil, lalu meneruskannya ke kueri luar. Semakin banyak baris yang dapat difilter kueri, semakin sedikit urutan yang perlu dilakukan kueri.

• Jika Anda tidak perlu mendapatkan set hasil lengkap, gunakan klausa LIMIT. Misalnya, jika Anda hanya menginginkan lima baris teratas, kueri yang menggunakan klausa LIMIT akan berhenti memberikan hasil. Dengan cara ini, kueri membutuhkan lebih sedikit memori dan file sementara.

Kueri yang menggunakan klausa GROUP BY juga dapat memerlukan file sementara. Kueri GROUP BY meringkas nilai dengan menggunakan fungsi seperti berikut:

• COUNT

• AVG

• MIN

• MAX

• SUM

• STDDEV

Untuk menyetel kueri GROUP BY, ikuti rekomendasi untuk kueri ORDER BY.

Mencegah penggunaan operasi DISTINCT

Jika memungkinkan, jangan gunakan operasi DISTINCT untuk menghapus baris duplikat. Semakin banyak baris duplikat yang tidak perlu, yang ditampilkan oleh kueri Anda, operasi DISTINCT menjadi semakin mahal. Jika memungkinkan, tambahkan filter dalam klausa WHERE meskipun Anda menggunakan filter yang sama untuk tabel yang berbeda. Memfilter kueri dan menggabungkan dengan benar akan meningkatkan performa Anda serta mengurangi penggunaan sumber daya. Hal tersebut juga mencegah laporan dan hasil yang salah.

Jika Anda perlu menggunakan DISTINCT untuk beberapa baris dari tabel yang sama, pertimbangkan untuk membuat indeks komposit. Mengelompokkan beberapa kolom dalam indeks dapat meningkatkan waktu untuk mengevaluasi baris yang berbeda. Selain itu, jika menggunakan Amazon Aurora PostgreSQL versi 10 atau yang lebih tinggi, Anda dapat menghubungkan statistik di antara beberapa kolom dengan menggunakan perintah CREATE STATISTICS.

Mempertimbangkan untuk menggunakan fungsi jendela alih-alih fungsi GROUP BY

Dengan menggunakan GROUP BY, Anda mengubah set hasil, lalu mengambil hasil agregat. Dengan menggunakan fungsi jendela, Anda mengumpulkan data tanpa mengubah set hasil. Fungsi jendela menggunakan klausa OVER untuk melakukan perhitungan di seluruh set yang ditentukan oleh kueri, dengan menghubungkan satu baris dengan yang lain. Anda dapat menggunakan semua fungsi GROUP BY dalam fungsi jendela, tetapi juga menggunakan fungsi seperti berikut:

• RANK

• ARRAY_AGG

• ROW_NUMBER

• LAG

• LEAD

Untuk meminimalkan jumlah file sementara yang dihasilkan oleh fungsi jendela, hapus duplikasi untuk set hasil yang sama saat Anda membutuhkan dua agregasi yang berbeda. Pertimbangkan kueri berikut.

SELECT sum(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as sum_salary
     , avg(salary) OVER (PARTITION BY dept ORDER BY salary ASC) as avg_salary
  FROM empsalary;

Anda dapat menulis ulang kueri dengan klausa WINDOW sebagai berikut:

SELECT sum(salary) OVER w as sum_salary
         , avg(salary) OVER w as_avg_salary
    FROM empsalary
  WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

Secara default, perencana eksekusi Aurora PostgreSQL menggabungkan simpul serupa, sehingga tidak menggandakan operasi. Namun, dengan menggunakan deklarasi eksplisit untuk blok jendela, Anda dapat mempertahankan kueri dengan lebih mudah. Anda juga dapat meningkatkan performa dengan mencegah duplikasi.

Menyelidiki tampilan terwujud dan pernyataan CTAS

Saat tampilan terwujud disegarkan, kueri akan dijalankan. Kueri ini dapat berisi operasi seperti GROUP BY, ORDER BY, atau DISTINCT. Selama penyegaran, Anda mungkin mengamati sejumlah besar file sementara dan peristiwa tunggu IO:BufFileWrite dan IO:BufFileRead. Demikian pula, saat Anda membuat tabel berdasarkan pernyataan SELECT, pernyataan CREATE TABLE tersebut menjalankan kueri. Untuk mengurangi file sementara yang dibutuhkan, optimalkan kueri.

Menggunakan pg_repack saat Anda membuat indeks

Saat Anda membuat indeks, mesin mengurutkan set hasil. Saat tabel bertambah besar, dan karena nilai di kolom yang diindeks menjadi lebih beragam, file sementara membutuhkan lebih banyak ruang. Dalam kebanyakan kasus, Anda tidak dapat mencegah pembuatan file sementara untuk tabel besar tanpa memodifikasi area memori kerja pemeliharaan. Untuk informasi selengkapnya, lihat Area memori kerja pemeliharaan.

Solusi yang memungkinkan saat membuat ulang indeks besar adalah menggunakan alat pg_repack. Untuk informasi selengkapnya, lihat Mengatur ulang tabel di basis data PostgreSQL dengan kunci minimal dalam dokumentasi pg_repack.

Meningkatkan maintenance_work_mem saat Anda mengelompokkan tabel

Perintah CLUSTER mengelompokkan tabel yang ditentukan oleh table_name berdasarkan indeks yang ada yang ditentukan oleh index_name. Aurora PostgreSQL secara fisik membuat ulang tabel agar sesuai dengan urutan indeks yang diberikan.

Saat penyimpanan magnetik lazim, klaster menjadi umum karena throughput penyimpanan terbatas. Sekarang penyimpanan berbasis SSD sudah umum, klaster menjadi kurang populer. Namun, jika mengelompokkan tabel, Anda masih dapat meningkatkan sedikit performa tergantung pada ukuran tabel, indeks, kueri, dan banyak lagi.

Jika Anda menjalankan perintah CLUSTER dan mengamati peristiwa tunggu IO:BufFileWrite dan IO:BufFileRead, sesuaikan maintenance_work_mem. Tingkatkan ukuran memori ke jumlah yang cukup besar. Nilai tinggi berarti mesin dapat menggunakan lebih banyak memori untuk operasi klaster.

Menyetel memori untuk mencegah IO:BufFileRead dan IO:BufFileWrite

Dalam situasi tertentu, Anda perlu menyetel memori. Tujuannya adalah menyeimbangkan persyaratan berikut:

• Nilai work_mem (lihat Area memori kerja)

• Memori yang tersisa setelah memangkas nilai shared_buffers (lihat Pool buffer)

• Koneksi maksimum yang dibuka dan digunakan, yang dibatasi oleh max_connections

Meningkatkan ukuran area memori kerja

Dalam beberapa situasi, satu-satunya pilihan adalah menambah memori yang digunakan oleh sesi Anda. Jika kueri Anda ditulis dengan benar dan menggunakan kunci yang benar untuk bergabung, pertimbangkan untuk meningkatkan nilai work_mem. Untuk informasi selengkapnya, lihat Area memori kerja.

Untuk mengetahui jumlah file sementara yang dihasilkan kueri, atur log_temp_files ke 0. Jika meningkatkan nilai work_mem ke nilai maksimum yang diidentifikasi dalam log, Anda mencegah kueri menghasilkan file sementara. Namun, work_mem menetapkan nilai maksimum per simpul rencana untuk setiap koneksi atau pekerja paralel. Jika basis data memiliki 5.000 koneksi, dan jika masing-masing menggunakan memori 256 MiB, mesin akan membutuhkan RAM 1,2 TiB. Karena itu, instans Anda dapat kehabisan memori.

Mencadangkan memori yang cukup untuk pool buffer bersama

Basis data Anda menggunakan area memori seperti pool buffer bersama, bukan hanya area memori kerja. Pertimbangkan persyaratan area memori tambahan ini sebelum Anda meningkatkan work_mem. Untuk informasi selengkapnya tentang pool buffer, lihat Pool buffer.

Misalnya, asumsikan bahwa kelas instans Aurora PostgreSQL Anda adalah db.r5.2xlarge. Kelas ini memiliki memori 64 GiB. Secara default, 75% memori dicadangkan untuk pool buffer bersama. Setelah Anda mengurangi jumlah yang dialokasikan ke area memori bersama, 16.384 MB tetap ada. Jangan alokasikan memori yang tersisa hanya ke area memori kerja karena sistem operasi dan mesin juga memerlukan memori.

Memori yang dapat dialokasikan ke work_mem bergantung pada kelas instans. Jika Anda menggunakan kelas instans yang lebih besar, memori yang tersedia akan lebih banyak. Namun, dalam contoh sebelumnya, Anda tidak dapat menggunakan lebih dari 16 GiB. Jika melakukannya, instans Anda menjadi tidak tersedia saat kehabisan memori. Untuk memulihkan instans dari status tidak tersedia, layanan otomatisasi Aurora PostgreSQL secara otomatis dimulai ulang.

Mengelola jumlah koneksi

Misalnya, instans basis data Anda memiliki 5.000 koneksi bersama. Setiap koneksi menggunakan setidaknya 4 MiB work_mem. Konsumsi memori yang tinggi dari koneksi cenderung menurunkan performa. Untuk itu, Anda memiliki opsi berikut:

• Tingkatkan ke kelas instans yang lebih besar.

• Kurangi jumlah koneksi basis data bersama dengan menggunakan proksi koneksi atau pooler.

Untuk proksi, pertimbangkan Proksi Amazon RDS, pgBouncer, atau pooler koneksi berdasarkan aplikasi Anda. Solusi ini mengurangi beban CPU. Solusi ini juga mengurangi risiko saat semua koneksi memerlukan area memori kerja. Saat koneksi basis data tersedia lebih sedikit, Anda dapat meningkatkan nilai work_mem. Dengan cara ini, Anda mengurangi munculnya peristiwa tunggu IO:BufFileRead dan IO:BufFileWrite. Selain itu, kueri yang menunggu area memori kerja dipercepat secara signifikan.