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 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
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:
-
Penurunan ketersediaan secara tiba-tiba dan drastis
-
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
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 klausaORDER 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 klausaLIMIT
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
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
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
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.