🥇Sukelduge PostgreSQL-i sisestatistikasse. Aleksei Lesovski

Aleksei Lesovski 2015. aasta aruande "Sügav sukeldumine PostgreSQL-i sisestatistikasse" transkriptsioon

Aruande autori lahtiütlus: Märgin, et see aruanne on dateeritud 2015. aasta novembrisse – sellest on möödunud rohkem kui 4 aastat ja palju aega. Aruandes käsitletud versiooni 9.4 enam ei toetata. Viimase 4 aasta jooksul on välja antud 5 uut väljaannet, milles on ilmunud palju statistikat puudutavaid uuendusi, täiustusi ja muudatusi ning osa materjalist on vananenud ega ole asjakohane. Ülevaatamise ajal püüdsin need kohad ära märkida, et mitte lugejat eksitada. Ma ei kirjutanud neid kohti ümber, neid on palju ja selle tulemusel tuleb hoopis teistsugune aruanne.

PostgreSQL DBMS on tohutu mehhanism ja see mehhanism koosneb paljudest alamsüsteemidest, mille koordineeritud töö mõjutab otseselt DBMS-i jõudlust. Töötamise ajal kogutakse statistikat ja teavet komponentide toimimise kohta, mis võimaldab hinnata PostgreSQL-i efektiivsust ja võtta meetmeid jõudluse parandamiseks. Seda teavet on aga palju ja see on esitatud üsna lihtsustatud kujul. Selle teabe töötlemine ja tõlgendamine on mõnikord täiesti mittetriviaalne ülesanne ning tööriistade ja utiliitide "loomaaed" võib isegi arenenud DBA hõlpsasti segadusse ajada.

Tere päevast Minu nimi on Aleksei. Nagu Ilja ütles, räägin ma PostgreSQL-i statistikast.

PostgreSQL-i tegevuse statistika. PostgreSQL-il on kaks statistikat. Tegevusstatistika, millest tuleb juttu. Ja ajakava statistika andmete levitamise kohta. Räägin konkreetselt PostgreSQL-i tegevusstatistikast, mis võimaldab jõudlust hinnata ja seda kuidagi parandada.

Ma räägin teile, kuidas statistikat tõhusalt kasutada mitmesuguste probleemide lahendamiseks, mis teil on või võivad tekkida.

Mida aruandes ei kuvata? Aruandes ma ajakava koostaja statistikat ei puuduta, sest. see on eraldi teema eraldi aruande jaoks selle kohta, kuidas andmeid andmebaasis hoitakse ja kuidas päringuplaneerija saab aimu nende andmete kvalitatiivsetest ja kvantitatiivsetest omadustest.

Ja tööriistade arvustusi ei tehta, ma ei võrdle üht toodet teisega. Reklaami ei tule. Jätame selle vahele.

Tahan teile näidata, et statistika kasutamine on kasulik. See on vajalik. Kasutage seda kartmatult. Kõik, mida vajame, on tavaline SQL ja põhiteadmised SQL-ist.

Ja me räägime sellest, millist statistikat probleemide lahendamiseks valida.

Kui vaatame PostgreSQL-i ja käivitame operatsioonisüsteemis protsesside vaatamiseks käsu, näeme "musta kasti". Me näeme mingeid protsesse, mis midagi teevad, ja nime järgi saame umbkaudu ette kujutada, mida nad seal teevad, mida nad teevad. Kuid tegelikult on see must kast, me ei saa sisse vaadata.

Saame vaadata protsessori koormust top, näeme mõnede süsteemiutiliitide mälukasutust, kuid me ei saa vaadata PostgreSQL-i. Selleks vajame muid tööriistu.

Ja edasi jätkates ütlen teile, kus aega veedetakse. Kui kujutame PostgreSQL-i sellise skeemi kujul, siis on võimalik vastata, kuhu aeg kulub. Need on kaks asja: see on rakenduste kliendipäringute töötlemine ja taustatoimingud, mida PostgreSQL selle töö jätkamiseks täidab.

Kui hakkame vaatama vasakut ülanurka, näeme, kuidas klientide taotlusi töödeldakse. Taotlus tuleb rakendusest ja edasiseks tööks avatakse kliendiseanss. Taotlus edastatakse planeerijale. Planeerija koostab päringuplaani. Saadab selle edasi täitmiseks. Tabelite ja indeksitega on seotud mingisugused ploki I/O andmed. Vajalikud andmed loetakse ketastelt mällu spetsiaalsesse piirkonda, mida nimetatakse "jagatud puhvriteks". Päringu tulemused, kui need on värskendused, kustutamised, salvestatakse WAL-i tehingulogi. Osa statistilist teavet läheb logisse või statistikakogujasse. Ja päringu tulemus antakse kliendile tagasi. Pärast seda saab klient kõike korrata uue sooviga.

Mis on meil taustaülesannete ja taustaprotsessidega? Meil on mitu protsessi, mis hoiavad andmebaasi normaalses töös ja töös. Neid protsesse käsitletakse ka aruandes: need on autovaakum, kontrollpointer, replikatsiooniga seotud protsessid, taustakirjutaja. Aruandes käsitlen neid kõiki.

