🥇Sügavne PostgreSQL sisestatistika. Aleksei Lesovskõi

Aleksei Lesovski 2015. aasta ettekande "Sügav tutvumine PostgreSQL sisestatistikaga" tõlgendus

Käesoleva ettekande autori märkus: Tahan märkida, et see ettekande kuupäev on novembrist 2015 — sellest ajast on möödunud üle 4 aasta ja palju on muutunud. Ettekandes käsitletud versioon 9.4 ei ole enam toetatud. Viimase 4 aasta jooksul on ilmunud 5 uut versiooni, mis on toonud endaga kaasa hulga uuendusi, täiustusi ja muudatusi seoses statistika ja osa materjalist on vananenud ja ei ole enam asjakohane. Ülevaate käigus püüdsin märkida neid kohti, et mitte eksitada sind, lugeja. Ma ei kirjutanud neid kohti aga ümber, neid on liiga palju ja lõpuks oleks saanud hoopis teistsugune ettekande.

PostgreSQL andmebaasihaldussüsteem on tohutu mehhanism, mis koosneb paljusid alam-süsteeme, mille sujuv töö sõltub otseselt andmebaasi jõudlusest. Töö käigus tagatakse statistika ja teabe kogumine komponentide toimimisest, mis võimaldab hinnata PostgreSQL-i efektiivsust ja võtta meetmeid jõudluse tõstmiseks. Kuid seda teavet on väga palju ja see on esitatud suhteliselt lihtsustatud vormis. Selle teabe töötlemine ja tõlgendamine on aeg-ajalt täiesti mittetriviaalne ülesanne ning tööriistade ja utiliitide "loomaaed" võib isegi edasijõudnut DBA-d segadusse ajada.

Tere! Minu nimi on Aleksei. Nagu Ilja ütles, räägin teile PostgreSQL statistikatest.

PostgreSQL aktiivsuse statistika. PostgreSQL-il on kaks statistikat. Aktiivsuse statistika, millest jutt tuleb. Ja planeerija statistika andmete jaotuse kohta. Räägin täna just PostgreSQL-i aktiivsuse statistikast, mis võimaldab meil hinnata jõudlust ja kuidas seda ka parandada.

Räägin, kuidas tõhusalt kasutada statistikat erinevate probleemide lahendamiseks, millega te seisate silmitsi või võite kokku puutuda.

Mida ettekandes ei käsitleta? Ettekandes ei puuduta ma planeerija statistikat, kuna see on eraldi teema, eraldi ettekande teema, kuidas andmed andmebaasis salvestatakse ja kuidas planeerija saab teavet andmete kvaliteetsete ja koguslike omaduste kohta.

Ja ma ei tee tööriistade ülevaateid, ma ei võrdle ühte toodet teisega. Reklaami ei tule. Jätame selle kõrvale.

Soovin teile näidata, et statistika kasutamine on kasulik. See on vajalik. Selle kasutamine ei ole hirmutav. Me vajame vaid tavalist SQL-i ja põhiteadmisi SQL-ist.

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

Kui vaatame PostgreSQL-i ja käivitame operatsioonisüsteemis käsu, et näha protsesse, siis näeme "must kasti". Näeme mingeid protsesse, mis midagi teevad, ja saame nimede järgi umbkaudu aru, millega nad tegelevad. Kuid tegelikult on see must kast, mille sisse me ei näe.

Saame vaadata protsessori koormust top, saame vaadata mälu kasutust mõne süsteemi utiliidi abil, kuid PostgreSQL-i sisse me ei näe. Selleks on meil vaja teisi tööriistu.

Ja jätkates räägiksin, kuhu kulub aeg. Kui kujutame PostgreSQL-i skeemi kujul, saame vastata, kuhu aeg kulub. Need on kaks asja: kliendipäringute töötlemine rakendustelt ja taustategevused, mida PostgreSQL teeb oma toimimise tagamiseks.

Kui hakkame vaatama vasakust ülanurgast, saame jälgida, kuidas kliendipäringud töödeldakse. Päring tuleb rakenduselt ja edasise töö jaoks avatakse kliendi seanss. Päring edastatakse planeerijale. Planeerija koostab päringu plaani. Saadetakse see edasise täitmise jaoks. Toimub mingisugune plokk-andmete sisendi-väljundi operatsioon, mis on seotud tabelite ja indeksitega. Vajalikud andmed loetakse kettadelt mällu spetsiaalsesse piirkonda "shared buffers". Päringu tulemused, kui need on uuendused, kustutamised, salvestatakse tehingu päevikusse WAL-i. Mõned statistilised andmed satuvad logisse või statistika kogujasse. Ja päringu tulemus antakse juba kliendile tagasi. Pärast seda võib klient kõik uuesti alustada uue päringuga.

Kuidas on taustategevuste ja taustaprotsessidega? Meil on mitmeid protsesse, mis tagavad toimimise ja toetavad andmebaasi normaalset töörežiimi. Need protsessid kajastuvad samuti ettekandes: autovacuum, checkpointer, replikatsiooniga seotud protsessid, background writer. Igaüht neist käsitlen ettekande käigus.

Milliseid probleeme on statistika kasutamisel?

