🥇 Nyilem menyang statistik internal PostgreSQL. Alexey Lesovsky

Transkripsi laporan 2015 dening Alexey Lesovsky "Deep nyilem menyang statistik internal PostgreSQL"

Penafian saka penulis laporan: Aku nyathet yen laporan iki tanggal November 2015 - luwih saka 4 taun wis liwati lan akeh wektu wis liwati. Versi 9.4 sing dibahas ing laporan ora didhukung maneh. Swara 4 taun kepungkur, 5 rilis anyar wis dirilis ing ngendi akeh inovasi, dandan lan owah-owahan babagan statistik wis muncul, lan sawetara materi wis ketinggalan jaman lan ora relevan. Nalika aku nyemak, aku nyoba menehi tandha ing papan kasebut supaya ora nyasarake sampeyan sing maca. Aku ora nulis maneh panggonan iki, ana akeh, lan minangka asil, laporan temen beda bakal metu.

DBMS PostgreSQL minangka mekanisme ageng, lan mekanisme iki kasusun saka akeh subsistem, karya terkoordinasi sing langsung mengaruhi kinerja DBMS. Sajrone operasi, statistik lan informasi babagan operasi komponen diklumpukake, sing ngidini sampeyan ngevaluasi efektifitas PostgreSQL lan njupuk langkah kanggo nambah kinerja. Nanging, ana akeh informasi iki lan diwenehi ing wangun sing rada disederhanakake. Ngolah informasi iki lan interpretasi kadhangkala dadi tugas sing ora pati penting, lan "kebun binatang" alat lan keperluan bisa gampang bingung sanajan DBA maju.

sugeng sonten Jenengku Aleksey. Kaya sing dikandhakake Ilya, aku bakal ngomong babagan statistik PostgreSQL.

Statistik aktivitas PostgreSQL. PostgreSQL duwe rong statistik. Statistik kegiatan, sing bakal dibahas. Lan statistik panjadwal babagan distribusi data. Aku bakal ngomong khusus babagan statistik kegiatan PostgreSQL, sing ngidini kita ngadili kinerja lan bisa nambah.

Aku bakal pitutur marang kowe carane èfèktif nggunakake statistik kanggo ngatasi macem-macem masalah sing duwe utawa bisa duwe.

Apa sing ora bakal ana ing laporan kasebut? Ing laporan, aku ora bakal ndemek statistik saka panjadwal, amarga. iki minangka topik sing kapisah kanggo laporan sing kapisah babagan carane data disimpen ing basis data lan carane perencana query entuk gagasan babagan karakteristik kualitatif lan kuantitatif data iki.

Lan ora bakal ana review alat, aku ora bakal mbandhingake produk siji karo liyane. Ora bakal ana pariwara. Ayo nyelehake.

Aku pengin nuduhake yen nggunakake statistik migunani. Iku perlu. Gunakake tanpa wedi. Kabeh sing kita butuhake yaiku SQL kosong lan kawruh dhasar babagan SQL.

Lan kita bakal ngomong babagan statistik sing kudu dipilih kanggo ngatasi masalah.

Yen kita katon ing PostgreSQL lan mbukak printah ing sistem operasi kanggo ndeleng pangolahan, kita bakal weruh "kothak ireng". Kita bakal weruh sawetara pangolahan sing nindakake soko, lan kanthi jeneng kita kira-kira bisa mbayangno apa sing ditindakake ing kana, apa sing ditindakake. Nanging, nyatane, iki kothak ireng, kita ora bisa ndeleng ing njero.

Kita bisa ndeleng beban CPU ing top, kita bisa ndeleng pemanfaatan memori dening sawetara keperluan sistem, nanging kita ora bakal bisa kanggo katon nang PostgreSQL. Kanggo iki kita butuh alat liyane.

Lan terus luwih, aku bakal pitutur marang kowe ngendi wektu ngginakaken. Yen kita makili PostgreSQL ing wangun rencana kuwi, iku bakal bisa kanggo njawab ngendi wektu ngginakaken. Iki minangka rong perkara: yaiku pangolahan panjaluk klien saka aplikasi lan tugas latar mburi sing ditindakake PostgreSQL supaya tetep mlaku.

Yen kita miwiti ndeleng pojok kiwa ndhuwur, kita bisa ndeleng carane panjalukan klien diproses. Panjaluk kasebut asale saka aplikasi lan sesi klien dibukak kanggo kerja luwih lanjut. Panjaluk kasebut diterusake menyang panjadwal. Planner nggawe rencana pitakon. Ngirim luwih kanggo eksekusi. Ana sawetara jinis data blok I / O sing ana gandhengane karo tabel lan indeks. Data sing dibutuhake diwaca saka disk menyang memori ing wilayah khusus sing disebut "buffer bareng". Asil pitakon, yen nganyari, mbusak, dicathet ing log transaksi ing WAL. Sawetara informasi statistik mlebu menyang log utawa kolektor statistik. Lan asil panyuwunan diwenehake maneh menyang klien. Sawise iku, klien bisa mbaleni kabeh kanthi panjaluk anyar.

Apa kita duwe tugas latar mburi lan proses latar mburi? Kita duwe sawetara pangolahan supaya database bisa mlaku lan mlaku kanthi normal. Proses kasebut uga bakal dibahas ing laporan: yaiku autovacuum, checkpointer, proses sing ana gandhengane karo replikasi, panulis latar mburi. Aku bakal ndemek saben wong nalika aku laporan.

