IO: DataFileRead - Amazon Aurora

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

IO: DataFileRead

Peristiwa IO:DataFileRead terjadi saat koneksi menunggu proses backend untuk membaca halaman yang diperlukan dari penyimpanan karena halaman tidak tersedia dalam memori bersama.

Versi mesin yang didukung

Informasi acara tunggu ini didukung untuk semua versi Aurora Postgre. SQL

Konteks

Semua kueri dan operasi manipulasi data (DML) mengakses halaman di kumpulan buffer. Pernyataan yang dapat menimbulkan pembacaan mencakup SELECT, UPDATE, dan DELETE. Misalnya, UPDATE dapat membaca halaman dari tabel atau indeks. Jika halaman yang diminta atau diperbarui tidak berada dalam pool buffer bersama, pembacaan ini dapat mengarah ke peristiwa IO:DataFileRead.

Karena bersifat terbatas, pool buffer bersama dapat diisi. Dalam hal ini, permintaan untuk halaman yang tidak berada dalam memori memaksa basis data untuk membaca blok dari disk. Jika peristiwa IO:DataFileRead sering terjadi, pool buffer bersama mungkin terlalu kecil untuk mengakomodasi beban kerja Anda. Masalah ini bersifat akut untuk kueri SELECT yang membaca sejumlah besar baris yang tidak sesuai dengan pool buffer. Untuk informasi selengkapnya tentang pool buffer, lihat Pool buffer.

Kemungkinan penyebab peningkatan peristiwa tunggu

Penyebab umum peristiwa IO:DataFileRead tersebut mencakup:

Lonjakan koneksi

Anda mungkin menemukan beberapa koneksi yang menghasilkan jumlah acara IO: DataFileRead wait yang sama. Dalam hal ini, lonjakan (peningkatan tiba-tiba dan besar) dalam peristiwa IO:DataFileRead dapat terjadi.

SELECTdan DML pernyataan yang melakukan pemindaian berurutan

Aplikasi Anda mungkin melakukan operasi baru. Operasi yang ada mungkin juga berubah karena rencana eksekusi baru. Dalam kasus ini, cari tabel (terutama tabel besar) yang memiliki nilai seq_scan yang lebih besar. Temukan tabel dengan membuat kueri pg_stat_user_tables. Untuk melacak kueri yang menghasilkan lebih banyak operasi baca, gunakan ekstensi pg_stat_statements.

CTASdan CREATE INDEX untuk kumpulan data yang besar

A CTASadalah CREATE TABLE AS SELECT pernyataan. Jika Anda menjalankan CTAS menggunakan kumpulan data besar sebagai sumber, atau membuat indeks pada tabel besar, IO:DataFileRead peristiwa dapat terjadi. Saat Anda membuat indeks, basis data mungkin perlu membaca seluruh objek menggunakan pemindaian berurutan. A CTAS menghasilkan IO:DataFile pembacaan saat halaman tidak ada dalam memori.

Beberapa pekerja vakum berjalan pada waktu yang sama

Pekerja vakum dapat dipicu secara manual atau otomatis. Sebaiknya adopsi strategi vakum yang agresif. Namun, saat tabel memiliki banyak baris yang diperbarui atau dihapus, peristiwa tunggu IO:DataFileRead bertambah. Setelah ruang direklamasi, waktu vakum yang dihabiskan untuk IO:DataFileRead akan berkurang.

Menyerap data dalam jumlah besar

Saat aplikasi Anda menyerap data dalam jumlah besar, operasi ANALYZE mungkin terjadi lebih sering. Proses ANALYZE dapat dipicu oleh peluncur autovacuum atau diinvokasi secara manual.

Operasi ANALYZE membaca subset dari tabel. Jumlah halaman yang harus dipindai dihitung menggunakan perkalian 30 dengan nilai default_statistics_target. Untuk informasi lebih lanjut, lihat dokumentasi Postgre SQL. Parameter default_statistics_target menerima nilai antara 1 hingga 10.000, dengan nilai default adalah 100.

Kekurangan sumber daya

Jika bandwidth jaringan instance atau CPU dikonsumsi, IO:DataFileRead peristiwa mungkin terjadi lebih sering.

Tindakan

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

Memeriksa filter predikat untuk kueri yang menghasilkan peristiwa tunggu

