Transkript izvješća Alexeya Lesovskog iz 2015. "Deep dive into PostgreSQL interna statistika"
Odricanje od odgovornosti autora izvješća: Napominjem da ovo izvješće nosi datum studeni 2015. - prošlo je više od 4 godine i puno je vremena prošlo. Verzija 9.4 o kojoj se govori u izvješću više nije podržana. Tijekom protekle 4 godine izašlo je 5 novih izdanja u kojima ima dosta inovacija, poboljšanja i promjena po pitanju statistike, a dio materijala je zastario i nije relevantan. Dok pregledavam, nastojao sam označiti ta mjesta kako ne bih doveo čitatelja u zabludu. Ove odlomke nisam prepisivao, ima ih puno i rezultat će biti potpuno drugačiji izvještaj.
PostgreSQL DBMS je ogroman mehanizam, a ovaj mehanizam se sastoji od mnogih podsustava, čiji koordinirani rad izravno utječe na performanse DBMS-a. Tijekom rada prikupljaju se statistike i informacije o radu komponenti, što vam omogućuje procjenu učinkovitosti PostgreSQL-a i poduzimanje mjera za poboljšanje performansi. Međutim, tih podataka ima jako puno i prikazani su u prilično pojednostavljenom obliku. Obrada tih informacija i njihovo tumačenje ponekad je potpuno netrivijalan zadatak, a "zoološki vrt" alata i uslužnih programa može lako zbuniti čak i naprednog DBA.
Dobar dan Moje ime je Aleksej. Kao što je Ilya rekao, govorit ću o PostgreSQL statistici.
PostgreSQL statistika aktivnosti. PostgreSQL ima dvije statistike. Statistika aktivnosti o kojoj će biti riječi. I statistika planera o distribuciji podataka. Posebno ću govoriti o statistici aktivnosti PostgreSQL-a, koja nam omogućuje da procijenimo izvedbu i nekako je poboljšamo.
Reći ću vam kako učinkovito koristiti statistiku za rješavanje različitih problema koje imate ili biste mogli imati.
Što neće biti u izvješću? U izvješću se neću doticati statistike planera jer... Ovo je posebna tema za zasebno izvješće o tome kako se podaci pohranjuju u bazi podataka i kako planer upita dobiva ideju o kvalitativnim i kvantitativnim karakteristikama tih podataka.
I neće biti recenzija alata, neću uspoređivati jedan proizvod s drugim. Neće biti oglašavanja. Ostavimo to po strani.
Želim vam pokazati da je korištenje statistike korisno. Nužno je. Siguran je za korištenje. Sve što nam treba je regularni SQL i osnovno znanje SQL-a.
I razgovarajmo o tome koju statistiku odabrati za rješavanje problema.
Ako pogledamo PostgreSQL i pokrenemo naredbu na operativnom sustavu za pregled procesa, vidjet ćemo "crnu kutiju". Vidjet ćemo neke procese koji nešto rade, a iz naziva možemo otprilike zamisliti što oni tamo rade, što rade. Ali, u biti, to je crna kutija, ne možemo pogledati unutra.
Možemo vidjeti opterećenje CPU-a top, možemo pogledati korištenje memorije od strane nekih uslužnih programa sustava, ali nećemo moći pogledati unutar PostgreSQL-a. Za ovo su nam potrebni drugi alati.
I nastavljajući dalje, reći ću vam gdje se provodi vrijeme. Ako zamislimo PostgreSQL u obliku takvog dijagrama, onda možemo odgovoriti gdje se troši vrijeme. To su dvije stvari: on obrađuje klijentske zahtjeve iz aplikacija i pozadinske zadatke koje PostgreSQL obavlja kako bi nastavio raditi.
Ako počnemo gledati u gornji lijevi kut, možemo vidjeti kako se obrađuju zahtjevi klijenata. Zahtjev dolazi iz aplikacije i otvara se klijentska sesija za daljnji rad. Zahtjev se šalje planeru. Planer izrađuje plan upita. Šalje dalje na izvršenje. Postoji neka vrsta ulaza/izlaza blok podataka povezanih s tablicama i indeksima. Potrebni podaci se učitavaju s diskova u memoriju u posebno područje "shared buffers". Rezultati zahtjeva, ako su ažuriranja, brisanja, bilježe se u dnevnik transakcija u WAL-u. Neke statističke informacije završe u dnevniku ili kolektoru statistike. I rezultat zahtjeva šalje se natrag klijentu. Nakon čega klijent sve može ponoviti s novim zahtjevom.
Što je s pozadinskim zadacima i pozadinskim procesima? Imamo nekoliko procesa koji održavaju bazu podataka i rade u normalnom načinu rada. Ovi će se procesi također dotaknuti u izvješću: autovacuum, checkpointer, procesi povezani s replikacijom, pozadinski pisac. Dotaknut ću se svakog od njih dok budem izvještavao.
Kakvi problemi postoje sa statistikom?
- Ima puno informacija. PostgreSQL 9.4 nudi 109 metrika za pregled statističkih podataka. Međutim, ako baza podataka pohranjuje mnogo tablica, shema, baza podataka, tada će se sve ove metrike morati pomnožiti s odgovarajućim brojem tablica, baza podataka. Odnosno, ima još više informacija. I u njemu se vrlo lako utopiti.
- Sljedeći problem je što statistiku predstavljaju brojači. Ako pogledamo ove statistike, vidjet ćemo stalno rastuće brojače. A ako je prošlo dosta vremena od resetiranja statistike, vidjet ćemo vrijednosti u milijardama. I ništa nam ne govore.
- Nema priče. Ako ste imali nekakav kvar, nešto je palo prije 15-30 minuta, nećete moći koristiti statistiku i vidjeti što se dogodilo prije 15-30 minuta. Ovo je problem.
- Problem je nedostatak alata ugrađenog u PostgreSQL. Programeri kernela ne pružaju nikakve uslužne programe. Oni nemaju ništa takvo. Oni jednostavno pružaju statistiku u bazi podataka. Iskoristite ga, postavite mu zahtjev, učinite što god želite.
- Budući da u PostgreSQL nema ugrađenog alata, to uzrokuje još jedan problem. Puno alata trećih strana. Svaka tvrtka koja ima više ili manje izravne ruke pokušava napisati vlastiti program. Kao rezultat toga, zajednica ima mnogo alata koji se mogu koristiti za rad sa statistikom. I neki alati imaju određene mogućnosti, drugi alati nemaju druge mogućnosti ili postoje neke nove mogućnosti. I dođe do situacije da trebate koristiti dva, tri ili četiri alata koji se međusobno preklapaju i imaju različite funkcije. Ovo je vrlo neugodno.
Što iz ovoga slijedi? Važno je moći izravno uzimati statistiku, kako ne biste ovisili o programima, ili nekako sami poboljšati te programe: dodajte neke funkcije kako biste imali vlastitu korist.
I potrebno vam je osnovno znanje SQL-a. Da biste dobili neke podatke iz statistike, morate kreirati SQL upite, tj. morate znati kako se sastavljaju select i join.
Statistika nam govori nekoliko stvari. Mogu se podijeliti u kategorije.
- Prva kategorija su događaji koji se događaju u bazi podataka. To je kada se dogodi neki događaj u bazi: zahtjev, pristup tablici, autovacuum, commit, onda su to sve događaji. Brojači koji odgovaraju tim događajima se povećavaju. I možemo pratiti te događaje.
- Druga kategorija su svojstva objekata kao što su tablice i baze podataka. Imaju svojstva. Ovo je veličina stolova. Možemo pratiti rast tablica i rast indeksa. Možemo vidjeti promjene u dinamici.
- I treća kategorija je vrijeme provedeno na događaju. Zahtjev je događaj. Ima svoju specifičnu mjeru trajanja. Počelo ovdje, završilo ovdje. Možemo to pratiti. Ili vrijeme potrebno za čitanje bloka s diska ili njegovo pisanje. Takve stvari se također prate.
Izvori statistike prikazani su kako slijedi:
- U zajedničkoj memoriji (shared buffers) postoji segment za pohranjivanje statičkih podataka, tu su i oni brojači koji se konstantno povećavaju kada se dogode određeni događaji ili se pojave neki momenti u radu baze podataka.
- Svi ti brojači nisu dostupni korisniku, a niti administratoru. To su stvari niske razine. Za pristup njima PostgreSQL nudi sučelje u obliku SQL funkcija. Pomoću ovih funkcija možemo odabrati bacanja i dobiti neku vrstu metrike (ili skup metrika).
- Međutim, korištenje ovih funkcija nije uvijek zgodno, pa su funkcije osnova za poglede (VIEW). To su virtualne tablice koje pružaju statistiku o određenom podsustavu ili o određenom skupu događaja u bazi podataka.
- Ovi ugrađeni pogledi (VIEW) su primarno korisničko sučelje za rad sa statistikom. Dostupni su standardno bez ikakvih dodatnih postavki, možete ih odmah koristiti, pregledavati i preuzimati informacije iz njih. A tu su i doprinosi. Doprinosi su službeni. Možete instalirati paket postgresql-contrib (na primjer, postgresql94-contrib), učitati traženi modul u konfiguraciji, odrediti parametre za njega, ponovno pokrenuti PostgreSQL i možete ga koristiti. (Bilješka. Ovisno o distribuciji, u novijim verzijama contrib paket je dio glavnog paketa).
- A tu su i neslužbeni doprinosi. Nisu uključeni u standardnu PostgreSQL distribuciju. Moraju se kompilirati ili instalirati kao biblioteka. Opcije mogu biti vrlo različite, ovisno o tome što je razvojni programer ovog neslužbenog doprinosa smislio.
Ovaj slajd predstavlja sve one VIEW-e i neke od funkcija koje su dostupne u PostgreSQL 9.4. Kao što vidimo, ima ih puno. I vrlo je lako zbuniti se ako se s tim susrećete prvi put.
Međutim, ako uzmemo prethodnu sliku Как тратится время на PostgreSQL i kompatibilan s ovim popisom, dobivamo ovu sliku. Možemo koristiti svaki pogled (VIEW) ili svaku funkciju za jednu ili drugu svrhu da dobijemo odgovarajuću statistiku kada se izvodi PostgreSQL. I već možemo dobiti neke informacije o radu podsustava.
Prvo što ćemo pogledati je pg_stat_database. Kao što vidimo, radi se o performansu. U njemu ima mnogo informacija. Najrazličitije informacije. I daje vrlo korisne informacije o tome što se događa u našoj bazi podataka.
Što korisne stvari možemo uzeti odatle? Počnimo s najjednostavnijim stvarima.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Prvo što možemo pogledati je postotak pogodaka predmemorije. Stopa pogodaka predmemorije korisna je metrika. Omogućuje vam procjenu koliko je podataka preuzeto iz zajedničke predmemorije međuspremnika i koliko je pročitano s diska.
Jasno je da što više cache pogodaka imamo, to bolje. Ovu metriku mjerimo kao postotak. I, na primjer, ako je naš postotak pogodaka predmemorije veći od 90%, onda je to dobro. Ako padne ispod 90%, to znači da nemamo dovoljno memorije za držanje vruće glave podataka u memoriji. A kako bi koristio te podatke, PostgreSQL je prisiljen pristupiti disku i to je sporije nego da su podaci čitani iz memorije. I trebate razmisliti o povećanju memorije: ili povećajte zajedničke međuspremnike ili povećajte hardversku memoriju (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;Što još možete uzeti iz ove izvedbe? Možete vidjeti anomalije koje se pojavljuju u bazi podataka. Što je ovdje prikazano? Postoje commitovi, vraćanja, stvaranje privremenih datoteka, njihova veličina, zastoji i sukobi.
Možemo iskoristiti ovaj zahtjev. Ovaj SQL je prilično jednostavan. A ove podatke možemo pogledati ovdje.
A ovdje su granične vrijednosti. Gledamo omjer predaja i vraćanja. Commits je uspješna potvrda transakcije. Vraćanja su vraćanja, tj. transakcija je obavila neki posao, opteretila bazu podataka, nešto izračunala, a onda je došlo do greške i rezultati transakcije su odbačeni. To je broj povrata koji se stalno povećava je loš. I treba ih nekako izbjeći, te urediti kod da se to ne dogodi.
Konflikti su povezani s replikacijom. I njih također treba izbjegavati. Ako imate neke upite koji se izvršavaju na replici i pojave se sukobi, tada trebate razvrstati te sukobe i vidjeti što se događa. Detalji se mogu pronaći u zapisnicima. I eliminirajte konfliktne situacije tako da zahtjevi aplikacije rade bez grešaka.
Zastoji su također loša situacija. Kada se zahtjevi bore za resurse, jedan je zahtjev pristupio jednom resursu i zaključao se, drugi zahtjev pristupio je drugom resursu i također preuzeo zaključavanje, a zatim su oba zahtjeva međusobno pristupila resursima i blokirala se čekajući da susjed otključa zaključavanje. Ovo je također problematična situacija. Njima se treba pozabaviti na razini prepisivanja aplikacija i serijaliziranja pristupa resursima. A ako vidite da se vaši zastoji stalno povećavaju, trebate pogledati detalje u zapisima, analizirati situacije koje se pojavljuju i vidjeti u čemu je problem.
Privremene datoteke (temp_files) također su loše. Kada korisnički zahtjev nema dovoljno memorije za smještaj operativnih, privremenih podataka, stvara se datoteka na disku. I sve operacije koje bi mogao izvesti u privremenom međuspremniku u memoriji počinju se izvoditi na disku. Sporo je. Ovo povećava vrijeme izvršenja upita. A klijent koji je poslao zahtjev PostgreSQL-u će dobiti odgovor malo kasnije. Ako se sve ove operacije izvode u memoriji, Postgres će puno brže reagirati i klijent će manje čekati.
Pg_stat_bgwriter - Ovaj prikaz opisuje rad dva pozadinska podsustava PostgreSQL: this checkpointer и background writer.
Prvo, pogledajmo kontrolne točke, tzv. checkpoints. Što su kontrolne točke? Kontrolna točka je pozicija u zapisniku transakcija koja pokazuje da su sve promjene podataka zabilježene u zapisniku uspješno sinkronizirane s podacima na disku. Proces, ovisno o opterećenju i postavkama, može biti dugotrajan i uglavnom se sastoji od sinkronizacije prljavih stranica u zajedničkim međuspremnicima s podatkovnim datotekama na disku. Čemu služi? Kad bi PostgreSQL stalno pristupao disku i dohvaćao podatke odatle, te pisao podatke pri svakom pristupu, bio bi spor. Stoga PostgreSQL ima memorijski segment čija veličina ovisi o postavkama u konfiguraciji. Postgres pohranjuje žive podatke u ovu memoriju za kasniju obradu ili postavljanje upita. U slučaju zahtjeva za promjenom podatka, isti se mijenja. I dobivamo dvije verzije podataka. Jedan je u našoj memoriji, drugi je na disku. I povremeno morate sinkronizirati ove podatke. Moramo sinkronizirati ono što je promijenjeno u memoriji na disk. Za ovo su vam potrebne kontrolne točke.
Kontrolna točka prolazi kroz zajedničke međuspremnike, označava prljave stranice koje su potrebne za kontrolnu točku. Zatim pokreće drugi prolaz kroz zajedničke međuspremnike. A stranice koje su označene za kontrolnu točku, već ih sinkronizira. Na taj se način podaci sinkroniziraju s diskom.
Postoje dvije vrste kontrolnih točaka. Jedna kontrolna točka se izvršava s vremenskim ograničenjem. Ova kontrolna točka je korisna i dobra – checkpoint_timed. A postoje i kontrolne točke na zahtjev - checkpoint required. Ova se kontrolna točka pojavljuje kada imamo vrlo velik zapis podataka. Zabilježili smo mnogo dnevnika transakcija. A PostgreSQL smatra da sve to treba što brže sinkronizirati, napraviti kontrolnu točku i krenuti dalje.
A ako ste pogledali statistiku pg_stat_bgwriter i vidio što imaš checkpoint_req je puno veći od checkpoint_timed, onda je ovo loše. Zašto loše? To znači da je PostgreSQL pod stalnim stresom kada treba pisati podatke na disk. Kontrolna točka vremenskog ograničenja manje je stresna i izvodi se prema internom rasporedu te je na neki način raspoređena u vremenu. PostgreSQL ima mogućnost pauziranja rada i ne opterećuje diskovni podsustav. Ovo je korisno za PostgreSQL. A upiti koji se izvršavaju tijekom kontrolne točke neće doživjeti stres zbog činjenice da je diskovni podsustav zauzet.
A za podešavanje kontrolne točke postoje tri parametra:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Omogućuju vam reguliranje rada kontrolnih točaka. Ali neću se zadržavati na njima. Njihov utjecaj je posebna tema.
Upozorenje: Verzija 9.4 o kojoj se govori u izvješću više nije relevantna. U modernim verzijama PostgreSQL-a parametar checkpoint_segments zamijenjen parametrima min_wal_size и max_wal_size.
Sljedeći podsustav je pozadinski pisac − background writer. Što on radi? Stalno radi u beskonačnoj petlji. Skenira stranice u zajedničkim međuspremnicima i izbacuje prljave stranice koje pronađe na disk. Stoga pomaže kontrolnom pokazivaču da radi manje posla tijekom izvođenja kontrolne točke.
Što još treba? Osigurava potrebu za praznim stranicama u zajedničkim međuspremnicima ako su iznenada potrebne (u velikim količinama i odmah) za smještaj podataka. Pretpostavimo da je došlo do situacije kada su bile potrebne prazne stranice za dovršetak zahtjeva, a one su već bile u dijeljenim međuspremnicima. Postgresive backend on ih samo ubere i koristi, ne mora sam ništa čistiti. Ali ako iznenada nema takvih stranica, backend pauzira rad i počinje tražiti stranice kako bi ih izbacio na disk i uzeo za svoje potrebe - što negativno utječe na vrijeme trenutnog izvršavanja zahtjeva. Ako vidite da imate parametar maxwritten_clean veliki, to znači da pisac u pozadini ne radi svoj posao i morate povećati parametre bgwriter_lru_maxpages, kako bi mogao obaviti više posla u jednom ciklusu, očistiti više stranica.
I još jedan vrlo koristan pokazatelj je buffers_backend_fsync. Pozadina se ne sinkronizira jer je spora. Oni prosljeđuju fsync kontrolnoj točki IO steka. Kontrolna točka ima vlastiti red čekanja, povremeno obrađuje fsync i sinkronizira stranice u memoriji s datotekama na disku. Ako je red čekanja na kontrolnoj točki velik i pun, tada je pozadina prisiljena sama izvršiti fsync i to usporava rad pozadine, odnosno klijent će dobiti odgovor kasnije nego što je mogao. Ako vidite da je vaša vrijednost veća od nule, to je već problem i trebate obratiti pozornost na postavke pozadinskog pisača i također procijeniti performanse diskovnog podsustava.
Upozorenje: _Sljedeći tekst opisuje statističke prikaze povezane s replikacijom. Većina naziva pogleda i funkcija preimenovana je u Postgres 10. Bit preimenovanja bila je zamijeniti xlog na wal и location na lsn u imenima funkcija/pogleda, itd. Poseban primjer, funkcija pg_xlog_location_diff() preimenovan je u pg_wal_lsn_diff()._
I ovdje imamo svašta. Ali trebamo samo stavke vezane uz lokaciju.
Ako vidimo da su sve vrijednosti jednake, onda je ovo idealna opcija i replika ne zaostaje za majstorom.
Ovaj heksadecimalni položaj ovdje je položaj u dnevniku transakcija. Stalno se povećava ako postoji bilo kakva aktivnost u bazi podataka: umetanja, brisanja itd.
сколько записано 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());Ako su te stvari različite, onda postoji neka vrsta zaostajanja. Lag je kašnjenje između replike i mastera, tj. podaci se razlikuju između poslužitelja.
Postoje tri razloga za kašnjenje:
- Ovaj podsustav diska ne može se nositi sa sinkronizacijom datoteke snimanja.
- Riječ je o mogućim mrežnim greškama, odnosno preopterećenju mreže, kada podaci ne stignu doći do replike i ona ih ne može reproducirati.
- I procesor. Procesor je vrlo rijedak slučaj. I ovo sam vidio dva-tri puta, ali i ovo se može dogoditi.
Evo tri upita koji nam omogućuju korištenje statistike. Možemo procijeniti koliko smo zabilježili u dnevniku transakcija. Postoji takva funkcija pg_xlog_location_diff i možemo procijeniti kašnjenje replikacije u bajtovima i sekundama. Za to također koristimo vrijednost iz ovog pogleda (VIEWS).
Napomena: _Umjesto pg_xlog_locationFunkcija diff() može koristiti operator oduzimanja i oduzimati jednu lokaciju od druge. Udobno.
Postoji jedan bod sa zaostatkom, koji je u sekundama. Ako nema aktivnosti na masteru, transakcija je bila tamo prije 15-ak minuta i nema aktivnosti, a ako pogledamo ovo kašnjenje na replici, vidjet ćemo kašnjenje od 15 minuta. Ovo vrijedi zapamtiti. I to može biti zbunjujuće kada gledate ovo kašnjenje.
Pg_stat_all_tables je još jedan koristan prikaz. Prikazuje statistiku na tablicama. Kada imamo tablice u bazi podataka, postoji neka aktivnost s njima, neke radnje, možemo dobiti te informacije iz ovog prikaza.
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;Prvo što možemo pogledati su sekvencijalna skeniranja preko stola. Sama brojka nakon ovih prolaza nije nužno loša i nije pokazatelj da trebamo nešto učiniti.
Međutim, postoji druga metrika - seq_tup_read. Ovo je broj redaka vraćenih sekvencijalnim skeniranjem. Ako prosječni broj prelazi 1, 000, 10, 000, to je već pokazatelj da možda negdje trebate izgraditi indeks tako da se upiti temelje na indeksu ili je moguće optimizirati upite koji koriste takva sekvencijalna skeniranja pa da se to ne dogodi bio.
Jednostavan primjer – recimo zahtjev s velikim OFFSET i LIMIT troškovima. Na primjer, skenira se 100 redaka u tablici i nakon toga se uzme 000 potrebnih redaka, a prethodni skenirani redovi se odbace. Ovo je također loš slučaj. I takve upite treba optimizirati. I ovdje je jednostavan SQL upit gdje možete pogledati ovo i procijeniti rezultirajuće brojeve.
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;Veličine tablica također se mogu dobiti pomoću ove tablice i pomoću dodatnih funkcija pg_total_relation_size(), pg_relation_size().
Općenito, postoje metanaredbe dt и di, koji se može koristiti u PSQL-u i također pregledavati veličine tablica i indeksa.
No korištenje funkcija nam pomaže da sagledamo veličine tablica, čak i uzimajući u obzir indekse, ili bez uzimanja u obzir indekse, i već na temelju rasta baze podataka, tj. kako raste, kojim intenzitetom i izvući neke zaključke o optimizaciji veličine.
Aktivnost snimanja. Što je snimka? Pogledajmo operaciju UPDATE – operacija ažuriranja redaka u tablici. Zapravo, ažuriranje se sastoji od dvije operacije (ili čak i više). Ovo je umetanje nove verzije retka i označavanje stare verzije retka kao zastarjele. Nakon toga, autovakuum će doći i očistiti ove zastarjele verzije vodova, označavajući ovo mjesto kao dostupno za ponovnu upotrebu.
Osim toga, ažuriranje nije samo ažuriranje tablice. Ovo je također ažuriranje indeksa. Ako imate mnogo indeksa u tablici, tada će se tijekom ažuriranja morati ažurirati svi indeksi koji uključuju polja ažurirana u upitu. Ovi će indeksi također sadržavati zastarjele verzije redaka koje će trebati očistiti.
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;A zbog svog novog dizajna, UPDATE je teška operacija. Ali mogu se olakšati. Jesti hot updates. Pojavili su se u PostgreSQL verziji 8.3. A što je ovo? Ovo je lagano ažuriranje koje ne uzrokuje ponovnu izgradnju indeksa. Odnosno, ažurirali smo zapis, ali ažuriran je samo zapis na stranici (koji pripada tablici), a indeksi i dalje pokazuju na isti zapis na stranici. Postoji pomalo zanimljiva operativna logika: kada dođe vakuum, stvara se ovi lanci hot ponovno gradi i sve nastavlja raditi bez ažuriranja indeksa, a sve se događa s manje rasipanja resursa.
A kad ti n_tup_hot_upd velika, onda je jako dobra. To znači da prevladavaju lagana ažuriranja i to nam je jeftinije u smislu resursa i sve je u redu.
ALTER TABLE table_name SET (fillfactor = 70);Kako povećati volumen hot updateov? Možemo koristiti fillfactor. Određuje veličinu rezerviranog slobodnog prostora prilikom popunjavanja stranice u tablici pomoću INSERT-a. Kada se dodaju umetci u tablicu, oni u potpunosti ispunjavaju stranicu i ne ostavljaju prazan prostor. Zatim je označena nova stranica. Ponovno se popunjavaju podaci. I ovo je zadano ponašanje, fillfactor = 100%.
Faktor ispune možemo učiniti 70%. To jest, tijekom umetanja, nova stranica je bila istaknuta, ali je samo 70% stranice bilo popunjeno. I ostalo nam je 30% rezerve. Kada trebate izvršiti ažuriranje, to će se najvjerojatnije dogoditi na istoj stranici, a nova verzija retka stat će na istu stranicu. I hot_update će biti gotov. To olakšava pisanje po tablicama.
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));Autovakuumski red. Autovacuum je podsustav za koji postoji vrlo malo statistike u PostgreSQL-u. Samo u tablicama u pg_stat_activity možemo vidjeti koliko vakuuma trenutno imamo. Međutim, vrlo je teško odmah razumjeti koliko je stolova u redu čekanja.
Napomena: _Počevši od Postgresa 10, situacija s praćenjem Vatovca se uvelike poboljšala - pojavio se pogled pg_stat_progressvakuum, što značajno pojednostavljuje pitanje praćenja vakuuma automobila.
Možemo koristiti ovaj pojednostavljeni upit. A možemo vidjeti kada će se morati napraviti vakuum. Ali kako i kada treba započeti vakuum? Ovo su naslijeđene verzije redaka o kojima sam ranije govorio. Došlo je do ažuriranja, umetnuta je nova verzija retka. Pojavila se zastarjela verzija niza. U stolu pg_stat_user_tables postoji takav parametar n_dead_tup. Prikazuje broj "mrtvih" linija. A čim broj mrtvih redova postane veći od određenog praga, autovakuum će doći na stol.
I kako se izračunava ovaj prag? Ovo je vrlo specifičan postotak ukupnog broja redaka u tablici. Postoji parametar autovacuum_vacuum_scale_factor. Određuje postotak. Recimo 10% + postoji dodatni osnovni prag od 50 redaka. I što se događa? Kada imamo više mrtvih redaka od “10% + 50” svih redaka u tablici, tada stavljamo tablicu na 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));Međutim, postoji jedna točka. Osnovni pragovi za parametre av_base_thresh и av_scale_factor mogu se dodijeliti pojedinačno. I, sukladno tome, prag neće biti globalni, već pojedinačni za tablicu. Stoga, da biste izračunali, morate koristiti trikove i trikove. A ako vas zanima, onda možete pogledati iskustva naših kolega iz Avita (link na slajdu je nevažeći i ažuriran je u tekstu).
Pisali su za , koji uzima u obzir ove stvari. Tamo je krpa od dva lista. Ali ispravno izračunava i prilično učinkovito nam omogućuje da procijenimo gdje trebamo puno vakuuma za stolove gdje ga ima malo.
Što možemo učiniti u vezi s tim? Ako imamo veliki red i autovakuum ne može da se nosi, onda možemo povećati broj usisivača, ili jednostavno usisavanje učiniti agresivnijim, tako da se aktivira ranije, obrađuje tablicu u malim dijelovima. I time će se smanjiti red čekanja. — Ovdje je najvažnije pratiti opterećenje diskova, jer... vakum nije besplatna stvar, iako je dolaskom SSD/NVMe uređaja problem postao manje uočljiv.
Pg_stat_all_indexes je statistika o indeksima. Ona nije velika. I možemo ga koristiti za dobivanje informacija o korištenju indeksa. Na primjer, možemo odrediti koje indekse imamo viška.
Kao što sam već rekao, ažuriranje nije samo ažuriranje tablica, to je također ažuriranje indeksa. Sukladno tome, ako imamo mnogo indeksa na tablici, tada prilikom ažuriranja redaka u tablici, indeksi indeksiranih polja također moraju biti ažurirani, i ako imamo neiskorištene indekse za koje nema skeniranja indeksa, onda oni vise kao balast. I moramo ih se riješiti. Za ovo nam je potrebno polje idx_scan. Jednostavno gledamo broj skeniranja indeksa. Ako indeksi imaju nula skeniranja tijekom relativno dugog razdoblja pohrane statistike (najmanje 2-3 tjedna), onda su to najvjerojatnije loši indeksi, moramo ih se riješiti.
Napomena: Kada tražite neiskorištene indekse u slučaju strujanja replikacijskih klastera, trebate provjeriti sve čvorove klastera, jer statistika nije globalna, i ako se indeks ne koristi na masteru, onda se može koristiti na replikama (ako tamo postoji opterećenje).
Dva linka:
Ovo su napredniji primjeri upita o tome kako tražiti neiskorištene indekse.
Drugi link je prilično zanimljiv zahtjev. Tu postoji vrlo netrivijalna logika. Preporučujem ga za referencu.
Što još vrijedi sažeti pomoću indeksa?
-
Neiskorišteni indeksi su loši.
-
Zauzimaju prostor.
-
Usporite operacije ažuriranja.
-
Dodatni posao za vakuum.
Ako uklonimo neiskorištene indekse, samo ćemo poboljšati bazu podataka.
Sljedeća prezentacija je pg_stat_activity. Ovo je analog uslužnog programa ps, samo u PostgreSQL-u. Ako psOnda pogledaj procese u operativnom sustavu pg_stat_activity Pokazat će vam aktivnost unutar PostgreSQL-a.
Što korisne stvari možemo uzeti odatle?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Možemo vidjeti opću aktivnost, što se događa u bazi podataka. Možemo napraviti novi raspored. Ovdje je sve eksplodiralo, nove veze se ne prihvaćaju, greške pljušte u aplikaciju.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Možemo pokrenuti ovakav upit i vidjeti ukupni postotak veza u odnosu na maksimalno ograničenje veza i vidjeti tko ima najviše veza. I u ovom slučaju vidimo tog korisnika cron_role otvorio 508 veza. I tamo mu se nešto dogodilo. Moramo se pozabaviti time i pogledati to. I vrlo je moguće da se radi o nekakvom anomalnom broju veza.
Ako imamo OLTP radno opterećenje, upiti bi trebali biti brzi, vrlo brzi i ne bi trebalo biti dugih upita. Međutim, ako se pojave dugi upiti, kratkoročno nema razloga za brigu, ali Dugoročno gledano, dugi upiti štete bazi podataka; povećavaju učinak nadimanja tablica kada dođe do fragmentacije tablice. Morate se riješiti i napuhanosti i dugih upita.
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;Imajte na umu: ovim zahtjevom možemo identificirati dugačke upite i transakcije. Koristimo funkciju clock_timestamp() za određivanje vremena rada. Dugi upiti koje smo pronašli, možemo ih zapamtiti, ispuniti ih explain, pogledati planove i nekako optimizirati. Odbacujemo trenutne duge zahtjeve i nastavljamo sa svojim životima.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Loše transakcije su transakcije u stanju mirovanja u transakciji i mirovanja u stanju transakcije (prekinuto).
Što to znači? Transakcije imaju višestruka stanja. I jedno od ovih stanja može se pretpostaviti u bilo kojem trenutku. Postoji polje za definiranje stanja state u ovoj prezentaciji. I njime određujemo stanje.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';I, kao što rekoh gore, ove dvije države mirovanje u transakciji i mirovanje u transakciji (prekinuto) su loši. Što je? Tada je aplikacija otvorila transakciju, napravila neke radnje i nastavila sa svojim poslom. Transakcija ostaje otvorena. Zastaje, ništa se u njemu ne događa, preuzima vezu, zaključava promijenjene retke i potencijalno povećava preopterećenost drugih tablica, zbog arhitekture Postrgesove transakcijske mašine. A takve transakcije također treba srušiti, jer su općenito štetne, u svakom slučaju.
Ako vidite da ih u bazi imate više od 5-10-20, onda se morate zabrinuti i početi nešto raditi s njima.
Ovdje također koristimo vrijeme izračuna clock_timestamp(). Snimamo transakcije i optimiziramo aplikaciju.
Kao što sam rekao gore, blokiranje je kada se dvije ili više transakcija bore za jedan ili grupu resursa. Za to imamo polje waiting s booleovom vrijednošću true ili false.
Istina – to znači da je proces u tijeku, treba nešto učiniti. Kada je proces na čekanju, to znači da klijent koji je pokrenuo ovaj proces također čeka. Klijent sjedi u pregledniku i također čeka.
Upozorenje: _Počevši od polja Postgres verzije 9.6 waiting uklonjeno i umjesto toga dodana još dva informativna polja wait_event_type и wait_event._
Što učiniti? Ako dugo vidite istinu, to znači da se morate riješiti takvih zahtjeva. Jednostavno obaramo takve transakcije. Pišemo programerima da moraju nekako optimizirati kako ne bi bilo utrke za resursima. A zatim programeri optimiziraju aplikaciju tako da se to ne dogodi.
A ekstremni, ali potencijalno nefatalan slučaj jest pojava zastoja. Dvije transakcije ažurirale su dva resursa, a zatim im ponovno pristupile, ovaj put suprotnim resursima. U ovom slučaju, PostgreSQL sam ubija transakciju kako bi druga mogla nastaviti s radom. Ovo je slijepa situacija i ona to ne može sama shvatiti. Stoga je PostgreSQL prisiljen poduzeti ekstremne mjere.
Evo dva upita koji vam omogućuju praćenje blokiranja. Koristimo pogled pg_locks, što vam omogućuje praćenje teških brava.
A prva poveznica je sam tekst zahtjeva. Prilično je dugačak.
A drugi link je članak o bravama. Korisno je čitati, vrlo je zanimljivo.
Dakle, što vidimo? Vidimo dva zahtjeva. Transakcija sa ALTER TABLE je blokirajuća transakcija. Pokrenuto je, ali nije dovršeno, a aplikacija koja je zabilježila ovu transakciju negdje radi druge stvari. A drugi zahtjev je ažuriranje. Čeka da završi stol za zamjenu prije nego što može nastaviti s radom.
Tako možemo saznati tko je koga zaključao, koga drži i time se dalje baviti.
Sljedeći modul je pg_stat_statements. Kao što sam rekao, ovo je modul. Da biste ga koristili, trebate učitati njegovu biblioteku u konfiguraciji, ponovno pokrenuti PostgreSQL, instalirati modul (jednom naredbom) i tada ćemo imati novi pogled.
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;Što možemo uzeti odatle? Ako govorimo o jednostavnim stvarima, možemo uzeti prosječno vrijeme izvršenja upita. Vrijeme raste, što znači da PostgreSQL sporo reagira i moramo nešto poduzeti.
Možemo pogledati najaktivnije transakcije pisanja u bazi podataka koje mijenjaju podatke u zajedničkim međuspremnicima. Pogledajte tko tamo ažurira ili briše podatke.
I možemo jednostavno pogledati različite statistike za te zahtjeve.
Mi pg_stat_statements Koristimo ga za izradu izvješća. Statistiku resetiramo jednom dnevno. Akumulirajmo ga. Prije ponovnog postavljanja statistike sljedeći put, napravimo izvješće. Ovdje je poveznica na izvješće. Možete ga gledati.
Što radimo? Izračunavamo opću statistiku za sve zahtjeve. Zatim, za svaki zahtjev, računamo njegov pojedinačni doprinos ovoj ukupnoj statistici.
A što možemo gledati? Možemo pogledati ukupno vrijeme izvršenja svih zahtjeva određene vrste u odnosu na sve ostale zahtjeve. Možemo pogledati CPU i upotrebu I/O resursa u odnosu na cjelokupnu sliku. I već optimizirajte ove upite. Gradimo glavne upite na temelju ovog izvješća i već dobivamo hranu za razmišljanje o tome što optimizirati.
Što nam je ostalo iza kulisa? Ostalo je još nekoliko prijava koje nisam uzeo u obzir jer je vrijeme ograničeno.
Tu je pgstattuple također je dodatni modul iz standardnog paketa doprinosa. Omogućuje vam procjenu bloat stolovi, tzv fragmentacija tablice. A ako postoji velika fragmentacija, trebate je ukloniti i koristiti različite alate. I funkcija pgstattuple radi dugo vremena. I što je više stolova, to će duže raditi.
Sljedeći doprinos je pg_buffercache. Omogućuje vam pregled zajedničkih međuspremnika: koliko se intenzivno i za koje tablice stranice međuspremnika koriste. I jednostavno vam omogućuje da pogledate u zajedničke međuspremnike i procijenite što se tamo događa.
Sljedeći modul je pgfincore. Omogućuje operacije tablica niske razine putem sistemskog poziva mincore(), tj. omogućuje vam da učitate tablicu u dijeljene međuspremnike ili je ispraznite. I omogućuje, između ostalog, pregled predmemorije stranica operativnog sustava, tj. koliko prostora tablica zauzima u predmemoriji stranica, u zajedničkim međuspremnicima, i jednostavno nam omogućuje procjenu radnog opterećenja tablice.
Sljedeći modul – pg_stat_kcache. Također koristi sistemski poziv getrusage(). I izvršava ga prije i nakon izvršenja zahtjeva. A u dobivenoj statistici, omogućuje nam da procijenimo koliko je naš zahtjev potrošio na I/O diska, tj. operacije s datotečnim sustavom i gleda korištenje procesora. No, modul je mlad (cough cough) i za svoj rad zahtijeva PostgreSQL 9.4 i pg_stat_statements, koje sam ranije spomenuo.
-
Korisno je znati kako se koristiti statistikom. Ne trebaju vam programi trećih strana. Možete ući, vidjeti, učiniti nešto, postići nešto.
-
Korištenje statistike nije teško, to je samo uobičajeni SQL. Prikupili ste zahtjev, sastavili ga, poslali, pogledali.
-
Statistika pomaže odgovoriti na pitanja. Ako imate pitanja, obratite se statistici - pogledajte, izvucite zaključke, analizirajte rezultate.
-
I eksperimentirajte. Puno je zahtjeva, puno podataka. Uvijek možete optimizirati postojeći upit. Možete napraviti vlastitu verziju zahtjeva koja vam više odgovara od izvorne i koristiti je.
reference
Odgovarajuće veze koje su pronađene u članku, na temelju materijala, bile su u izvješću.
Autor napiši više
(eng)
Sakupljač statistike
Funkcije administracije sustava
Moduli doprinosa
SQL alati i primjeri sql koda
Hvala svima na pažnji!
Izvor: www.habr.com