Apa masalah karo statistik?

Kathah informasi. PostgreSQL 9.4 nyedhiyakake 109 metrik kanggo ndeleng data statistik. Nanging, yen database nyimpen akeh tabel, skema, database, kabeh metrik iki kudu dikalikan karo jumlah tabel, database sing cocog. Tegese, ana informasi liyane. Lan gampang banget kanggo klelep.
Masalah sabanjure yaiku statistik diwakili dening counter. Yen kita katon ing statistik iki, kita bakal weruh terus nambah counters. Lan yen akeh wektu wis liwati wiwit statistik direset, kita bakal weruh milyaran nilai. Lan dheweke ora ngandhani apa-apa.
Ora ana sejarahe. Yen sampeyan duwe sawetara jenis Gagal, soko ambruk 15-30 menit ago, sampeyan ora bakal bisa nggunakake statistik lan ndeleng apa kedaden 15-30 menit ago. Iki masalah.
Kekurangan alat sing dibangun ing PostgreSQL minangka masalah. Pangembang kernel ora nyedhiyakake sarana apa wae. Dheweke ora duwe apa-apa kaya ngono. Dheweke mung menehi statistik ing database. Gunakake, njaluk panjaluk, apa wae sing dikarepake, banjur lakoni.
Amarga ora ana alat sing dibangun ing PostgreSQL, iki nyebabake masalah liyane. Akeh alat pihak katelu. Saben perusahaan sing duwe tangan luwih utawa kurang langsung nyoba nulis program dhewe. Lan minangka asil, komunitas duwe akeh alat sing bisa digunakake kanggo nggarap statistik. Lan ing sawetara alat ana sawetara fitur, ing alat liyane ora ana fitur liyane, utawa ana sawetara fitur anyar. Lan ana kahanan sing sampeyan kudu nggunakake rong, telu, utawa papat alat sing tumpang tindih lan duwe fungsi sing beda. Iki banget ora nyenengake.

Apa sing diterusake saka iki? Penting kanggo bisa njupuk statistik langsung supaya ora gumantung ing program, utawa piye wae nambah program iki dhewe: nambah sawetara fungsi kanggo entuk manfaat.

Lan sampeyan butuh kawruh dhasar babagan SQL. Kanggo entuk sawetara data saka statistik, sampeyan kudu nggawe pitakon SQL, yaiku sampeyan kudu ngerti carane milih, gabung digawe.

Statistik ngandhani sawetara perkara. Padha bisa dipérang dadi kategori.

Kategori pisanan yaiku acara sing kedadeyan ing basis data. Iki nalika sawetara acara dumadi ing database: pitakonan, akses meja, autovacuum, commits, banjur iki kabeh acara. Counter sing cocog karo acara kasebut ditambah. Lan kita bisa nglacak acara kasebut.
Kategori kapindho yaiku sifat obyek kayata tabel, database. Dheweke duwe sifat. Iki ukuran meja. Kita bisa nglacak wutah tabel, wutah indeks. Kita bisa ndeleng owah-owahan ing dinamika.
Lan kategori katelu yaiku wektu sing ditindakake ing acara kasebut. Panjaluk minangka acara. Wis ukuran tartamtu saka durasi. Diwiwiti ing kene, rampung ing kene. Kita bisa nglacak. Salah siji wektu maca blok saka disk utawa nulis. Iki uga dilacak.

Sumber statistik ditampilake ing ngisor iki:

Ing memori sambungan (buffers bareng) ana bagean kanggo manggonke data statis ana, ana uga counters sing terus-terusan nambah nalika acara tartamtu kedaden, utawa sawetara wektu njedhul ing operasi database.
Kabeh counter iki ora kasedhiya kanggo pangguna lan malah ora kasedhiya kanggo administrator. Iki minangka barang tingkat rendah. Kanggo ngakses, PostgreSQL nyedhiyakake antarmuka ing wangun fungsi SQL. Kita bisa milih pilihan nggunakake fungsi kasebut lan entuk sawetara jinis metrik (utawa set metrik).
Nanging, ora tansah trep kanggo nggunakake fungsi kasebut, mula fungsi minangka basis kanggo tampilan (VIEWs). Iki minangka tabel virtual sing nyedhiyakake statistik ing subsistem tartamtu, utawa ing sawetara acara ing basis data.
Tampilan (VIEW) sing dibangun iki minangka antarmuka panganggo utama kanggo nggarap statistik. Padha kasedhiya kanthi standar tanpa setelan tambahan, sampeyan bisa langsung nggunakake, nonton, njupuk informasi saka ing kono. Lan uga ana sing nyumbang. Kontribusi resmi. Sampeyan bisa nginstal paket postgresql-contrib (contone, postgresql94-contrib), mbukak modul perlu ing konfigurasi, nemtokake paramèter kanggo, miwiti maneh PostgreSQL lan sampeyan bisa nggunakake. (Cathetan. Gumantung ing distribusi, ing versi anyar saka contrib paket iku bagéan saka paket utama).
Lan ana kontribusi ora resmi. Dheweke ora diwenehake karo distribusi PostgreSQL standar. Padha kudu dikompilasi utawa diinstal minangka perpustakaan. Opsi bisa beda-beda, gumantung saka apa sing digawe dening pangembang sumbangan ora resmi iki.