Millised on statistikaga seotud probleemid?

Palju infot. PostgreSQL 9.4 pakub statistikaandmete vaatamiseks 109 mõõdikut. Kui aga andmebaasis on palju tabeleid, skeeme, andmebaase, siis tuleb kõik need mõõdikud korrutada vastava arvu tabelite, andmebaasidega. See tähendab, et teavet on veelgi rohkem. Ja sellesse on väga lihtne uppuda.
Järgmine probleem seisneb selles, et statistikat kujutavad loendurid. Kui me vaatame seda statistikat, siis näeme pidevalt kasvavaid loendureid. Ja kui statistika lähtestamisest on möödunud palju aega, näeme miljardeid väärtusi. Ja nad ei räägi meile midagi.
Ajalugu ei ole. Kui teil on mingi rike, midagi kukkus 15-30 minutit tagasi, ei saa te statistikat kasutada ja vaadata, mis juhtus 15-30 minutit tagasi. See on probleem.
Probleemiks on PostgreSQL-i sisseehitatud tööriista puudumine. Kerneli arendajad ei paku mingit utiliiti. Neil pole midagi sellist. Nad lihtsalt annavad andmebaasis statistikat. Kasutage seda, esitage sellele taotlus, mida iganes soovite, ja siis tehke seda.
Kuna PostgreSQL-i pole sisse ehitatud ühtegi tööriista, põhjustab see veel ühe probleemi. Palju kolmanda osapoole tööriistu. Iga ettevõte, kellel on rohkem või vähem otsesed käed, püüab kirjutada oma programmi. Selle tulemusena on kogukonnal palju tööriistu, mida saate statistikaga töötamiseks kasutada. Ja mõnes tööriistas on mõned funktsioonid, teistes tööriistades pole muid funktsioone või on mõned uued funktsioonid. Ja tekib olukord, et peate kasutama kahte, kolme või nelja tööriista, mis kattuvad ja millel on erinevad funktsioonid. See on väga ebameeldiv.

Mis sellest järeldub? Oluline on osata statistikat otse võtta, et mitte programmidest sõltuda, või neid programme kuidagi ise täiustada: kasu saamiseks lisage mõned funktsioonid.

Ja teil on vaja algteadmisi SQL-i kohta. Statistikast osa andmete saamiseks tuleb teha SQL-päringuid, st sa pead teadma, kuidas valimine, liitumine toimub.

Statistika ütleb meile mitmeid asju. Neid saab jagada kategooriatesse.

Esimene kategooria on andmebaasis toimuvad sündmused. See on siis, kui andmebaasis toimub mõni sündmus: päring, juurdepääs tabelile, automaatne vaakum, kohustused, siis on need kõik sündmused. Nendele sündmustele vastavaid loendureid suurendatakse. Ja me saame neid sündmusi jälgida.
Teine kategooria on selliste objektide omadused nagu tabelid, andmebaasid. Neil on omadused. See on laudade suurus. Saame jälgida tabelite kasvu, indeksite kasvu. Näeme muutusi dünaamikas.
Ja kolmas kategooria on üritusele kulutatud aeg. Taotlus on sündmus. Sellel on oma spetsiifiline kestuse mõõt. Algas siit, lõppes siin. Me saame seda jälgida. Kas kettalt ploki lugemise või kirjutamise aeg. Neid asju jälgitakse ka.

Statistika allikad on esitatud järgmiselt:

Jagatud mälus (jagatud puhvrites) on segment staatiliste andmete paigutamiseks sinna, seal on ka need loendurid, mida teatud sündmuste toimumisel või andmebaasi töös mingid hetked tekivad pidevalt juurde.
Kõik need loendurid pole kasutajale kättesaadavad ega isegi administraatorile kättesaadavad. Need on madala taseme asjad. Nendele juurdepääsuks pakub PostgreSQL liidest SQL-i funktsioonide kujul. Nende funktsioonide abil saame teha valikuid ja saada mingisuguse mõõdiku (või mõõdikute komplekti).
Neid funktsioone ei ole aga alati mugav kasutada, seega on funktsioonid vaadete (VIEW) aluseks. Need on virtuaalsed tabelid, mis pakuvad statistikat konkreetse alamsüsteemi või mõne andmebaasi sündmuste kogumi kohta.
Need sisseehitatud vaated (VIEW-id) on peamine kasutajaliides statistikaga töötamiseks. Need on vaikimisi saadaval ilma lisaseadeteta, neid saab kohe kasutada, vaadata, sealt infot võtta. Ja on ka panuseid. Kaastööd on ametlikud. Saate installida paketi postgresql-contrib (näiteks postgresql94-contrib), laadida konfiguratsioonis vajaliku mooduli, määrata selle parameetrid, taaskäivitada PostgreSQL ja saate seda kasutada. (Märge. Olenevalt distributsioonist on pakett contrib viimastes versioonides osa põhipaketist).
Ja on ka mitteametlikke panuseid. Neid ei tarnita standardse PostgreSQL-i distributsiooniga. Need tuleb kas kompileerida või installida raamatukoguna. Valikud võivad olla väga erinevad, olenevalt sellest, mida selle mitteametliku kaastöö arendaja välja mõtles.