Teavet on palju. PostgreSQL 9.4 pakub 109 meetrit statistika andmete vaatamiseks. Siiski, kui andmebaasis on palju tabeleid, skeeme, baase, tuleb kõiki neid mõõdikuid korrutada vastava tabelite, baaside arvuga. See tähendab, et teavet on veelgi rohkem. Ja selles ära uppuda on väga lihtne.
Järgmine probleem on see, et statistika on esitatud loenditest. Kui me vaatame seda statistikat, näeme pidevalt suurenevaid loendeid. Ja kui alates statistika nullimises on möödunud palju aega, näeme miljardilisi väärtusi. Need ei ütle meile midagi.
Ajalugu puudub. Kui teil on toimunud mingi tõrge, mis juhtus 15-30 minutit tagasi, ei saa te kasutada statistikat ja vaadata, mis juhtus 15-30 minutit tagasi. See on probleem.
PostgreSQL-i sisseehitatud tööriista puudumine on probleem. Tuuma arendajad ei paku ühtegi utiliiti. Neil pole midagi sellist. Nad annavad lihtsalt statistika andmebaasis. Kasutage, koostage päring, mida soovite, seda tehke.
Kuna PostgreSQL-is ei ole sisseehitatud tööriista, on see põhjuseks teisele probleemile. Palju kolmandate osapoolte tööriistu. Igas firmas, kus on vähemalt veidi oskuslikke käsi, püüab kirjutada oma programmi. Ja lõpuks on kogukonnas palju tööriistu, mida saab kasutada statistika töötlemiseks. Ühtedes tööriistades on teatud funktsioonid, teistes aga ei ole neid funktsioone või on mõned uued funktsioonid. Ja tekib olukord, kus tuleb kasutada kahte-kolme-nelja tööriista, mis kattuvad ja omavad erinevaid funktsioone. See on väga ebameeldiv.

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

Ja on vajalikud põhiteadmised SQL-ist. Et saada statistika andmeid, tuleb koostada SQL päringud, st peate teadma, kuidas luua select, join.

Statistika pakub meile mitmeid asju. Need saab jagada kategooriatesse.

Esimene kategooria on sündmused, mis toimuvad andmebaasis. See on siis, kui andmebaasis toimub mõni sündmus: päring, tabeli juurde pääsemine, automaatne vahekohtumine, kinnitused — kõik need on sündmused. Nende sündmustega seotud loendurid suurenevad. Me saame jälgida neid sündmusi.
Teine kategooria on objektide omadused, nagu tabelid ja andmebaasid. Neil on omadused. See on tabelite suurus. Saame jälgida tabelite kasvu, indeksite kasvu. Saame vaadata muutusi dünaamikas.
Kolmas kategooria on sündmusele kuluv aeg. Päring on sündmus. Sellel on oma konkreetne kestuse mõõt. Siin käivitati, siin lõpetati. Saame seda jälgida. Kas siis olema ajaks, mis kulus ploki lugemiseks kettalt või kirjutamiseks. Selliseid asju jälgitakse samuti.

Statistika allikad on esitatud järgmisel viisil:

Jagatud mälu (shared buffers) sisaldab segmenti statistiliste andmete salvestamiseks, seal on ka need samad loendurid, mis pidevalt suurenevad, kui need või need sündmused toimuvad, või tekivad andmebaasi töös mingid hetked.
Kõik need loendurid ei ole kasutajale ja isegi administraatorile kergesti kättesaadavad. Need on madala taseme asjad. Nendele juurde pääsemiseks pakub PostgreSQL liidest SQL funktsioonide näol. Saame nende funktsioonide abil teha select päringuid ja saada mingeid mõõdikuid (või mõõdikutest koosnevaid komplekte).
Siiski, nende funktsioonide kasutamine ei ole alati mugav, seega on funktsioonid põhiks vaadete (VIEWs) koostamiseks. Need on virtuaalsed tabelid, mis pakuvad statistikat konkreetse alamsüsteemi või andmebaasi mingite sündmuste kogumi kohta.
Need sisseehitatud vaated (VIEWs) on peamine kasutajaliides statistika töötlemiseks. Need on saadaval vaikimisi ilma lisaseadeteta, saate neid kohe kasutada, vaadata, jne sealt teavet võtta. Ja on ka contrib'id. Contrib'id on ametlikud. Saate installida paketi postgresql-contrib (näiteks postgresql94-contrib), laadides vajalikud moodulid konfiguratsiooni, määrates neile parameetrid, taaskäivitades PostgreSQL ja saate kasutada. (Märkus. Olenevalt distributsioonist on viimastes versioonides contrib-pakett osa põhiversioonist.).
Ja on olemas mitteametlikud contrib. Need ei kuulu PostgreSQL standardpaketti. Need tuleb kas kompileerida või installida teekina. Valikud võivad olla väga erinevad, sõltuvalt sellest, mida on välja mõelnud selle mitteametliku contrib'i arendaja.

Sellel slaidil on esitatud kõik need vaated (VIEWs) ja osa neist funktsioonidest, mis on saadaval PostgreSQL 9.4. Nagu me näeme, on neid väga palju. Ja üsna lihtne on segadusse minna, kui olete sellega esmakordselt kokku puutunud.

