🥇Ponorte sa hlboko do interných štatistík PostgreSQL. Alexey Lesovský

Prepis správy z roku 2015 od Alexeja Lesovského „Hlboký ponor do interných štatistík PostgreSQL“

Vyhlásenie autora správy: Podotýkam, že táto správa je z novembra 2015 - prešli viac ako 4 roky a prešlo veľa času. Verzia 9.4, o ktorej sa hovorí v správe, už nie je podporovaná. Za posledné 4 roky bolo vydaných 5 nových vydaní, v ktorých sa objavilo množstvo inovácií, vylepšení a zmien týkajúcich sa štatistík a niektoré materiály sú zastarané a nerelevantné. Pri recenzovaní som sa snažil tieto miesta označiť, aby som vás čitateľa nezaviedol. Tieto miesta som neprepísal, je ich veľa a v dôsledku toho vyjde úplne iná správa.

PostgreSQL DBMS je obrovský mechanizmus a tento mechanizmus pozostáva z mnohých podsystémov, ktorých koordinovaná práca priamo ovplyvňuje výkon DBMS. Počas prevádzky sa zhromažďujú štatistiky a informácie o prevádzke komponentov, čo umožňuje vyhodnocovať efektivitu PostgreSQL a prijímať opatrenia na zlepšenie výkonu. Týchto informácií je však veľa a sú podané v dosť zjednodušenej forme. Spracovať tieto informácie a interpretovať ich je niekedy úplne netriviálna úloha a „zoo“ nástrojov a utilít môže ľahko zmiasť aj pokročilého DBA.

Dobrý deň Moje meno je Aleksey. Ako povedal Ilya, budem hovoriť o štatistikách PostgreSQL.

Štatistika aktivity PostgreSQL. PostgreSQL má dve štatistiky. Štatistika aktivity, o ktorej sa bude diskutovať. A štatistiky plánovača o distribúcii údajov. Budem hovoriť konkrétne o štatistikách aktivity PostgreSQL, ktoré nám umožňujú posúdiť výkon a nejako ho zlepšiť.

Poviem vám, ako efektívne využiť štatistiky na riešenie rôznych problémov, ktoré máte alebo môžete mať.

Čo v správe nebude? V prehľade sa nebudem dotýkať štatistiky plánovača, pretože. toto je samostatná téma pre samostatnú správu o tom, ako sa údaje ukladajú v databáze a ako plánovač dotazov získa predstavu o kvalitatívnych a kvantitatívnych charakteristikách týchto údajov.

A nebudú žiadne recenzie nástrojov, nebudem porovnávať jeden produkt s druhým. Reklama nebude. Nechajme to.

Chcem vám ukázať, že používanie štatistík je užitočné. Je to nevyhnutné. Použite to nebojácne. Všetko, čo potrebujeme, je obyčajný SQL a základné znalosti SQL.

A povieme si, akú štatistiku zvoliť na riešenie problémov.

Ak sa pozrieme na PostgreSQL a spustíme v operačnom systéme príkaz na zobrazenie procesov, uvidíme „čiernu skrinku“. Uvidíme nejaké procesy, ktoré niečo robia a podľa názvu si vieme zhruba predstaviť, čo tam robia, čo robia. Ale v skutočnosti je to čierna skrinka, nemôžeme sa pozrieť dovnútra.

Môžeme sa pozrieť na zaťaženie procesora top, môžeme vidieť využitie pamäte niektorými systémovými utilitami, ale nebudeme môcť nahliadnuť do PostgreSQL. Na to potrebujeme ďalšie nástroje.

A pokračujúc ďalej, poviem vám, kde trávite čas. Ak budeme PostgreSQL reprezentovať vo forme takejto schémy, potom bude možné odpovedať, kde sa trávi čas. Toto sú dve veci: je to spracovanie požiadaviek klientov z aplikácií a úlohy na pozadí, ktoré PostgreSQL vykonáva, aby ho udržal v chode.

Ak sa začneme pozerať do ľavého horného rohu, môžeme vidieť, ako sa spracúvajú požiadavky klientov. Požiadavka prichádza z aplikácie a otvorí sa relácia klienta pre ďalšiu prácu. Požiadavka sa odovzdá plánovaču. Plánovač vytvorí plán dotazov. Pošle ho ďalej na vykonanie. Existuje nejaký druh blokových I/O údajov spojených s tabuľkami a indexmi. Potrebné údaje sa načítajú z diskov do pamäte v špeciálnej oblasti nazývanej „zdieľané vyrovnávacie pamäte“. Výsledky dotazu, ak sú aktualizácie, vymazania, sú zaznamenané v transakčnom protokole vo WAL. Niektoré štatistické informácie idú do denníka alebo zberača štatistík. A výsledok žiadosti sa vráti klientovi. Potom môže klient všetko zopakovať s novou požiadavkou.

Čo máme s úlohami na pozadí a procesmi na pozadí? Máme niekoľko procesov, ktoré udržujú databázu v prevádzke v normálnom prevádzkovom režime. Správa sa bude zaoberať aj týmito procesmi: sú to automatické vákuum, kontrolný ukazovateľ, procesy súvisiace s replikáciou, zapisovač na pozadí. Pri hlásení sa dotknem každého z nich.

Aké sú problémy so štatistikou?

