Transkript izvještaja Alekseja Lesovskog iz 2015. "Duboko zaronite u internu statistiku PostgreSQL-a"
Odricanje od odgovornosti autora izvještaja: Napominjem da je ovaj izvještaj datiran u novembru 2015. godine – prošlo je više od 4 godine i prošlo je dosta vremena. Verzija 9.4 o kojoj se govori u izvještaju više nije podržana. U protekle 4 godine objavljeno je 5 novih izdanja u kojima ima dosta inovacija, poboljšanja i promjena u statistici, a dio materijala je zastario i nije relevantan. Dok recenziram, trudio sam se da obeležim ova mesta kako ne bih obmanuo čitaoca. Nisam prepisivao ove pasuse, ima ih puno i rezultat će biti potpuno drugačiji izvještaj.
PostgreSQL DBMS je ogroman mehanizam, a ovaj mehanizam se sastoji od mnogih podsistema, čiji koordiniran rad direktno utiče na performanse DBMS-a. Tokom rada prikupljaju se statistike i informacije o radu komponenti, što vam omogućava da procenite efikasnost PostgreSQL-a i preduzmete mere za poboljšanje performansi. Međutim, ovih informacija ima dosta i oni su predstavljeni u prilično pojednostavljenom obliku. Obrada ovih 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 Aleksey. Kao što je Ilya rekao, govoriću o PostgreSQL statistici.
PostgreSQL statistika aktivnosti. PostgreSQL ima dvije statistike. Statistika aktivnosti o kojoj će biti riječi. I statistiku rasporeda o distribuciji podataka. Govoriću posebno o statistici aktivnosti PostgreSQL-a, koja nam omogućava da procenimo performanse i nekako ih poboljšamo.
Reći ću vam kako efikasno koristiti statistiku za rješavanje raznih problema koje imate ili biste mogli imati.
Šta neće biti u izvještaju? U izvještaju se neću doticati statistike planera, jer... Ovo je posebna tema za poseban izvještaj o tome kako se podaci pohranjuju u bazi podataka i kako planer upita dobiva predstavu o kvalitativnim i kvantitativnim karakteristikama ovih podataka.
I neće biti recenzija alata, neću upoređivati jedan proizvod s drugim. Neće biti reklame. Ostavimo to po strani.
Želim vam pokazati da je korištenje statistike korisno. Neophodno je. Bezbedan je za upotrebu. Sve što nam treba je redovan SQL i osnovno poznavanje SQL-a.
I razgovarajmo o tome koju statistiku odabrati za rješavanje problema.
Ako pogledamo PostgreSQL i pokrenemo naredbu na operativnom sistemu za pregled procesa, vidjet ćemo "crnu kutiju". Vidjet ćemo neke procese koji nešto rade, a po imenu možemo otprilike zamisliti šta oni tamo rade, šta rade. Ali, u suštini, to je crna kutija, ne možemo gledati unutra.
Možemo vidjeti opterećenje CPU-a top, možemo pogledati korištenje memorije od strane nekih sistemskih uslužnih programa, ali nećemo moći pogledati unutar PostgreSQL-a. Za to 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 na to gdje se troši vrijeme. To su dvije stvari: obrađuje zahtjeve klijenata iz aplikacija i pozadinske zadatke koje PostgreSQL obavlja da bi nastavio da radi.
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 dalji rad. Zahtjev se šalje planeru. Planer gradi plan upita. Šalje ga dalje na izvršenje. Postoji neka vrsta blok ulaza/izlaza podataka povezanih sa tabelama i indeksima. Potrebni podaci se čitaju sa 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šavaju u dnevniku ili sakupljaču statistike. I rezultat zahtjeva se šalje nazad klijentu. Nakon čega klijent može ponovo sve ponoviti sa novim zahtjevom.
Šta je sa pozadinskim zadacima i pozadinskim procesima? Imamo nekoliko procesa koji održavaju bazu podataka u normalnom radnom režimu. Ovi procesi će se također dotaknuti u izvještaju: autovacuum, checkpointer, procesi vezani za replikaciju, pozadinski pisac. Dotaknut ću se svakog od njih u izvještaju.
Koji problemi postoje sa statistikom?
- Ima mnogo informacija. PostgreSQL 9.4 pruža 109 metrika za pregled statističkih podataka. Međutim, ako baza podataka pohranjuje mnogo tabela, shema, baza podataka, tada će se sve ove metrike morati pomnožiti s odgovarajućim brojem tabela, baza podataka. Odnosno, ima još više informacija. I vrlo je lako udaviti se u njemu.
- Sljedeći problem je što su statistike predstavljene brojačima. Ako pogledamo ove statistike, vidjet ćemo konstantno povećanje brojača. A ako je prošlo mnogo vremena od resetovanja statistike, videćemo vrednosti u milijardama. I ništa nam ne govore.
- Nema priče. Ako ste imali neki kvar, nešto je palo prije 15-30 minuta, nećete moći koristiti statistiku i vidjeti šta se dogodilo prije 15-30 minuta. Ovo je problem.
- Problem je nedostatak alata ugrađenog u PostgreSQL. Programeri kernela ne pružaju nikakav uslužni program. Oni nemaju ništa slično. Oni jednostavno pružaju statistiku u bazi podataka. Koristite ga, zatražite od njega, radite šta god želite.
- Budući da nema alata ugrađenog u PostgreSQL, to uzrokuje još jedan problem. Mnogo alata trećih strana. Svaka kompanija koja ima više ili manje direktne ruke pokušava da napiše sopstveni program. I kao rezultat toga, zajednica ima mnogo alata koji se mogu koristiti za rad sa statistikom. Neki alati imaju određene mogućnosti, drugi alati nemaju druge mogućnosti ili postoje neke nove mogućnosti. I dolazi do situacije da trebate koristiti dva, tri ili četiri alata koji se međusobno preklapaju i imaju različite funkcije. Ovo je veoma neprijatno.
Šta iz ovoga slijedi? Važno je moći direktno uzimati statistiku, kako ne bi ovisili o programima, ili nekako sami poboljšali ove programe: dodajte neke funkcije da biste dobili svoju korist.
I potrebno vam je osnovno znanje o SQL-u. Da biste dobili neke podatke iz statistike, potrebno je kreirati SQL upite, tj. morate znati kako se kompajliraju select i join.
Statistika nam govori nekoliko stvari. Mogu se podijeliti u kategorije.
- Prva kategorija su događaji koji se dešavaju u bazi podataka. To je kada se u bazi podataka dogodi neki događaj: zahtjev, pristup tabeli, autovakuum, urezivanje, onda su to sve događaji. Brojači koji odgovaraju ovim događajima se povećavaju. I možemo pratiti ove događaje.
- Druga kategorija su svojstva objekata kao što su tabele i baze podataka. Imaju imovinu. Ovo je veličina stolova. Možemo pratiti rast tabela i rast indeksa. Vidimo 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đe prate.
Izvori statistike su predstavljeni na sljedeći način:
- U dijeljenoj memoriji (dijeljenim baferima) postoji segment za pohranjivanje statičkih podataka, postoje i oni brojači koji se konstantno povećavaju kada se pojave određeni događaji, ili neki momenti u radu baze podataka.
- Svi ovi brojači nisu dostupni korisniku, pa čak ni administratoru. Ovo su stvari niskog nivoa. Za pristup im, PostgreSQL pruža sučelje u obliku SQL funkcija. Možemo napraviti odabir bacanja koristeći ove funkcije i dobiti neku vrstu metrike (ili skupa metrika).
- Međutim, korištenje ovih funkcija nije uvijek zgodno, pa su funkcije osnova za poglede (VIEWs). Ovo su virtuelne tabele koje daju statistiku o određenom podsistemu, ili o određenom skupu događaja u bazi podataka.
- Ovi ugrađeni prikazi (VIEWs) su primarni korisnički interfejs za rad sa statistikom. Oni su podrazumevano dostupni bez ikakvih dodatnih podešavanja, možete ih odmah koristiti, pogledati i od njih uzimati informacije. A tu su i doprinosi. Doprinosi su zvanični. Možete instalirati paket postgresql-contrib (na primjer, postgresql94-contrib), učitati potrebni modul u konfiguraciju, odrediti parametre za njega, restartovati PostgreSQL i možete ga koristiti. (Bilješka. Ovisno o distribuciji, u novijim verzijama paket contrib je dio glavnog paketa).
- A tu su i nezvanični doprinosi. Oni nisu uključeni u standardnu PostgreSQL distribuciju. Moraju se ili kompajlirati ili instalirati kao biblioteka. Opcije mogu biti veoma različite, u zavisnosti od toga šta je programer ovog nezvaničnog doprinosa smislio.
Ovaj slajd predstavlja sve one PRIKAZE i neke od funkcija koje su dostupne u PostgreSQL 9.4. Kao što vidimo, ima ih mnogo. I prilično je lako zbuniti se ako se prvi put susrećete s njim.
Međutim, ako uzmemo prethodnu sliku Как тратится время на PostgreSQL i kompatibilan sa ovom listom, dobijamo ovu sliku. Možemo koristiti svaki pogled (VIEWs) ili svaku funkciju za jednu ili drugu svrhu da dobijemo odgovarajuću statistiku kada je PostgreSQL pokrenut. I već možemo dobiti neke informacije o radu podsistema.
Prva stvar koju ćemo pogledati je pg_stat_database. Kao što vidimo, ovo je performans. U njemu ima mnogo informacija. Najrazličitije informacije. I daje vrlo korisna znanja o tome šta se dešava u našoj bazi podataka.
Koje korisne stvari možemo uzeti odatle? Počnimo od najjednostavnijih stvari.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Prva stvar koju možemo pogledati je postotak pogodaka u keš memoriji. Stopa pogodaka u keš memoriji je korisna metrika. Omogućava vam da procijenite koliko podataka je uzeto iz keša dijeljenih bafera i koliko je pročitano s diska.
To je jasno što više keš hitova imamo, to bolje. Ovu metriku mjerimo u procentima. I, na primjer, ako je naš postotak ovih cache pogodaka veći od 90%, onda je to dobro. Ako padne ispod 90%, to znači da nemamo dovoljno memorije da zadržimo vruću glavu podataka u memoriji. A da bi koristio ove podatke, PostgreSQL je prisiljen da pristupi disku i to je sporije nego da su podaci čitani iz memorije. I morate razmišljati o povećanju memorije: ili povećajte dijeljene bafere 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;Šta još možete uzeti iz ovog nastupa? Možete vidjeti anomalije koje se javljaju u bazi podataka. Šta je ovdje prikazano? Postoje urezivanja, vraćanja, kreiranje privremenih datoteka, njihova veličina, zastoji i konflikti.
Možemo iskoristiti ovaj zahtjev. Ovaj SQL je prilično jednostavan. A ove podatke možemo pogledati ovdje.
A evo i graničnih vrijednosti. Gledamo omjer urezivanja i vraćanja. Commits je uspješna potvrda transakcije. Rollbacks su rollback, tj. transakcija je obavila neki posao, naprezala bazu podataka, nešto izračunala, a onda je došlo do greške i rezultati transakcije se odbacuju. To je broj vraćanja unatrag u stalnom porastu je loš. I trebali biste ih nekako izbjegavati i urediti kod da se to ne dogodi.
Konflikti su povezani sa replikacijom. I njih takođe treba izbegavati. Ako imate neke upite koji se izvršavaju na replici i dođe do sukoba, onda morate riješiti te konflikte i vidjeti što se događa. Detalje možete 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 zahtjev je pristupio jednom resursu i preuzeo zaključavanje, drugi zahtjev je pristupio drugom resursu i također je preuzeo zaključavanje, a zatim su oba zahtjeva pristupila resursima jedan drugog i blokirala se dok čekaju da susjed otpusti zaključavanje. Ovo je također problematična situacija. Njima se treba pozabaviti na nivou ponovnog pisanja aplikacija i serijalizacije pristupa resursima. A ako vidite da vam se zastoji stalno povećavaju, morate pogledati detalje u logovima, analizirati situacije koje se pojavljuju i vidjeti u čemu je problem.
Privremeni fajlovi (temp_files) su takođe loši. Kada korisnički zahtjev nema dovoljno memorije za smještaj operativnih, privremenih podataka, kreira datoteku na disku. I sve operacije koje bi mogao izvršiti 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 u PostgreSQL će dobiti odgovor nešto kasnije. Ako se sve ove operacije izvode u memoriji, Postgres će reagirati mnogo brže i klijent će manje čekati.
Pg_stat_bgwriter - Ovaj prikaz opisuje rad dva PostgreSQL pozadinska podsistema: checkpointer и background writer.
Prvo, pogledajmo kontrolne tačke, tzv. checkpoints. Šta su kontrolne tačke? Kontrolna točka je pozicija u dnevniku transakcija koja pokazuje da su sve promjene podataka zabilježene u dnevniku uspješno sinkronizirane s podacima na disku. Proces, ovisno o opterećenju i postavkama, može biti dugotrajan i uglavnom se sastoji od sinhronizacije prljavih stranica u zajedničkim baferima sa datotekama podataka na disku. čemu služi? Da je PostgreSQL stalno pristupao disku i preuzimao podatke odatle i pisao podatke o svakom pristupu, to bi bilo sporo. Stoga PostgreSQL ima memorijski segment čija veličina ovisi o postavkama u konfiguraciji. Postgres pohranjuje podatke uživo u ovu memoriju za kasniju obradu ili upite. U slučaju zahtjeva za promjenom podataka, isti se mijenjaju. I dobijamo dvije verzije podataka. Jedan je u našoj memoriji, drugi je na disku. I periodično trebate sinkronizirati ove podatke. Moramo sinkronizirati ono što je promijenjeno u memoriji na disk. Za to su vam potrebne kontrolne tačke.
Checkpoint prolazi kroz zajedničke bafere, označava prljave stranice koje su potrebne za kontrolnu tačku. Zatim pokreće drugi prolaz kroz dijeljene bafere. A stranice koje su označene za kontrolnu tačku, već ih sinhronizuje. Na ovaj način se podaci sinkroniziraju s diskom.
Postoje dvije vrste kontrolnih punktova. Jedna kontrolna tačka se izvršava sa timeoutom. Ova kontrolna tačka je korisna i dobra – checkpoint_timed. I postoje kontrolni punktovi na zahtjev - checkpoint required. Ova kontrolna tačka se javlja kada imamo veoma veliki zapis podataka. Zabilježili smo mnogo dnevnika transakcija. A PostgreSQL vjeruje da sve ovo treba što prije sinhronizirati, napraviti kontrolnu tačku i krenuti dalje.
A ako pogledate statistiku pg_stat_bgwriter i video šta imaš checkpoint_req je mnogo 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 da upiše podatke na disk. Kontrolna tačka vremenskog ograničenja je manje stresna i izvodi se prema internom rasporedu i na neki način je raspoređena tokom vremena. PostgreSQL ima mogućnost da pauzira rad i ne opterećuje diskovni podsistem. Ovo je korisno za PostgreSQL. A upiti koji se izvršavaju tokom kontrolne tačke neće doživjeti stres zbog činjenice da je podsistem diska zauzet.
A za podešavanje kontrolne tačke postoje tri parametra:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Oni vam omogućavaju da regulišete rad kontrolnih tačaka. Ali neću se zadržavati na njima. Njihov uticaj je posebna tema.
Upozorenje: Verzija 9.4 o kojoj se govori u izvještaju više nije relevantna. U modernim verzijama PostgreSQL parametar checkpoint_segments zamijenjen parametrima min_wal_size и max_wal_size.
Sljedeći podsistem je pozadinski pisac − background writer. Šta on radi? Stalno radi u beskonačnoj petlji. Skenira stranice u zajedničkim baferima i izbacuje prljave stranice koje pronađe na disk. Dakle, pomaže kontrolnom pokazivaču da radi manje posla tokom izvršavanja kontrolne tačke.
Za šta je još potrebno? Pruža potrebu za praznim stranicama u zajedničkim baferima ako su iznenada potrebne (u velikim količinama i odmah) za smještaj podataka. Pretpostavimo da je nastala situacija kada su prazne stranice bile potrebne za dovršetak zahtjeva, a one su već bile u zajedničkim baferima. Postgresivna backend samo ih pokupi i koristi, ne mora ništa sam da čisti. Ali ako odjednom nema takvih stranica, backend pauzira rad i počinje tražiti stranice kako bi ih izbacio na disk i preuzeo za svoje potrebe - što negativno utječe na vrijeme trenutnog izvršavanja zahtjeva. Ako vidite da imate parametar maxwritten_clean velika, to znači da pozadinski pisac ne radi svoj posao i morate povećati parametre bgwriter_lru_maxpages, tako da može da uradi više posla u jednom ciklusu, očisti više stranica.
I još jedan vrlo koristan indikator je buffers_backend_fsync. Pozadina se ne sinhronizuje jer je spora. Oni prosleđuju fsync na IO stack kontrolni pokazivač. Kontrolni pokazivač ima svoj vlastiti red čekanja, periodično obrađuje fsync i sinkronizira stranice u memoriji s datotekama na disku. Ako je red na kontrolnom pokazivaču velik i pun, onda je pozadina prisiljena da sama radi fsync i to usporava rad pozadine, odnosno klijent će dobiti odgovor kasnije nego što bi mogao. Ako vidite da je vaša vrijednost veća od nule, to je već problem i morate obratiti pažnju na postavke pisača u pozadini i također procijeniti performanse podsistema diska.
Upozorenje: _Sljedeći tekst opisuje statističke poglede povezane s replikacijom. Većina imena pogleda i funkcija preimenovana je u Postgres 10. Suština preimenovanja je bila zamjena xlog na wal и location na lsn u nazivima funkcija/prikaza, itd. Konkretan primjer, funkcija pg_xlog_location_diff() je preimenovan u pg_wal_lsn_diff()._
Imamo puno stvari i ovdje. Ali trebaju nam samo stavke koje se odnose na lokaciju.
Ako vidimo da su sve vrijednosti jednake, onda je ovo idealna opcija i replika ne zaostaje za masterom.
Ova heksadecimalna pozicija ovdje je pozicija u dnevniku transakcija. Stalno se povećava ako postoji bilo kakva aktivnost u bazi podataka: umetanje, brisanje 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 ove stvari različite, onda postoji neka vrsta zaostajanja. Lag je kašnjenje između replike i mastera, tj. podaci se razlikuju između servera.
Tri su razloga za kašnjenje:
- Ovaj podsistem diska ne može da se nosi sa sinhronizacijom datoteka snimanja.
- To su moguće mrežne greške, odnosno preopterećenje mreže, kada podaci nemaju vremena da stignu do replike i ona je ne može reproducirati.
- I procesor. Procesor je vrlo rijedak slučaj. I to sam vidio dva-tri puta, ali i ovo se može dogoditi.
A evo tri upita koja nam omogućavaju da koristimo statistiku. Možemo procijeniti koliko smo evidentirali u dnevniku transakcija. Postoji takva funkcija pg_xlog_location_diff i možemo procijeniti kašnjenje replikacije u bajtovima i sekundama. Za ovo 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. Udoban.
Postoji jedna tačka sa kašnjenjem, koja je u sekundama. Ako nema aktivnosti na masteru, transakcija je bila tu prije otprilike 15 minuta i nema aktivnosti, a ako pogledamo ovo kašnjenje na replici, vidjet ćemo kašnjenje od 15 minuta. Ovo vrijedi zapamtiti. I ovo može biti zbunjujuće kada gledate ovo kašnjenje.
Pg_stat_all_tables je još jedan koristan pogled. Prikazuje statistiku na tabelama. Kada imamo tabele u bazi podataka, postoji neka aktivnost sa njom, neke akcije, možemo dobiti ove informacije iz ovog pogleda.
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;Prva stvar koju možemo pogledati su sekvencijalna skeniranja preko stola. Sam broj nakon ovih prolaza nije nužno loš i nije pokazatelj da nešto treba da radimo.
Međutim, postoji i druga metrika - seq_tup_read. Ovo je broj redova vraćenih iz sekvencijalnog skeniranja. Ako prosječan broj prelazi 1, 000, 10, 000, onda je to već pokazatelj da možda trebate negdje izgraditi indeks kako bi upiti bili bazirani na indeksu ili je moguće optimizirati upite koji koriste takva sekvencijalna skeniranja tako da da se to ne dešava je bilo.
Jednostavan primjer - recimo zahtjev sa velikim OFFSET i LIMIT troškovima. Na primjer, skenira se 100 redova u tabeli i nakon toga se uzima 000 potrebnih redova, a prethodni skenirani redovi se odbacuju. Ovo je takođe loš slučaj. I takve upite treba optimizirati. A evo jednostavnog SQL upita u kojem 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 tablice se također mogu dobiti pomoću ove tablice i korištenjem dodatnih funkcija pg_total_relation_size(), pg_relation_size().
Generalno, postoje metanaredbe dt и di, koji se može koristiti u PSQL-u i također vidjeti veličine tablica i indeksa.
Međutim, korištenje funkcija nam pomaže da pogledamo veličine tablica, čak i uzimajući u obzir indekse, ili bez uzimanja u obzir indekse, i već napravimo neke procjene na osnovu rasta baze podataka, odnosno kako raste, kojim intenzitetom i izvući neke zaključke o optimizaciji veličine.
Snimanje aktivnosti. Šta je snimak? Pogledajmo operaciju UPDATE – operacija ažuriranja redova u tabeli. U stvari, ažuriranje su dvije operacije (ili čak više). Ovo je umetanje nove verzije reda i označavanje stare verzije reda kao zastarjele. Nakon toga će doći autovakuum i očistiti ove zastarjele verzije linija, označavajući ovo mjesto kao dostupno za ponovnu upotrebu.
Osim toga, ažuriranje se ne odnosi samo na ažuriranje tabele. Ovo je također ažuriranje indeksa. Ako imate mnogo indeksa u tabeli, tada će tokom ažuriranja svi indeksi koji uključuju polja ažurirana u upitu takođe morati da se ažuriraju. Ovi indeksi će također imati zastarjele verzije redova 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 oni se mogu olakšati. Jedi hot updates. Pojavili su se u PostgreSQL verziji 8.3. A šta je ovo? Ovo je lagano ažuriranje koje ne uzrokuje ponovnu izgradnju indeksa. Odnosno, ažurirali smo zapis, ali je ažuriran samo zapis na stranici (koji pripada tabeli), a indeksi i dalje upućuju na isti zapis na stranici. Postoji pomalo zanimljiva operativna logika: kada dođe vakuum, on stvara ove lance hot ponovo gradi i sve nastavlja da radi bez ažuriranja indeksa, a sve se dešava sa manje rasipanja resursa.
A kada ti n_tup_hot_upd veliko, onda je jako dobro. 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 jačinu zvuka hot updateov? Možemo koristiti fillfactor. Određuje veličinu rezervisanog slobodnog prostora prilikom popunjavanja stranice u tabeli pomoću INSERT-a. Kada se dodaci dodaju u tabelu, oni u potpunosti ispunjavaju stranicu i ne ostavljaju prazan prostor. Zatim je nova stranica istaknuta. Podaci se ponovo popunjavaju. A ovo je zadano ponašanje, fillfactor = 100%.
Možemo napraviti faktor punjenja 70%. Odnosno, prilikom umetanja nova stranica je bila istaknuta, ali je popunjeno samo 70% stranice. I ostalo nam je 30% kao rezerva. Kada trebate izvršiti ažuriranje, to će se najvjerovatnije dogoditi na istoj stranici, a nova verzija linije će stati na istu stranicu. I hot_update će biti urađeno. Ovo 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));Autovacuum queue. Autovacuum je podsistem za koji postoji vrlo malo statistike u PostgreSQL-u. U tabelama u pg_stat_activity možemo vidjeti samo koliko vakuuma trenutno imamo. Međutim, vrlo je teško odmah shvatiti koliko je tablica u redu čekanja.
Napomena: _Počevši od Postgresa 10, situacija sa praćenjem Vatovca se znatno poboljšala - pojavio se pg_stat_progress prikazvakuum, što značajno pojednostavljuje pitanje praćenja vakuuma automobila.
Možemo koristiti ovaj pojednostavljeni upit. I možemo vidjeti kada će vakuum morati da se napravi. Ali kako i kada treba da počne vakuum? Ovo su naslijeđene verzije linija o kojima sam ranije govorio. Došlo je do ažuriranja, umetnuta je nova verzija linije. Pojavila se zastarjela verzija niza. U tabeli pg_stat_user_tables postoji takav parametar n_dead_tup. Prikazuje broj "mrtvih" linija. I čim broj mrtvih redova postane veći od određenog praga, na stol će doći autovakuum.
I kako se izračunava ovaj prag? Ovo je vrlo specifičan procenat ukupnog broja redova u tabeli. Postoji parametar autovacuum_vacuum_scale_factor. Određuje procenat. Recimo 10% + postoji dodatni osnovni prag od 50 linija. I šta se dešava? Kada imamo više mrtvih redova od “10% + 50” svih redova u tabeli, onda tabelu stavljamo na autovakuum.
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 stvar. Osnovni pragovi za parametre av_base_thresh и av_scale_factor može se dodijeliti pojedinačno. I, shodno tome, prag neće biti globalan, već individualan za sto. Stoga, da biste izračunali, morate koristiti trikove i trikove. A ako ste zainteresovani, onda možete pogledati iskustvo naših kolega iz Avita (link na slajdu je nevažeći i ažuriran je u tekstu).
Oni su pisali za , koji ove stvari uzima u obzir. Tamo je krpa za noge od dva lista. Ali izračunava ispravno i prilično efikasno nam omogućava da procijenimo gdje nam je potrebno puno vakuuma za stolove gdje je malo.
Šta možemo učiniti povodom toga? Ako imamo veliki red i autovakum se ne može nositi, onda možemo povećati broj radnika usisivača ili jednostavno učiniti usisivač agresivnijim, tako da se aktivira ranije, obrađuje tabelu u malim komadima. I time će se red čekanja smanjiti. — Ovdje je glavna stvar pratiti opterećenje diskova, jer... vakuum nije besplatna stvar, iako je pojavom SSD/NVMe uređaja problem postao manje primjetan.
Pg_stat_all_indexes je statistika o indeksima. Ona nije velika. I možemo ga koristiti za dobijanje informacija o upotrebi indeksa. I na primjer, možemo odrediti koje indekse imamo ekstra.
kao što sam već rekao, ažuriranje nije samo ažuriranje tabela, već je i ažuriranje indeksa. Shodno tome, ako imamo mnogo indeksa u tabeli, onda prilikom ažuriranja redova u tabeli, indeksi indeksiranih polja takođe treba da se ažuriraju, a 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 skeniranih indeksa. Ako indeksi imaju nula skeniranja tokom relativno dugog perioda skladištenja statistike (najmanje 2-3 sedmice), onda su najvjerovatnije to loši indeksi, moramo ih se riješiti.
Napomena: Kada tražite neiskorištene indekse u slučaju klastera strujne replikacije, morate provjeriti sve čvorove klastera, jer statistika nije globalna, a ako se indeks ne koristi na masteru, onda se može koristiti na replikama (ako postoji opterećenje).
dva linka:
Ovo su napredniji primjeri upita o tome kako potražiti neiskorištene indekse.
Drugi link je prilično zanimljiv zahtjev. Postoji jedna vrlo netrivijalna logika. Preporučujem ga za referencu.
Šta još vrijedi sumirati pomoću indeksa?
-
Nekorišteni indeksi su loši.
-
Zauzimaju prostor.
-
Usporite operacije ažuriranja.
-
Dodatni rad za usisivač.
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. Ako psOnda pogledate procese u operativnom sistemu pg_stat_activity Pokazaće vam aktivnost unutar PostgreSQL-a.
Koje korisne stvari možemo uzeti odatle?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Vidimo opštu aktivnost, šta se dešava u bazi podataka. Možemo napraviti novi raspored. Ovdje je sve eksplodiralo, nove veze se ne prihvataju, greške se slijevaju 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 ukupan postotak veza u odnosu na maksimalno ograničenje veze i vidjeti ko ima najviše veza. I u ovom datom slučaju vidimo tog korisnika cron_role otvorio 508 veza. I tamo mu se nešto dogodilo. Moramo se pozabaviti time i pogledati. I sasvim 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, onda kratkoročno nema razloga za brigu, osim Dugoročno, dugi upiti štete bazi podataka; oni povećavaju efekat napuhanosti tabela kada dođe do fragmentacije tabele. Morate se riješiti i nadutosti 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;Napomena: ovim zahtjevom možemo identificirati duge upite i transakcije. Koristimo funkciju clock_timestamp() za određivanje radnog vremena. Duge upite koje smo pronašli, možemo ih zapamtiti, ispuniti explain, pogledaj planove i nekako optimizuj. Obaramo 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 transakciji (prekinuto).
Šta to znači? Transakcije imaju više stanja. I jedno od ovih stanja se može pretpostaviti u bilo kom trenutku. Postoji polje za definiranje stanja state u ovoj prezentaciji. I koristimo ga da odredimo stanje.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';I, kao što sam rekao gore, ova dva stanja mirovanje u transakciji i mirovanje u transakciji (prekinuto) su loši. Šta je to? Tada je aplikacija otvorila transakciju, izvršila neke radnje i nastavila sa svojim poslom. Transakcija ostaje otvorena. Visi, ništa se u njemu ne događa, preuzima vezu, zaključava promijenjene redove i potencijalno povećava naduvanost drugih tabela, zbog arhitekture Postrges transakcijskog motora. I takve transakcije bi također trebale biti oborene, jer su one općenito štetne, u svakom slučaju.
Ako vidite da ih u bazi podataka imate više od 5-10-20, onda morate da se zabrinete i počnete nešto da radite sa njima.
Ovdje također koristimo za izračunavanje vremena clock_timestamp(). Snimamo transakcije i optimiziramo aplikaciju.
Kao što sam već rekao, blokiranje je kada se dvije ili više transakcija bore za jedan ili grupu resursa. Za ovo imamo teren waiting sa logičkom vrijednošću true ili false.
Tačno – to znači da je proces na čekanju, da se nešto mora učiniti. Kada proces čeka, to znači da i klijent koji je pokrenuo ovaj proces čeka. Klijent sjedi u pretraživaču i takođe čeka.
Upozorenje: _Počevši od polja Postgres verzije 9.6 waiting uklonjena i umjesto toga dodana još dva informativna polja wait_event_type и wait_event._
Što da radim? Ako dugo vidite istinito, to znači da morate da se riješite takvih zahtjeva. Takve transakcije jednostavno obaramo. Pišemo programerima da se moraju nekako optimizirati kako ne bi došlo do utrke za resursima. A zatim programeri optimiziraju aplikaciju tako da se to ne dogodi.
A ekstreman, ali potencijalno nefatalan slučaj jeste pojava zastoja. Dvije transakcije su ažurirale dva resursa, a zatim im ponovo pristupile, ovaj put na suprotne resurse. U ovom slučaju, PostgreSQL ubija samu transakciju kako bi druga mogla nastaviti s radom. Ovo je ćorsokak i ona to ne može sama da shvati. Zbog toga je PostgreSQL primoran da preduzme ekstremne mere.
A evo i dva upita koja vam omogućavaju praćenje blokiranja. Koristimo pogled pg_locks, što vam omogućava da pratite teške brave.
A prva veza je sam tekst zahtjeva. Prilično je dugo.
A drugi link je članak o bravama. Korisno je čitati, veoma je zanimljivo.
Dakle, šta vidimo? Vidimo dva zahtjeva. Transakcija sa ALTER TABLE je transakcija koja blokira. Počelo je, ali nije završeno, a aplikacija koja je snimila ovu transakciju negdje radi druge stvari. A drugi zahtjev je ažuriranje. On čeka da se alter table završi prije nego što nastavi svoj posao.
Tako možemo saznati ko je koga zaključao, koga drži i možemo dalje da se bavimo time.
Sljedeći modul je pg_stat_statements. Kao što sam rekao, ovo je modul. Da biste ga koristili, morate učitati njegovu biblioteku u konfiguraciju, ponovo 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;Šta 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 reaguje i da moramo nešto učiniti.
Možemo pogledati najaktivnije transakcije pisanja u bazi podataka koje mijenjaju podatke u zajedničkim baferima. Pogledajte ko tamo ažurira ili briše podatke.
I možemo jednostavno pogledati različite statistike za ove zahtjeve.
Mi pg_stat_statements Koristimo ga za izradu izvještaja. Resetujemo statistiku jednom dnevno. Hajde da ga akumuliramo. Prije resetiranja statistike sljedeći put, napravimo izvještaj. Evo linka do izvještaja. Možete ga gledati.
Šta mi radimo? Izračunavamo opštu statistiku za sve zahtjeve. Zatim, za svaki zahtjev računamo njegov pojedinačni doprinos ovoj ukupnoj statistici.
I šta možemo gledati? Možemo sagledati ukupno vrijeme izvršenja svih zahtjeva određenog tipa u odnosu na pozadinu svih ostalih zahtjeva. Možemo pogledati korištenje CPU-a i I/O resursa u odnosu na cjelokupnu sliku. I već optimizirajte ove upite. Na osnovu ovog izveštaja gradimo najpopularnije upite i već dobijamo hranu za razmišljanje o tome šta da optimizujemo.
Šta nam je ostalo iza kulisa? Ostalo je još nekoliko podnesaka koje nisam razmotrio jer je vrijeme ograničeno.
Postoje pgstattuple je također dodatni modul iz standardnog paketa doprinosi. Omogućava vam da procenite bloat tablice, tzv fragmentacija tablice. A ako postoji mnogo fragmentacije, morate je ukloniti i koristiti različite alate. I funkcija pgstattuple radi dugo vremena. I što više stolova ima, to će duže raditi.
Sledeći doprinos je pg_buffercache. Omogućava vam da provjerite dijeljene bafere: koliko se intenzivno i za koje tabele koriste stranice bafera. I jednostavno vam omogućava da pogledate u zajedničke bafere i procenite šta se tamo dešava.
Sljedeći modul je pgfincore. Omogućava operacije sa tablicom niskog nivoa putem sistemskog poziva mincore(), tj. omogućava vam da učitate tabelu u deljene bafere ili da je skinete. I, između ostalog, omogućava vam da pregledate keš stranice operativnog sistema, odnosno koliko prostora tabela zauzima u kešu stranice, u zajedničkim baferima, i jednostavno vam omogućava da procijenite radno opterećenje tabele.
Sljedeći modul - pg_stat_kcache. Takođe koristi sistemski poziv getrusage(). I izvršava ga prije i nakon izvršenja zahtjeva. A u rezultujućoj statistici nam omogućava da procijenimo koliko je naš zahtjev potrošio na disk I/O, tj. operacije sa datotečnim sistemom i gleda na korištenje procesora. Međutim, modul je mlad (kašalj, kašalj) i za rad mu je potreban PostgreSQL 9.4 i pg_stat_statements, koje sam ranije spomenuo.
-
Znati kako koristiti statistiku je korisno. Ne trebaju vam programi trećih strana. Možete ući, vidjeti, učiniti nešto, nešto postići.
-
Korištenje statistike nije teško, to je samo običan SQL. Sakupili ste zahtjev, sastavili ga, poslali, pogledali.
-
Statistika pomaže u odgovorima na pitanja. Ako imate pitanja, obratite se statistici - pogledajte, izvucite zaključke, analizirajte rezultate.
-
I eksperimentirajte. Mnogo je zahtjeva, puno podataka. Uvijek možete optimizirati postojeći upit. Možete napraviti vlastitu verziju zahtjeva koja vam više odgovara od originala i koristiti je.
reference
Odgovarajući linkovi koji su pronađeni u članku, na osnovu materijala, bili su u izvještaju.
Autor napišite više
(eng)
Sakupljač statistike
Funkcije sistemske administracije
Doprinos modulima
SQL alati i primjeri sql koda
Hvala svima na pažnji!
izvor: www.habr.com