Kuid kui me võtame eelmist pilti Kuidas kulutatakse aega PostgreSQL-is ja sobitame selle nimekirjaga, siis saame sellise pildi. Iga vaade (VIEWs) või iga funktsioon, mida saame kasutada erinevatel eesmärkidel, et saada vastavat statistikat siis, kui PostgreSQL on töös. Ja me saame juba mingit teavet alamsüsteemi töö kohta.

Esimene, mida me vaadata hakkame, on pg_stat_database. Nagu me näeme, on see vaade. See sisaldab väga palju teavet. Väga erinevat teavet. Ja see annab väga kasulikku arusaama sellest, mis meie andmebaasis toimub.

Mida kasulikku 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, mida me vaatame, on määr mängude osakaal. Mängude osakaal – see on kasulik mõõdik. See võimaldab hinnata, kui palju andmeid saadakse jagatud puhversalvest, ja kui palju andmeid loetakse kettalt.

Mõistagi on mida rohkem on meil mängude osakaalu, seda parem. Me hindame seda mõõdikut protsendina. Ja näiteks, kui meil on mängude osakaal suurem kui 90%, siis see on hea. Kui see langeb alla 90%, siis tähendab, et meil pole piisavalt mälu, et hoida andmete „kuum pead” mälus. Ja et neid andmeid kasutada, peab PostgreSQL pöörduma ketta poole, mis on aeglasem, kui andmeid loetakse mälust. Ja tuleb mõelda mäluta suurendamisele: kas suurendada jagatud puhver või suurendada riistvaramälu (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;

Mida veel saab sellest vaatest võtta? Saame vaadata andmebaasis toimuvaid anomaaliaid. Mida siin näidatakse? Siin on commits, rollbacks, ajutiste failide loomine, nende maht, deadlocks ja konfliktid.

Me saame kasutada seda päringut. See SQL on üsna lihtne. Ja saame vaadata neid andmeid enda juures.

Ja siin on kohe läviväärtused. Me vaatame commitide ja rollbackide suhet. Commits on eduka tehingu kinnitamine. Rollbacks on tagasivõtmine, st tehing viis läbi mingi töö, koormas andmebaasi, tegi arvutusi, kuid seejärel tekkis rike ja tehingu tulemused jäeti kõrvale. St. rollbaki pidev suurenemine on halb. Ja peaksite neid vältima ning koodi muutma, et seda ei juhtuks.

Konflikid (conflicts) on seotud replikatsiooniga. Ja neid tuleks ka vältida. Kui teil on päringud, mis täidetakse replikas ja tekivad konfliktid, tuleb need konfliktid lahendada, vaadata, mis toimub. Üksikasju saab leida logidest. Ja lahendada konfliktid, et rakenduse päringud töötaksid ilma vigadeta.

Deadlock'id on samuti halb olukord. Kui päringud võitlevad ressursside pärast, üks päring pöördus ühe ressursi poole ja sai lukustuse, teine päring pöördus teise ressursi poole ja sai samuti lukustuse, ja seejärel pöördusid mõlemad päringud üksteise ressursside poole ning lukustusid, oodates, kuni naaber vabastab lukustuse. See on samuti probleemne olukord. Need tuleb lahendada rakenduste ümberkirjutamise ja juurdepääsu serialiseerimise tasandil. Ja kui näete, et teie deadlock'id pidevalt suurenevad, tuleb logides vaadata üksikasju, lahendada tekkinud olukordi ja leida probleem.

Ajutised failid (temp_files) on samuti halb. Kui kasutaja päringule ei piisa mälu ajutiste, ajutiste andmete salvestamiseks, loob see kettale faili. Ja kõik toimingud, mida ta võiks ajutises mälupuhvrisse teha, hakkavad toimuma juba kettal. See on aeglane. See suurendab päringu täitmise aega. Ja klient, kes saatis päringu PostgreSQL-ile, saab vastuse natuke hiljem. Kui kõik need toimingud toimuksid mälus, vastaks Postgres palju kiiremini ja klient ootaks vähem.

Pg_stat_bgwriter on esitus, mis kirjeldab kahte PostgreSQL-i taustaprotsessi tööd: need on kontrollpunkt ja taustakirjutaja.

Alustame kontrollpunktidest, nn. kontrollpunktidest. Mis on kontrollpunktid? Kontrollpunkt on tehingu žurnalis olev positsioon, mis näitab, et kõik andmete muudatused, mis on registreeritud žurnalisse, on edukalt sünkroonitud andmetega kettas. Protsess võib sõltuvalt töökoormusest ja seadistustest olla aeganõudev ning see seisneb enamasti määrdunud lehtede sünkroonimises jagatud puhvris (shared buffers) andmefailidega kettas. Miks see vajalik on? Kui PostgreSQL pöördus pidevalt kettale andmete saamiseks ja salvestamiseks iga päringu jaoks, oleks see aeglane. Seetõttu on PostgreSQL-l mälu segment, mille suurus sõltub seadistustest konfiguratsioonis. Postgres paigutab sellesse mällu töötavad andmed edasiseks töötlemiseks või päringute edastamiseks. Andmete muutmise päringute puhul toimub nende muutmine. Saame kaks versiooni andmetest. Üks on meil mälus, teine kettas. Ja perioodiliselt tuleb neid andmeid sünkroonida. Peame sünkroonima mälus muudetud andmed kettale. Selleks on vajalik checkpoint.

Checkpoint läbib jagatud puhvri (shared buffers), märgib määrdunud lehed, mis on vajalikud kontrollpunktiks. Siis käivitab ta teise läbimise jagatud puhvris. Ja lehed, mis on märgitud kontrollpunktiks, sünkroonitakse. Nii toimub andmete sünkroniseerimine kettaga.

On kaks tüüpi kontrollpunkte. Üks kontrollpunkt toimub ajautuse järgi. See on kasulik ja hea kontrollpunkt - checkpoint_timed. Ja on ka nõudmisel kontrollpunkte - checkpoint required. Selline kontrollpunkt toimub siis, kui meil toimub väga suur andmete kirjutamine. Oleme kirjutanud väga palju tehingu žurnaleid. Ja PostgreSQL arvab, et tal on vaja see kõik võimalikult kiiresti sünkroonida, teha kontrollpunkt ja edasi elada.

Ja kui vaatasite statistikat pg_stat_bgwriter ja nägite, et teil on checkpoint_req on palju suurem kui checkpoint_timed, siis on see halb. Miks halb? See tähendab, et PostgreSQL on pidevas stressisituatsioonis, kus tal on vaja andmeid kettale kirjutada. Ajautuse järgi kontrollpunkt on vähem stressirohke ja toimub vastavalt sisemisele ajakavale, pikendatud ajavahemikuks. PostgreSQL-l on võimalus töö käigus pause teha ja mitte koormata kettaalust süsteemi. See on PostgreSQL-le kasulik. Ja päringud, mis toimuvad kontrollpunkti ajal, ei tunne stressi, et kettasüsteem on hõivatud.

Ja ja checkpoint'i reguleerimiseks on kolm parameetrit:

checkpoint_segments.
checkpoint_timeout.
checkpoint_completion_target.

Need võimaldavad reguleerida kontrollpunktide tööd. Kuid ma ei peatu nende peal pikalt. Nende mõju on eraldi teema.

Tähelepanu: Arutatud ettekande versioon 9.4 on juba aegunud. Kaasaegsetes PostgreSQL versioonides on parameeter checkpoint_segments asendatud parameetritega min_wal_size ja max_wal_size.

Järgmine alamsüsteem on taustakirjutaja — taustakirjutaja. Mida ta teeb? Ta töötab pidevalt lõputus silmuses. Skaneerib lehti jagatud mälupuhvrid ja logib lehed, mis on mustad, diskile. Nii aitab ta checkpointer'il teha vähem tööd kontrollpunktide täitmise ajal.

Milleks ta veel vajalik on? Ta tagab vajaduse puhaste lehtede järele jagatud mälupuhvrid, juhul kui need äkki on vajalikud (suurtes kogustes ja korraga) andmete paigutamiseks. Oletame, et on tekkinud olukord, kus päringu täitmiseks on vaja puhtaid lehti ja need on juba jagatud mälupuhvrid. PostgreSQL backend lihtsalt võtab need ja kasutab, tal ei ole vaja ise midagi puhastada. Kuid kui äkki ei ole selliseid lehti, peatab backend töö ja hakkab otsima lehti, et need diskile logida ja oma vajaduste jaoks võtta — mis mõjutab negatiivselt hetkel täituvat päringu aega. Kui näete, et teie parameeter maxwritten_clean on suur, tähendab see, et taustakirjutaja ei tule oma tööga toime ja parameetreid tuleks suurendada bgwriter_lru_maxpages, et ta saaks ühe silmuse ajal rohkem tööd teha ja rohkem lehti puhastada.

Ja teine väga kasulik näitaja on buffers_backend_fsync. Backendid ei tee fsync'i, sest see on aeglane. Nad edastavad fsync'i üles IO stack checkpointer'ile. Checkpointer'il on oma järjekord, ta töötleb aeg-ajalt fsync'i ja sünkroniseerib mälu lehed failidega diskil. Kui checkpointer'il on suur ja täidetud järjekord, on backend sunnitud ise tegema fsync'i ja see aeglustab backend'i tööd, st klient saab vastuse hiljem, kui võiks. Kui näete, et teie väärtus on suurem kui null, siis on see juba probleem ja peate pöörama tähelepanu taustakirjutaja seadistustele ja samuti hindama diskiallikate jõudlust.

Tähelepanu: _Järgmine tekst kirjeldab statistilisi esitusi, mis on seotud replikatsiooniga. Enamik esinduste ja funktsioonide nimesid muudeti Postgres 10-s. Muudatuste olemus seisnes asendamises xlog . Tundub, et wal ja asukoht . Tundub, et lsn funktsioonide/ettepanekute nimedes jne. Konkreetne näide, funktsioon pg_xlog_location_diff() muudeti nimeks pg_wal_lsn_diff()._

Siin on meil samuti palju asju. Kuid vajadus on vaid punktide järele, mis on seotud asukohaga.

Kui me näeme, et kõik väärtused on võrdsed, on see ideaalne olukord ja replikatsioon ei jää meistrist maha.

See kuusakoodipositsioon on positsioon tehingute logis. See suureneb pidevalt, kui andmebaasis on mingit tegevust: lisamine, kustutamine jne.

kui palju xlog-i on kirjutatud baitides
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
replikatsiooni viivitus baitides
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
replikatsiooni viivitus sekundites
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

Kui need asjad erinevad, tähendab see, et on mingi viivitus. Viivitus on replikatsiooni mahajäämus meistrist, st andmed erinevad serverite vahel.

On kolm viivituse põhjust:

See on kettasüsteem, mis ei suuda faile sünkroonimise kirjutamisel hakkama saada.
Need võivad olla võrgu vead või võrgu ülekoormus, kui andmed ei jõua replikatsiooni ja see ei suuda neid taastada.
Ja protsessor. Protsessor on väga harv juhtum. Olen seda näinud kaks või kolm korda, kuid see võib siiski juhtuda.

Ja siin on kolm päringut, mis võimaldavad meil kasutada statistikat. Saame hinnata, kui palju on meie tehingute logis kirjutatud. Selleks on olemas funktsioon pg_xlog_location_diff ja saame hinnata replikatsiooni viivituse baitides ja sekundites. Kasutame ka selleks väärtust sellest esitusest (VIEWs).

Märkus: _pg_xlog_location diff() funktsiooni asemel saab kasutada lahutamise operaatorit ja lahutada ühe asukoha teisest. See on mugav.Sekundites oleva viivituse puhul on üks hetk. Kui meistril ei toimu mingit tegevust, oli tehing kuskil 15 minutit tagasi ja mingit aktiivsust ei toimu, ja kui me vaataksem selle replikatsiooni viivitust, siis näeme 15-minutilist viivitust. Sellest tasub meeles pidada. See võib segadusse ajada, kui olete seda viivitust vaadanud.

Sekundites oleva mahajäämuse osas on üks moment. Kui masteris ei toimu mingit tegevust, olid tehingud umbes 15 minutit tagasi ja aktiivsust ei ole, ning kui vaatasime seda mahajäämust replikas, siis näeme 15-minutilist mahajäämust. Seda tasub meeles pidada. See võib tuua segadusse, kui vaatate seda mahajäämust.

Pg_stat_all_tables – veel kasulik vaade. See näitab statistikat tabelite kohta. Kui meie andmebaasis on tabelid ja nende suhtes toimub mingit tegevust, saame selle teabe sellest 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 skaneeringud tabeli kohta. Number, mis nende käikude järel tuleb, ei pruugi veel tingimata halb olla ega ole näitaja, et peaksime midagi ette võtma.

Siiski on teine mõõdik – seq_tup_read. See on ridade arv, mis tagastatakse järjestikuste skaneerimiste tulemusena. Kui keskmine arv ületab 1 000, 10 000, 50 000, 100 000, siis on see juba näitaja, et võib-olla on teil vaja kuhugi indeks luua, et päringud toimuksid indeksi kaudu, või võib-olla optimeerida päringuid, mis selliseid järjestikulisi skaneerimisi kasutavad, et seda vältida.

Lihtne näide – oletame, et päring sisaldab suurt OFFSET'i ja LIMIT'i. Näiteks skaneeritakse tabelis 100 000 rida ja seejärel valitakse 50 000 vajalikku rida, samas kui eelnevalt skaneeritud read tõrjutakse kõrvale. See on samuti halb juhtum ja selliseid päringuid tuleb optimeerida. Siin on selline lihtne SQL-päring, mille abil saab seda vaadata 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;

Tabelite suurused on samuti saadaval selle tabeli ja täiendavate funktsioonide abil. pg_total_relation_size(), pg_relation_size().

Üldiselt on olemas meta-komandod dt ja di, mida saab kasutada PSQL-is ning samuti vaadata tabelite ja indeksite suurusi.

Kuid funktsioonide kasutamine aitab meil vaadata tabelite suurusi ka koos indeksitega või ilma ja teha juba teatud hinnanguid andmebaasi kasvamise kohta, st kui intensiivselt see kasvab ning teha järeldusi suuruste optimeerimise kohta.

Kirjutamisaktiivsus. Mis on kirjutamine? Vaatame operatsiooni UPDATE – ridade ridade uuendamise operatsioon. Sisuliselt on update kaks operatsiooni (ja võib-olla isegi rohkem). See on uue ridade versiooni sisestamine ja vana ridade versiooni märkimine kui aegunud. Hiljem tuleb autovakuum ja need aegunud versioonid eemaldatakse, märkides selle koha ümberkasutatavaks.

Lisaks ei ole update vaid tabeli uuendamine. See on ka indeksite uuendamine. Kui tabelil on palju indekse, tuleb update'i korral uuendada ka kõik indeksi, milles osaleb päringu kaudu uuendatud väljad. Nendes indeksites on samuti aegunud versioonid, 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 oma disaini tõttu on UPDATE – see on raska operatsioon. Kuid neid saab kergendada. On kuumad uuendused. Need ilmusid PostgreSQL versioonis 8.3. Mis need siis on? Need on kerged uuendused, mis ei põhjusta indeksite ümberkujundamist. St me uuendame kirje, kuid seejärel uuendatakse ainult kirje lehel (mis kuulub tabelisse), samas kui indeksid viitavad endiselt sellele samale kirjele lehel. Seal on veidi huvitav loogika, kuidas vakkuum töötab, siis see kohandab neid ahelikke kuumad ja kõik töötab edasi ilma indeksite uuendamiseta, ning see toimub väiksemate ressursikuludega.

Ja kui teil on n_tup_hot_upd suured, siis see on väga hea. See tähendab, et kerged uuendused domineerivad ja see on ressursiküsimisena meile odavam ja kõik on suurepärane.

ALTER TABLE table_name SET (fillfactor = 70);

Kuidas suurendada kuumade uuendustemahtu? Saame kasutada fillfactor. See määrab, kui suur on reserveeritud vabade kohtade suurus tabeli lehe täitmisel INSERT-dega. Kui tabelisse tulevad sisestused, täidavad nad täielikult lehe, jättes sinna tühja ruumi. Siis määratakse uus leht. Jälle andmed täidetakse. Ja see käitumine on vaikimisi, fillfactor = 100%.

Me saame seadistada fillfactor'i 70%. See tähendab, et insertide korral eraldub uus leht, kuid seda täidetakse vaid 70% ulatuses. 30% jääb meile reserviks. Kui tuleks teha uuendus, toimub see tõenäoliselt samal lehel ja uue rea versioon paigutatakse samasse lehte. Tehakse kuum uuendus. Nii lihtsustatakse tabelites kirjutamist.

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

Autovacuumi ootejärjekord. Autovacuum on selline alam-süsteem, mille statistika PostgreSQLis on väga vähene. Me saame tabelites ainult pg_stat_activity's näha, kui palju meie vacuum'e praegu kestab. Siiski on raske mõista, kui palju tabeleid on tal järjekorras.

Märkus: _Postgres 10 versioonist alates on autovacuum'i jälgimise olukord märgatavalt paranenud - lisandunud on pg_stat_progress vaade.vacuum, mis oluliselt lihtsustab autovacuum'i jälgimise küsimust.

Me saame kasutada sellist lihtsustatud päringut. Ja võime vaadata, millal peaks vacuum toimuma. Kuid kuidas ja millal peaks vacuum käivituma? Need vananenud ridade versioonid, millest ma räägin. Uuendus toimus, uus rea versioon lisati. Ilmus vananenud rea versioon. Tabelis pg_stat_user_tables on olemas selline parameeter n_dead_tup. See näitab "surnud" ridade arvu. Ja niipea, kui surnud ridade arv ületab teatud piiri, tuleb tabelis autovacuum.

Kuidas seda piiri arvutatakse? See on üsna konkreetne protsentuaalne suhe tabelis olevate ridade koguarvust. On olemas parameeter autovacuum_vacuum_scale_factor. See määrab protsentuaalse suhte. Oletame, et 10% + seal on täiendav baaspatassee 50 rida. Mis juhtub? Kui meie surnud ridade arv ületab "10% + 50" kogu tabeli ridadest, paneme tabeli autovacuum'i.

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. Baaspiirangud parameetritele av_base_thresh ja av_scale_factor võivad olla määratud individuaalselt. Seega ei ole künnis üldine, vaid individuaalne tabeli jaoks. Seetõttu on arvutamiseks vaja kasutada trikke ja näpunäiteid. Ja kui teid huvitab, siis võite vaadata meie kolleegide kogemusi Avitos (link slaidil ei kehti ja on tekstis uuendatud).

Nad kirjutasid munin plugin, mis arvestab neid aspekte. Seal on kahesugune leht. Kuid loendamine toimub õigesti ja see võimaldab tõhusalt hinnata, kus meil on palju vaakume, kus vähe.

Mida me saame sellega teha? Kui meil on suur järjekord ja automaatne vaakum ei suuda sellega toime tulla, saame suurendada vaakumitöötajate arvu või lihtsalt teha vaakumi agressiivsemaks, et see käivituks varem ja töötleks tabelit väikeste tükkidena. Nii et järjekord väheneb. — Peamine on siin jälgida kettakoormust, kuna vaakum on tasuta asi, kuigi SSD/NVMe seadmete kasutuselevõtuga on probleem muutunud vähem nähtavaks.

Pg_stat_all_indexes – see on statistika indeksite kohta. See on väike. Ja me saame selle põhjal teavet indeksite kasutamise kohta. Ja näiteks saame määrata, millised indeksid on meile üleliigsed.

Nagu ma juba ütlesin, update – see ei ole ainult tabelite värskendamine, vaid ka indeksite värskendamine. Seega, kui meil on tabelis palju indekseid, peab tabeli ridade värskendamisel ka indeksitud väljade indekseid värskendama, ja kui meil on kasutamata indeksid, mille jaoks ei ole indeksiskeeme, siis nad jäävad meile ballastiks. Ja neist on vaja lahti saada. Selleks on meil vaja välja idx_scan. Lihtsalt vaatame indeksiskeemide arvu. Kui indeksitel ei ole analüüse pika statistika hoidmise perioodi (mitte vähem kui 2-3 nädalat) jooksul, siis on tõenäoliselt tegemist halva indeksiga, millest peame lahti saama.

Märkus: Kasutamata indeksite otsimisel voolureplikaatori klastrite puhul on vajalik kontrollida kõiki klastrite sõlmi, kuna statistika ei ole globaalne, ja kui indeksit ei kasutata meistris, võib see olla kasutusel koopiatel (kui seal on koormust).

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 edasijõudnumad näidised päringutest, kuidas leida kasutamata indekseid.

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

Mida veel kokku võtta indeksite kohta?

Kasuta ülemääraseid indekseid ei ole hea.
Need võtavad ruumi.
Need aeglustavad uuendamise operatsioone.
See on liigne töö mahus.

Kui me eemaldasime kasutamata indeksid, siis teeme andmebaasi ainult paremaks.

Järgmine esitus on pg_stat_activity. See on PostgreSQL’i utiliidi ps, ainult PostgreSQL’is. Kui pste vaatate protsesse operatsioonisüsteemis, siis pg_stat_activity näitab aktiivsust PostgreSQL’is.

Mida me sealt kasulikku saame?

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

Me saame vaadata üldist aktiivsust, mis andmebaasis toimub. Saame teha uue juurutuse. Meil on seal kõik plahvatanud, uusi ühendusi ei võeta, rakenduses visatakse vigu.

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

Saame teha sellise päringu ja vaadata, kui suur on ühenduste üldprotsent maksimaalsest ühenduste piirist ning vaadata, kes meie juures kõige rohkem ühendusi kasutab. Antud juhul näeme, et kasutaja cron_role avaldas 508 ühendust. Ja tema kohta on midagi toimunud. Tuleb temaga tegeleda ja vaadata. On täiesti võimalik, et see on mingi ebanormaalne ühenduste arv.

Kui meil on OLTP koormus, peavad päringud toimuma kiiresti, väga kiiresti ja pikad päringud ei ole lubatud. Kuid kui tekivad pikad päringud, siis lühiajaliselt pole hullu, kuid pikaajalises perspektiivis kahjustavad pikad päringud andmebaasi, need suurendavad tabelite äraoleva efekti, kui tabelid fragmenteeruvad. Ja nii äraolevast kui ka pikkadest päringutest tuleb vabaneda.

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: selle päringuga saame tuvastada pikad päringud ja tehingud. Kasutame funktsiooni clock_timestamp() töötamise aja määramiseks. Pikad päringud, mille leidime, saame salvestada, teostada explain, vaadata plaane ja kuidagi optimeerida. Kaasnevad pikad päringud lakkame ja elame edasi.

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

Halvad tehingud on tehingud, mis on idle in transaction ja idle in transaction (aborted) olekus.

Mida see tähendab? Tehingutel on mitu olekut. Ja üks neist olekutest võib olla mis tahes ajahetkel. Oleku määramiseks on siin kasutusel väli state selles esituses. Ja me kasutame seda oleku määramiseks.

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

Ja nagu juba varem mainitud, need kaks olekut idle in transaction ja idle in transaction (aborted) – see on halb. Mis see on? See on siis, kui rakendus avas tehingu, tegi mingid toimingud ja läks oma asjadega edasi. Tehing jäi avatuks. See ripub, selles ei toimu midagi, see kasutab ühendust, blokeeringud muudetud ridadele ja potentsiaalselt suurendab ka teiste tabelite bloat'i, Postgres'i tehingu mootori arhitektuuri tõttu. Selliseid tehinguid tuleb samuti elimineerida, sest need on igal juhul kahjulikud.

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

Siin kasutame samuti aja arvutamiseks clock_timestamp(). Elimineerime tehingud, optimeerime rakendust.

Kuidas juba varem mainitud, blokeeringud – need on siis, kui kaks või enam tehingut konkureerivad ühe või grupi ressursside pärast. Selleks on meil väljad waiting boolean väärtusega true või false.

True – see tähendab, et protsess ootab, midagi peab tegema. Kui protsess on ootel, tähendab see, et ka klient, kes selle protsessi käivitas, ootab. Klient brauseris istub ja ootab samuti.

Tähelepanu: _Alates Postgres versioonist 9.6 on väli waiting eemaldatud ja selle asemel on lisatud kaks informatiivsemat välja wait_event_type ja wait_event._

Mida teha? Kui näete true pikka aega, tähendab see, et sellistest päringutest tuleb vabaneda. Me lihtsalt elimineerime sellised tehingud. Arendajatele kirjutame, et nad peaksid kuidagi optimeerima, et ressursi järele ei tekiks võistlust. Edasi arendajad optimeerivad rakendust, et selliseid olukordi ei esineks.

Ja viimane, kuid potentsiaalselt mitte fatidne olukord – see on deadlock'ide tekkimine. Kaks tehingut uuendavad kahte ressurssi, seejärel pöörduvad uuesti nende poole, juba vastandlikele ressurssidele. PostgreSQL võtab sel juhul ette ja elimineerib tehingu, et teine saaks tööd jätkata. See on ummik ja see ei lahene iseenesest. Seetõttu peab PostgreSQL võtma äärmuslikud meetmed.

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 need on kaks päringut, mis võimaldavad blokeeringute jälgimist. Me kasutame esitust pg_locks, mis võimaldab jälgida suuri lukustusi.

Ja esimene link – see on päringu tekst. See on üsna pikk.

Ja teine link – see on artikkel lukustuste kohta. Seda on kasulik lugeda, see on väga huvitav.

Nii et, mida me näeme? Me näeme kahte päringut. Tehingut koos ALTER TABLE – see on blokeeriv tehing. See käivitati, kuid ei lõpetatud ja rakendus, mis selle tehingu käivitas, tegeleb praegu teiste asjadega. Ja teine päring – uuendus. See ootab, kuni ALTER TABLE lõpetab, et jätkata oma tööd.

Nii saame välja selgitada, kes kedagi lukustas, hoiab ja saame sellega edasi tegeleda.

Järgmine moodul – see on pg_stat_statements. Nagu ma juba ütlesin, see on moodul. Selleks, et seda kasutada, tuleb laadida selle teegi konfiguratsioonis, taaskäivitada PostgreSQL, installida moodul (ühe käsuga) ja siis on meil uus vaade.

Keskmine päringu aeg millisekundites
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Kõige aktiivsemad kirjutavad (shared_buffers) päringud
$ 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ääkida lihtsatest asjadest, siis saame keskmise päringu täitmise aja võtta. Aeg kasvab, tähendab, et meie PostgreSQL vastab aeglaselt ja tuleb midagi ette võtta.

Saame vaadata kõige aktiivsemaid kirjutavaid tehingute andmebaasis, mis muudavad andmeid shared buffers. Vaadata, kes meil seal andmeid uuendab või kustutab.

Ja saame lihtsalt vaadata erinevat statistikat nende päringute kohta.

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

Meie pg_stat_statements kasutame aruannete koostamiseks. Kord päevas kustutame statistika. Kogume seda. Enne järgmise kord statistika kustutamist koostame aruande. Siin on link aruandele. Sa saad seda vaadata.

Mida me teeme? Me arvestame kogu statistika kõigi päringute kohta. Seejärel arvutame iga päringu individuaalse panuse sellesse kogustatistikasse.

Ja mida me saame vaadata? Saame vaadata kõigi konkreetse tüübi päringute kogumisaega kõigi teiste päringute taustal. Saame vaadata protsessori ja sisendi- ja väljundiressursside kasutamist suhtes üldisele pildile. Ja saame juba neid päringuid optimeerida. Koostame selle aruande põhjal top päringud ja juba saame mõtteainet, mida optimeerida.

Mida meil kaadriväliselt jäi? Jõudis veel paar esitlust, mida ma ei hakanud käsitlema, kuna aeg on piiratud.

Jah pgstattuple – see on samuti täiendav moodul tavalises contrib paketis. See võimaldab hinnata bloat tabelite, nn tabelifragmentatsiooni. Ja kui fragmentatsioon on suur, tuleb see eemaldada, kasutades erinevaid tööriistu. Ja funktsioon pgstattuple töötab kaua. Ja mida rohkem tabeleid, seda kauem see töötab.

Järgmine contrib on pg_buffercache. See võimaldab teha ülevaate jagatud puhverest: kui intensiivselt ja milliste tabelite jaoks kasutatakse puhvri lehti. Ja lihtsalt võimaldab piiluda jagatud puhvri sisse ja hinnata, mis seal toimub.

Järgmine moodul – see on pgfincore. See võimaldab teha madala taseme toiminguid tabelitega süsteemikutsungi kaudu mincore(), st see võimaldab laadida tabeli jagatud puhvrites või selle välja laadida. Ja lisaks võimaldab see teha süsteemi lehekülje vahemälu ülevaate, st millises mahus meie tabel on lehe vahemälus, jagatud puhvrites ja lihtsalt hinnata tabeli koormust.

Järgmine moodul on pg_stat_kcache. See kasutab samuti süsteemikutsungit getrusage(). Ja täidab selle enne ja pärast päringu täitmist. Ja saadud statistikas võimaldab hinnata, kui palju meie päring kulutas kettajuurdepääsu jaoks, st operatsioone failisüsteemiga ja vaatab protsessori kasutamist. Kuid moodul on noor (ahem-ahem) ja oma tööks nõuab PostgreSQL 9.4 ja pg_stat_statements, millest ma varem rääkisin.

Statistika kasutamine on kasulik. Teie ei vaja kolmandate osapoolte programme. Te saate ise piiluda, vaadata, midagi teha, ellu viia.
Statistika kasutamine ei ole keeruline, see on tavaline SQL. Te koondete päringu, koostate selle, saadate, vaatate.
Statistika aitab vastata küsimustele. Kui teil tekivad küsimused, pöördute statistika poole – vaatate, teete järeldusi, analüüsite tulemusi.
Ja katsetage. Päringuid on palju, andmeid on palju. Alati on võimalik optimeerida mõnda olemasolevat päringut. Saate teha oma versiooni päringust, mis sobib teile rohkem kui originaal ja kasutada seda.