Slide iki nuduhake kabeh tampilan kasebut (VIEWS) lan sawetara fungsi sing kasedhiya ing PostgreSQL 9.4. Minangka kita bisa ndeleng, ana akeh. Lan cukup gampang bingung yen sampeyan ngalami pisanan.

Nanging, yen kita njupuk gambar sadurunge Как тратится время на PostgreSQL lan kompatibel karo dhaftar iki, kita njaluk gambar iki. Saben tampilan (VIEWs), utawa saben fungsi, kita bisa nggunakake siji waé utawa liyane kanggo njupuk statistik cocok nalika kita wis PostgreSQL mlaku. Lan kita wis bisa njaluk sawetara informasi bab operasi saka subsistem.

Babagan pisanan sing bakal kita deleng yaiku pg_stat_database. Minangka kita bisa ndeleng, iki minangka perwakilan. Isine akeh informasi. Informasi paling mawarni-warni. Lan menehi kawruh banget migunani apa kita wis arep ing database.

Apa sing bisa dijupuk saka kana? Ayo dadi miwiti karo bab sing paling prasaja.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

Wangsulan: Bab ingkang pisanan kita bisa ndeleng ing persentasi hit cache. Persentase hit cache minangka metrik sing migunani. Iku ngijini sampeyan kanggo ngira pinten data dijupuk saka cache buffer sambungan, lan pinten diwaca saka disk.

Sing jelas cache liyane hit kita duwe, sing luwih apik. Kita ngevaluasi metrik iki minangka persentase. Lan, contone, yen kita duwe persentasi saka cache iki hits luwih saka 90%, banjur iki apik. Yen irungnya ngisor 90%, banjur kita ora duwe cukup memori kanggo njaga sirah panas data ing memori. Lan kanggo nggunakake data iki, PostgreSQL dipeksa kanggo ngakses disk lan iki luwih alon tinimbang yen data diwaca saka memori. Lan sampeyan kudu mikir babagan nambah memori: nambah buffer bareng, utawa nambah memori wesi (RAM).

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

Apa maneh sing bisa dijupuk saka pagelaran iki? Sampeyan bisa ndeleng anomali sing kedadeyan ing database. Apa sing dituduhake ing kene? Ana commits, rollbacks, nggawe file sauntara, ukurane, deadlocks lan konflik.

Kita bisa nggunakake panjalukan iki. SQL iki cukup prasaja. Lan kita bisa ndeleng data iki kanggo awake dhewe.

Lan ing kene ana nilai ambang. Kita ndeleng rasio commits lan rollbacks. Komit minangka konfirmasi sukses transaksi kasebut. Rollbacks minangka rollback, yaiku transaksi nindakake sawetara pakaryan, nyaring database, nganggep soko, banjur ana kegagalan, lan asil transaksi dibuwang. i.e. nomer rollbacks saya tambah ala. Lan sampeyan kudu piye wae supaya wong-wong mau, lan ngowahi kode supaya iki ora kelakon.

Konflik ana hubungane karo replikasi. Lan uga kudu nyingkiri. Yen sampeyan duwe sawetara pitakon sing ditindakake ing replika lan konflik muncul, mula sampeyan kudu nganalisa konflik kasebut lan ndeleng apa sing kedadeyan. Rincian bisa ditemokake ing log. Lan mutusake konflik supaya panjaluk aplikasi bisa tanpa kesalahan.

Deadlocks uga kahanan sing ala. Nalika panjalukan saingan kanggo sumber daya, siji panjalukan ngakses siji sumber lan njupuk kunci, panjalukan kaloro ngakses sumber kaloro lan uga njupuk kunci, banjur loro panjalukan ngakses sumber daya lan diblokir nunggu pepadhamu kanggo ngeculake kunci. Iki uga kahanan masalah. Padha kudu ditangani ing tingkat nulis ulang aplikasi lan serializing akses kanggo sumber daya. Lan yen sampeyan ndeleng manawa deadlocks terus saya tambah, sampeyan kudu ndeleng rincian ing log, nganalisa kahanan sing wis ana lan ndeleng apa masalahe.

File sementara (temp_files) uga ala. Nalika panyuwunan pangguna ora duwe memori sing cukup kanggo nampung data operasional, sementara, nggawe file ing disk. Lan kabeh operasi sing bisa nindakake ing buffer sauntara ing memori, kang wiwit nindakake ing disk. Iku alon. Iki nambah wektu eksekusi pitakon. Lan klien sing ngirim panjalukan kanggo PostgreSQL bakal nampa respon mengko. Yen kabeh operasi kasebut ditindakake ing memori, Postgres bakal nanggapi luwih cepet lan klien bakal ngenteni luwih sithik.

pg_stat_bgwriter - Tampilan iki nggambarake operasi rong subsistem latar mburi PostgreSQL: checkpointer и background writer.

Kanggo miwiti, ayo analisa titik kontrol, sing diarani. checkpoints. Apa iku checkpoints? Checkpoint minangka posisi ing log transaksi sing nuduhake yen kabeh owah-owahan data sing ditindakake ing log wis kasil disinkronake karo data ing disk. Proses kasebut, gumantung saka beban kerja lan setelan, bisa dadi dawa lan biasane kalebu nyinkronake kaca sing reged ing buffer sing dienggo bareng karo file data ing disk. Kanggo apa? Yen PostgreSQL ngakses disk kabeh wektu lan njupuk data saka ing kono, lan nulis data ing saben akses, iku bakal alon. Mulane, PostgreSQL duwe bagean memori, ukurane gumantung ing paramèter ing konfigurasi. Postgres nyedhiyakake data operasional ing memori iki kanggo proses luwih lanjut utawa pitakon. Ing kasus panjalukan pangowahan data, owah-owahan. Lan kita entuk rong versi data. Siji ing memori, liyane ing disk. Lan kanthi periodik sampeyan kudu nyinkronake data iki. We kudu apa diganti ing memori kanggo diselarasake menyang disk. Iki mbutuhake checkpoint.