See slaid näitab kõiki neid vaateid (VIEW) ja mõningaid funktsioone, mis on saadaval PostgreSQL 9.4-s. Nagu näeme, on neid palju. Ja kui kogete seda esimest korda, on üsna lihtne segadusse sattuda.

Kui aga võtta eelmine pilt Как тратится время на PostgreSQL ja ühildub selle loendiga, saame selle pildi. Iga vaadet (VIEW) või iga funktsiooni saame kasutada ühel või teisel eesmärgil sobiva statistika saamiseks, kui meil töötab PostgreSQL. Ja alamsüsteemi toimimise kohta saame juba veidi teavet.

Esimene asi, mida me vaatame, on pg_stat_database. Nagu näeme, on see esitus. See sisaldab palju teavet. Kõige mitmekesisem teave. Ja see annab väga kasulikke teadmisi selle kohta, mis meil andmebaasis toimub.

Mida me sealt võtta saame? Alustame kõige lihtsamatest asjadest.

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

Esimene asi, mida saame vaadata, on vahemälu tabamuse protsent. Vahemälu tabamusprotsent on kasulik mõõdik. See võimaldab teil hinnata, kui palju andmeid võetakse jagatud puhvrite vahemälust ja kui palju loetakse kettalt.

On selge, et mida rohkem vahemälu tabame, seda parem. Hindame seda mõõdikut protsentides. Ja näiteks kui meil on nende vahemälu tabamuste protsent suurem kui 90%, siis on see hea. Kui see langeb alla 90%, siis pole meil piisavalt mälu, et kuuma andmepea mälus hoida. Ja nende andmete kasutamiseks on PostgreSQL sunnitud kettale juurde pääsema ja see on aeglasem kui andmete mälust lugemisel. Ja peate mõtlema mälu suurendamisele: suurendage jagatud puhvreid või suurendage RAM-i.

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;

Mida sellest etendusest veel võtta on? Näete andmebaasis esinevaid kõrvalekaldeid. Mida siin näidatakse? Seal on sissekanded, tagasipööramised, ajutiste failide loomine, nende suurus, ummikseisud ja konfliktid.

Saame seda taotlust kasutada. See SQL on üsna lihtne. Ja me näeme neid andmeid ise.

Ja siin on läviväärtused. Vaatame kohustuste ja tagasivõtmise suhet. Commits on tehingu edukas kinnitus. Rollbacks on tagasivõtmine, st tehing tegi tööd, pingutas andmebaasi, kaalus midagi ja siis tekkis tõrge ning tehingu tulemused visatakse kõrvale. st. pidevalt kasvav tagasipööramiste arv on halb. Ja peaksite neid kuidagi vältima ja koodi muutma, et seda ei juhtuks.

Konfliktid on seotud replikatsiooniga. Ja ka neid tuleks vältida. Kui teil on koopias täidetavaid päringuid ja tekivad konfliktid, peate neid konflikte analüüsima ja vaatama, mis juhtub. Üksikasjad leiate logidest. Ja lahendage konfliktid, et rakendustaotlused töötaksid vigadeta.

Halb olukord on ka ummikseisud. Kui päringud konkureerivad ressursside pärast, pääses üks päring ühele ressursile ja võttis luku, teine päring pääses juurde teisele ressursile ja võttis ka luku ning seejärel pääsesid mõlemad päringud üksteise ressurssidele ja blokeerisid ootamise, millal naaber luku vabastab. See on ka probleemne olukord. Nendega tuleb tegeleda rakenduste ümberkirjutamise ja ressurssidele juurdepääsu järjestamise tasandil. Ja kui näete, et teie ummikseisud kasvavad pidevalt, peate logides üksikasju vaatama, analüüsima tekkinud olukordi ja vaatama, milles probleem on.

Ajutised failid (temp_files) on samuti halvad. Kui kasutaja taotlusel ei ole operatiivsete ajutiste andmete mahutamiseks piisavalt mälu, loob see kettale faili. Ja kõik toimingud, mida ta saaks ajutises mälupuhvris teha, hakkavad täitma juba kettal. See on aeglane. See pikendab päringu täitmise aega. Ja PostgreSQL-ile päringu saatnud klient saab vastuse veidi hiljem. Kui kõik need toimingud tehakse mälus, reageerib Postgres palju kiiremini ja klient ootab vähem.

pg_stat_bgwriter – see vaade kirjeldab kahe PostgreSQL-i taustaallsüsteemi tööd: checkpointer и background writer.

