Aleksejaus Lesovskio 2015 m. pranešimo „Giliai pasinerti į PostgreSQL vidinę statistiką“ stenograma
Pranešimo autoriaus atsisakymas: Atkreipiu dėmesį, kad ši ataskaita yra 2015 m. lapkričio mėn. - praėjo daugiau nei 4 metai ir praėjo daug laiko. Ataskaitoje aptarta 9.4 versija nebepalaikoma. Per pastaruosius 4 metus buvo išleisti 5 nauji leidimai, kuriuose yra daug naujovių, patobulinimų ir pakeitimų, susijusių su statistika, o dalis medžiagos yra pasenusi ir neaktuali. Peržiūrėdamas stengiausi šias vietas pažymėti, kad neklaidinčiau skaitytojo. Šių ištraukų neperrašiau, jų daug ir rezultatas bus visai kitoks reportažas.
PostgreSQL DBVS yra didžiulis mechanizmas, ir šis mechanizmas susideda iš daugybės posistemių, kurių koordinuotas veikimas tiesiogiai veikia DBVS veikimą. Eksploatacijos metu renkama statistika ir informacija apie komponentų veikimą, kuri leidžia įvertinti PostgreSQL efektyvumą ir imtis priemonių našumui gerinti. Tačiau šios informacijos yra labai daug ir ji pateikiama gana supaprastinta forma. Šios informacijos apdorojimas ir interpretavimas kartais yra visiškai nebanali užduotis, o įrankių ir paslaugų „zoologijos sodas“ gali lengvai supainioti net pažangų DBA.
Laba diena Mano vardas Aleksejus. Kaip sakė Ilja, kalbėsiu apie PostgreSQL statistiką.
PostgreSQL veiklos statistika. PostgreSQL turi du statistinius duomenis. Veiklos statistika, kuri bus aptarta. Ir planuotojo statistika apie duomenų paskirstymą. Pakalbėsiu konkrečiai apie PostgreSQL veiklos statistiką, kuri leidžia spręsti apie našumą ir kaip nors jį pagerinti.
Aš jums pasakysiu, kaip efektyviai panaudoti statistiką sprendžiant įvairias problemas, kurias turite ar galite turėti.
Ko nebus ataskaitoje? Pranešime neliesiu planuotojo statistikos, nes... Tai yra atskira tema, skirta atskirai ataskaitai apie tai, kaip duomenys saugomi duomenų bazėje ir kaip užklausų planuotojas gauna idėją apie šių duomenų kokybines ir kiekybines charakteristikas.
Ir priemonių apžvalgų nebus, vieno produkto su kitu nelyginsiu. Reklamos nebus. Atidėkime tai į šalį.
Noriu jums parodyti, kad statistikos naudojimas yra naudingas. Tai būtina. Tai saugu naudoti. Viskas, ko mums reikia, yra įprastas SQL ir pagrindinės SQL žinios.
Ir pakalbėkime apie tai, kokią statistiką pasirinkti problemoms spręsti.
Jei pažvelgsime į PostgreSQL ir paleisime komandą operacinėje sistemoje, kad peržiūrėtume procesus, pamatysime „juodąją dėžę“. Pamatysime kažkokius procesus, kurie kažką daro, ir iš pavadinimo galime maždaug įsivaizduoti, ką jie ten veikia, ką daro. Tačiau iš esmės tai yra juoda dėžė, kurios viduje negalime žiūrėti.
Matome procesoriaus apkrovą top, galime pažvelgti į kai kurių sistemos paslaugų programų atminties naudojimą, bet negalėsime žiūrėti į PostgreSQL. Tam mums reikia kitų įrankių.
Ir tęsdamas toliau, pasakysiu, kur praleidžiamas laikas. Jeigu įsivaizduotume PostgreSQL tokios diagramos pavidalu, tai galėtume atsakyti, kur praleidžiamas laikas. Tai yra du dalykai: jis apdoroja klientų užklausas iš programų ir fonines užduotis, kurias atlieka PostgreSQL, kad veiktų.
Jei pradėsime žiūrėti viršutiniame kairiajame kampe, pamatysime, kaip apdorojamos klientų užklausos. Prašymas gaunamas iš programos ir atidaroma kliento sesija tolesniam darbui. Prašymas siunčiamas planuotojui. Planuotojas sukuria užklausos planą. Siunčia toliau vykdyti. Yra tam tikra bloko duomenų įvestis / išvestis, susijusi su lentelėmis ir indeksais. Reikalingi duomenys nuskaitomi iš diskų į atmintį į specialią sritį „bendrieji buferiai“. Užklausos rezultatai, jei jie yra atnaujinimai, ištrynimai, įrašomi į WAL operacijų žurnalą. Dalis statistinės informacijos patenka į žurnalą arba statistikos rinktuvą. O užklausos rezultatas siunčiamas atgal klientui. Po to klientas gali viską pakartoti su nauju prašymu.
O kaip su foninėmis užduotimis ir foniniais procesais? Turime keletą procesų, kurie palaiko duomenų bazę ir veikia įprastu darbo režimu. Šie procesai taip pat bus paliečiami ataskaitoje: automatinis vakuumas, kontrolinis žymeklis, su replikacija susiję procesai, fono rašiklis. Pranešdamas paliesiu kiekvieną iš jų.
Kokios problemos kyla su statistika?
- Yra daug informacijos. „PostgreSQL 9.4“ pateikia 109 metrikas statistiniams duomenims peržiūrėti. Tačiau jei duomenų bazėje saugoma daug lentelių, schemų, duomenų bazių, tai visus šiuos rodiklius teks padauginti iš atitinkamo lentelių, duomenų bazių skaičiaus. Tai yra, yra dar daugiau informacijos. Ir paskęsti joje labai lengva.
- Kita problema yra ta, kad statistiką vaizduoja skaitikliai. Jei pažvelgsime į šią statistiką, pamatysime nuolat didėjančius skaitiklius. Ir jei nuo statistikos nustatymo iš naujo praėjo daug laiko, matysime vertes milijardais. Ir jie mums nieko nesako.
- Jokios istorijos. Jei prieš 15–30 minučių jums nutiko kokia nors nesėkmė, kažkas nukrito, negalėsite naudotis statistika ir pamatyti, kas nutiko prieš 15–30 minučių. Tai yra problema.
- „PostgreSQL“ integruoto įrankio trūkumas yra problema. Branduolio kūrėjai nepateikia jokių paslaugų. Jie nieko panašaus neturi. Jie tiesiog pateikia statistiką duomenų bazėje. Naudokite jį, paprašykite, darykite ką norite.
- Kadangi PostgreSQL nėra įmontuoto įrankio, tai sukelia kitą problemą. Daug trečiųjų šalių įrankių. Kiekviena įmonė, kuri turi daugiau ar mažiau tiesioginių rankų, stengiasi rašyti savo programą. Dėl to bendruomenė turi daugybę įrankių, kuriuos galima naudoti dirbant su statistika. Ir kai kurie įrankiai turi tam tikras galimybes, kiti įrankiai neturi kitų galimybių arba yra keletas naujų galimybių. Ir susidaro situacija, kad reikia naudoti du, tris ar keturis įrankius, kurie persidengia vienas su kitu ir atlieka skirtingas funkcijas. Tai labai nemalonu.
Kas iš to seka? Svarbu turėti galimybę tiesiogiai imti statistiką, kad nepriklausytumėte nuo programų, arba kaip nors patiems patobulinti šias programas: pridėkite keletą funkcijų, kad gautumėte naudos.
Ir jums reikia pagrindinių SQL žinių. Norėdami gauti kai kuriuos duomenis iš statistikos, turite sukurti SQL užklausas, t. y. jūs turite žinoti, kaip sudaromas pasirinkimas ir prisijungimas.
Statistika mums sako keletą dalykų. Juos galima suskirstyti į kategorijas.
- Pirmoji kategorija yra duomenų bazėje vykstantys įvykiai. Tai yra tada, kai duomenų bazėje įvyksta koks nors įvykis: užklausa, prieiga prie lentelės, automatinis vakuumas, įsipareigojimai, tada visa tai yra įvykiai. Šiuos įvykius atitinkantys skaitikliai didinami. Ir mes galime stebėti šiuos įvykius.
- Antroji kategorija yra objektų, tokių kaip lentelės ir duomenų bazės, savybės. Jie turi savybių. Tai yra lentelių dydis. Galime sekti lentelių augimą ir indeksų augimą. Matome dinamikos pokyčius.
- Ir trečia kategorija – laikas, praleistas renginyje. Prašymas yra įvykis. Jis turi savo specifinį trukmės matą. Čia prasidėjo, čia ir baigėsi. Galime sekti. Laikas, kurio reikia norint nuskaityti bloką iš disko, arba jį įrašyti. Tokie dalykai taip pat sekami.
Statistikos šaltiniai pateikiami taip:
- Bendrojoje atmintyje (bendruosiuose buferiuose) yra segmentas statiniams duomenims saugoti, taip pat yra tie skaitikliai, kurie nuolat didinami įvykus tam tikriems įvykiams ar iškylant tam tikriems duomenų bazės veikimo momentams.
- Visi šie skaitikliai nepasiekiami vartotojui ir net nepasiekiami administratoriui. Tai žemo lygio dalykai. Norėdami juos pasiekti, PostgreSQL suteikia sąsają SQL funkcijų pavidalu. Naudodami šias funkcijas galime atlikti pasirinktus metimus ir gauti tam tikrą metriką (arba metrikų rinkinį).
- Tačiau naudotis šiomis funkcijomis ne visada patogu, todėl funkcijos yra vaizdų (VIEW) pagrindas. Tai virtualios lentelės, kuriose pateikiama statistika apie konkretų posistemį arba tam tikrą duomenų bazės įvykių rinkinį.
- Šie įterptieji rodiniai (RODŽIAI) yra pagrindinė vartotojo sąsaja dirbant su statistika. Pagal numatytuosius nustatymus jie pasiekiami be jokių papildomų nustatymų, galite iš karto jais naudotis, žiūrėti, paimti iš jų informaciją. Ir tada yra indėliai. Įnašai yra oficialūs. Galite įdiegti paketą postgresql-contrib (pavyzdžiui, postgresql94-contrib), įkelti reikiamą modulį į konfigūraciją, nurodyti jo parametrus, iš naujo paleisti PostgreSQL ir galite jį naudoti. (Pastaba. Priklausomai nuo platinimo, naujausiose versijose „contrib“ paketas yra pagrindinio paketo dalis).
- Ir yra neoficialių indėlių. Jie neįtraukti į standartinį PostgreSQL paskirstymą. Jie turi būti sudaryti arba įdiegti kaip biblioteka. Parinktys gali būti labai įvairios, priklausomai nuo to, ką sugalvojo šio neoficialaus įnašo kūrėjas.
Šioje skaidrėje pateikiami visi „PostgreSQL 9.4“ galimi RODYMAI ir kai kurios funkcijos. Kaip matome, jų yra labai daug. Ir gana lengva susipainioti, jei su tuo susiduri pirmą kartą.
Tačiau jei paimtume ankstesnį vaizdą Как тратится время на PostgreSQL ir suderinama su šiuo sąrašu, gauname šį paveikslėlį. Mes galime naudoti kiekvieną rodinį (VIEW) arba kiekvieną funkciją vienam ar kitam tikslui gauti atitinkamą statistiką, kai veikia PostgreSQL. Ir jau galime gauti šiek tiek informacijos apie posistemio veikimą.
Pirmas dalykas, į kurį pažvelgsime, yra pg_stat_database. Kaip matome, tai spektaklis. Jame yra daug informacijos. Pati įvairiausia informacija. Ir tai suteikia labai naudingų žinių apie tai, kas vyksta mūsų duomenų bazėje.
Kokių naudingų dalykų galime iš ten pasiimti? Pradėkime nuo paprasčiausių dalykų.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Pirmas dalykas, į kurį galime atkreipti dėmesį, yra talpyklos pataikymo procentas. Talpyklos pataikymo rodiklis yra naudinga metrika. Tai leidžia įvertinti, kiek duomenų paimama iš bendrinamų buferių talpyklos ir kiek nuskaitoma iš disko.
Tai aišku kuo daugiau talpyklos įvykių turėsime, tuo geriau. Šią metriką matuojame procentais. Ir, pavyzdžiui, jei mūsų šių talpyklos įvykių procentas yra didesnis nei 90%, tai yra gerai. Jei jis nukrenta žemiau 90%, tai reiškia, kad neturime pakankamai atminties, kad atmintyje būtų galima laikyti karštus duomenis. Ir norint naudoti šiuos duomenis, PostgreSQL yra priverstas pasiekti diską ir tai vyksta lėčiau nei tuo atveju, jei duomenys būtų nuskaitomi iš atminties. Ir jūs turite galvoti apie atminties padidinimą: arba padidinti bendrinamą buferį, arba padidinti aparatinės įrangos atmintį (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;Ką dar galite pasiimti iš šio spektaklio? Duomenų bazėje galite matyti anomalijas. Kas čia parodyta? Yra įsipareigojimai, atšaukimai, laikinųjų failų kūrimas, jų dydis, aklavietės ir konfliktai.
Galime pasinaudoti šia užklausa. Šis SQL yra gana paprastas. Ir mes galime pažvelgti į šiuos duomenis čia.
Ir čia yra slenkstinės vertės. Mes žiūrime į įsipareigojimų ir atšaukimų santykį. Įsipareigojimai yra sėkmingas operacijos patvirtinimas. Atšaukimas yra atšaukimas, t. y. operacija atliko tam tikrą darbą, įtempė duomenų bazę, kažką apskaičiavo, tada įvyko gedimas ir operacijos rezultatai atmetami. Tai yra nuolat didėjantis atšaukimų skaičius yra blogas. Ir jūs turėtumėte kažkaip jų vengti ir redaguoti kodą, kad taip neatsitiktų.
Konfliktai yra susiję su replikacija. Ir jų taip pat reikėtų vengti. Jei turite užklausų, kurios vykdomos kopijoje ir kyla konfliktų, turite išspręsti šiuos konfliktus ir pamatyti, kas vyksta. Išsamią informaciją galite rasti žurnaluose. Ir pašalinkite konfliktines situacijas, kad programų užklausos veiktų be klaidų.
Aklavietės taip pat yra bloga situacija. Kai užklausos kovoja dėl išteklių, viena užklausa pasiekė vieną išteklį ir užrakino, antroji užklausa pasiekė antrąjį šaltinį ir taip pat užrakino, o tada abi užklausos pasiekė viena kitos išteklius ir buvo užblokuotos laukiant, kol kaimynas atleis užraktą. Tai irgi probleminė situacija. Jie turi būti sprendžiami perrašant programas ir suteikiant prieigą prie išteklių. O jei matote, kad jūsų aklavietės nuolat daugėja, reikia žvilgtelėti į detales žurnaluose, išanalizuoti iškylančias situacijas ir pažiūrėti, kokia yra problema.
Laikini failai (temp_files) taip pat yra blogi. Kai vartotojo užklausa neturi pakankamai atminties operatyviniams, laikiniems duomenims, ji sukuria failą diske. Ir visos operacijos, kurias jis galėtų atlikti laikinajame atminties buferyje, pradedamos atlikti diske. Tai lėta. Tai padidina užklausos vykdymo laiką. O užklausą PostgreSQL išsiuntęs klientas atsakymą gaus kiek vėliau. Jei visos šios operacijos bus atliekamos atmintyje, Postgres reaguos daug greičiau, o klientas lauks mažiau.
Pg_stat_bgwriter – šiame rodinyje aprašomas dviejų PostgreSQL foninių posistemių veikimas: tai checkpointer и background writer.
Pirmiausia pažvelkime į valdymo taškus, vadinamuosius. checkpoints. Kas yra valdymo taškai? Patikrinimo taškas – tai pozicija operacijų žurnale, nurodanti, kad visi žurnale įrašyti duomenų pakeitimai buvo sėkmingai sinchronizuoti su duomenimis diske. Procesas, priklausomai nuo darbo krūvio ir nustatymų, gali būti ilgas ir daugiausia susideda iš nešvarių puslapių sinchronizavimo bendrinamuose buferiuose su duomenų failais diske. Kam tai? Jei PostgreSQL nuolat prieitų prie disko ir gautų duomenis iš jo ir rašytų duomenis apie kiekvieną prieigą, tai būtų lėta. Todėl PostgreSQL turi atminties segmentą, kurio dydis priklauso nuo konfigūracijos nustatymų. Postgres saugo tiesioginius duomenis šioje atmintyje, kad vėliau būtų galima apdoroti arba pateikti užklausas. Esant prašymams pakeisti duomenis, jie keičiami. Ir gauname dvi duomenų versijas. Vienas yra mūsų atmintyje, kitas yra diske. Ir periodiškai turite sinchronizuoti šiuos duomenis. Turime sinchronizuoti tai, kas pakeista atmintyje, į diską. Tam jums reikia kontrolinių punktų.
Patikrinimo taškas eina per bendrus buferius, pažymi nešvarius puslapius, kad jie reikalingi patikros taškui. Tada jis paleidžia antrąjį praėjimą per bendrinamus buferius. O puslapiai, kurie pažymėti kaip kontrolinis taškas, juos jau sinchronizuoja. Tokiu būdu duomenys sinchronizuojami su disku.
Yra dviejų tipų patikros punktai. Vienas kontrolinis taškas įvykdomas su skirtuoju laiku. Šis patikros taškas yra naudingas ir geras – checkpoint_timed. Ir yra patikros punktai pagal poreikį - checkpoint required. Šis kontrolinis taškas atsiranda, kai turime labai didelį duomenų įrašą. Užregistravome daug operacijų žurnalų. Ir PostgreSQL mano, kad reikia kuo greičiau visa tai sinchronizuoti, padaryti patikrinimo tašką ir judėti toliau.
O jei pažiūrėtum statistiką pg_stat_bgwriter ir pamatė ką turi checkpoint_req yra daug didesnis nei checkpoint_timed, tai yra blogai. Kodėl negerai? Tai reiškia, kad PostgreSQL patiria nuolatinį stresą, kai reikia įrašyti duomenis į diską. Laiko pabaigos kontrolinis taškas yra mažiau įtemptas ir vykdomas pagal vidinį grafiką ir yra paskirstytas laikui bėgant. PostgreSQL turi galimybę pristabdyti darbą ir neapkrauti disko posistemio. Tai naudinga PostgreSQL. Ir užklausos, kurios vykdomos tikrinimo taško metu, nepatirs streso dėl to, kad disko posistemis yra užimtas.
Ir norint sureguliuoti kontrolinį tašką, yra trys parametrai:
сheckpoint_segments.сheckpoint_timeout.сheckpoint_competion_target.
Jie leidžia reguliuoti valdymo taškų darbą. Bet aš apie juos nesigilinsiu. Jų įtaka – atskira tema.
Įspėjimas: Ataskaitoje aptarta 9.4 versija nebeaktuali. Šiuolaikinėse PostgreSQL versijose parametras checkpoint_segments pakeisti parametrais min_wal_size и max_wal_size.
Kitas posistemis yra fono rašytojas − background writer. Ką jis daro? Jis nuolat veikia begaliniu ciklu. Nuskaito puslapius bendrinamuose buferiuose ir išmeta nešvarius puslapius, kuriuos rado į diską. Taigi tai padeda kontroliniui atlikti mažiau darbo vykdant kontrolinį tašką.
Kam dar to reikia? Tai numato tuščių puslapių poreikį bendrinamuose buferiuose, jei staiga jų prireikia (dideliais kiekiais ir nedelsiant), kad būtų galima talpinti duomenis. Tarkime, susiklostė situacija, kai užklausai užpildyti reikėjo tuščių puslapių ir jie jau buvo bendrai naudojamuose buferiuose. Postgresive backend jis tik pasiima ir naudojasi, jam pačiam nieko nereikia valyti. Bet jei staiga tokių puslapių nėra, užpakalinė programa pristabdo darbą ir pradeda ieškoti puslapių, kad išmestų juos į diską ir paimtų savo reikmėms – tai neigiamai veikia šiuo metu vykdomos užklausos laiką. Jei matote, kad turite parametrą maxwritten_clean didelis, tai reiškia, kad foninis rašytojas neatlieka savo darbo ir jums reikia padidinti parametrus bgwriter_lru_maxpages, kad per vieną ciklą galėtų atlikti daugiau darbų, išvalyti daugiau puslapių.
Ir dar vienas labai naudingas rodiklis yra buffers_backend_fsync. Backends neveikia fsync, nes jis lėtas. Jie perduoda fsync IO kamino kontroliniam žymekliui. Kontrolinis žymeklis turi savo eilę, jis periodiškai apdoroja fsync ir sinchronizuoja puslapius atmintyje su failais diske. Jei eilė prie kontrolinio žymeklio yra didelė ir pilna, tada užpakalinė programa yra priversta pati atlikti fsync ir tai sulėtina užpakalinės programos darbą, t. y. klientas gaus atsakymą vėliau nei galėtų. Jei matote, kad jūsų vertė yra didesnė už nulį, tai jau yra problema ir reikia atkreipti dėmesį į foninio rašiklio nustatymus ir įvertinti disko posistemio veikimą.
Įspėjimas: _Toliau pateiktame tekste aprašomi statistiniai rodiniai, susiję su replikavimu. Dauguma rodinių ir funkcijų pavadinimų buvo pervadinti Postgres 10. Pervadinimo esmė buvo pakeisti xlog apie wal и location apie lsn funkcijų / rodinių pavadinimuose ir kt. Ypatingas pavyzdys, funkcija pg_xlog_location_diff() buvo pervadintas į pg_wal_lsn_diff()._
Čia taip pat turime daug dalykų. Bet mums reikia tik su vieta susijusių dalykų.
Jei matome, kad visos vertės yra lygios, tai yra idealus variantas, o kopija neatsilieka nuo meistro.
Ši šešioliktainė padėtis čia yra pozicija operacijų žurnale. Jis nuolat didėja, jei duomenų bazėje yra kokia nors veikla: įterpia, ištrina ir pan.
сколько записано 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());Jei šie dalykai skiriasi, tai yra tam tikras atsilikimas. Lag yra delsa tarp replikos ir pagrindinio, ty serverių duomenys skiriasi.
Yra trys vėlavimo priežastys:
- Šis disko posistemis negali susidoroti su įrašo failų sinchronizavimu.
- Tai galimos tinklo klaidos arba tinklo perkrova, kai duomenys nespėja pasiekti kopijos ir ji negali jos atkurti.
- Ir procesorius. Procesorius yra labai retas atvejis. Ir aš tai mačiau du ar tris kartus, bet taip pat gali atsitikti.
Ir čia yra trys užklausos, leidžiančios naudoti statistiką. Galime įvertinti, kiek užfiksavome operacijų žurnale. Yra tokia funkcija pg_xlog_location_diff ir galime įvertinti replikacijos vėlavimą baitais ir sekundėmis. Tam taip pat naudojame vertę iš šio rodinio (PERŽIŪRA).
Pastaba: _Vietoj pg_xlog_locationFunkcija diff() gali naudoti atimties operatorių ir atimti vieną vietą iš kitos. Patogus.
Yra vienas taškas su atsilikimu, kuris yra sekundėmis. Jei pagrindiniame kompiuteryje nėra jokios veiklos, sandoris įvyko maždaug prieš 15 minučių ir nėra jokios veiklos, o jei pažvelgsime į šį vėlavimą kopijoje, pamatysime 15 minučių vėlavimą. Tai verta prisiminti. Ir tai gali būti painu, kai stebite šį atsilikimą.
Pg_stat_all_tables yra dar vienas naudingas vaizdas. Tai rodo statistiką lentelėse. Kai duomenų bazėje turime lenteles, su ja vyksta tam tikra veikla, kai kurie veiksmai, mes galime gauti šią informaciją iš šio rodinio.
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;Pirmas dalykas, į kurį galime atkreipti dėmesį, yra nuoseklūs nuskaitymai visoje lentelėje. Pats skaičius po šių perdavimų nebūtinai yra blogas ir nėra rodiklis, kad turime ką nors padaryti.
Tačiau yra ir antra metrika – seq_tup_read. Tai eilučių skaičius, grąžintas iš nuoseklaus nuskaitymo. Jei vidutinis skaičius viršija 1 000, 10 000, 50 000, 100 000, tai jau yra rodiklis, kad galbūt reikia kur nors sukurti indeksą, kad užklausos būtų pagrįstos indeksu, arba galima optimizuoti užklausas, kurios naudoja tokius nuoseklius nuskaitymus. kad taip neatsitiks buvo.
Paprastas pavyzdys – tarkime užklausa su dideliu OFFSET ir LIMIT išlaidomis. Pavyzdžiui, nuskaitoma 100 000 lentelės eilučių ir po to paimama 50 000 reikiamų eilučių, o ankstesnės nuskaitytos eilutės atmetamos. Tai irgi blogas atvejis. Ir tokias užklausas reikia optimizuoti. Ir čia yra paprasta SQL užklausa, kurioje galite pažvelgti į tai ir įvertinti gautus skaičius.
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;Lentelių dydžius taip pat galima gauti naudojant šią lentelę ir naudojant papildomas funkcijas pg_total_relation_size(), pg_relation_size().
Apskritai yra metakomandos dt и di, kurį galima naudoti PSQL, taip pat peržiūrėti lentelių ir indeksų dydžius.
Tačiau funkcijų naudojimas padeda pažvelgti į lentelių dydžius, net atsižvelgiant į indeksus arba neatsižvelgiant į indeksus, ir jau atlikti tam tikrus įvertinimus pagal duomenų bazės augimą, t.y. kaip ji auga, kokiu intensyvumu ir padaryti keletą išvadų apie dydžio optimizavimą.
Veiklos įrašymas. Kas yra įrašas? Pažiūrėkime į operaciją UPDATE – lentelės eilučių atnaujinimo operacija. Tiesą sakant, atnaujinimas yra dvi operacijos (ar net daugiau). Taip įterpiama nauja eilutės versija ir pažymima senoji eilutės versija kaip pasenusi. Vėliau ateis automatinis siurblys ir išvalys šias pasenusias linijų versijas, pažymėdamas šią vietą kaip galimą naudoti pakartotinai.
Be to, atnaujinimas yra ne tik lentelės atnaujinimas. Tai taip pat yra indekso atnaujinimas. Jei lentelėje turite daug indeksų, naujinimo metu taip pat reikės atnaujinti visus indeksus, kuriuose yra užklausoje atnaujintų laukų. Šiuose indeksuose taip pat bus pasenusių eilučių versijų, kurias reikės išvalyti.
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;Ir dėl savo naujo dizaino UPDATE yra sunkiasvorė operacija. Bet juos galima padaryti lengviau. Valgyk hot updates. Jie pasirodė PostgreSQL 8.3 versijoje. Ir kas tai yra? Tai lengvas naujinimas, dėl kurio indeksai nėra atkuriami. Tai yra, mes atnaujinome įrašą, bet buvo atnaujintas tik įrašas puslapyje (kuris priklauso lentelei), o indeksai vis tiek nurodo tą patį įrašą puslapyje. Yra šiek tiek įdomios veikimo logikos: kai atsiranda vakuumas, jis sukuria šias grandines hot perstato ir viskas veikia toliau neatnaujinus indeksų, o viskas vyksta su mažiau resursų švaistymu.
O kada tu n_tup_hot_upd didelis, tada labai gerai. Tai reiškia, kad dominuoja lengvi atnaujinimai, o tai mums yra pigiau išteklių požiūriu ir viskas yra gerai.
ALTER TABLE table_name SET (fillfactor = 70);Kaip padidinti garsumą hot updateov? Galime naudoti fillfactor. Jis nustato rezervuotos laisvos vietos dydį užpildant puslapį lentelėje naudojant INSERT. Kai įdėklai pridedami prie lentelės, jie visiškai užpildo puslapį ir nepalieka tuščios vietos. Tada paryškinamas naujas puslapis. Duomenys pildomi dar kartą. Ir tai yra numatytasis elgesys, užpildymo koeficientas = 100%.
Mes galime padaryti užpildymo koeficientą 70%. Tai yra, įterpimų metu buvo paryškintas naujas puslapis, tačiau užpildyta tik 70% puslapio. O mums rezervui liko 30 proc. Kai reikės atlikti atnaujinimą, greičiausiai tai įvyks tame pačiame puslapyje, o nauja eilutės versija tilps tame pačiame puslapyje. Ir hot_update bus atlikta. Taip lengviau rašyti ant lentelių.
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));Autovakuumo eilė. Autovacuum yra posistemis, apie kurį PostgreSQL yra labai mažai statistikos. Tik pg_stat_activity esančiose lentelėse matome, kiek šiuo metu turime vakuumų. Tačiau labai sunku suprasti, kiek lentelių iš karto yra eilėje.
Pastaba: _Pradedant „Postgres 10“, padėtis su Vatovac stebėjimu labai pagerėjo – atsirado pg_stat_progress vaizdasvakuumas, o tai žymiai supaprastina automobilio vakuumo stebėjimo klausimą.
Galime naudoti šią supaprastintą užklausą. Ir mes matome, kada reikės padaryti vakuumą. Bet kaip ir kada turėtų prasidėti vakuumas? Tai yra senos eilučių, apie kurias kalbėjau anksčiau, versijos. Įvyko atnaujinimas, buvo įterpta nauja eilutės versija. Pasirodė pasenusi eilutės versija. Lentelėje pg_stat_user_tables yra toks parametras n_dead_tup. Tai rodo „negyvų“ eilučių skaičių. Ir kai tik negyvų eilučių skaičius tampa didesnis už tam tikrą slenkstį, prie stalo ateis automatinis vakuumas.
Ir kaip ši riba apskaičiuojama? Tai labai konkretus procentas nuo bendro lentelės eilučių skaičiaus. Yra parametras autovacuum_vacuum_scale_factor. Jis nustato procentą. Tarkime, 10% + yra papildomas pagrindinis 50 eilučių slenkstis. Ir kas atsitiks? Kai turime daugiau negyvų eilučių nei „10% + 50“ iš visų lentelės eilučių, tada lentelę perkeliame į automatinį vakuumą.
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));Tačiau yra vienas dalykas. Pagrindiniai parametrų slenksčiai av_base_thresh и av_scale_factor galima priskirti individualiai. Ir atitinkamai lentelės riba bus ne pasaulinė, o individuali. Todėl norint apskaičiuoti, reikia naudoti gudrybes ir gudrybes. O jei susidomėjote, galite pažvelgti į mūsų kolegų iš Avito patirtį (nuoroda skaidrėje negalioja ir buvo atnaujinta tekste).
Jie rašė už , kuriame atsižvelgiama į šiuos dalykus. Ten yra dviejų lakštų pėdų audeklas. Bet skaičiuoja teisingai ir gana efektyviai leidžia įvertinti, kur reikia daug vakuumo stalams, kur jo mažai.
Ką galime dėl to padaryti? Jei turime didelę eilę ir autovakuumas negali susidoroti, galime padidinti vakuuminių darbuotojų skaičių arba tiesiog padaryti siurblį agresyvesnį, kad jis įsijungtų anksčiau, apdoroja lentelę mažais gabalėliais. Ir taip eilė sumažės. — Čia svarbiausia stebėti diskų apkrovą, nes... vakuumas nėra nemokamas dalykas, nors atsiradus SSD/NVMe įrenginiams problema tapo mažiau pastebima.
Pg_stat_all_indexes yra indeksų statistika. Ji nėra didelė. Ir mes galime jį naudoti norėdami gauti informacijos apie indeksų naudojimą. Ir, pavyzdžiui, galime nustatyti, kokius indeksus turime papildomai.
Kaip jau sakiau, atnaujinimas yra ne tik lentelių, bet ir indeksų atnaujinimas. Atitinkamai, jei lentelėje turime daug indeksų, tada atnaujinant lentelės eilutes reikia atnaujinti ir indeksuotų laukų indeksus, o Jei turime nenaudojamų indeksų, kuriems nėra indeksų nuskaitymo, tada jie kabo kaip balastas. Ir mes turime jų atsikratyti. Tam mums reikia lauko idx_scan. Mes tiesiog žiūrime į rodyklės nuskaitymų skaičių. Jei per gana ilgą statistikos saugojimo laikotarpį (bent 2-3 savaites) indeksai nuskaito nulį, greičiausiai tai yra blogi indeksai, turime jų atsikratyti.
Pastaba: Ieškant nenaudojamų indeksų srautinio replikacijos klasterių atveju, reikia patikrinti visus klasterio mazgus, nes statistika nėra pasaulinė, o jei indeksas nenaudojamas pagrindiniame kompiuteryje, tada jį galima naudoti kopijose (jei ten yra apkrova).
Dvi nuorodos:
Tai yra sudėtingesnių užklausų pavyzdžiai, kaip ieškoti nenaudojamų indeksų.
Antroji nuoroda yra gana įdomus prašymas. Ten yra labai nebanali logika. Rekomenduoju kaip nuoroda.
Ką dar verta apibendrinti naudojant indeksus?
Nenaudojami indeksai yra blogi.
Jie užima vietą.
Sulėtinkite atnaujinimo operacijas.
Papildomas darbas vakuumui.
Jei pašalinsime nenaudojamus indeksus, duomenų bazę tik pagerinsime.
Kitas pristatymas yra pg_stat_activity. Tai yra naudingumo analogas ps, tik „PostgreSQL“. Jeigu psTada pažvelkite į procesus operacinėje sistemoje pg_stat_activity Tai parodys veiklą PostgreSQL viduje.
Kokių naudingų dalykų galime iš ten pasiimti?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Duomenų bazėje matome bendrą veiklą, kas vyksta. Galime atlikti naują dislokavimą. Čia viskas sprogo, nauji ryšiai nepriimami, klaidos liejasi programoje.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Galime paleisti tokią užklausą ir pamatyti bendrą ryšių procentą, palyginti su maksimaliu ryšio limitu, ir pamatyti, kas turi daugiausiai ryšių. Ir šiuo konkrečiu atveju matome tą vartotoją cron_role atidarytos 508 jungtys. Ir ten jam kažkas atsitiko. Turime su tuo susitvarkyti ir pažvelgti. Ir visai gali būti, kad tai kažkoks nenormalus jungčių skaičius.
Jei turime OLTP darbo krūvį, užklausos turi būti greitos, labai greitos ir neturėtų būti ilgų užklausų. Tačiau jei kyla ilgų užklausų, trumpuoju laikotarpiu nėra ko jaudintis, bet Ilgainiui ilgos užklausos kenkia duomenų bazei, jos padidina lentelių išpūtimo efektą, kai atsiranda lentelės fragmentacija. Turite atsikratyti ir išsipūtimo, ir ilgų užklausų.
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;Atkreipkite dėmesį: su šia užklausa galime nustatyti ilgas užklausas ir operacijas. Mes naudojame funkciją clock_timestamp() nustatyti veikimo laiką. Ilgas užklausas, kurias radome, galime prisiminti, įvykdyti explain, pažiūrėk planus ir kaip nors optimizuok. Pašaliname dabartinius ilgus prašymus ir judame toliau.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Blogos operacijos yra neaktyvios operacijos būsenos ir neaktyvios operacijos (nutrauktos) būsenos.
Ką tai reiškia? Sandoriai turi kelias būsenas. Ir bet kuriuo metu galima numanyti vieną iš šių būsenų. Yra laukas būsenoms apibrėžti state šiame pristatyme. Ir mes naudojame jį būsenai nustatyti.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Ir, kaip sakiau aukščiau, šios dvi valstybės neveikianti operacija ir neaktyvi operacija (nutraukta) yra blogai. Kas tai yra? Tai yra tada, kai programa atidarė operaciją, atliko tam tikrus veiksmus ir pradėjo savo verslą. Sandoris lieka atviras. Jis pakimba, jame nieko nevyksta, užmezga ryšį, užrakina pakeistas eilutes ir galimai padidina kitų lentelių išsipūtimą dėl Postrges transakcijų variklio architektūros. Ir tokius sandorius taip pat reikėtų numušti, nes jie apskritai žalingi, bet kokiu atveju.
Jei matote, kad jūsų duomenų bazėje jų yra daugiau nei 5-10-20, tuomet reikia susirūpinti ir pradėti su jais ką nors daryti.
Čia taip pat naudojame skaičiuojant laiką clock_timestamp(). Mes fotografuojame sandorius ir optimizuojame programą.
Kaip minėjau aukščiau, blokavimas yra tada, kai du ar daugiau sandorių kovoja už vieną ar grupę išteklių. Tam turime lauką waiting su logine verte true arba false.
Tiesa – tai reiškia, kad procesas laukia, reikia kažką daryti. Kai procesas laukia, tai reiškia, kad klientas, kuris inicijavo šį procesą, taip pat laukia. Klientas sėdi naršyklėje ir taip pat laukia.
Įspėjimas: _Pradedant nuo 9.6 versijos Postgres lauko waiting pašalintas, o vietoj jo pridėti dar du informaciniai laukai wait_event_type и wait_event._
Ką daryti? Jei ilgą laiką matai tiesa, vadinasi, reikia tokių prašymų atsikratyti. Mes tiesiog numušame tokius sandorius. Rašome kūrėjams, kad reikia kažkaip optimizuoti, kad nebūtų lenktynių dėl resursų. Ir tada kūrėjai optimizuoja programą, kad taip neatsitiktų.
Ir yra kraštutinis, bet potencialiai nemirtingas atvejis aklavietės atsiradimas. Dvi operacijos atnaujino du išteklius, tada vėl juos pasiekė, šį kartą į priešingus išteklius. Tokiu atveju „PostgreSQL“ užmuša pačią operaciją, kad kitas galėtų tęsti darbą. Tai yra aklavietės situacija ir ji pati negali to išsiaiškinti. Todėl PostgreSQL yra priversta imtis kraštutinių priemonių.
Ir čia yra dvi užklausos, leidžiančios sekti blokavimą. Mes naudojame vaizdą pg_locks, kuri leidžia sekti sunkias spynas.
Ir pirmoji nuoroda yra pats užklausos tekstas. Tai gana ilgas.
O antroji nuoroda – straipsnis apie spynas. Skaityti naudinga, labai įdomu.
Taigi ką mes matome? Matome du prašymus. Sandoris su ALTER TABLE yra blokuojanti operacija. Pradėta, bet neužbaigta, o šią operaciją įrašiusi programa kažkur atlieka kitus darbus. Ir antrasis prašymas yra atnaujinti. Jis laukia, kol baigsis keitimo stalas, kad galėtų tęsti darbą.
Taip galime sužinoti, kas ką užrakino, ką laiko ir galime spręsti toliau.
Kitas modulis yra pg_stat_statements. Kaip sakiau, tai modulis. Norint juo naudotis, reikia įkelti jos biblioteką į konfigūraciją, iš naujo paleisti PostgreSQL, įdiegti modulį (viena komanda) ir tada turėsime naują vaizdą.
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;Ką galime iš ten pasiimti? Jei kalbame apie paprastus dalykus, galime imti vidutinį užklausos vykdymo laiką. Laikas auga, o tai reiškia, kad PostgreSQL reaguoja lėtai ir mums reikia ką nors padaryti.
Galime peržiūrėti aktyviausias duomenų bazės įrašymo operacijas, kurios keičia duomenis bendruose buferiuose. Sužinokite, kas ten atnaujina arba ištrina duomenis.
Ir mes galime tiesiog pažvelgti į skirtingą šių užklausų statistiką.
Mes pg_stat_statements Mes naudojame jį ataskaitoms kurti. Kartą per dieną iš naujo nustatome statistiką. Sukaupkime jį. Prieš iš naujo nustatydami statistiką kitą kartą, sukurkime ataskaitą. Čia yra nuoroda į ataskaitą. Galite žiūrėti.
Ką mes darome? Skaičiuojame bendrąją visų užklausų statistiką. Tada kiekvienoje užklausoje skaičiuojame individualų indėlį į šią bendrą statistiką.
Ir ką mes galime žiūrėti? Galime žiūrėti į bendrą visų tam tikro tipo užklausų vykdymo laiką visų kitų užklausų fone. Galime pažvelgti į procesoriaus ir įvesties / išvesties išteklių naudojimą, palyginti su bendra nuotrauka. Ir jau optimizuokite šias užklausas. Remdamiesi šia ataskaita, kuriame populiariausias užklausas ir jau gauname peno pagalvoti, ką optimizuoti.
Ką mes palikome už kadro? Dar liko keletas pasiūlymų, kurių nesvarsčiau, nes laikas ribotas.
Yra pgstattuple taip pat yra papildomas modulis iš standartinio įnašų paketo. Tai leidžia įvertinti bloat stalai, vadinamieji stalo suskaidymas. O jei yra daug suskaidymo, reikia jį pašalinti ir naudoti skirtingus įrankius. Ir funkcija pgstattuple veikia ilgai. Ir kuo daugiau lentelių, tuo ilgiau jis veiks.
Kitas indėlis yra pg_buffercache. Tai leidžia apžiūrėti bendrinamus buferius: kaip intensyviai ir kokioms lentelėms naudojami buferio puslapiai. Ir tai tiesiog leidžia pažvelgti į bendrus buferius ir įvertinti, kas ten vyksta.
Kitas modulis yra pgfincore. Tai leidžia atlikti žemo lygio lentelės operacijas per sistemos skambutį mincore(), ty leidžia įkelti lentelę į bendrinamus buferius arba iškrauti. Ir, be kita ko, leidžia apžiūrėti operacinės sistemos puslapio talpyklą, t.y., kiek vietos užima lentelė puslapio talpykloje, bendruose buferiuose ir tiesiog leidžia įvertinti lentelės darbo krūvį.
Kitas modulis - pg_stat_kcache. Jis taip pat naudoja sistemos skambutį getrusage(). Ir ji ją vykdo prieš ir po to, kai įvykdoma užklausa. O gautoje statistikoje tai leidžia įvertinti, kiek mūsų užklausa išleido disko I/O, t.y. operacijoms su failų sistema, ir žiūri į procesoriaus naudojimą. Tačiau modulis yra jaunas (cough cough) ir jo veikimui reikalinga PostgreSQL 9.4 ir pg_stat_statements, apie kuriuos minėjau anksčiau.
Žinoti, kaip naudoti statistiką, naudinga. Jums nereikia trečiųjų šalių programų. Galite ateiti, pamatyti, ką nors padaryti, ką nors nuveikti.
Naudoti statistiką nėra sunku, tai tiesiog įprastas SQL. Jūs surinkote užklausą, surašėte, išsiuntėte, peržiūrėjote.
Statistika padeda atsakyti į klausimus. Jei kyla klausimų, kreipiatės į statistiką – pažiūrėkite, padarykite išvadas, analizuokite rezultatus.
Ir eksperimentuoti. Yra daug užklausų, daug duomenų. Visada galite optimizuoti esamą užklausą. Galite sukurti savo užklausos versiją, kuri jums tinka labiau nei originalas, ir ją naudoti.
Nuorodos
Tinkamos nuorodos, kurios buvo rastos straipsnyje, remiantis medžiaga, buvo ataskaitoje.
Autorius parašyk daugiau
(angl.)
Statistikos rinkėjas
Sistemos administravimo funkcijos
Įnašų moduliai
SQL utils ir sql kodo pavyzdžiai
Ačiū visiems už dėmesį!
Šaltinis: www.habr.com