Checkpoint ngliwati buffer sing dienggo bareng, menehi tandha kaca sing reged sing dibutuhake kanggo checkpoint. Banjur miwiti pass kaloro liwat buffer sambungan. Lan kaca-kaca sing ditandhani kanggo checkpoint, dheweke wis nyinkronake. Mangkono, data wis disinkronake karo disk.

Ana rong jinis titik kontrol. Siji checkpoint dieksekusi nalika entek. Titik mriksa iki migunani lan apik - checkpoint_timed. Lan ana checkpoints sing dikarepake - checkpoint required. Checkpoint kasebut kedadeyan nalika kita duwe rekaman data sing gedhe banget. Kita nyathet akeh log transaksi. Lan PostgreSQL percaya yen kudu nyinkronake kabeh iki kanthi cepet, nggawe checkpoint lan nerusake.

Lan yen sampeyan ndeleng statistik pg_stat_bgwriter lan ndeleng apa sing sampeyan duwe checkpoint_req luwih gedhe tinimbang checkpoint_timed, banjur iki ala. Kok ala? Iki tegese PostgreSQL ngalami stres sing terus-terusan nalika kudu nulis data menyang disk. Checkpoint dening wektu entek kurang ngepenakke lan kaleksanan miturut jadwal internal lan, kaya, digawe dowo liwat wektu. PostgreSQL nduweni kemampuan kanggo ngaso ing karya lan ora ketegangan subsistem disk. Iki migunani kanggo PostgreSQL. Lan panjaluk sing ditindakake sajrone checkpoint ora bakal ngalami stres amarga subsistem disk sibuk.

Lan ana telung paramèter kanggo nyetel checkpoint:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Dheweke ngidini sampeyan ngontrol operasi titik kontrol. Nanging aku ora bakal mikir babagan dheweke. Pengaruhe minangka masalah sing kapisah.

Pènget: Versi 9.4 sing dianggep ing laporan wis ora cocog maneh. Ing versi modern PostgreSQL, parameter checkpoint_segments diganti dening paramèter min_wal_size и max_wal_size.

Subsistem sabanjure yaiku panulis latar mburi − background writer. Dheweke nyapo? Iku mlaku terus-terusan ing daur ulang tanpa wates. Iki mindai kaca menyang buffer sing dienggo bareng lan ngresiki kaca reged sing ditemokake ing disk. Kanthi cara iki, iku mbantu checkpointer kanggo nindakake kurang karya sak checkpointing.

Apa maneh dheweke dibutuhake? Iki nyedhiyakake kabutuhan kaca sing resik ing buffer sing dienggo bareng yen dumadakan dibutuhake (kanthi jumlah akeh lan langsung) kanggo nampung data. Upamane ana kahanan nalika panyuwunan mbutuhake kaca sing resik lan wis ana ing buffer sing dienggo bareng. Postgres backend dheweke mung njupuk lan nggunakake, dheweke ora kudu ngresiki apa-apa dhewe. Nanging yen dumadakan ora ana kaca kasebut, backend ngaso lan wiwit nggoleki kaca kanggo disiram menyang disk lan njupuk kanggo kabutuhan dhewe - sing mengaruhi wektu panyuwunan sing lagi ditindakake. Yen sampeyan ndeleng sampeyan duwe parameter maxwritten_clean gedhe, iki tegese panulis latar mburi ora nindakake tugas lan sampeyan kudu nambah paramèter bgwriter_lru_maxpagessupaya dheweke bisa nindakake luwih akeh ing siji siklus, mbusak luwih akeh kaca.

Lan indikator liyane sing migunani yaiku buffers_backend_fsync. Backends ora nindakake fsync amarga iku alon. Padha pass fsync munggah checkpointer tumpukan IO. Checkpointer duwe antrian dhewe, kanthi periodik ngolah fsync lan nyinkronake kaca ing memori karo file ing disk. Yen antrian checkpointer gedhe lan kebak, backend kepeksa nindakake fsync dhewe lan iki bakal nyuda backend., IE klien bakal nampa respon luwih saka iku bisa. Yen sampeyan ndeleng sing duwe nilai iki luwih saka nul, banjur iki wis masalah lan sampeyan kudu menehi perhatian marang setelan panulis latar mburi lan uga ngevaluasi kinerja subsistem disk.

Pènget: _Teks ing ngisor iki nggambarake tampilan statistik sing ana gandhengane karo replikasi. Umume jeneng tampilan lan fungsi wis diganti jeneng ing Postgres 10. Inti saka jeneng kasebut yaiku ngganti jeneng kasebut. xlog ing wal и location ing lsn ing jeneng fungsi / tampilan, etc. Tuladha tartamtu, fungsi pg_xlog_location_diff() diganti jeneng dadi pg_wal_lsn_diff()._

Kita uga duwe akeh ing kene. Nanging kita mung butuh barang sing ana gandhengane karo lokasi.