Alustuseks analüüsime kontrollpunkte, nn. checkpoints. Mis on kontrollpunktid? Kontrollpunkt on positsioon tehingulogis, mis näitab, et kõik logis tehtud andmemuudatused on edukalt sünkroonitud kettal olevate andmetega. Protsess võib olenevalt töökoormusest ja sätetest olla pikk ja seisneb enamasti jagatud puhvrites olevate määrdunud lehtede sünkroonimises kettal olevate andmefailidega. Milleks see mõeldud on? Kui PostgreSQL pääseks kettale kogu aeg juurde ja võtaks sealt andmeid ning kirjutaks iga juurdepääsu kohta andmeid, oleks see aeglane. Seetõttu on PostgreSQL-il mälusegment, mille suurus sõltub konfiguratsioonis olevatest parameetritest. Postgres eraldab selles mälus tööandmed edasiseks töötlemiseks või päringute tegemiseks. Andmete muutmise taotluste puhul neid muudetakse. Ja me saame andmetest kaks versiooni. Üks on mälus, teine on kettal. Ja perioodiliselt peate neid andmeid sünkroonima. Vajame mälus muudetud kettaga sünkroonimiseks. Selleks on vaja kontrollpunkte.

Kontrollpunkt läbib jagatud puhvreid, märgib määrdunud lehtedele, et neid on kontrollpunkti jaoks vaja. Seejärel alustab see teist läbimist jagatud puhvrite kaudu. Ja need lehed, mis on kontrollpunktiks märgitud, sünkroniseerib need juba. Seega sünkroonitakse andmed juba kettaga.

Kontrollpunkte on kahte tüüpi. Üks kontrollpunkt täidetakse ajalõpu ajal. See kontrollpunkt on kasulik ja hea - checkpoint_timed. Ja nõudmisel on kontrollpunktid - checkpoint required. Selline kontrollpunkt tekib siis, kui meil on väga suur andmekirje. Salvestasime palju tehingute logisid. Ja PostgreSQL usub, et ta peab selle kõik võimalikult kiiresti sünkroniseerima, tegema kontrollpunkti ja edasi liikuma.

Ja kui vaadata statistikat pg_stat_bgwriter ja vaata, mis sul on checkpoint_req on palju suurem kui checkpoint_timed, siis on see halb. Miks halb? See tähendab, et PostgreSQL on pideva stressi all, kui tal on vaja andmeid kettale kirjutada. Kontrollpunkt ajalõpu järgi on pingevabam ja see viiakse läbi vastavalt sisemisele ajakavale ja aja jooksul justkui venitatud. PostgreSQL-il on võimalus töö peatada ja ketta alamsüsteemi mitte koormata. See on PostgreSQL-i jaoks kasulik. Ja kontrollpunkti ajal täidetavad päringud ei koge stressi, kuna ketta alamsüsteem on hõivatud.

Ja kontrollpunkti reguleerimiseks on kolm parameetrit:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Need võimaldavad teil juhtida kontrollpunktide tööd. Aga ma ei hakka neil pikemalt peatuma. Nende mõju on omaette teema.

Hoiatus: Aruandes käsitletud versioon 9.4 ei ole enam asjakohane. PostgreSQL-i kaasaegsetes versioonides on parameeter checkpoint_segments asendatakse parameetritega min_wal_size и max_wal_size.

Järgmine alamsüsteem on taustakirjutaja − background writer. Mida ta teeb? See töötab pidevalt lõputus tsüklis. See skannib lehed jagatud puhvritesse ja loputab leitud määrdunud lehed kettale. Nii aitab see kontrollpunktil kontrollpunkti tegemise ajal vähem tööd teha.

Milleks teda veel vaja on? See näeb ette vajaduse ühispuhvrites olevate puhaste lehtede järele, kui neid ootamatult (suurtes kogustes ja kohe) andmete mahutamiseks vaja läheb. Oletame, et tekkis olukord, kui päring nõuab puhtaid lehti ja need on juba jagatud puhvrites. Postgres backend ta lihtsalt võtab ja kasutab, ise ei pea midagi koristama. Kui aga järsku selliseid lehti pole, peatab taustaprogramm pausi ja hakkab otsima lehti, et need kettale loputada ja enda vajadusteks võtta – mis mõjutab negatiivselt hetkel täidetava päringu aega. Kui näete, et teil on parameeter maxwritten_clean suur, see tähendab, et taustakirjutaja ei tee oma tööd ja peate parameetreid suurendama bgwriter_lru_maxpageset ta saaks ühe tsükliga rohkem tööd teha, puhastage rohkem lehti.

Ja veel üks väga kasulik näitaja on buffers_backend_fsync. Taustaprogrammid ei tee fsynci, kuna see on aeglane. Nad läbivad fsynci IO pinu kontrollpointeris. Kontrollpointeril on oma järjekord, see töötleb perioodiliselt fsynci ja sünkroonib mälus olevaid lehti kettal olevate failidega. Kui kontrollpunkti järjekord on suur ja täis, on taustaprogramm sunnitud ise fsynci tegema ja see aeglustab taustaprogrammist klient saab vastuse hiljem, kui saaks. Kui näete, et teil on see väärtus suurem kui null, siis on see juba probleem ja peate pöörama tähelepanu taustakirjutaja sätetele ja hindama ka ketta alamsüsteemi jõudlust.

Hoiatus: _Järgmine tekst kirjeldab replikatsiooniga seotud statistilisi vaateid. Enamik vaadete ja funktsioonide nimesid on Postgres 10-s ümber nimetatud. Ümbernimetamise põhiolemus oli asendada xlog edasi wal и location edasi lsn funktsioonide/vaadete nimedes jne. Konkreetne näide, funktsioon pg_xlog_location_diff() nimetati ümber pg_wal_lsn_diff()._

