IO: BufFileRead dan IO: BufFileWrite - Amazon Aurora
Versi mesin yang didukungKonteksKemungkinan penyebab peningkatan peristiwa tungguTindakan

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

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 kadang-kadang disebut tumpah ke disk. Untuk informasi selengkapnya tentang file sementara dan penggunaannya, lihatMengelola file sementara dengan PostgreSQL.

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 ini akan 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-file sementara ini dapat mengurangi kinerja.

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.

Identifikasi masalah

Anda dapat melihat penggunaan file sementara secara langsung di Performance Insights. Untuk informasi selengkapnya, lihat Melihat penggunaan file sementara dengan Performance Insights. Jika Performance Insights dinonaktifkan, Anda mungkin melihat peningkatan IO:BufFileRead dan IO:BufFileWrite operasi. Untuk memecahkan masalah, lakukan hal berikut:

  1. Periksa FreeLocalStorage metrik 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 Error Reporting and Logging 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 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 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 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 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.

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.

Menggunakan pg_repack saat Anda membuat 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, lihat Area memori kerja pemeliharaan.

Solusi yang memungkinkan saat membuat ulang indeks besar adalah menggunakan alat pg_repack. Untuk informasi selengkapnya, lihat Reorganize tables in PostgreSQL databases with minimal locks dalam dokumentasi 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. 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, 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.

Tune 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. 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. 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 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 Aurora PostgreSQL secara otomatis dimulai ulang.

Mengelola 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:

  • Tingkatkan 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.