Veľa informácií. PostgreSQL 9.4 poskytuje 109 metrík na prezeranie štatistických údajov. Ak je však v databáze uložených veľa tabuliek, schém, databáz, tak všetky tieto metriky budú musieť byť vynásobené zodpovedajúcim počtom tabuliek, databáz. To znamená, že informácií je ešte viac. A je veľmi ľahké sa v ňom utopiť.
Ďalším problémom je, že štatistiky sú reprezentované počítadlami. Ak sa pozrieme na tieto štatistiky, uvidíme neustále pribúdajúce počítadlá. A ak od vynulovania štatistík uplynulo veľa času, uvidíme miliardy hodnôt. A nič nám nehovoria.
Neexistuje žiadna história. Ak máte nejaké zlyhanie, niečo spadlo pred 15-30 minútami, nebudete môcť použiť štatistiky a zistiť, čo sa stalo pred 15-30 minútami. Toto je problém.
Problémom je nedostatok nástroja zabudovaného do PostgreSQL. Vývojári jadra neposkytujú žiadny nástroj. Nič také nemajú. Len dávajú štatistiky v databáze. Použite to, požiadajte o to, čokoľvek chcete, a potom to urobte.
Keďže v PostgreSQL nie je zabudovaný žiadny nástroj, spôsobuje to ďalší problém. Veľa nástrojov tretích strán. Každá firma, ktorá má viac či menej priame ruky, sa snaží napísať vlastný program. A vďaka tomu má komunita množstvo nástrojov, ktoré môžete použiť na prácu so štatistikami. A v niektorých nástrojoch sú niektoré funkcie, v iných nástrojoch nie sú žiadne iné funkcie alebo sú tam nejaké nové funkcie. A nastane situácia, že musíte použiť dva, tri alebo štyri nástroje, ktoré sa navzájom prekrývajú a majú rôzne funkcie. To je veľmi nepríjemné.

Čo z toho vyplýva? Je dôležité, aby ste mohli brať štatistiku priamo, aby ste neboli závislí od programov, alebo si tieto programy nejako vylepšiť sami: pridajte niektoré funkcie, aby ste získali svoj prospech.

A potrebujete základné znalosti SQL. Ak chcete získať nejaké údaje zo štatistík, musíte urobiť SQL dotazy, t. j. musíte vedieť, ako sa robia select, join.

Štatistiky nám hovoria niekoľko vecí. Môžu byť rozdelené do kategórií.

Prvou kategóriou sú udalosti odohrávajúce sa v databáze. Toto je, keď sa v databáze vyskytne nejaká udalosť: dotaz, prístup k tabuľke, automatické vákuum, potvrdenia, potom sú to všetko udalosti. Počítadlá zodpovedajúce týmto udalostiam sa zvýšia. A tieto udalosti môžeme sledovať.
Druhou kategóriou sú vlastnosti objektov ako tabuľky, databázy. Majú vlastnosti. Toto je veľkosť tabuliek. Môžeme sledovať rast tabuliek, rast indexov. Vidíme zmeny v dynamike.
A treťou kategóriou je čas strávený na podujatí. Žiadosť je udalosť. Má svoju špecifickú mieru trvania. Tu to začalo, tu to skončilo. Môžeme to sledovať. Buď čas čítania bloku z disku alebo zápisu. Aj tieto veci sa sledujú.

Zdroje štatistík sú uvedené takto:

V zdieľanej pamäti (zdieľané vyrovnávacie pamäte) je segment na umiestnenie statických údajov, sú tam aj tie počítadlá, ktoré sa neustále inkrementujú, keď nastanú určité udalosti alebo nastanú nejaké momenty v prevádzke databázy.
Všetky tieto počítadlá nie sú dostupné pre používateľa a nie sú dostupné ani pre administrátora. Toto sú veci nízkej úrovne. Na prístup k nim poskytuje PostgreSQL rozhranie vo forme funkcií SQL. Pomocou týchto funkcií môžeme urobiť výber a získať nejaký druh metriky (alebo množiny metrík).
Nie vždy je však vhodné používať tieto funkcie, takže funkcie sú základom pre pohľady (POHĽADY). Sú to virtuálne tabuľky, ktoré poskytujú štatistiky o konkrétnom podsystéme alebo o nejakej množine udalostí v databáze.
Tieto vstavané zobrazenia (VIEWs) sú hlavným používateľským rozhraním pre prácu so štatistikami. Štandardne sú dostupné bez akýchkoľvek dodatočných nastavení, môžete ich okamžite používať, sledovať, brať odtiaľ informácie. A sú tam aj príspevky. Príspevky sú oficiálne. Môžete si nainštalovať balík postgresql-contrib (napríklad postgresql94-contrib), načítať potrebný modul v konfigurácii, špecifikovať mu parametre, reštartovať PostgreSQL a môžete ho používať. (Poznámka. V závislosti od distribúcie je v najnovších verziách contrib balík súčasťou hlavného balíka).
A existujú aj neoficiálne príspevky. Nie sú dodávané so štandardnou distribúciou PostgreSQL. Musia byť buď skompilované alebo nainštalované ako knižnica. Možnosti sa môžu veľmi líšiť v závislosti od toho, s čím prišiel vývojár tohto neoficiálneho príspevku.