Meil on ka siin palju. Kuid me vajame ainult asukohaga seotud esemeid.

Kui näeme, et kõik väärtused on võrdsed, on see ideaalne ja koopia ei jää kaptenist maha.

See kuueteistkümnendsüsteem siin on positsioon tehingulogis. See suureneb pidevalt, kui andmebaasis on mingi tegevus: lisab, kustutab jne.

сколько записано 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());

Kui need asjad on erinevad, siis on mingi mahajäämus. Lag on koopia viivitus põhiseadmest, st andmed on serveriti erinevad.

Hilinemisel on kolm põhjust:

See on ketta alamsüsteem, mis ei saa failide sünkroonimise kirjutistega hakkama.
Need on võimalikud võrguvead või võrgu ülekoormus, kui andmetel ei ole aega koopiani jõuda ja see ei suuda seda reprodutseerida.
Ja protsessor. Protsessor on väga haruldane juhtum. Ja ma olen seda kaks või kolm korda näinud, aga ka see võib juhtuda.

Ja siin on kolm päringut, mis võimaldavad meil statistikat kasutada. Saame hinnata, kui palju on meie tehingulogis kajastatud. Selline funktsioon on olemas pg_xlog_location_diff ja me saame hinnata replikatsiooni viivitust baitides ja sekundites. Kasutame selleks ka selle vaate väärtust (VIEWs).

Märkus: _pg_xlog_location asemelfunktsiooni diff(), saate kasutada lahutamise operaatorit ja lahutada üks asukoht teisest. Mugav.

Viivitusega, mis on sekundites, on üks hetk. Kui kaptenil tegevust ei toimu, oli tehing seal umbes 15 minutit tagasi ja tegevust ei toimu ja kui vaatame seda viivitust koopial, siis näeme 15 minutit viivitust. Seda tasub meeles pidada. Ja see võib viia stuuporini, kui vaatate seda mahajäämust.

pg_stat_all_tables on veel üks kasulik vaade. See näitab statistikat tabelites. Kui meil on andmebaasis tabelid, siis sellega toimub mingi tegevus, mingid toimingud, siis saame selle info siit vaatest.

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;

Esimene asi, mida saame vaadata, on järjestikused tabeli skannimised. Arv ise pärast neid lõike ei pruugi olla halb ega viita sellele, et me peaksime midagi juba tegema.

Siiski on ka teine mõõdik – seq_tup_read. See on järjestikusest skannimisest tagastatud ridade arv. Kui keskmine arv ületab 1, 000 10, 000 50, 000 100, siis see on juba indikaator, et võib-olla tuleb kuskil indeks ehitada, et juurdepääsud oleksid indeksi järgi või on võimalik optimeerida selliseid järjestikuseid skaneeringuid kasutavaid päringuid, et seda ei juhtu.oli.

Lihtne näide – oletame, et suure OFFSET-i ja LIMIT-iga taotlus on seda väärt. Näiteks skannitakse tabelis 100 000 rida ja pärast seda võetakse 50 000 vajalikku rida ning eelmised skannitud read visatakse ära. See on ka halb juhtum. Ja selliseid taotlusi tuleb optimeerida. Ja siin on selline lihtne SQL-päring, mille abil saate seda näha ja saadud numbreid hinnata.

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;

Selle tabeli ja lisafunktsioonide abil on võimalik saada ka tabeli suurusi pg_total_relation_size(), pg_relation_size().

Üldiselt on metakäsud dt и di, mida saate kasutada PSQL-is ning näha ka tabelite ja indeksi suurusi.

Funktsioonide kasutamine aitab meil aga vaadata tabelite suurusi, kasvõi indekseid arvesse võttes või indekseid arvestamata ning teha juba mõningaid hinnanguid andmebaasi kasvu põhjal, st kuidas see meiega koos kasvab. millise intensiivsusega, ja juba teha mõned järeldused suuruse optimeerimise kohta.

Kirjutage tegevus. Mis on rekord? Vaatame operatsiooni UPDATE – tabeli ridade värskendamise toiming. Tegelikult on värskendamine kaks toimingut (või isegi rohkem). See on uue reaversiooni lisamine ja vana reaversiooni märkimine aegunuks. Hiljem tuleb autovaakum ja puhastab need liinide vananenud versioonid, märgi see koht taaskasutamiseks saadaolevaks.

Samuti ei tähenda värskendamine ainult tabeli värskendamist. See on ikkagi indeksi värskendus. Kui sul on tabelis palju indekseid, siis uuendamisega tuleb uuendada ka kõiki indekseid, milles päringus uuendatud väljad osalevad. Nendel indeksitel on ka vananenud reaversioonid, mis tuleb puhastada.

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;