Asumsikan bahwa Anda mengidentifikasi kueri spesifik yang menghasilkan peristiwa tunggu IO:DataFileRead. Anda dapat mengidentifikasinya menggunakan teknik berikut:

  • Wawasan Performa

  • Tampilan katalog seperti yang disediakan oleh ekstensi pg_stat_statements

  • Tampilan katalog pg_stat_all_tables, jika secara berkala menunjukkan peningkatan jumlah pembacaan fisik

  • Tampilan pg_statio_all_tables, jika menunjukkan bahwa penghitung _read meningkat

Sebaiknya Anda menentukan filter yang akan digunakan dalam predikat (klausa WHERE) kueri ini. Ikuti pedoman berikut:

  • Jalankan perintah EXPLAIN. Pada output, identifikasi jenis pemindaian yang digunakan. Pemindaian berurutan tidak selalu menunjukkan adanya masalah. Kueri yang menggunakan pemindaian berurutan secara alami menghasilkan lebih banyak peristiwa IO:DataFileRead jika dibandingkan dengan kueri yang menggunakan filter.

    Cari tahu apakah kolom yang tercantum dalam klausa WHERE telah diindeks. Jika tidak, coba buat indeks untuk kolom ini. Pendekatan ini mencegah pemindaian berurutan dan mengurangi peristiwa IO:DataFileRead. Jika kueri memiliki filter yang ketat dan masih menghasilkan pemindaian berurutan, evaluasi apakah indeks yang tepat sedang digunakan.

  • Cari tahu apakah kueri mengakses tabel yang sangat besar. Dalam beberapa kasus, partisi tabel dapat meningkatkan performa, dengan memungkinkan kueri hanya membaca partisi yang diperlukan.

  • Periksa kardinalitas (jumlah total baris) dari operasi gabungan Anda. Perhatikan seberapa ketat nilai yang Anda teruskan di filter untuk klausa WHERE Anda. Jika memungkinkan, setel kueri Anda untuk mengurangi jumlah baris yang diteruskan di setiap langkah rencana.

Meminimalkan efek operasi pemeliharaan

Operasi pemeliharaan seperti VACUUM dan ANALYZE bersifat penting. Sebaiknya jangan dinonaktifkan karena peristiwa tunggu IO:DataFileRead berkaitan dengan operasi pemeliharaan ini. Pendekatan berikut dapat meminimalkan efek operasi ini:

  • Jalankan operasi pemeliharaan secara manual selama di luar jam sibuk. Teknik ini mencegah basis data mencapai ambang batas untuk operasi otomatis.

  • Untuk tabel yang sangat besar, pertimbangkan untuk mempartisi tabel. Teknik ini mengurangi overhead operasi pemeliharaan. Basis data hanya mengakses partisi yang membutuhkan pemeliharaan.

  • Saat Anda menyerap data dalam jumlah besar, coba nonaktifkan fitur analisis otomatis.

Fitur autovacuum secara otomatis dipicu untuk tabel saat rumus berikut benar.

pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor) + autovacuum_vacuum_threshold

Tampilan pg_stat_user_tables dan katalog pg_class berisi beberapa baris. Satu baris dapat sesuai dengan satu baris di tabel Anda. Rumus ini mengasumsikan bahwa reltuples adalah untuk tabel tertentu. Parameter autovacuum_vacuum_scale_factor (0,20 secara default) dan autovacuum_vacuum_threshold (50 tuple secara default) biasanya diatur secara global untuk seluruh instans. Namun, Anda dapat mengatur nilai yang berbeda untuk tabel tertentu.

Menemukan tabel yang mengonsumsi ruang secara tidak perlu

Untuk menemukan tabel yang mengonsumsi ruang lebih dari yang diperlukan, jalankan kueri berikut. Saat dijalankan oleh peran pengguna basis data yang tidak memiliki peran rds_superuser, kueri ini hanya menampilkan informasi tentang tabel yang diizinkan dibaca oleh peran pengguna. Kueri ini didukung oleh Postgre SQL versi 12 dan versi yang lebih baru.