Yen kita ndeleng manawa kabeh nilai padha, mula iki becik lan replika ora ketinggalan master.

Posisi heksadesimal iki minangka posisi ing log transaksi. Iku terus-terusan mundhak yen ana sawetara kegiatan ing database: sisipan, mbusak, etc.

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

Yen iki beda, banjur ana sawetara jenis lag. Lag punika lag replika saka master, IE data beda antarane server.

Ana telung alasan kanggo wektu tundha:

Iku subsistem disk sing ora bisa nangani file sinkronisasi nyerat.
Iki minangka kesalahan jaringan, utawa kakehan jaringan, nalika data ora duwe wektu kanggo nggayuh replika lan ora bisa ngasilake.
Lan prosesor. Prosesor minangka kasus sing arang banget. Lan aku wis ndeleng kaping pindho utawa telu, nanging uga bisa kedadeyan.

Lan ing kene ana telung pitakon sing ngidini kita nggunakake statistik. Kita bisa ngira jumlah sing dicathet ing log transaksi. Ana fungsi kuwi pg_xlog_location_diff lan kita bisa ngira replikasi lag ing bita lan detik. Kita uga nggunakake nilai saka tampilan iki (VIEWs) kanggo iki.

Wigati: _Tinimbang pg_xlog_locationdiff (), sampeyan bisa nggunakake operator subtract lan nyuda siji lokasi saka liyane. Nyaman.

Kanthi lag, yaiku ing detik, ana siji wayahe. Yen ora ana kegiatan ing master, transaksi ana bab 15 menit kepungkur lan ora ana kegiatan, lan yen kita katon ing lag iki tiron, kita bakal weruh lag 15 menit. Iki worth ngelingi. Lan bisa mimpin kanggo stupor nalika nonton lag iki.

pg_stat_all_tables minangka tampilan liyane sing migunani. Iku nuduhake statistik ing tabel. Nalika kita duwe tabel ing database, ana sawetara kegiatan karo, sawetara tumindak, kita bisa njaluk informasi iki saka tampilan iki.

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

Wangsulan: Bab ingkang pisanan sing bisa kita deleng yaiku scan tabel urutan. Nomer kasebut sawise perangan kasebut ora mesthi ala lan ora nuduhake manawa kita kudu nindakake apa wae.

Nanging, ana metrik kapindho - seq_tup_read. Iki minangka jumlah larik sing bali saka scan sequential. Yen jumlah rata-rata ngluwihi 1, 000, 10, 000, mula iki wis dadi indikator yen sampeyan kudu nggawe indeks ing endi wae supaya akses kasebut miturut indeks, utawa bisa ngoptimalake pitakon sing nggunakake pemindaian urut-urutan kasebut. iki ora kelakon.

Conto prasaja - umpamane panyuwunan kanthi OFFSET lan LIMIT sing gedhe. Contone, 100 larik ing tabel dipindai lan sawise iku 000 larik sing dibutuhake dijupuk, lan baris sing dipindai sadurunge dibuwang. Iki uga kasus ala. Lan panjaluk kasebut kudu dioptimalake. Lan ing kene ana pitakon SQL sing gampang sampeyan bisa ndeleng lan ngevaluasi nomer sing ditampa.

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

Ukuran meja uga bisa dipikolehi nggunakake tabel iki lan nggunakake fungsi tambahan pg_total_relation_size(), pg_relation_size().

Umumé, ana metacommands dt и di, sing bisa digunakake ing PSQL lan uga ndeleng ukuran tabel lan indeks.

Nanging, nggunakake fungsi mbantu kita katon ing ukuran tabel, malah njupuk menyang akun indeks, utawa tanpa njupuk menyang akun indeks, lan wis nggawe sawetara prakiraan adhedhasar wutah saka database, IE, carane tuwuh karo kita, karo apa kakiyatan, lan wis tarik sawetara Serat bab Optimization ukuran.

Tulis kegiatan. Apa iku rekaman? Ayo katon ing operasi UPDATE – operasi nganyari baris ing tabel. Nyatane, nganyari minangka rong operasi (utawa luwih). Iki nglebokake versi baris anyar lan menehi tandha versi baris lawas minangka lungse. Mengko, autovacuum bakal teka lan ngresiki metu iki versi lungse saka garis, tandha panggonan iki kasedhiya kanggo digunakake maneh.

Uga, nganyari ora mung babagan nganyari tabel. Iku isih nganyari indeks. Yen sampeyan duwe akeh indeks ing meja, banjur kanthi nganyari, kabeh indeks sing kolom sing dianyari ing query melu uga kudu dianyari. Indeks kasebut uga bakal duwe versi baris sing ora bisa digunakake sing kudu diresiki.

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

Lan amarga desaine, UPDATE minangka operasi abot. Nanging padha bisa digawe luwih gampang. mangan hot updates. Dheweke muncul ing versi PostgreSQL 8.3. Lan apa iki? Iki minangka nganyari entheng sing ora nyebabake indeks dibangun maneh. Yaiku, kita nganyari rekaman, nanging mung rekaman ing kaca (sing kalebu tabel) sing dianyari, lan indeks isih nuduhake rekaman sing padha ing kaca kasebut. Ana sawetara logika kerja sing menarik, nalika ana vakum, banjur ana rantai kasebut hot mbangun maneh lan kabeh terus bisa tanpa nganyari indeks, lan kabeh kedadeyan kanthi kurang sumber daya.