Ja tänu oma disainile on UPDATE raskekaaluline operatsioon. Kuid neid saab lihtsamaks teha. Sööma hot updates. Need ilmusid PostgreSQL-i versioonis 8.3. Ja mis see on? See on kerge värskendus, mis ei põhjusta indeksite ümberehitamist. See tähendab, et uuendasime kirjet, kuid uuendati ainult lehe kirjet (mis kuulub tabelisse) ja indeksid viitavad endiselt lehel samale kirjele. Natuke on selline huvitav tööloogika, et kui tuleb vaakum, siis sellel on need ahelad hot ehitab ümber ja kõik töötab edasi ilma indekseid uuendamata ning kõik toimub vähema ressursside raiskamisega.

Ja kui sul on n_tup_hot_upd suur, see on väga hea. See tähendab, et ülekaalus on kerged uuendused ja see on meie jaoks ressursside poolest odavam ja kõik on korras.

ALTER TABLE table_name SET (fillfactor = 70);

Kuidas helitugevust suurendada hot updateov? Saame kasutada fillfactor. See määrab reserveeritud vaba ruumi suuruse tabelis lehe täitmisel INSERTide abil. Kui lisad lähevad tabelisse, täidavad need lehe täielikult, ei jäta sinna tühja ruumi. Seejärel tõstetakse esile uus leht. Andmed täidetakse uuesti. Ja see on vaikekäitumine, täitmistegur = 100%.

Saame seada täiteteguriks 70%. See tähendab, et sisestustega eraldati uus leht, kuid täidetud sai ainult 70% lehest. Ja meil on 30% reservi. Kui teil on vaja värskendust teha, toimub see suure tõenäosusega samal lehel ja rea uus versioon mahub samale lehele. Ja hot_update tehakse. See muudab tabelitele kirjutamise lihtsamaks.

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));

Autovaakumi järjekord. Autovacuum on selline alamsüsteem, mille kohta on PostgreSQL-is väga vähe statistikat. Me näeme ainult pg_stat_activity tabelites, kui palju vaakume meil hetkel on. Siiski on väga raske aru saada, mitu tabelit järjekorras on liikvel olles.

Märkus: _Alates Postgres 10-st on olukord vaakumvaakumi jälgimisega palju paranenud - ilmus pg_stat_progressi vaadevaakum, mis lihtsustab oluliselt autovaakumi jälgimise küsimust.

Saame kasutada seda lihtsustatud päringut. Ja me näeme, millal tuleks vaakum teha. Aga kuidas ja millal peaks vaakum algama? Need on vanad versioonid stringidest, millest ma varem rääkisin. Värskendus on toimunud, rea uus versioon on lisatud. Ilmunud on stringi vananenud versioon. Tabel pg_stat_user_tables selline parameeter on olemas n_dead_tup. See näitab "surnud" ridade arvu. Ja niipea, kui surnud ridade arv on ületanud teatud läve, tuleb lauale autovaakum.

Ja kuidas seda läve arvutatakse? See on väga konkreetne protsent tabeli ridade koguarvust. Parameeter on olemas autovacuum_vacuum_scale_factor. See määrab protsendi. Oletame, et 10% + on täiendav baaslävi 50 rida. Ja mis juhtub? Kui meil on rohkem surnud ridu kui "10% + 50" kõigist tabeli ridadest, paneme tabeli autovaakumile.

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));

Siiski on üks punkt. Põhilised parameetrite läved av_base_thresh и av_scale_factor võib määrata individuaalselt. Ja vastavalt sellele ei ole künnis globaalne, vaid tabeli jaoks individuaalne. Seetõttu peate seal arvutamiseks kasutama nippe ja nippe. Ja kel huvi, võib vaadata meie Avito kolleegide kogemust (link slaidil on kehtetu ja tekstis uuendatud).

Nad kirjutasid eest munin pluginmis võtab neid asju arvesse. Kahel linal on jalalapp. Kuid ta arvab õigesti ja võimaldab üsna tõhusalt hinnata, kus vajame laudade jaoks palju vaakumit, kus on vähe.

Mida me saame sellega teha? Kui meil on pikk järjekord ja autovaakum ei saa hakkama, siis saame suurendada vaakumtöötajate arvu või lihtsalt muuta tolmuimeja agressiivsemaksnii et see käivitub varem, töötleb tabelit väikesteks tükkideks. Ja seega järjekord väheneb. - Siin on peamine jälgida ketaste koormust, sest. Vaakum pole tasuta, kuigi SSD / NVMe seadmete tulekuga on probleem muutunud vähem märgatavaks.

pg_stat_all_indexes on indeksite statistika. Ta ei ole suur. Ja sealt saame infot indeksite kasutamise kohta. Ja näiteks saame määrata, millised indeksid meil lisaväärtused on.

Nagu ma juba ütlesin, värskendus ei ole ainult tabelite värskendamine, vaid ka indeksite värskendamine. Vastavalt sellele, kui meil on tabelis palju indekseid, siis tuleb tabeli ridade värskendamisel uuendada ka indekseeritud väljade indekseid ja kui meil on kasutamata indekseid, mille jaoks pole indeksi skaneeringuid, siis need ripuvad meiega ballastina. Ja sa pead neist lahti saama. Selleks vajame põldu idx_scan. Vaatame lihtsalt indeksi skannimiste arvu. Kui indeksitel on suhteliselt pika statistika säilitamise perioodi jooksul (vähemalt 2-3 nädalat) skaneeritud nulli, siis suure tõenäosusega on tegemist halbade indeksidega, millest tuleb lahti saada.