Táto snímka zobrazuje všetky tieto zobrazenia (POHĽADY) a niektoré z funkcií, ktoré sú dostupné v PostgreSQL 9.4. Ako vidíme, je ich veľa. A je celkom ľahké nechať sa zmiasť, ak to zažívate prvýkrát.

Ak však vezmeme predchádzajúci obrázok Как тратится время на PostgreSQL a kompatibilný s týmto zoznamom, dostaneme tento obrázok. Každý pohľad (VIEWs), alebo každú funkciu, môžeme použiť na ten či onen účel na získanie príslušných štatistík, keď máme spustený PostgreSQL. A už môžeme získať nejaké informácie o fungovaní subsystému.

Prvá vec, na ktorú sa pozrieme, je pg_stat_database. Ako vidíme, ide o reprezentáciu. Obsahuje množstvo informácií. Najrozmanitejšie informácie. A poskytuje veľmi užitočné poznatky o tom, čo sa deje v databáze.

Čo si odtiaľ môžeme zobrať? Začnime tými najjednoduchšími vecami.

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

Prvá vec, na ktorú sa môžeme pozrieť, je percento zásahov do vyrovnávacej pamäte. Percento prístupov do vyrovnávacej pamäte je užitočná metrika. Umožňuje vám odhadnúť, koľko údajov sa odoberie zo zdieľanej vyrovnávacej pamäte a koľko sa načíta z disku.