Lan nalika sampeyan duwe n_tup_hot_upd gedhe, iku apik banget. Iki tegese nganyari entheng menang lan iki luwih murah kanggo kita babagan sumber daya lan kabeh apik.

ALTER TABLE table_name SET (fillfactor = 70);

Carane nambah volume hot updateov? Kita bisa nggunakake fillfactor. Nemtokake ukuran papan kosong sing dilindhungi nalika ngisi kaca ing tabel nggunakake INSERT. Nalika sisipan menyang meja, dheweke ngisi kaca kasebut, aja ninggalake papan kosong. Banjur kaca anyar disorot. Data diisi maneh. Lan iki minangka prilaku standar, fillfactor = 100%.

Kita bisa nyetel fillfactor dadi 70%. Yaiku, kanthi sisipan, kaca anyar dialokasikan, nanging mung 70% kaca sing diisi. Lan kita duwe 30% ing cadangan. Nalika sampeyan kudu nindakake nganyari, paling kamungkinan bakal kelakon ing kaca sing padha, lan versi anyar saka baris pas ing kaca sing padha. Lan hot_update bakal rampung. Iki nggawe luwih gampang kanggo nulis ing tabel.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Autovacuum antrian. Autovacuum minangka subsistem sing ana sawetara statistik ing PostgreSQL. Kita mung bisa ndeleng ing tabel ing pg_stat_activity carane akeh vakum kita duwe ing wayahe. Nanging, iku banget angel kanggo ngerti carane akeh tabel ing antrian wis ing pamindhahan.

Wigati: _Wiwit Postgres 10, kahanan nelusuri vakum vakum saya tambah akeh - tampilan pg_stat_progress wis munculvakum, kang nemen simplifies Jeksa Agung bisa ngetokake saka ngawasi autovacuum.

Kita bisa nggunakake pitakon sing disederhanakake iki. Lan kita bisa ndeleng kapan vakum kudu digawe. Nanging, kepiye lan kapan kudu miwiti vakum? Iki minangka versi lawas saka senar sing dakkandhakake sadurunge. Nganyari wis kedadeyan, versi anyar baris wis dilebokake. Versi lungse saka senar wis katon. Tabel pg_stat_user_tables ana parameter kuwi n_dead_tup. Iku nuduhake nomer "mati" larik. Lan sanalika jumlah larik mati wis dadi luwih saka batesan tartamtu, autovacuum bakal teka ing meja.

Lan carane ambang iki diwilang? Iki persentasi banget tartamtu saka jumlah total larik ing tabel. Ana parameter autovacuum_vacuum_scale_factor. Iku nemtokake persentasi. Ayo dadi 10% + ana batesan dhasar tambahan 50 garis. Lan apa sing kedadeyan? Nalika kita duwe larik mati luwih saka "10% + 50" kabeh larik ing meja, kita sijine meja ing autovacuum.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Nanging, ana siji titik. Ambang dhasar kanggo paramèter av_base_thresh и av_scale_factor bisa diutus individu. Lan, miturut, ambang ora bakal global, nanging individu kanggo meja. Mulane, kanggo ngetung, sampeyan kudu nggunakake trik lan trik. Lan yen sampeyan kasengsem, sampeyan bisa ndeleng pengalaman kolega saka Avito (link ing slide ora bener lan wis dianyari ing teks).

Padha nulis kanggo munin pluginsing njupuk menyang akun iki. Ana footcloth ing rong lembar. Nanging nimbang bener lan cukup èfèktif ngidini kita kanggo netepke ngendi kita kudu akèh vakum kanggo tabel, ngendi ana sethitik.

Apa sing bisa ditindakake? Yen kita duwe antrian dawa lan autovacuum ora bisa ngatasi, banjur kita bisa nambah jumlah buruh vakum, utawa mung nggawe vakum luwih agresifsupaya micu sadurungé, pangolahan meja ing bêsik cilik. Lan kanthi mangkono antrian bakal suda. - Sing utama ing kene yaiku ngawasi beban ing disk, amarga. Bab vakum ora gratis, sanajan ana piranti SSD / NVMe, masalah kasebut dadi kurang katon.

pg_stat_all_indexes minangka statistik ing indeks. Dheweke ora gedhe. Lan kita bisa entuk informasi babagan panggunaan indeks saka iku. Lan contone, kita bisa nemtokake indeks sing kita duwe ekstra.

Kaya sing wis dakkandhakake, nganyari ora mung nganyari tabel, nanging uga nganyari indeks. Dadi, yen kita duwe akeh indeks ing meja, banjur nalika nganyari baris ing tabel, indeks kolom sing diindeks uga kudu dianyari, lan yen kita duwe indeks sing ora digunakake sing ora ana pindai indeks, mula digantung karo kita minangka ballast. Lan sampeyan kudu nyingkirake. Kanggo iki kita butuh lapangan idx_scan. Kita mung ndeleng jumlah pindai indeks. Yen indeks duwe nol pindai sajrone panyimpenan statistik sing relatif suwe (paling ora 2-3 minggu), mula kemungkinan iki minangka indeks sing ala, mula kudu diilangi.

Wigati: Nalika nggoleki indeks sing ora digunakake ing kasus kluster replikasi streaming, sampeyan kudu mriksa kabeh simpul kluster, amarga statistik ora global, lan yen indeks ora digunakake ing master, banjur bisa digunakake ing réplika (yen ana mbukak).