Märkus: Voogesituse replikatsiooniklastrite puhul kasutamata indeksite otsimisel peate kontrollima kõiki klastri sõlme, kuna statistika ei ole globaalne ja kui indeksit masteris ei kasutata, siis saab seda kasutada koopiatel (kui on koormus).

Kaks linki:

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

Need on täiustatud päringunäited, kuidas otsida kasutamata indekseid.

Teine link on üsna huvitav päring. Seal on väga mittetriviaalne loogika. Soovitan seda ülevaatamiseks.

Mida tuleks veel indeksite järgi kokku võtta?

Kasutamata indeksid on halvad.
Nad võtavad ruumi.
Aeglustage värskendustoiminguid.
Lisatöö vaakumi jaoks.

Kui eemaldame kasutamata indeksid, muudame andmebaasi ainult paremaks.

Järgmine vaade on pg_stat_activity. See on utiliidi analoog ps, ainult PostgreSQL-is. Kui psOhh, siis jälgige operatsioonisüsteemi protsesse pg_stat_activity näitab teile tegevust PostgreSQL-is.

Mida me sealt võtta saame?

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

Näeme andmebaasis toimuvat üldist tegevust. Saame teha uue juurutuse. Seal plahvatas kõik, uusi ühendusi vastu ei võeta, rakenduses sajab vigu.

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

Saame käivitada sellise päringu ja vaadata ühenduste koguprotsenti maksimaalsest ühenduse limiidist ja näha, kellel on meil kõige rohkem ühendusi. Ja antud juhul näeme seda kasutajat cron_role avas 508 ühendust. Ja temaga juhtus midagi. Peate sellega tegelema ja vaatama. Ja on täiesti võimalik, et see on mingi anomaalne ühenduste arv.

Kui meil on OLTP-koormus, peaksid päringud olema kiired, väga kiired ja ei tohiks olla pikki päringuid. Kui aga on pikad taotlused, siis lühiajaliselt pole millegi pärast muretseda, aga Pikas perspektiivis kahjustavad pikad päringud andmebaasi, suurendavad tabelite killustumise korral tabelite paisumist. Nii ülepaisutatud kui ka pikad päringud tuleb ära visata.

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;

Pange tähele: sellise päringuga saame määratleda pikad päringud ja tehingud. Me kasutame funktsiooni clock_timestamp() tööaja määramiseks. Pikad taotlused, mille leidsime, suudame neid meeles pidada, täita explain, vaata plaane ja optimeeri kuidagi. Laseme praegused pikad taotlused maha ja elame edasi.

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

Halvad tehingud on tehingute puhul jõude ja tehingu (katkestatud) tehingute puhul jõude.

Mida see tähendab? Tehingutel on mitu olekut. Ja üks neist seisunditest võib kesta igal ajal. Olekute määratlemiseks on väli state selles vaates. Ja me kasutame seda oleku määramiseks.

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

Ja nagu ma eespool ütlesin, need kaks olekut idle in tehingu ja idle in tehingu (katkestatud) on halvad. Mis see on? See on siis, kui rakendus avas tehingu, tegi mõned toimingud ja asus tegutsema. Tehing jääb avatuks. See hangub, selles ei juhtu midagi, see võtab ühendust, lukustub muudetud ridadele ja võib Postrgesi tehingumootori arhitektuuri tõttu potentsiaalselt siiski suurendada teiste tabelite paisumist. Ja sellised tehingud tuleks ka maha lasta, sest need on üldiselt kahjulikud igal juhul.

Kui näete, et teie andmebaasis on neid rohkem kui 5-10-20, siis peate muretsema ja nendega midagi ette võtma.

Siin kasutame ka arvutusaega clock_timestamp(). Pildistame tehinguid, optimeerime rakendust.

Nagu ma eespool ütlesin, on lukud siis, kui kaks või enam tehingut konkureerivad ühe või ressursside rühma pärast. Selleks on meil väli waiting tõeväärtusega true või false.

Tõsi – see tähendab, et protsess ootab, midagi tuleb ette võtta. Kui protsess ootab, siis ootab ka protsessi algatanud klient. Klient brauseris istub ja samuti ootab.

Hoiatus: _Alates Postgres 9.6-st, väli waiting eemaldati ja asendati kahe informatiivsema väljaga wait_event_type и wait_event._

Mida teha? Kui näete pikka aega tõsi, peaksite sellistest taotlustest vabanema. Me lihtsalt tulistame selliseid tehinguid. Kirjutame arendajatele, mida on vaja kuidagi optimeerida, et poleks võidujooksu ressursside pärast. Ja siis arendajad optimeerivad rakendust nii, et seda ei juhtuks.

