IO:BufFileRead dan IO:BufFileWrite

Peristiwa IO:BufFileRead dan IO:BufFileWrite terjadi ketika RDS for PostgreSQL membuat file sementara. Saat operasi membutuhkan lebih banyak memori daripada yang saat ini ditentukan oleh parameter memori kerja, operasi ini 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 RDS for PostgreSQL.

Konteks

IO:BufFileRead dan IO:BufFileWrite berkaitan dengan area memori kerja dan area memori kerja pemeliharaan. Untuk informasi selengkapnya tentang penyetelan memori, lihat Resource Consumption dalam dokumentasi PostgreSQL.

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

2. Pemulihan cepat untuk ruang kosong

Anda mungkin juga melihat pola "gergaji". Pola ini dapat menunjukkan bahwa basis data Anda membuat file kecil 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:

• Hash join

• 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

• Identifikasi masalah
• Periksa kueri join
• Periksa kueri ORDER BY dan GROUP BY
• Hindari menggunakan operasi DISTINCT
• Mempertimbangkan untuk menggunakan fungsi jendela alih-alih fungsi GROUP BY
• Selidiki tampilan terwujud dan pernyataan CTAS
• Gunakan pg_repack saat Anda membuat kembali indeks
• Tingkatkan maintenance_work_mem saat Anda membuat klaster tabel
• Setel memori untuk mencegah IO:BufFileRead dan IO:BufFileWrite

Identifikasi masalah

Misalkan ada situasi saat Wawasan Performa tidak diaktifkan dan Anda menduga bahwa IO:BufFileRead dan IO:BufFileWrite terjadi lebih sering daripada biasanya. Untuk mengidentifikasi sumber masalahnya, Anda dapat mengatur parameter log_temp_files untuk mencatat log semua kueri yang menghasilkan file sementara lebih dari ambang batas KB yang Anda tentukan. Secara default, log_temp_files diatur ke -1, yang menonaktifkan fitur logging ini. Jika Anda mengatur parameter ini ke 0, RDS for PostgreSQL mencatat log semua file sementara. Jika nilainya 1024, Aurora PostgreSQL mencatat semua kueri yang menghasilkan file sementara yang berukuran lebih besar dari 1 MB. Untuk informasi selengkapnya tentang log_temp_files, lihat Error Reporting and Logging dalam dokumentasi PostgreSQL.

Periksa kueri join

Kemungkinan kueri Anda menggunakan join. 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 join dengan benar. Pertimbangkan inner join 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 join yang tak terduga ini menghasilkan file sementara yang besar. Tergantung pada ukuran tabel, kueri Cartesian bahkan dapat memenuhi penyimpanan. Aplikasi Anda dapat memiliki join Cartesian jika kondisi berikut terpenuhi:

• Anda melihat penurunan besar dan drastis 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.

Periksa kueri ORDER BY dan GROUP BY

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

• Hanya sertakan kolom dalam klausa ORDER BY saat kolom tersebut 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 direkomendasikan karena lebih kecil. Indeks yang lebih kecil lebih cepat untuk dibaca dan di-traverse.

• 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 set hasil, lalu meneruskannya ke kueri luar. Semakin banyak baris yang dapat difilter kueri, semakin sedikit pengurutan 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 tidak akan terus 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.

Hindari menggunakan 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 melakukan join 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 Anda menggunakan RDS for PostgreSQL versi 10 atau lebih tinggi, Anda dapat mengorelasikan 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 penghitungan di seluruh set yang ditentukan oleh kueri, dengan mengorelasikan 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 yang serupa, sehingga tidak menggandakan operasi. Namun, dengan menggunakan deklarasi eksplisit untuk blok jendela, Anda dapat mengelola kueri dengan lebih mudah. Anda juga dapat meningkatkan performa dengan mencegah duplikasi.

Selidiki 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 serta 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.

Gunakan pg_repack saat Anda membuat kembali indeks

Saat Anda membuat indeks, mesin mengurutkan set hasil. Seiring tabel bertambah besar, dan seiring 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 tentang maintenance_work_mem, lihat https://www.postgresql.org/docs/current/runtime-config-resource.html dalam dokumentasi PostgreSQL.

Solusi yang mungkin saat membuat ulang indeks besar adalah dengan menggunakan ekstensi pg_repack. Untuk informasi selengkapnya, lihat Reorganize tables in PostgreSQL databases with minimal locks dalam dokumentasi pg_repack. Untuk informasi tentang menyiapkan ekstensi di instans DB RDS for PostgreSQL Anda, lihat Mengurangi bloat dalam tabel dan indeks dengan ekstensi pg_repack.

Tingkatkan maintenance_work_mem saat Anda membuat klaster tabel

Perintah CLUSTER membuat klaster tabel yang ditentukan menurut table_name berdasarkan indeks yang ada yang ditentukan menurut index_name. RDS for PostgreSQL secara fisik membuat ulang tabel agar sesuai dengan urutan indeks yang diberikan.

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

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

Setel memori untuk mencegah IO:BufFileRead dan IO:BufFileWrite

Dalam beberapa situasi, Anda perlu menyetel memori. Tujuan Anda adalah menyeimbangkan memori di seluruh area konsumsi berikut menggunakan parameter yang sesuai, sebagai berikut.

• Nilai work_mem

• Memori yang tersisa setelah mengecualikan nilai shared_buffers

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

Untuk informasi selengkapnya tentang penyetelan memori, lihat Resource Consumption dalam dokumentasi PostgreSQL.

Tingkatkan 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 join, pertimbangkan untuk meningkatkan nilai work_mem.

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. Oleh karena itu, instans Anda dapat kehabisan memori.

Cadangkan 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.

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

Memori yang dapat Anda alokasikan ke work_mem tergantung 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 PostgreSQL Aurora secara otomatis dimulai ulang.

Kelola jumlah koneksi

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

• Upgrade ke kelas instans yang lebih besar.

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

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 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 akan dipercepat secara signifikan.