Loro pranala:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Iki minangka conto pitakon sing luwih maju babagan carane nggoleki indeks sing ora digunakake.

Link kapindho minangka pitakon sing rada menarik. Ana logika banget non-trivial ing. Aku nyaranake kanggo review.

Apa maneh sing kudu diringkes dening indeks?

Indeks sing ora digunakake ora apik.
Dheweke njupuk papan.
Alon mudhun operasi nganyari.
Karya ekstra kanggo vakum.

Yen kita mbusak indeks sing ora digunakake, kita mung bakal nggawe database luwih apik.

Tampilan sabanjure yaiku pg_stat_activity. Iki minangka analog saka sarana ps, mung ing PostgreSQL. Yen ps'ohm sampeyan nonton proses ing sistem operasi, banjur pg_stat_activity bakal nuduhake sampeyan kegiatan ing PostgreSQL.

Apa sing bisa dijupuk saka kana?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

Kita bisa ndeleng aktivitas sakabèhé sing kedadeyan ing database. Kita bisa nggawe penyebaran anyar. Kabeh mbledhos ing kana, sambungan anyar ora ditampa, kesalahane nyebar ing aplikasi kasebut.

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Kita bisa mbukak pitakonan kaya iki lan ndeleng total persentasi sambungan relatif kanggo watesan sambungan maksimum lan ndeleng sing kita duwe paling sambungan. Lan ing kasus iki, kita ndeleng pangguna kasebut cron_role kabuka 508 sambungan. Lan soko kedaden kanggo wong. Sampeyan kudu menehi hasil karo lan ndeleng. Lan iku cukup bisa sing iki sawetara jinis anomali nomer sambungan.

Yen kita duwe beban OLTP, pitakon kudu cepet, cepet banget, lan ora ana pitakon sing dawa. Nanging, yen ana panjaluk sing dawa, banjur ing wektu sing cendhak ora ana sing kudu kuwatir, nanging ing roto dawa, pitakon dawa cilaka database, padha nambah efek bloat saka tabel nalika fragmentasi Tabel occurs. Pitakonan bloat lan dawa kudu dibuwang.

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

Wigati dicathet: kanthi panjaluk kasebut, kita bisa nemtokake panjalukan lan transaksi sing dawa. Kita nggunakake fungsi clock_timestamp() kanggo nemtokake wektu kerja. Panjaluk dawa sing ditemokake, kita bisa ngelingi, nglakokake explain, katon ing plans lan piye wae ngoptimalake. We njupuk panjalukan dawa saiki lan manggon ing.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Transaksi ala yaiku nganggur ing transaksi lan nganggur ing transaksi (aborted).

Iki artine apa? Transaksi duwe sawetara negara. Lan salah sawijining negara kasebut bisa ditindakake kapan wae. Ana lapangan kanggo nemtokake negara state ing tampilan iki. Lan kita nggunakake kanggo nemtokake negara.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Lan, kaya sing dakkandhakake ing ndhuwur, rong negara kasebut nganggur ing transaksi lan nganggur ing transaksi (aborted) iku ala. Opo iki? Iki nalika aplikasi mbukak transaksi, nindakake sawetara tumindak lan nindakake bisnis. Transaksi tetep mbukak. Digantung, ora ana sing kedadeyan, butuh sambungan, ngunci baris sing diganti lan bisa uga nambah kembung tabel liyane, amarga arsitektur mesin transaksional Postrges. Lan transaksi kasebut uga kudu ditembak, amarga padha mbebayani ing umum, ing kasus apa wae.

Yen sampeyan ndeleng manawa sampeyan duwe luwih saka 5-10-20 ing database sampeyan, mula sampeyan kudu kuwatir lan miwiti nindakake apa wae karo dheweke.

Kene kita uga digunakake kanggo wektu pitungan clock_timestamp(). Kita njupuk transaksi, kita ngoptimalake aplikasi.

Nalika aku ngandika ndhuwur, kunci nalika loro utawa luwih transaksi saingan siji utawa klompok sumber. Kanggo iki kita duwe lapangan waiting kanthi nilai boolean true utawa false.

Bener - iki tegese proses nunggu, ana sing kudu ditindakake. Nalika proses nunggu, banjur klien sing miwiti proses uga nunggu. Klien ing browser lenggah lan uga ngenteni.

Pènget: _Miwiti saka Postgres 9.6, lapangan waiting dibusak lan diganti dening rong lapangan liyane informatif wait_event_type и wait_event._

Apa aku kudu? Yen sampeyan ndeleng bener kanggo dangu, sampeyan kudu njaluk nyisihaken saka panjalukan kuwi. Kita mung njupuk transaksi kasebut. Kita nulis kanggo pangembang apa sing kudu dioptimalake supaya ora ana balapan kanggo sumber daya. Banjur para pangembang ngoptimalake aplikasi kasebut supaya ora kedadeyan.

Lan kasus sing ekstrem, nanging bisa uga ora fatal kedadeyan deadlock. Loro transaksi wis nganyari loro sumber daya, banjur padha ngakses maneh, wis kanggo sumber ngelawan. PostgreSQL ing kasus iki njupuk lan njupuk transaksi kasebut dhewe supaya sing liyane bisa terus kerja. Iki kahanan buntu lan dheweke ora ngerti awake dhewe. Mulane, PostgreSQL kepeksa njupuk langkah-langkah ekstrem.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

Lan ing kene ana rong pitakon sing ngidini sampeyan nglacak kunci. Kita nggunakake tampilan pg_locks, sing ngidini sampeyan nglacak kunci sing abot.