Ja ekstreemne, kuid potentsiaalselt mitte surmav juhtum on ummikseisude tekkimine. Kaks tehingut on värskendanud kahte ressurssi ja seejärel pääsevad neile uuesti juurde, juba vastupidistele ressurssidele. PostgreSQL võtab sel juhul ja tulistab tehingu ise maha, et teine saaks tööd jätkata. See on ummikus olukord ja ta ei mõista ennast. Seetõttu on PostgreSQL sunnitud kasutama äärmuslikke meetmeid.

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/

Ja siin on kaks päringut, mis võimaldavad teil lukke jälgida. Kasutame vaadet pg_locks, mis võimaldab jälgida raskeid lukke.

Ja esimene link on taotluse tekst ise. See on päris pikk.

Ja teine link on artikkel lukkudest. Seda on kasulik lugeda, see on väga huvitav.

Mida me siis näeme? Näeme kahte taotlust. Tehing koos ALTER TABLE on blokeeriv tehing. See algas, kuid ei lõppenud ja selle tehingu postitanud rakendus teeb kuskil muid asju. Ja teine taotlus on värskendamine. Enne töö jätkamist ootab see, kuni muudatuste tabel lõpeb.

Nii saame teada, kes kelle kinni pani, kes keda kinni hoiab, ja saame sellega edasi tegeleda.

Järgmine moodul on pg_stat_statements. Nagu ma ütlesin, on see moodul. Selle kasutamiseks tuleb konfiguratsioonis laadida selle teek, taaskäivitada PostgreSQL, installida moodul (ühe käsuga) ja siis on meil uus vaade.

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;

Mida me sealt võtta saame? Kui räägime lihtsatest asjadest, siis võib võtta keskmise päringu täitmise aja. Aeg kasvab, mis tähendab, et PostgreSQL reageerib aeglaselt ja midagi tuleb ette võtta.

Näeme andmebaasis kõige aktiivsemaid kirjutamistehinguid, mis muudavad andmeid jagatud puhvrites. Vaadake, kes seal andmeid värskendab või kustutab.

Ja me saame lihtsalt vaadata nende taotluste erinevat statistikat.

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

Me pg_stat_statements kasutatakse aruannete koostamiseks. Nullistame statistika kord päevas. Kogume seda. Enne statistika järgmisel korral lähtestamist koostame aruande. Siin on link raportile. Saate seda vaadata.

Mida me teeme? Arvutame kõigi päringute üldise statistika. Seejärel arvestame iga päringu puhul selle individuaalse panuse üldisesse statistikasse.

Ja mida me näeme? Näeme kõigi teatud tüüpi päringute kogu täitmisaega kõigi teiste päringute taustal. Saame vaadata CPU ja I/O kasutust seoses üldpildiga. Ja juba nende taotluste optimeerimiseks. Koostame selle aruande põhjal populaarseimaid päringuid ja anname juba mõtlemisainet selle üle, mida optimeerida.

Mis meil kulisside taga on? On veel mõned ettepanekud, mida ma ei arvestanud, sest aeg on piiratud.

On pgstattuple on ka lisamoodul standardsest kaastööpaketist. See võimaldab teil hinnata bloat lauad, nn. tabeli killustatus. Ja kui killustatus on suur, peate selle eemaldama, kasutama erinevaid tööriistu. Ja funktsioon pgstattuple töötab pikka aega. Ja mida rohkem tabeleid, seda kauem see töötab.

Järgmine panus on pg_buffercache. See võimaldab teil kontrollida jagatud puhvreid: kui intensiivselt ja milliste tabelite jaoks puhverlehti kasutatakse. Ja see võimaldab teil lihtsalt vaadata jagatud puhvritesse ja hinnata seal toimuvat.

Järgmine moodul on pgfincore. See võimaldab teil süsteemikõne kaudu teha madala taseme tabelitoiminguid mincore(), st see võimaldab teil laadida tabeli jagatud puhvritesse või selle maha laadida. Ja see võimaldab muu hulgas kontrollida operatsioonisüsteemi lehe vahemälu, st seda, kui palju tabel lehe vahemälus, jagatud puhvrites hõivab, ja võimaldab lihtsalt hinnata tabeli koormust.

Järgmine moodul on pg_stat_kcache. See kasutab ka süsteemikõnet getrusage(). Ja see täidab selle enne ja pärast päringu täitmist. Ja saadud statistikas võimaldab see hinnata, kui palju meie päring kulutas ketta I / O, st failisüsteemiga toimingutele, ja vaatab protsessori kasutamist. Moodul on aga noor (khe-khe) ja selle tööks on vaja PostgreSQL 9.4 ja pg_stat_statements, millest juba varem mainisin.

Kasuks tuleb statistika kasutamise oskus. Te ei vaja kolmanda osapoole tarkvara. Saab vaadata, näha, midagi teha, esineda.
Statistika kasutamine on lihtne, see on tavaline SQL. Kogusite päringu, koostasite selle, saatsite selle, vaatasite seda.
Statistika aitab küsimustele vastata. Küsimuste korral pöörduge statistika poole – vaadake, tehke järeldusi, analüüsige tulemusi.
Ja katsetada. Palju taotlusi, palju andmeid. Alati saate mõnda olemasolevat päringut optimeerida. Saate teha päringust oma versiooni, mis sobib teile originaalist paremini ja seda kasutada.