Je jasné že čím viac sme našli cache, tým lepšie. Túto metriku hodnotíme v percentách. A napríklad, ak máme percento týchto zásahov do vyrovnávacej pamäte väčšie ako 90 %, je to dobré. Ak klesne pod 90 %, potom nemáme dostatok pamäte na udržanie horúcej hlavy dát v pamäti. A aby bolo možné tieto dáta použiť, PostgreSQL je nútený pristupovať na disk a to je pomalšie, ako keby sa dáta čítali z pamäte. A musíte myslieť na zvýšenie pamäte: buď zvýšte zdieľané vyrovnávacie pamäte, alebo zvýšte pamäť železa (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;

Čo ešte možno vyčítať z tejto prezentácie? Môžete vidieť anomálie vyskytujúce sa v databáze. Čo je tu zobrazené? Existujú commity, rollbacky, vytváranie dočasných súborov, ich veľkosť, uviaznutia a konflikty.

Túto požiadavku môžeme využiť. Tento SQL je pomerne jednoduchý. A tieto údaje môžeme vidieť sami.

A tu sú prahové hodnoty. Pozeráme sa na pomer commitov a rollbackov. Commits je úspešné potvrdenie transakcie. Vrátenie zmien je vrátenie späť, t. j. transakcia vykonala nejakú prácu, zaťažila databázu, niečo zvážila a potom došlo k zlyhaniu a výsledky transakcie sa zahodia. t.j. neustále sa zvyšujúci počet rollbackov je zlý. A mali by ste sa im nejako vyhnúť a upraviť kód tak, aby sa to nestalo.

Konflikty súvisia s replikáciou. A treba sa im tiež vyhýbať. Ak máte nejaké dotazy, ktoré sa vykonajú na replike a vzniknú konflikty, musíte tieto konflikty analyzovať a zistiť, čo sa stane. Podrobnosti nájdete v protokoloch. A vyriešiť konflikty tak, aby požiadavky aplikácií fungovali bez chýb.

Uviaznutie je tiež zlá situácia. Keď požiadavky súťažia o zdroje, jedna požiadavka pristúpila k jednému zdroju a vzala zámok, druhá požiadavka pristúpila k druhému zdroju a tiež vzala zámok, a potom obe požiadavky navzájom pristupovali k zdrojom a zablokovali čakanie, kým sused uvoľní zámok. Toto je tiež problematická situácia. Treba ich riešiť na úrovni prepisovania aplikácií a serializácie prístupu k zdrojom. A ak vidíte, že vaše uviaznutia neustále narastajú, musíte sa pozrieť na podrobnosti v protokoloch, analyzovať vzniknuté situácie a zistiť, v čom je problém.

Dočasné súbory (temp_files) sú tiež zlé. Keď požiadavka používateľa nemá dostatok pamäte na uloženie operačných, dočasných údajov, vytvorí súbor na disku. A všetky operácie, ktoré mohol vykonávať v dočasnej vyrovnávacej pamäti v pamäti, sa začnú vykonávať už na disku. Ide to pomaly. To zvyšuje čas vykonania dotazu. A klient, ktorý poslal požiadavku do PostgreSQL, dostane odpoveď o niečo neskôr. Ak sú všetky tieto operácie vykonávané v pamäti, Postgres bude reagovať oveľa rýchlejšie a klient bude čakať menej.

pg_stat_bgwriter - Toto zobrazenie popisuje fungovanie dvoch podsystémov PostgreSQL na pozadí: checkpointer и background writer.

Na začiatok si rozoberme kontrolné body, tzv. checkpoints. Čo sú kontrolné body? Kontrolný bod je pozícia v protokole transakcií, ktorá označuje, že všetky zmeny údajov vykonané v protokole sú úspešne synchronizované s údajmi na disku. Proces môže byť v závislosti od záťaže a nastavení zdĺhavý a väčšinou pozostáva zo synchronizácie nečistých stránok v zdieľaných vyrovnávacích pamätiach s dátovými súbormi na disku. Načo to je? Ak by PostgreSQL neustále pristupoval k disku a bral odtiaľ údaje a zapisoval údaje pri každom prístupe, bolo by to pomalé. PostgreSQL má teda pamäťový segment, ktorého veľkosť závisí od parametrov v konfigurácii. Postgres alokuje operačné dáta v tejto pamäti na ďalšie spracovanie alebo dopytovanie. V prípade žiadostí o zmenu údajov dochádza k ich zmene. A dostávame dve verzie údajov. Jeden je v pamäti, druhý na disku. A pravidelne musíte tieto údaje synchronizovať. Potrebujeme, aby sa to, čo sa zmenilo v pamäti, synchronizovalo na disk. To si vyžaduje kontrolný bod.

Kontrolný bod prechádza zdieľanými vyrovnávacími pamäťami, označuje špinavé stránky, že sú potrebné pre kontrolný bod. Potom začne druhý prechod cez zdieľané vyrovnávacie pamäte. A stránky, ktoré sú označené ako kontrolný bod, už synchronizuje. Dáta sú teda synchronizované už s diskom.

Existujú dva typy kontrolných bodov. Jeden kontrolný bod sa vykoná po uplynutí časového limitu. Tento kontrolný bod je užitočný a dobrý - checkpoint_timed. A na požiadanie sú kontrolné body - checkpoint required. Takýto kontrolný bod nastáva, keď máme veľmi veľký dátový záznam. Zaznamenali sme veľa transakčných protokolov. A PostgreSQL verí, že to všetko potrebuje čo najrýchlejšie zosynchronizovať, spraviť kontrolný bod a ísť ďalej.

A keby ste si pozreli štatistiky pg_stat_bgwriter a uvidis co mas checkpoint_req je oveľa väčší ako checkpoint_timed, potom je to zlé. Prečo zle? To znamená, že PostgreSQL je pod neustálym stresom, keď potrebuje zapisovať dáta na disk. Kontrolný bod podľa časového limitu je menej stresujúci a vykonáva sa podľa interného plánu a akoby sa časom predĺžil. PostgreSQL má schopnosť pozastaviť prácu a nezaťažovať diskový subsystém. To je užitočné pre PostgreSQL. A požiadavky, ktoré sa vykonajú počas kontrolného bodu, nebudú mať stres zo skutočnosti, že diskový subsystém je zaneprázdnený.

A existujú tri parametre na úpravu kontrolného bodu:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Umožňujú vám ovládať činnosť kontrolných bodov. Ale nebudem sa nimi zaoberať. Ich vplyv je samostatný problém.

varovanie: Verzia 9.4 zvažovaná v správe už nie je relevantná. V moderných verziách PostgreSQL je parameter checkpoint_segments nahradené parametrami min_wal_size и max_wal_size.

Ďalším podsystémom je zapisovač na pozadí − background writer. Čo robí? Beží neustále v nekonečnej slučke. Skenuje stránky do zdieľaných vyrovnávacích pamätí a nájde nečisté stránky na disk. Týmto spôsobom pomáha kontrolnému ukazateľu vykonávať menej práce počas kontroly.

Na čo ešte potrebuje? Poskytuje potrebu čistých stránok v zdieľaných vyrovnávacích pamätiach, ak sú náhle potrebné (vo veľkom množstve a okamžite) na uloženie údajov. Predpokladajme, že nastala situácia, keď požiadavka vyžadovala čisté stránky a tie sú už v zdieľaných vyrovnávacích pamätiach. Postgres backend len si ich vezme a pouzije, nemusi sam nic upratovat. Ak však zrazu žiadne takéto stránky nie sú, backend sa pozastaví a začne hľadať stránky, aby ich vyprázdnil na disk a vzal si ich pre svoje potreby – čo negatívne ovplyvňuje čas aktuálne vykonávanej požiadavky. Ak vidíte, že máte parameter maxwritten_clean veľký, to znamená, že zapisovač na pozadí nerobí svoju prácu a musíte zvýšiť parametre bgwriter_lru_maxpagesaby mohol urobiť viac práce v jednom cykle, vyčistiť viac strán.

A ďalší veľmi užitočný ukazovateľ je buffers_backend_fsync. Backendy nerobia fsync, pretože je pomalý. Prechádzajú fsync cez kontrolný ukazovateľ zásobníka IO. Kontrolný ukazovateľ má svoj vlastný front, periodicky spracováva fsync a synchronizuje stránky v pamäti so súbormi na disku. Ak je front kontrolných ukazovateľov veľký a plný, potom je backend nútený vykonať fsync sám a to spomaľuje backend, t.j. klient dostane odpoveď neskôr, ako by mohol. Ak vidíte, že máte túto hodnotu väčšiu ako nula, potom je to už problém a treba si dať pozor na nastavenia zapisovača na pozadí a hodnotiť aj výkon diskového podsystému.

varovanie: _Nasledujúci text popisuje štatistické zobrazenia súvisiace s replikáciou. Väčšina názvov zobrazení a funkcií bola v Postgres 10 premenovaná. Podstatou premenovania bolo nahradiť xlog na wal и location na lsn v názvoch funkcií/zobrazení atď. Konkrétny príklad, funkcia pg_xlog_location_diff() bol premenovaný na pg_wal_lsn_diff()._

Aj tu máme veľa. Potrebujeme však iba položky súvisiace s umiestnením.

Ak vidíme, že všetky hodnoty sú rovnaké, potom je to ideálne a replika nezaostáva za majstrom.

Táto hexadecimálna pozícia je tu pozícia v protokole transakcií. Neustále sa zvyšuje, ak je v databáze nejaká aktivita: vkladanie, mazanie atď.

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

Ak sú tieto veci iné, potom je tam nejaké oneskorenie. Oneskorenie je oneskorenie repliky od hlavného servera, t.j. údaje sa medzi servermi líšia.

Meškanie má tri dôvody:

Je to diskový subsystém, ktorý nedokáže spracovať synchronizačné zápisy súborov.
Ide o možné chyby siete alebo preťaženie siete, kedy sa dáta nestihnú dostať do repliky a tá ich nedokáže reprodukovať.
A procesor. Procesor je veľmi zriedkavý prípad. A videl som to dva alebo trikrát, ale aj to sa môže stať.

A tu sú tri dotazy, ktoré nám umožňujú používať štatistiky. Môžeme odhadnúť, koľko je zaznamenaných v našom denníku transakcií. Existuje taká funkcia pg_xlog_location_diff a môžeme odhadnúť oneskorenie replikácie v bajtoch a sekundách. Používame na to aj hodnotu z tohto zobrazenia (VIEWs).

Poznámka: _Namiesto pg_xlog_locationdiff(), môžete použiť operátor odčítania a odčítať jedno miesto od druhého. Pohodlné.

S oneskorením, ktoré je v sekundách, je jeden moment. Ak na predlohe nie je žiadna aktivita, transakcia tam bola asi pred 15 minútami a nie je žiadna aktivita, a ak sa pozrieme na toto oneskorenie na replike, uvidíme oneskorenie 15 minút. Toto stojí za zapamätanie. A môže to viesť k strnulosti, keď ste sledovali toto oneskorenie.

pg_stat_all_tables je ďalší užitočný pohľad. Zobrazuje štatistiky v tabuľkách. Keď máme tabuľky v databáze, je s tým nejaká aktivita, nejaké akcie, môžeme tieto informácie získať z tohto pohľadu.

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;

Prvá vec, na ktorú sa môžeme pozrieť, je sekvenčné skenovanie tabuliek. Samotné číslo po týchto pasážach nie je nevyhnutne zlé a nenaznačuje, že už musíme niečo urobiť.

Existuje však aj druhá metrika - seq_tup_read. Toto je počet riadkov vrátených zo sekvenčného skenovania. Ak priemerné číslo presiahne 1 000, 10 000, 50 000, 100 000, potom je to už indikátor, že možno budete musieť niekde vybudovať index, aby boli prístupy podľa indexu, alebo je možné optimalizovať dotazy, ktoré používajú takéto sekvenčné skenovanie, aby toto sa nestane.

Jednoduchý príklad – povedzme požiadavka s veľkým OFFSET a LIMIT stojí za to. Napríklad sa naskenuje 100 000 riadkov v tabuľke a potom sa zoberie 50 000 požadovaných riadkov a predchádzajúce naskenované riadky sa zahodia. Toto je tiež zlý prípad. A takéto požiadavky je potrebné optimalizovať. A tu je taký jednoduchý SQL dotaz, na ktorom si ho môžete pozrieť a vyhodnotiť prijaté čísla.

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;

Veľkosti tabuľky je možné získať aj pomocou tejto tabuľky a pomocou dodatočných funkcií pg_total_relation_size(), pg_relation_size().

Vo všeobecnosti existujú metapríkazy dt и di, ktorý môžete použiť v PSQL a tiež vidieť veľkosti tabuliek a indexov.

Využitie funkcií nám však pomáha pozrieť sa na veľkosti tabuliek, a to aj s prihliadnutím na indexy, alebo bez zohľadnenia indexov, a urobiť už nejaké odhady na základe rastu databázy, teda ako rastie s nami, s akú intenzitu, a už vyvodiť nejaké závery o optimalizácii veľkosti.

Napíšte aktivitu. čo je to záznam? Pozrime sa na operáciu UPDATE – operácia aktualizácie riadkov v tabuľke. Aktualizácia sú v skutočnosti dve operácie (alebo dokonca viac). Toto je vloženie novej verzie riadka a označenie starej verzie riadka ako zastarané. Neskôr príde autovakuum a vyčistí tieto zastarané verzie liniek, označí toto miesto ako dostupné na opätovné použitie.

Aktualizácia tiež nie je len o aktualizácii tabuľky. Stále ide o aktualizáciu indexu. Ak máte v tabuľke veľa indexov, potom pri aktualizácii bude potrebné aktualizovať aj všetky indexy, na ktorých sa zúčastňujú polia aktualizované v dotaze. Tieto indexy budú mať aj zastarané verzie riadkov, ktoré bude potrebné vyčistiť.

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 vďaka svojmu dizajnu je UPDATE náročná operácia. Ale dajú sa zjednodušiť. Jedzte hot updates. Objavili sa v PostgreSQL verzii 8.3. a čo je toto? Toto je ľahká aktualizácia, ktorá nespôsobuje prebudovanie indexov. To znamená, že sme aktualizovali záznam, ale aktualizoval sa iba záznam na stránke (ktorý patrí do tabuľky) a indexy stále ukazujú na rovnaký záznam na stránke. Je tam trochu taká zaujímavá logika práce, keď príde vákuum, tak to má tieto reťaze hot prebuduje a všetko funguje ďalej bez aktualizácie indexov a všetko sa deje s menším plytvaním zdrojmi.

A keď máte n_tup_hot_upd veľký, je veľmi dobrý. To znamená, že prevažujú odľahčené aktualizácie a to je pre nás lacnejšie z hľadiska zdrojov a všetko je v poriadku.

ALTER TABLE table_name SET (fillfactor = 70);

Ako zvýšiť hlasitosť hot updateov? Môžeme použiť fillfactor. Určuje veľkosť rezervovaného voľného miesta pri vypĺňaní stránky v tabuľke pomocou INSERTov. Keď vložky idú na stôl, úplne vyplnia stránku, nenechávajú v nej prázdne miesto. Potom sa zvýrazní nová stránka. Údaje sa znova vyplnia. A toto je predvolené správanie, faktor plnenia = 100 %.

Fillfactor môžeme nastaviť na 70%. To znamená, že s prílohami bola pridelená nová strana, ale bolo vyplnených iba 70% strany. A v rezerve nám zostáva 30 %. Keď potrebujete vykonať aktualizáciu, s najväčšou pravdepodobnosťou sa tak stane na tej istej stránke a nová verzia riadka sa zmestí na rovnakú stránku. Hot_update sa vykoná. To uľahčuje písanie na tabuľky.

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

Front na automatické vákuovanie. Autovacuum je taký subsystém, pre ktorý je v PostgreSQL veľmi málo štatistík. Koľko vákuov momentálne máme, vidíme len v tabuľkách v pg_stat_activity. Je však veľmi ťažké pochopiť, koľko stolov má vo fronte na cestách.

Poznámka: _Od Postgres 10 sa situácia so sledovaním vákuového vákua výrazne zlepšila - objavil sa pohľad pg_stat_progressvákuum, čo značne zjednodušuje problematiku monitorovania autovákua.

Môžeme použiť tento zjednodušený dotaz. A vidíme, kedy by sa malo vákuum vytvoriť. Ale ako a kedy by sa malo vákuum spustiť? Toto sú staré verzie strún, o ktorých som hovoril predtým. Vyskytla sa aktualizácia, nová verzia riadku bola vložená. Objavila sa zastaraná verzia reťazca. Tabuľka pg_stat_user_tables existuje taký parameter n_dead_tup. Zobrazuje počet "mŕtvych" riadkov. A akonáhle počet mŕtvych radov prekročí určitú hranicu, na stôl príde autovákuum.

A ako sa vypočíta táto hranica? Ide o veľmi špecifické percento z celkového počtu riadkov v tabuľke. Existuje parameter autovacuum_vacuum_scale_factor. Definuje percento. Povedzme, že 10 % + je dodatočná základná hranica 50 riadkov. a čo sa stane? Keď máme viac mŕtvych riadkov ako "10% + 50" všetkých riadkov v tabuľke, umiestnime tabuľku do autovakua.

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

Je tu však jeden bod. Základné prahové hodnoty pre parametre av_base_thresh и av_scale_factor môžu byť pridelené individuálne. A preto prah nebude globálny, ale individuálny pre tabuľku. Preto na výpočet musíte použiť triky a triky. A ak by vás to zaujímalo, môžete sa pozrieť na skúsenosti našich kolegov z Avita (odkaz na snímke je neplatný a v texte bol aktualizovaný).

Napísali pre munin pluginktorý berie do úvahy tieto veci. Na dvoch obliečkach je nánožník. Ale zvažuje správne a celkom efektívne nám umožňuje posúdiť, kde potrebujeme veľa vákua pre stoly, kde je málo.

Čo s tým môžeme urobiť? Ak máme dlhý rad a autovysávač to nezvláda, môžeme zvýšiť počet pracovníkov vysávača alebo jednoducho urobiť vysávač agresívnejšímaby sa spustil skôr, spracuje tabuľku po malých kúskoch. A tým sa zmenší rad. - Hlavná vec je tu sledovať zaťaženie diskov, pretože. Vákuová vec nie je zadarmo, aj keď s príchodom zariadení SSD / NVMe sa problém stal menej viditeľným.

pg_stat_all_indexes je štatistika indexov. Nie je veľká. A môžeme z nej získať informácie o používaní indexov. A môžeme napríklad určiť, ktoré indexy máme navyše.

Ako som už povedal, aktualizácia nie je len aktualizácia tabuliek, ale aj aktualizácia indexov. Ak teda máme v tabuľke veľa indexov, pri aktualizácii riadkov v tabuľke je potrebné aktualizovať aj indexy indexovaných polí a ak máme nepoužívané indexy, pre ktoré neexistujú žiadne skenovanie indexov, visia s nami ako balast. A treba sa ich zbaviť. Na to potrebujeme pole idx_scan. Len sa pozrieme na počet indexových skenov. Ak majú indexy nulové skenovanie počas relatívne dlhého obdobia ukladania štatistík (aspoň 2-3 týždne), potom s najväčšou pravdepodobnosťou ide o zlé indexy, musíme sa ich zbaviť.

Poznámka: Pri hľadaní nepoužitých indexov v prípade klastrov streamingovej replikácie musíte skontrolovať všetky uzly klastra, pretože štatistiky nie sú globálne a ak sa index nepoužíva na hlavnom serveri, možno ho použiť na replikách (ak je tam zaťaženie).

Dva odkazy:

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

Toto sú pokročilejšie príklady dotazov, ako vyhľadať nepoužívané indexy.

Druhý odkaz je celkom zaujímavý dotaz. Je v tom veľmi netriviálna logika. Odporúčam na recenziu.

Čo ešte treba zhrnúť do indexov?

Nepoužívané indexy sú zlé.
Zaberajú miesto.
Spomaliť operácie aktualizácie.
Extra práca pre vysávač.

Ak odstránime nepoužívané indexy, potom databázu len vylepšíme.

Ďalší pohľad je pg_stat_activity. Toto je analógový nástroj ps, iba v PostgreSQL. Ak ps'Ohm, potom sleduješ procesy v operačnom systéme pg_stat_activity vám ukáže aktivitu vo vnútri PostgreSQL.

Čo si odtiaľ môžeme zobrať?

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

Môžeme vidieť celkovú aktivitu, ktorá sa deje v databáze. Môžeme urobiť nové nasadenie. Všetko tam vybuchlo, nové spojenia neakceptujú, chyby sa sypú do aplikácie.

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

Môžeme spustiť takýto dotaz a vidieť celkové percento pripojení vzhľadom na maximálny limit pripojení a zistiť, kto máme najviac pripojení. A v tomto danom prípade vidíme daného používateľa cron_role otvorených 508 spojení. A niečo sa mu stalo. Treba sa s tým vyrovnať a uvidíš. A je dosť možné, že ide o nejaký anomálny počet spojení.

Ak máme zaťaženie OLTP, dopyty by mali byť rýchle, veľmi rýchle a nemali by existovať dlhé dopyty. Ak však existujú dlhé požiadavky, potom sa z krátkodobého hľadiska nie je čoho obávať, ale z dlhodobého hľadiska dlhé dopyty poškodzujú databázu, zvyšujú efekt nafúknutia tabuliek, keď dôjde k fragmentácii tabuľky. Je potrebné zbaviť sa nadutých aj dlhých otázok.

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;

Poznámka: s takouto požiadavkou môžeme definovať dlhé požiadavky a transakcie. Používame funkciu clock_timestamp() na určenie pracovného času. Dlhé požiadavky, ktoré sme našli, si vieme zapamätať, zrealizovať explain, pozrieť si plány a nejako optimalizovať. Natáčame aktuálne dlhé požiadavky a žijeme ďalej.

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

Chybné transakcie sú nečinné v transakcii a nečinné v transakciách (prerušené).

Čo to znamená? Transakcie majú viacero stavov. A jeden z týchto stavov môže nadobudnúť kedykoľvek. Existuje pole na definovanie stavov state v tomto pohľade. A používame to na určenie stavu.

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

A ako som povedal vyššie, tieto dva štáty nečinnosť v transakcii a nečinná transakcia (prerušená) sú zlé. Čo to je? Vtedy aplikácia otvorila transakciu, vykonala nejaké akcie a pokračovala vo svojej činnosti. Transakcia zostáva otvorená. Visí, nič sa v ňom nedeje, vyžaduje spojenie, uzamkne zmenené riadky a potenciálne stále zvyšuje nadúvanie iných tabuliek, kvôli architektúre transakčného enginu Postrges. A takéto transakcie by sa tiež mali strieľať, pretože v každom prípade sú vo všeobecnosti škodlivé.

Ak vidíte, že ich máte v databáze viac ako 5-10-20, tak sa musíte znepokojiť a začať s nimi niečo robiť.

Tu používame aj čas výpočtu clock_timestamp(). Natáčame transakcie, optimalizujeme aplikáciu.

Ako som povedal vyššie, zámky sú, keď dve alebo viac transakcií súťaží o jeden alebo skupinu zdrojov. Na to máme pole waiting s boolovskou hodnotou true alebo false.

Pravda - to znamená, že proces čaká, je potrebné niečo urobiť. Keď proces čaká, potom čaká aj klient, ktorý proces inicioval. Klient v prehliadači sedí a tiež čaká.

varovanie: _Počnúc od Postgres 9.6, pole waiting odstránené a nahradené ďalšími dvoma informačnými poliami wait_event_type и wait_event._

Čo robiť? Ak dlho vidíte pravdu, mali by ste sa takýchto žiadostí zbaviť. Takéto transakcie len natáčame. Píšeme vývojárom, čo treba nejako optimalizovať, aby sa nekonali preteky o zdroje. A potom vývojári optimalizujú aplikáciu, aby sa to nestalo.

A extrémny, no zároveň potenciálne nie fatálny prípad je výskyt patových situácií. Dve transakcie aktualizovali dva zdroje, potom k nim znova pristupujú, už k opačným zdrojom. PostgreSQL v tomto prípade prevezme a vystrelí samotnú transakciu, aby ten druhý mohol pokračovať v práci. Toto je slepá ulička a ona sama sebe nerozumie. PostgreSQL je preto nútený prijať extrémne opatrenia.

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/

A tu sú dva dotazy, ktoré vám umožňujú sledovať zámky. Používame pohľad pg_locks, ktorý umožňuje sledovať ťažké zámky.

A prvým odkazom je samotný text žiadosti. Je to dosť dlhé.

A druhý odkaz je článok o zámkoch. Je užitočné čítať, je to veľmi zaujímavé.

Čo teda vidíme? Vidíme dve žiadosti. Transakcia s ALTER TABLE je blokujúca transakcia. Začalo to, ale neskončilo a aplikácia, ktorá túto transakciu zaúčtovala, niekde robí iné veci. A druhá požiadavka je aktualizácia. Pred pokračovaním v práci čaká na dokončenie zmeny tabuľky.

Takto vieme zistiť, kto koho zamkol, kto koho drží a môžeme sa tým ďalej zaoberať.

Ďalší modul je pg_stat_statements. Ako som povedal, je to modul. Pre jeho použitie je potrebné načítať jeho knižnicu v konfigurácii, reštartovať PostgreSQL, nainštalovať modul (jedným príkazom) a potom budeme mať nový pohľad.

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;

Čo si odtiaľ môžeme zobrať? Ak hovoríme o jednoduchých veciach, môžeme použiť priemerný čas vykonania dotazu. Čas rastie, čo znamená, že PostgreSQL reaguje pomaly a je potrebné niečo urobiť.

V databáze môžeme vidieť najaktívnejšie transakcie zápisu, ktoré menia dáta v zdieľaných vyrovnávacích pamätiach. Pozrite sa, kto tam aktualizuje alebo odstraňuje údaje.

A môžeme sa len pozrieť na rôzne štatistiky pre tieto dopyty.

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

My pg_stat_statements používa na vytváranie prehľadov. Štatistiky resetujeme raz denne. Poďme to nahromadiť. Pred ďalším resetovaním štatistík vytvoríme prehľad. Tu je odkaz na správu. Môžete to sledovať.

Čo robíme? Vypočítavame celkové štatistiky pre všetky dopyty. Potom pre každý dopyt spočítame jeho individuálny príspevok k tejto celkovej štatistike.

A čo môžeme vidieť? Môžeme vidieť celkový čas vykonania všetkých požiadaviek konkrétneho typu na pozadí všetkých ostatných požiadaviek. Môžeme sa pozrieť na využitie CPU a I/O vo vzťahu k celkovému obrazu. A už na optimalizáciu týchto požiadaviek. Na základe tohto prehľadu vytvárame hlavné dopyty a už teraz dostávame podnety na premýšľanie o tom, čo optimalizovať.

Čo máme v zákulisí? Stále existuje niekoľko príspevkov, ktoré som nezohľadnil, pretože čas je obmedzený.

K dispozícii je pgstattuple je tiež doplnkový modul zo štandardného balíka contribs. Umožňuje vám hodnotiť bloat tabuľky, tzv. fragmentácia tabuľky. A ak je fragmentácia veľká, musíte ju odstrániť, použiť rôzne nástroje. A funkcia pgstattuple funguje dlhodobo. A čím viac tabuliek, tým dlhšie to bude fungovať.

Ďalší príspevok je pg_buffercache. Umožňuje vám kontrolovať zdieľané vyrovnávacie pamäte: ako intenzívne a pre ktoré tabuľky sa stránky vyrovnávacej pamäte využívajú. A to vám len umožňuje nahliadnuť do zdieľaných vyrovnávacích pamätí a vyhodnocovať, čo sa tam deje.

Ďalší modul je pgfincore. Umožňuje vám vykonávať operácie s tabuľkami na nízkej úrovni prostredníctvom systémového volania mincore(), t.j. umožňuje načítať tabuľku do zdieľaných vyrovnávacích pamätí alebo ju uvoľniť. A umožňuje okrem iného kontrolovať cache stránok operačného systému, teda koľko tabuľka zaberá v cache stránok, v zdieľaných bufferoch a jednoducho umožňuje vyhodnocovať zaťaženie tabuľky.

Ďalší modul je pg_stat_kcache. Používa tiež systémové volanie getrusage(). A vykoná ho pred a po vykonaní požiadavky. A v získaných štatistikách nám umožňuje odhadnúť, koľko naša požiadavka minula na diskové I/O, teda operácie so súborovým systémom a pozerá sa na využitie procesora. Modul je však mladý (khe-khe) a pre svoju prácu vyžaduje PostgreSQL 9.4 a pg_stat_statements, ktoré som už spomínal.

Schopnosť používať štatistiky je užitočná. Nepotrebujete softvér tretích strán. Môžete sa pozerať, vidieť, niečo robiť, hrať.
Používanie štatistík je jednoduché, je to obyčajný SQL. Zozbierali ste žiadosť, zostavili ju, odoslali, pozreli ste sa na ňu.
Štatistiky pomáhajú odpovedať na otázky. Ak máte otázky, obráťte sa na štatistiky - pozrite sa, vyvodzujte závery, analyzujte výsledky.
A experimentujte. Veľa žiadostí, veľa údajov. Vždy môžete optimalizovať niektorý existujúci dotaz. Môžete si vytvoriť vlastnú verziu žiadosti, ktorá vám vyhovuje viac ako originál a použiť ju.