Lan pranala pisanan yaiku teks panyuwunan dhewe. Iku cukup dawa.

Lan pranala kapindho yaiku artikel babagan kunci. Iku migunani kanggo maca, menarik banget.

Dadi apa sing kita deleng? We ndeleng loro panjalukan. Transaksi karo ALTER TABLE iku transaksi pamblokiran. Diwiwiti, nanging ora rampung, lan aplikasi sing ngirim transaksi iki nindakake perkara liya ing endi wae. Lan panjalukan kapindho yaiku nganyari. Ngenteni tabel alter rampung sadurunge nerusake karyane.

Mangkene carane kita bisa ngerteni sapa sing ngunci, sapa sing nyekel, lan kita bisa ngatasi iki.

Modul sabanjure yaiku pg_stat_statements. Kaya sing dakkandhakake, iku modul. Kanggo nggunakake, sampeyan kudu mbukak perpustakaan ing konfigurasi, miwiti maneh PostgreSQL, nginstal modul (karo siji printah), banjur kita bakal duwe tampilan anyar.

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

Apa sing bisa dijupuk saka kana? Yen kita ngomong babagan prasaja, kita bisa njupuk wektu eksekusi query rata-rata. Wektu saya tambah akeh, tegese PostgreSQL nanggapi alon-alon lan ana sing kudu ditindakake.

Kita bisa ndeleng transaksi nulis paling aktif ing database sing ngganti data ing buffer sambungan. Delengen sapa sing nganyari utawa mbusak data ing kana.

Lan kita mung bisa ndeleng statistik sing beda kanggo pitakon kasebut.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

Kita pg_stat_statements digunakake kanggo nggawe laporan. Kita ngreset statistik sapisan dina. Ayo dikumpulake. Sadurunge ngreset statistik ing wektu sabanjure, kita nggawe laporan. Punika link kanggo laporan. Sampeyan bisa nonton.

Apa sing kita tindakake? We ngetung statistik sakabèhé kanggo kabeh panjalukan. Banjur, kanggo saben pitakon, kita ngetung kontribusi individu kanggo statistik sakabèhé iki.

Lan apa sing bisa kita deleng? Kita bisa ndeleng total wektu eksekusi kabeh panjalukan saka jinis tartamtu ing latar mburi kabeh panjalukan liyane. Kita bisa ndeleng CPU lan I / O panggunaan ing hubungan kanggo gambar sakabèhé. Lan wis kanggo ngoptimalake panjalukan iki. Kita nggawe pitakon ndhuwur adhedhasar laporan iki lan wis entuk panganan kanggo dipikirake babagan apa sing kudu dioptimalake.

Apa sing ana ing mburi layar? Isih ana kiriman sing ora dakanggep, amarga wektune mung winates.

Ana pgstattuple uga modul tambahan saka paket contribus standar. Iku ngijini sampeyan kanggo ngevaluasi bloat tabel, supaya disebut-. fragmentasi meja. Lan yen fragmentasi gedhe, sampeyan kudu nyopot, gunakake alat sing beda. Lan fungsi pgstattuple dianggo kanggo dangu. Lan liyane tabel, maneh bakal bisa.

Kontribusi sabanjure yaiku pg_buffercache. Ngidini sampeyan mriksa buffer sing dienggo bareng: sepira intensif lan kaca buffer tabel sing digunakake. Lan mung ngidini sampeyan ndeleng buffer sing dienggo bareng lan ngevaluasi apa sing kedadeyan ing kana.

Modul sabanjure yaiku pgfincore. Ngidini sampeyan nindakake operasi meja tingkat rendah liwat telpon sistem mincore(), IE ngidini sampeyan mbukak tabel menyang buffer sing dienggo bareng, utawa mbongkar. Lan ngidini, antara liya, kanggo mriksa cache kaca sistem operasi, yaiku, pira tabel sing ana ing cache kaca, ing buffer sing dienggo bareng, lan mung ngidini sampeyan ngevaluasi beban ing meja.

Modul sabanjure yaiku pg_stat_kcache. Iku uga nggunakake telpon sistem getrusage(). Lan nglakokake sadurunge lan sawise panyuwunan dieksekusi. Lan ing statistik sing dipikolehi, ngidini kita ngira jumlah panjaluk kita ing disk I / O, yaiku, operasi karo sistem file lan ndeleng panggunaan prosesor. Nanging, modul kasebut isih enom (khe-khe) lan kanggo karyane mbutuhake PostgreSQL 9.4 lan pg_stat_statements, sing dakkandhakake sadurunge.

Kemampuan kanggo nggunakake statistik migunani. Sampeyan ora mbutuhake piranti lunak pihak katelu. Sampeyan bisa ndeleng, ndeleng, nindakake soko, nindakake.
Nggunakake statistik gampang, iku SQL kosong. Sampeyan ngumpulake panjalukan, nyusun, ngirim, ndeleng.
Statistik mbantu njawab pitakonan. Yen sampeyan duwe pitakonan, pindhah menyang statistik - goleki, tarik kesimpulan, analisa asil.
Lan eksperimen. Akeh panjaluk, akeh data. Sampeyan bisa tansah ngoptimalake sawetara pitakon sing wis ana. Sampeyan bisa nggawe versi dhewe saka panjalukan sing cocog karo sampeyan luwih saka asli lan nggunakake.