WITH report AS ( SELECT schemaname ,tblname ,n_dead_tup ,n_live_tup ,block_size*tblpages AS real_size ,(tblpages-est_tblpages)*block_size AS extra_size ,CASE WHEN tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages)/tblpages::float ELSE 0 END AS extra_ratio, fillfactor, (tblpages-est_tblpages_ff)*block_size AS bloat_size ,CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float ELSE 0 END AS bloat_ratio ,is_na FROM ( SELECT ceil( reltuples / ( (block_size-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages ,ceil( reltuples / ( (block_size-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff ,tblpages ,fillfactor ,block_size ,tblid ,schemaname ,tblname ,n_dead_tup ,n_live_tup ,heappages ,toastpages ,is_na FROM ( SELECT ( 4 + tpl_hdr_size + tpl_data_size + (2*ma) - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END ) AS tpl_size ,block_size - page_hdr AS size_per_block ,(heappages + toastpages) AS tblpages ,heappages ,toastpages ,reltuples ,toasttuples ,block_size ,page_hdr ,tblid ,schemaname ,tblname ,fillfactor ,is_na ,n_dead_tup ,n_live_tup FROM ( SELECT tbl.oid AS tblid ,ns.nspname AS schemaname ,tbl.relname AS tblname ,tbl.reltuples AS reltuples ,tbl.relpages AS heappages ,coalesce(toast.relpages, 0) AS toastpages ,coalesce(toast.reltuples, 0) AS toasttuples ,psat.n_dead_tup AS n_dead_tup ,psat.n_live_tup AS n_live_tup ,24 AS page_hdr ,current_setting('block_size')::numeric AS block_size ,coalesce(substring( array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor ,CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma ,23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END AS tpl_hdr_size ,sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size ,bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR count(att.attname) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON (att.attrelid = tbl.oid) JOIN pg_stat_all_tables AS psat ON (tbl.oid = psat.relid) JOIN pg_namespace AS ns ON (ns.oid = tbl.relnamespace) LEFT JOIN pg_stats AS s ON (s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname) LEFT JOIN pg_class AS toast ON (tbl.reltoastrelid = toast.oid) WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind = 'r' GROUP BY tbl.oid, ns.nspname, tbl.relname, tbl.reltuples, tbl.relpages, toastpages, toasttuples, fillfactor, block_size, ma, n_dead_tup, n_live_tup ORDER BY schemaname, tblname ) AS s ) AS s2 ) AS s3 ORDER BY bloat_size DESC ) SELECT * FROM report WHERE bloat_ratio != 0 -- AND schemaname = 'public' -- AND tblname = 'pgbench_accounts' ; -- WHERE NOT is_na -- AND tblpages*((pst).free_percent + (pst).dead_tuple_percent)::float4/100 >= 1

Anda dapat memeriksa bloat indeks dan tabel di aplikasi Anda. Untuk informasi selengkapnya, lihat Mendiagnosis bloat tabel dan indeks.

Menemukan indeks yang mengonsumsi ruang yang tidak perlu

Untuk menemukan indeks yang mengonsumsi ruang yang tidak perlu, jalankan kueri berikut.

-- WARNING: run with a nonsuperuser role, the query inspects -- only indexes on tables you have permissions to read. -- WARNING: rows with is_na = 't' are known to have bad statistics ("name" type is not supported). -- This query is compatible with PostgreSQL 8.2 and later. SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size, bs*(relpages-est_pages)::bigint AS extra_size, 100 * (relpages-est_pages)::float / relpages AS extra_ratio, fillfactor, bs*(relpages-est_pages_ff) AS bloat_size, 100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio, is_na -- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, -- maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages -- (DEBUG INFO) FROM ( SELECT coalesce(1 + ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth) ) AS est_pages, coalesce(1 + ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0 ) AS est_pages_ff, bs, nspname, table_oid, tblname, idxname, relpages, fillfactor, is_na -- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, -- index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO) FROM ( SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, relam, table_oid, fillfactor, ( index_tuple_hdr_bm + maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign ELSE index_tuple_hdr_bm%maxalign END + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN WHEN nulldatawidth = 0 THEN 0 WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign ELSE nulldatawidth::integer%maxalign END )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO) FROM ( SELECT i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid AS table_oid, current_setting('block_size')::numeric AS bs, fillfactor, CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS maxalign, /* per page header, fixed size: 20 for 7.X, 24 for others */ 24 AS pagehdr, /* per page btree opaque data */ 16 AS pageopqdata, /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */ CASE WHEN max(coalesce(s.null_frac,0)) = 0 THEN 2 -- IndexTupleData size ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8) END AS index_tuple_hdr_bm, /* data len: we remove null values save space using it fractionnal part from stats */ sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth, max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na FROM pg_attribute AS a JOIN ( SELECT nspname, tbl.relname AS tblname, idx.relname AS idxname, idx.reltuples, idx.relpages, idx.relam, indrelid, indexrelid, indkey::smallint[] AS attnum, coalesce(substring( array_to_string(idx.reloptions, ' ') from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor FROM pg_index JOIN pg_class idx ON idx.oid=pg_index.indexrelid JOIN pg_class tbl ON tbl.oid=pg_index.indrelid JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace WHERE pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0 ) AS i ON a.attrelid = i.indexrelid JOIN pg_stats AS s ON s.schemaname = i.nspname AND ((s.tablename = i.tblname AND s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl OR (s.tablename = i.idxname AND s.attname = a.attname)) -- stats from functionnal cols JOIN pg_type AS t ON a.atttypid = t.oid WHERE a.attnum > 0 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9 ) AS s1 ) AS s2 JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree' ) AS sub -- WHERE NOT is_na ORDER BY 2,3,4;

Menemukan tabel yang memenuhi syarat untuk autovacuum

Untuk menemukan tabel yang memenuhi syarat untuk autovacuum, jalankan kueri berikut.

--This query shows tables that need vacuuming and are eligible candidates. --The following query lists all tables that are due to be processed by autovacuum. -- During normal operation, this query should return very little. WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold') , vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor') , fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') , sto AS (SELECT opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value FROM (SELECT oid opt_oid, unnest(reloptions) setting FROM pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation , pg_size_pretty(pg_table_size(c.oid)) as table_size , age(relfrozenxid) as xid_age , coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age , (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) as autovacuum_vacuum_tuples , n_dead_tup as dead_tuples FROM pg_class c JOIN pg_namespace ns ON ns.oid = c.relnamespace JOIN pg_stat_all_tables stat ON stat.relid = c.oid JOIN vbt on (1=1) JOIN vsf ON (1=1) JOIN fma on (1=1) LEFT JOIN sto cvbt ON cvbt.param = 'autovacuum_vacuum_threshold' AND c.oid = cvbt.opt_oid LEFT JOIN sto cvsf ON cvsf.param = 'autovacuum_vacuum_scale_factor' AND c.oid = cvsf.opt_oid LEFT JOIN sto cfma ON cfma.param = 'autovacuum_freeze_max_age' AND c.oid = cfma.opt_oid WHERE c.relkind = 'r' AND nspname <> 'pg_catalog' AND ( age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) or coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup -- or 1 = 1 ) ORDER BY age(relfrozenxid) DESC;

Merespons jumlah koneksi yang tinggi

Saat Anda memantau Amazon CloudWatch, Anda mungkin menemukan bahwa DatabaseConnections metrik melonjak. Peningkatan ini menunjukkan bertambahnya jumlah koneksi ke basis data Anda. Sebaiknya lakukan pendekatan berikut:

  • Membatasi jumlah koneksi yang dapat dibuka aplikasi dengan setiap instans. Jika aplikasi Anda memiliki fitur kumpulan koneksi tertanam, tetapkan jumlah koneksi yang wajar. Dasarkan angka pada apa yang vCPUs dalam contoh Anda dapat paralelkan secara efektif.

    Jika aplikasi Anda tidak menggunakan fitur kumpulan koneksi, pertimbangkan untuk menggunakan Amazon RDS Proxy atau alternatifnya. Pendekatan ini memungkinkan aplikasi Anda membuka beberapa koneksi dengan penyeimbang beban. Penyeimbang selanjutnya dapat membuka sejumlah koneksi terbatas dengan basis data. Karena lebih sedikit koneksi yang berjalan secara paralel, instans DB Anda melakukan lebih sedikit peralihan konteks di kernel. Kueri harus berkembang lebih cepat, yang mengarah ke lebih sedikit peristiwa tunggu. Untuk informasi selengkapnya, lihat RDSProxy Amazon untuk Aurora.

  • Bila memungkinkan, manfaatkan node pembaca untuk Aurora Postgre SQL dan baca replika untuk Postgre. RDS SQL Saat aplikasi Anda menjalankan operasi hanya-baca, kirim permintaan ini ke titik akhir khusus pembaca. Teknik ini menyebarkan permintaan aplikasi di semua simpul pembaca, sehingga mengurangi tekanan I/O pada simpul penulis.

  • Coba naikkan skala instans DB Anda. Kelas instance berkapasitas lebih tinggi memberikan lebih banyak memori, yang memberi Aurora Postgre kumpulan buffer bersama SQL yang lebih besar untuk menampung halaman. Ukuran yang lebih besar juga memberi instans DB lebih banyak vCPUs untuk menangani koneksi. Lebih vCPUs banyak sangat membantu ketika operasi yang menghasilkan acara IO:DataFileRead tunggu ditulis.