Transkripsje fan Alexey Lesovsky's 2015-rapport "Deep dive into PostgreSQL internal statistics"
Disclaimer fan de skriuwer fan it rapport: Ik konstatearje dat dit rapport is datearre novimber 2015 - mear as 4 jier binne foarby en in protte tiid is foarby. De ferzje 9.4 besprutsen yn it rapport wurdt net langer stipe. Yn 'e ôfrûne 4 jier binne 5 nije releases frijlitten wêryn d'r in protte ynnovaasjes, ferbetteringen en feroaringen binne oangeande statistiken, en guon fan it materiaal is ferâldere en net relevant. Wylst ik besjoch, besocht ik dizze plakken te markearjen om de lêzer net te mislieden. Ik haw dizze passaazjes net oerskreaun, der binne in protte fan en it resultaat sil in folslein oar rapport wêze.
De PostgreSQL DBMS is in enoarm meganisme, en dit meganisme bestiet út in protte subsystemen, wêrfan de koördinearre operaasje direkt de prestaasjes fan 'e DBMS beynfloedet. Tidens operaasje wurde statistiken en ynformaasje oer de wurking fan komponinten sammele, wêrtroch jo de effektiviteit fan PostgreSQL kinne evaluearje en maatregels nimme om prestaasjes te ferbetterjen. D'r is lykwols in protte fan dizze ynformaasje en it wurdt presintearre yn in frij ferienfâldige foarm. It ferwurkjen fan dizze ynformaasje en it ynterpretearjen is soms in folslein net-triviale taak, en de "dierentuin" fan ark en nutsbedriuwen kin sels in avansearre DBA maklik betiizje.
Goeiemiddei Myn namme is Aleksey. Lykas Ilya sei, sil ik prate oer PostgreSQL-statistiken.
PostgreSQL-aktiviteitsstatistiken. PostgreSQL hat twa statistiken. Aktiviteitsstatistiken dy't besprutsen wurde. En scheduler statistyk oer gegevens distribúsje. Ik sil spesifyk prate oer PostgreSQL-aktiviteitsstatistiken, wêrtroch wy prestaasjes kinne beoardielje en it op ien of oare manier ferbetterje.
Ik sil jo fertelle hoe't jo statistiken effektyf kinne brûke om in ferskaat oan problemen op te lossen dy't jo hawwe of kinne hawwe.
Wat sil net yn it rapport stean? Yn it rapport sil ik de statistyk fan planner net oanreitsje, om't ... Dit is in apart ûnderwerp foar in apart rapport oer hoe't gegevens wurde opslein yn 'e database en hoe't de queryplanner in idee krijt fan' e kwalitative en kwantitative skaaimerken fan dizze gegevens.
En d'r sille gjin arkbeurzen wêze, ik sil it iene produkt net mei it oare fergelykje. Der sil gjin reklame wêze. Litte wy dat oan 'e kant sette.
Ik wol jo sjen litte dat it brûken fan statistiken nuttich is. It is nedich. It is feilich te brûken. Alles wat wy nedich binne gewoane SQL en basiskennis fan SQL.
En lit ús prate oer hokker statistyk te kiezen om problemen op te lossen.
As wy nei PostgreSQL sjogge en it kommando op it bestjoeringssysteem útfiere om prosessen te besjen, sille wy in "swarte doaze" sjen. Wy sille wat prosessen sjen dy't wat dogge, en út 'e namme kinne wy ús rûchwei yntinke wat se dêr dogge, wat se dogge. Mar, yn wêzen, it is in swarte doaze wy kinne net sjen nei binnen.
Wy kinne de CPU-laden yn sjen top, kinne wy sjen op ûnthâld gebrûk troch guon systeem nutsbedriuwen, mar wy sille net by steat wêze om te sjen binnen PostgreSQL. Dêrfoar hawwe wy oare ark nedich.
En fierder fierder, sil ik jo fertelle wêr't de tiid trochbrocht wurdt. As wy ús PostgreSQL yn 'e foarm fan sa'n diagram foarstelle, dan kinne wy antwurdzje wêr't de tiid trochbrocht wurdt. Dit binne twa dingen: it ferwurket kliïntoanfragen fan applikaasjes en de eftergrûntaken dy't PostgreSQL útfiert om himsels rinnend te hâlden.
As wy begjinne te sjen nei de boppeste linker hoeke, kinne wy sjen hoe't kliïnt fersiken wurde ferwurke. It fersyk komt fan 'e applikaasje en in kliïntsesje wurdt iepene foar fierdere wurk. It fersyk wurdt stjoerd nei de planner. De planner bout in queryplan. Stjoert it fierder foar útfiering. D'r is in soarte fan ynfier / útfier fan blokgegevens ferbûn mei tabellen en yndeksen. De nedige gegevens wurdt lêzen fan de skiven yn it ûnthâld yn in spesjaal gebiet "dielde buffers". De resultaten fan it fersyk, as se updates, wiskjes binne, wurde opnommen yn it transaksjelog yn WAL. Guon statistyske ynformaasje einiget yn it log of statistyksamler. En it resultaat fan it fersyk wurdt weromstjoerd nei de klant. Dêrnei kin de klant alles wer werhelje mei in nij fersyk.
Hoe sit it mei eftergrûntaken en eftergrûnprosessen? Wy hawwe ferskate prosessen dy't de databank op en rinnen hâlde yn normale bestjoeringsmodus. Dizze prosessen sille ek wurde oanrekke yn it rapport: autovacuum, checkpointer, replikaasje-relatearre prosessen, eftergrûnskriuwer. Ik sil elk fan har oanreitsje as ik rapportearje.
Hokker problemen binne der mei statistiken?
- Der is in soad ynformaasje. PostgreSQL 9.4 biedt 109 metriken foar it besjen fan statistyske gegevens. As de databank lykwols in protte tabellen, skema's, databases opslacht, dan moatte al dizze metriken fermannichfâldige wurde mei it oerienkommende oantal tabellen, databases. Dat is, der is noch mear ynformaasje. En it is hiel maklik om te ferdrinken yn it.
- It folgjende probleem is dat statistiken wurde fertsjintwurdige troch tellers. As wy sjogge nei dizze statistiken, wy sille sjen hieltyd tanimmende tellers. En as der in protte tiid is ferrûn sûnt de statistyk waard reset, sille wy wearden yn miljarden sjen. En se fertelle ús neat.
- Gjin ferhaal. As jo hie in soarte fan mislearjen, wat foel 15-30 minuten lyn, do silst net by steat wêze om te brûken statistyk en sjen wat der bard 15-30 minuten lyn. Dit is probleem.
- It ûntbrekken fan in ark ynboud yn PostgreSQL is in probleem. De kernel-ûntwikkelders leverje gjin nut. Soks ha se net. Se jouwe gewoan statistiken yn 'e databank. Brûk it, meitsje der in fersyk nei, doch wat jo wolle.
- Om't d'r gjin ark is ynboud yn PostgreSQL, feroarsake dit in oar probleem. In protte ark fan tredden. Elk bedriuw dat min of mear direkte hannen hat, besiket in eigen programma te skriuwen. En as gefolch hat de mienskip in protte ark dy't brûkt wurde kinne om te wurkjen mei statistiken. En guon ark hawwe bepaalde mooglikheden, oare ark hawwe gjin oare mooglikheden, of der binne wat nije mooglikheden. En der ûntstiet in situaasje dat jo twa, trije of fjouwer ark brûke moatte dy't elkoar oerlappe en ferskate funksjes hawwe. Dit is tige onaangenaam.
Wat folget hjirút? It is wichtich om statistyk direkt te nimmen, om net ôfhinklik te wêzen fan programma's, of om dizze programma's sels op ien of oare manier te ferbetterjen: foegje guon funksjes ta om jo eigen foardiel te krijen.
En jo hawwe basiskennis fan SQL nedich. Om wat gegevens fan statistiken te krijen, moatte jo SQL-fragen oanmeitsje, dus jo moatte witte hoe't selektearje en meidwaan wurde kompilearre.
Statistiken fertelle ús ferskate dingen. Se kinne wurde ferdield yn kategoryen.
- De earste kategory is eveneminten dy't foarkomme yn 'e databank. Dit is as ien of oare evenemint yn 'e databank foarkomt: in fersyk, tagong ta in tafel, autovacuum, commits, dan binne dit allegear eveneminten. De tellers dy't oerienkomme mei dizze eveneminten wurde ferhege. En wy kinne dizze eveneminten folgje.
- De twadde kategory is de eigenskippen fan objekten lykas tabellen en databases. Se hawwe eigenskippen. Dit is de grutte fan 'e tabellen. Wy kinne de groei fan tabellen en de groei fan yndeksen folgje. Wy kinne feroaringen yn dynamyk sjen.
- En de tredde kategory is de tiid bestege oan it evenemint. In fersyk is in evenemint. It hat in eigen spesifike mjitte fan doer. Begûn hjir, einige hjir. Wy kinne it folgje. Of de tiid dy't it nimt om in blok fan skiif te lêzen of it te skriuwen. Sokke dingen wurde ek kontrolearre.
Boarnen fan statistiken wurde as folget presintearre:
- Yn dielde ûnthâld (dielde buffers) is d'r in segmint foar it bewarjen fan statyske gegevens, d'r binne ek dy tellers dy't hieltyd ferhege wurde as bepaalde eveneminten foarkomme, of guon mominten ûntsteane yn 'e wurking fan' e databank.
- Al dizze tellers binne net tagonklik foar de brûker en net iens tagonklik foar de behearder. Dit binne dingen op leech nivo. Om tagong te krijen ta har, biedt PostgreSQL in ynterface yn 'e foarm fan SQL-funksjes. Wy kinne selekteare werjeften meitsje mei dizze funksjes en in soarte fan metrik krije (as set fan metriken).
- It brûken fan dizze funksjes is lykwols net altyd handich, dus funksjes binne de basis foar werjeften (VIEWS). Dit binne firtuele tabellen dy't statistiken leverje oer in spesifyk subsysteem, of op in bepaalde set fan eveneminten yn 'e databank.
- Dizze ynbêde werjeften (VIEWs) binne de primêre brûkersynterface foar wurkjen mei statistyk. Se binne standert beskikber sûnder ekstra ynstellingen, jo kinne se daliks brûke, nei se sjen en ynformaasje fan har nimme. En dan binne der bydragen. Bydragen binne offisjeel. Jo kinne it postgresql-contrib-pakket ynstallearje (bygelyks postgresql94-contrib), de fereaske module yn 'e konfiguraasje lade, parameters foar it opjaan, PostgreSQL opnij starte en jo kinne it brûke. (Noat. Ofhinklik fan de distribúsje is yn resinte ferzjes it bydragepakket diel fan it haadpakket).
- En d'r binne net-offisjele bydragen. Se binne net opnommen yn 'e standert PostgreSQL-distribúsje. Se moatte wurde kompilearre of ynstalleare as in bibleteek. De opsjes kinne hiel oars wêze, ôfhinklik fan wat de ûntwikkelder fan dizze net-offisjele bydrage kaam.
Dizze slide presintearret al dy VIEWS en guon fan 'e funksjes dy't beskikber binne yn PostgreSQL 9.4. Sa't wy sjogge, der binne in protte fan harren. En it is frij maklik om yn 'e war te kommen as jo it foar it earst tsjinkomme.
Lykwols, as wy nimme de foarige foto Как тратится время на PostgreSQL en kompatibel mei dizze list, wy krije dizze foto. Wy kinne elke werjefte (VIEWs) as elke funksje foar ien of oare doel brûke om de oerienkommende statistiken te krijen as PostgreSQL rint. En wy kinne al wat ynformaasje krije oer de wurking fan it subsysteem.
It earste ding dat wy sille sjen is pg_stat_database. Sa't wy sjen kinne, dit is in foarstelling. Der sit in soad ynformaasje yn. De meast fariearre ynformaasje. En it jout heul nuttige kennis fan wat der bart yn ús databank.
Hokker nuttige dingen kinne wy dêrwei nimme? Litte wy begjinne mei de ienfâldichste dingen.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;It earste ding dat wy kinne sjen is it cache hit persintaazje. Cache hit rate is in brûkbere metrik. It makket it mooglik om te skatten hoefolle gegevens wurdt nommen út de dielde buffers cache en hoefolle wurdt lêzen fan skiif.
Dat is dúdlik hoe mear cache-hits wy hawwe, hoe better. Wy mjitte dizze metrik as in persintaazje. En, bygelyks, as ús persintaazje fan dizze cache-hits mear is dan 90%, dan is dit goed. As it falt ûnder 90%, betsjut it dat wy net genôch ûnthâld hawwe om de heule kop fan gegevens yn it ûnthâld te hâlden. En om dizze gegevens te brûken, wurdt PostgreSQL twongen om tagong te krijen ta de skiif en dit is stadiger dan as de gegevens út it ûnthâld waarden lêzen. En jo moatte tinke oer it fergrutsjen fan it ûnthâld: of fergrutsje dielde buffers, of fergrutsje hardware ûnthâld (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;Wat kinne jo oars nimme fan dizze foarstelling? Jo kinne anomalies sjen dy't foarkomme yn 'e databank. Wat wurdt hjir toand? D'r binne commits, rollbacks, oanmeitsjen fan tydlike bestannen, har grutte, deadlocks en konflikten.
Wy kinne dit fersyk brûke. Dizze SQL is frij simpel. En wy kinne hjir nei dizze gegevens sjen.
En hjir binne de drompelwearden. Wy sjogge nei de ferhâlding fan commits en rollbacks. Commits is de suksesfolle befêstiging fan in transaksje. Rollbacks binne in rollback, d.w.s. in transaksje die wat wurk, spande de databank, berekkene wat, en dan barde der in mislearring en de resultaten fan 'e transaksje wurde wegere. Dat is it oantal rollbacks hieltyd tanimmend is min. En jo moatte se op ien of oare manier foarkomme, en de koade bewurkje sadat dit net bart.
Konflikten binne relatearre oan replikaasje. En se moatte ek foarkommen wurde. As jo wat fragen hawwe dy't wurde útfierd op in replika en konflikten ûntsteane, dan moatte jo dizze konflikten sortearje en sjen wat der bart. Details kinne fûn wurde yn 'e logs. En eliminearje konfliktsituaasjes sadat applikaasjefersiken sûnder flaters wurkje.
Deadlocks binne ek in minne situaasje. Wannear't fersiken fjochtsje foar middels, ien fersyk tagong ta ien boarne en naam it slot, in twadde fersyk tagong ta de twadde boarne en ek naam it slot, en dan beide fersiken tagong ta elkoars middels en blokkearre wylst wachtsjen foar de buorman te loslitte it slot. Dit is ek in problematyske situaasje. Se moatte wurde oanpakt op it nivo fan it herskriuwen fan applikaasjes en serialisearjen fan tagong ta boarnen. En as jo sjogge dat jo deadlocks konstant tanimme, moatte jo de details yn 'e logs besjen, de situaasjes analysearje dy't ûntsteane en sjen wat it probleem is.
Tydlike triemmen (temp_files) binne ek min. As in brûker fersyk hat net genôch ûnthâld foar in plak operasjonele, tydlike gegevens, it makket in triem op skiif. En alle operaasjes dy't it koe útfiere yn in tydlike buffer yn it ûnthâld begjinne te wurde útfierd op 'e skiif. It is stadich. Dit fergruttet de útfieringstiid fan query. En de kliïnt dy't in fersyk stjoerde nei PostgreSQL sil in bytsje letter in antwurd krije. As al dizze operaasjes wurde útfierd yn it ûnthâld, Postgres sil reagearje folle flugger en de klant sil wachtsje minder.
Pg_stat_bgwriter - Dizze werjefte beskriuwt de wurking fan twa PostgreSQL eftergrûn subsystemen: dit checkpointer и background writer.
Lit ús earst sjen nei de kontrôle punten, de saneamde. checkpoints. Wat binne kontrôlepunten? In kontrôlepunt is in posysje yn it transaksjelogboek dat oanjout dat alle gegevenswizigingen opnommen yn it logboek binne mei súkses syngronisearre mei de gegevens op skiif. It proses, ôfhinklik fan de wurkdruk en ynstellingen, kin lang wêze en bestiet meast út it syngronisearjen fan smoarge siden yn dielde buffers mei gegevensbestannen op skiif. Wêr is it foar? As PostgreSQL konstant tagong hie ta de skiif en gegevens dêrwei ophelle en gegevens oer elke tagong skriuwt, soe it stadich wêze. Dêrom hat PostgreSQL in ûnthâldsegment wêrfan de grutte hinget fan 'e ynstellings yn' e konfiguraasje. Postgres bewarret live gegevens yn dit ûnthâld foar letter ferwurking of query. Yn it gefal fan fersiken om gegevens te feroarjen, wurdt it feroare. En wy krije twa ferzjes fan 'e gegevens. Ien is yn ús ûnthâld, de oare is op skiif. En periodyk moatte jo dizze gegevens syngronisearje. Wy moatte syngronisearje wat is feroare yn it ûnthâld oan skiif. Hjirfoar hawwe jo kontrôlepunten nedich.
Checkpoint giet troch dielde buffers, markearret smoarge siden dat se binne nedich foar checkpoint. Dan lansearret it in twadde pass troch de dielde buffers. En de siden dy't binne markearre foar kontrôlepunt, it syngronisearret se al. Op dizze manier wurde de gegevens syngronisearre mei de skiif.
D'r binne twa soarten kontrôlepunten. Ien kontrôlepunt wurdt útfierd mei in timeout. Dit kontrôlepunt is nuttich en goed - checkpoint_timed. En d'r binne kontrôlepunten op oanfraach - checkpoint required. Dit kontrôlepunt komt foar as wy in heul grut gegevensrekord hawwe. Wy hawwe in protte transaksjelogboeken opnommen. En PostgreSQL is fan betinken dat it dit alles sa rap mooglik moat syngronisearje, in kontrôlepunt meitsje en fierder gean.
En as jo sjogge nei de statistiken pg_stat_bgwriter en seach wat jo hawwe checkpoint_req is folle grutter dan checkpoint_timed, dan is dit min. Wêrom min? Dit betsjut dat PostgreSQL ûnder konstante stress is as it gegevens op skiif skriuwe moat. Timeout checkpoint is minder stressfolle en wurdt útfierd neffens it ynterne skema en is in soarte fan ferspraat oer de tiid. PostgreSQL hat de mooglikheid om it wurk te stopjen en it skiifsubsysteem net te spannen. Dit is nuttich foar PostgreSQL. En fragen dy't wurde útfierd tidens checkpoint sille gjin stress ûnderfine fan it feit dat it skiifsubsysteem drok is.
En om it kontrôlepunt oan te passen binne d'r trije parameters:
сheckpoint_segments.сheckpoint_timeout.сheckpoint_competion_target.
Se tastean jo te regeljen de wurking fan kontrôle punten. Mar ik sil net by harren stilstean. Har ynfloed is in apart ûnderwerp.
Warskôging: De yn it rapport besprutsen ferzje 9.4 is net mear relevant. Yn moderne ferzjes fan PostgreSQL de parameter checkpoint_segments ferfongen troch parameters min_wal_size и max_wal_size.
It folgjende subsysteem is de eftergrûnskriuwer - background writer. Wat docht hy? It rint konstant yn in einleaze loop. Scant siden yn dielde buffers en dumpt smoarge siden dy't it fynt op skiif. Sa helpt it de checkpointer om minder wurk te dwaan by it útfieren fan checkpoint.
Wêr is it oars foar nedich? It soarget foar de needsaak foar lege siden yn dielde buffers as se ynienen nedich binne (yn grutte hoemannichten en fuortendaliks) om gegevens te behertigjen. Stel dat der in situaasje ûntstie doe't lege siden nedich wiene om in fersyk te foltôgjen en se wiene al yn 'e dielde buffers. Postgresive backend hy pakt se mar op en brûkt se, hy hoecht sels neat skjin te meitsjen. Mar as d'r ynienen gjin sokke siden binne, stopet de backend it wurk en begjint te sykjen nei siden om se op skiif te dumpen en te nimmen foar har eigen behoeften - wat negatyf beynfloedet de tiid fan it op it stuit útfierende fersyk. As jo sjogge dat jo in parameter hawwe maxwritten_clean grut, dit betsjut dat de eftergrûn skriuwer net docht syn wurk en jo moatte fergrutsje de parameters bgwriter_lru_maxpages, sadat er yn ien syklus mear wurk dwaan kin, mear siden wiskje.
En in oare heul nuttige yndikator is buffers_backend_fsync. Backends fsync net om't it stadich is. Se passe fsync de IO stack checkpointer troch. De checkpointer hat in eigen wachtrige, it ferwurket periodyk fsync en syngronisearret siden yn it ûnthâld mei bestannen op skiif. As de wachtrige by de checkpointer grut en fol is, dan wurdt de backend twongen om fsync sels te dwaan en dit fertraget it wurk fan 'e backend, d.w.s. de kliïnt krijt letter in antwurd as it koe. As jo sjogge dat jo wearde is grutter as nul, dan is dit al in probleem en jo moatte omtinken jaan oan de ynstellings fan 'e eftergrûnskriuwer en ek de prestaasjes fan it skiifsubsysteem evaluearje.
Warskôging: _De folgjende tekst beskriuwt de statistyske werjeften ferbûn mei replikaasje. De measte werjefte- en funksjenammen waarden omneamd yn Postgres 10. De essinsje fan 'e omneaming wie om te ferfangen xlog op wal и location op lsn yn funksje-/werjeftenammen, ensfh. Bysûnder foarbyld, funksje pg_xlog_location_diff() waard omneamd ta pg_wal_lsn_diff()._
Wy hawwe hjir ek in protte dingen. Mar wy hawwe allinich items nedich yn ferbân mei lokaasje.
As wy sjogge dat alle wearden gelyk binne, dan is dit in ideale opsje en de replika bliuwt net efter de master.
Dizze heksadesimale posysje hjir is de posysje yn it transaksjelogboek. It nimt konstant ta as d'r aktiviteit is yn 'e databank: ynfoegje, wiskje, ensfh.
сколько записано 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());As dizze dingen oars binne, dan is der in soarte fan efterstân. Lag is de efterstân tusken de replika en de master, d.w.s. de gegevens ferskille tusken servers.
D'r binne trije redenen foar de efterstân:
- Dit skiif subsysteem kin net omgean mei opname triem syngronisaasje.
- Dit binne mooglike netwurkflaters, of netwurk oerlêst, as de gegevens gjin tiid hawwe om de replika te berikken en it kin it net reprodusearje.
- En de prosessor. De prosessor is in heul seldsum gefal. En ik seach dit twa of trije kear, mar dit kin ek barre.
En hjir binne trije fragen dy't ús tastean statistyk te brûken. Wy kinne skatte hoefolle wy hawwe opnommen yn it transaksjelogboek. Der is sa'n funksje pg_xlog_location_diff en wy kinne de replikaasjefertraging yn bytes en sekonden skatte. Wy brûke ek de wearde fan dizze werjefte (VIEWs) foar dit.
Tink derom: _Ynstee fan pg_xlog_locationDe funksje diff () kin de subtraksjeoperator brûke en ien lokaasje fan in oare ôflûke. Komfortabel.
D'r is ien punt mei de efterstân, dy't yn sekonden is. As d'r gjin aktiviteit is op 'e master, wie de transaksje der sawat 15 minuten lyn en d'r is gjin aktiviteit, en as wy nei dizze efterstân op 'e replika sjogge, sille wy in efterstân fan 15 minuten sjen. Dit is it wurdich te ûnthâlden. En dit kin betiizjend wêze as jo dizze efterstân besjen.
Pg_stat_all_tables is in oare nuttige werjefte. It toant statistiken op tabellen. As wy tabellen yn 'e databank hawwe, is d'r wat aktiviteit mei, guon aksjes, wy kinne dizze ynformaasje krije fan dizze werjefte.
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;It earste ding dat wy kinne sjen is de opienfolgjende scans oer de tafel. It nûmer sels nei dizze passes is net needsaaklik min en is gjin yndikator dat wy wat moatte dwaan.
D'r is lykwols in twadde metrik - seq_tup_read. Dit is it oantal rigen weromjûn fan 'e opfolgjende scan. As it gemiddelde oantal grutter is as 1, 000, 10, 000, dan is dit al in yndikator dat jo miskien earne in yndeks moatte bouwe sadat queries basearre binne op 'e yndeks, of it is mooglik om queries te optimalisearjen dy't sokke opfolgjende scans brûke. dat dit net bart wie.
In ienfâldich foarbyld - lit ús sizze in fersyk mei in grutte OFFSET en LIMIT kosten. Bygelyks, 100 rigen yn in tabel wurde skansearre en dêrnei wurde 000 ferplichte rigen nommen, en de foarige skansearre rigen wurde wegere. Dit is ek in minne saak. En sokke fragen moatte wurde optimalisearre. En hjir is in ienfâldige SQL-query wêr't jo dit kinne sjen en de resultearjende nûmers evaluearje.
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;Tabel maten kinne ek krije mei help fan dizze tabel en mei help fan ekstra funksjes pg_total_relation_size(), pg_relation_size().
Yn 't algemien binne d'r metakommando's dt и di, dy't brûkt wurde kin yn PSQL en besjoch ek de grutte fan tabellen en yndeksen.
It brûken fan funksjes helpt ús lykwols te sjen nei de grutte fan tabellen, sels mei yndeksen yn rekken brocht, of sûnder rekken te hâlden mei yndeksen, en meitsje al wat skattingen basearre op 'e groei fan' e database, dus hoe't it groeit, mei hokker yntensiteit, en lûk wat konklúzjes oer optimalisaasje fan grutte.
Recording aktiviteit. Wat is in opname? Litte wy nei de operaasje sjen UPDATE - de operaasje fan it bywurkjen fan rigen yn in tabel. Yn feite, update is twa operaasjes (of noch mear). Dit is it ynfoegjen fan in nije ferzje fan 'e rige en markearje de âlde ferzje fan' e rige as ferâldere. Dêrnei sil it autovacuum komme en dizze ferâldere ferzjes fan 'e rigels skjinmeitsje, dit plak markearje as beskikber foar werbrûk.
Derneist is update net allinich oer it bywurkjen fan in tabel. Dit is ek in yndeksupdate. As jo in protte yndeksen op 'e tafel hawwe, dan moatte by it bywurkjen fan alle yndeksen dy't fjilden bywurke binne yn' e query ek bywurke wurde. Dizze yndeksen sille ek ferâldere ferzjes hawwe fan rigen dy't moatte wurde skjinmakke.
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;En troch syn nije ûntwerp is UPDATE in swiergewicht operaasje. Mar se kinne wurde makke makliker. Ite hot updates. Se ferskynden yn PostgreSQL ferzje 8.3. En wat is dit? Dit is in lichtgewicht update dy't net feroarsaket dat yndeksen opnij wurde opboud. Dat is, wy hawwe it rekord bywurke, mar allinich it rekôr op 'e side (dy't by de tabel heart) waard bywurke, en de yndeksen wize noch op deselde rekord op' e side. D'r is in bytsje in nijsgjirrige operearjende logika: as in fakuüm komt, makket it dizze keatlingen hot rebuilds en alles bliuwt te wurkjen sûnder it bywurkjen fan yndeksen, en alles bart mei minder fergriemen fan middels.
En wannear dogge jo n_tup_hot_upd grut, dan is it hiel goed. Dit betsjut dat lichtgewicht updates oerhearskje en dit is goedkeaper foar ús yn termen fan boarnen en alles is goed.
ALTER TABLE table_name SET (fillfactor = 70);Hoe te fergrutsjen folume hot updateov? Wy kinne brûke fillfactor. It bepaalt de grutte fan 'e reservearre frije romte by it ynfoljen fan in side yn in tabel mei INSERTs. As ynfoegingen wurde tafoege oan in tabel, folje se de side folslein en litte gjin lege romte. Dan wurdt in nije side markearre. De gegevens wurde wer ynfolle. En dit is it standertgedrach, fillfactor = 100%.
Wy kinne de fillfactor 70% meitsje. Dat is, by ynfoegingen waard in nije side markearre, mar mar 70% fan 'e side wie fol. En wy hawwe 30% oer as reserve. As jo moatte meitsje in update, it sil nei alle gedachten barre op deselde side, en de nije ferzje fan de line sil passe op deselde side. En hot_update sil dien wurde. Dit makket it makliker om te skriuwen op tabellen.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Autovacuum wachtrige. Autovacuum is in subsysteem wêrfoar d'r heul pear statistiken binne yn PostgreSQL. Wy kinne allinnich sjen yn de tabellen yn pg_stat_activity hoefolle fakuüm wy hawwe op it stuit. It is lykwols heul lestich om te begripen hoefolle tabellen direkt yn 'e wachtrige steane.
Tink derom: _Begjinnend mei Postgres 10 is de situaasje mei Vatovac-tracking gâns ferbettere - de werjefte pg_stat_progress is ferskyndfakuüm, dy't de kwestje fan it kontrolearjen fan it fakuüm fan 'e auto signifikant simplifies.
Wy kinne dizze ferienfâldige query brûke. En wy kinne sjen wannear't it fakuüm makke wurde sil. Mar hoe en wannear moat it fakuüm begjinne? Dit binne de legacy ferzjes fan 'e rigels dêr't ik it earder oer hie. Update barde, in nije ferzje fan de line waard ynfoege. In ferâldere ferzje fan 'e tekenrige is ferskynd. Yn 'e tabel pg_stat_user_tables der is sa'n parameter n_dead_tup. It toant it oantal "deade" rigels. En sa gau as it oantal deade rigen wurdt grutter as in bepaalde drompel, in autovacuum komt op 'e tafel.
En hoe wurdt dizze drompel berekkene? Dit is in hiel spesifyk persintaazje fan it totale oantal rigen yn 'e tabel. Der is in parameter autovacuum_vacuum_scale_factor. It bepaalt it persintaazje. Litte wy sizze 10% + der is in ekstra basisdrompel fan 50 rigels. En wat bart der? As wy mear deade rigen hawwe as "10% + 50" fan alle rigen yn 'e tabel, dan sette wy de tafel op autovacuum.
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));Der is lykwols ien punt. Basis drompels foar parameters av_base_thresh и av_scale_factor kin yndividueel tawiisd wurde. En dêrom sil de drompel net globaal wêze, mar yndividueel foar de tafel. Dêrom, om te berekkenjen, moatte jo trúkjes en trúkjes brûke. En as jo ynteressearre binne, dan kinne jo sjen nei de ûnderfining fan ús kollega's fan Avito (de keppeling op 'e slide is ûnjildich en is bywurke yn' e tekst).
Se skreau foar , dy't mei dizze dingen rekken hâldt. Dêr leit in fuotdoek fan twa blêden. Mar it berekkent korrekt en frij effektyf lit ús beoardielje wêr't wy in protte fakuüm nedich binne foar tabellen wêr't net folle is.
Wat kinne wy der oan dwaan? As wy in grutte wachtrige hawwe en it autovacuum kin it net oan, dan kinne wy it oantal fakuümwurkers ferheegje, of it fakuüm gewoan agressiver meitsje, sadat it earder trigger, ferwurket de tafel yn lytse stikken. En sa sil de wachtrige ôfnimme. - It wichtichste ding hjir is om de lading op 'e skiven te kontrolearjen, om't ... fakuüm is gjin fergees ding, hoewol mei de komst fan SSD / NVMe-apparaten is it probleem minder opfallend wurden.
Pg_stat_all_indexes is statistiken oer yndeksen. Se is net grut. En wy kinne it brûke om ynformaasje te krijen oer it gebrûk fan yndeksen. En bygelyks kinne wy bepale hokker yndeksen wy ekstra hawwe.
Lykas ik al sei, update is net allinnich in update fan tabellen, it is ek in update fan yndeksen. As wy dus in protte yndeksen op 'e tafel hawwe, dan moatte by it bywurkjen fan de rigen yn' e tabel de yndeksen fan 'e yndeksearre fjilden ek bywurke wurde, en as wy net brûkte yndeksen hawwe dêr't gjin yndeksscans foar binne, dan hingje se as ballast. En wy moatte har kwytreitsje. Dêrfoar hawwe wy in fjild nedich idx_scan. Wy sjogge gewoan nei it oantal yndeksscans. As yndeksen nul scans hawwe oer in relatyf lange perioade fan statistyk opslach (op syn minst 2-3 wiken), dan binne dit wierskynlik minne yndeksen, wy moatte har kwytreitsje.
Tink derom: By it sykjen nei net brûkte yndeksen yn it gefal fan streamende replikaasjeklusters, moatte jo alle klusterknooppunten kontrolearje, om't statistiken binne net wrâldwiid, en as de yndeks net brûkt wurdt op 'e master, dan kin it brûkt wurde op replika's (as d'r in lading is).
Twa keppelings:
Dit binne mear avansearre query-foarbylden oer hoe't jo net brûkte yndeksen kinne opsykje.
De twadde link is in nochal nijsgjirrich fersyk. D'r is in heul net-triviale logika. Ik rekommandearje it foar referinsje.
Wat oars is it wurdich op te summajen mei yndeksen?
Net brûkte yndeksen binne min.
Se nimme romte yn.
Fertrage update operaasjes.
Ekstra wurk foar it fakuüm.
As wy net brûkte yndeksen fuortsmite, sille wy de databank allinich better meitsje.
De folgjende presintaasje is pg_stat_activity. Dit is in analoog fan it nut ps, allinich yn PostgreSQL. As ps'om jo sjogge nei de prosessen yn it bestjoeringssysteem, dan pg_stat_activity It sil jo de aktiviteit yn PostgreSQL sjen litte.
Hokker nuttige dingen kinne wy dêrwei nimme?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Wy kinne de algemiene aktiviteit sjen, wat der bart yn 'e databank. Wy kinne in nije ynset meitsje. Alles is hjir eksplodearre, nije ferbiningen wurde net akseptearre, flaters streame yn 'e applikaasje.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Wy kinne in query lykas dizze útfiere en it totale persintaazje ferbiningen sjen relatyf oan de maksimale ferbiningslimyt en sjen wa't de measte ferbiningen hat. En yn dit opjûne gefal sjogge wy dy brûker cron_role iepene 508 ferbinings. En dêr barde him wat. Wy moatte der mei omgean en der nei sjen. En it is goed mooglik dat dit in soarte fan anomale oantal ferbinings is.
As wy in OLTP-wurkdruk hawwe, moatte de queries rap wêze, heul rap en d'r moatte gjin lange queries wêze. As der lykwols lange fragen ûntsteane, dan is der op koarte termyn neat te soargen oer, mar Op 'e lange termyn, lange queries skea oan de databank se fergrutsje de bloat effekt fan tabellen as tabel fragmintaasje optreedt. Jo moatte sawol bloat as lange fragen kwytreitsje.
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;Tink derom: mei dit fersyk kinne wy lange fragen en transaksjes identifisearje. Wy brûke de funksje clock_timestamp() te bepalen operaasje tiid. Lange fragen dy't wy fûnen, wy kinne se ûnthâlde, se ferfolje explain, sjoch nei de plannen en op ien of oare manier optimalisearje. Wy sjitte de hjoeddeistige lange oanfragen del en geane troch mei ús libben.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Minne transaksjes binne transaksjes yn 'e idle yn transaksje en idle yn transaksje (ôfbrutsen) steaten.
Wat betsjut dat? Transaksjes hawwe meardere steaten. En ien fan dizze steaten kin op elk momint wurde oannommen. D'r is in fjild om steaten te definiearjen state yn dizze presintaasje. En wy brûke it om de steat te bepalen.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';En, lykas ik hjirboppe sei, dizze twa steaten idle yn transaksje en idle yn transaksje (ôfbrutsen) binne min. Wat is it? Dit is doe't de applikaasje in transaksje iepene, wat aksjes die en gie oer har bedriuw. De transaksje bliuwt iepen. It hinget, der bart neat yn, it nimt de ferbining op, slûpt op feroare rigen en fergruttet mooglik de bloat fan oare tabellen, troch de arsjitektuer fan 'e Postrges-transaksjemotor. En sokke transaksjes moatte ek delsketten wurde, om't se yn alle gefallen oer it algemien skealik binne.
As jo sjogge dat jo mear as 5-10-20 fan har yn jo databank hawwe, dan moatte jo soargen meitsje en begjinne wat mei har te dwaan.
Hjir brûke wy ek foar de berekkeningstiid clock_timestamp(). Wy sjitte transaksjes en optimalisearje de applikaasje.
Lykas ik hjirboppe sei, is blokkearjen as twa of mear transaksjes fjochtsje foar ien of in groep boarnen. Dêrfoar hawwe wy in fjild waiting mei Booleaanske wearde true of false.
Wier - dit betsjut dat it proses yn ôfwachting is, der moat wat dien wurde. As in proses wachtet, betsjut it dat de kliïnt dy't dit proses inisjearre ek wachtet. De kliïnt sit yn 'e browser en wachtet ek.
Warskôging: _Begjinnend fan Postgres ferzje 9.6 fjild waiting fuortsmiten en twa mear ynformative fjilden tafoege ynstee wait_event_type и wait_event._
Wat moat ik dwaan? As jo sjogge wier foar in lange tiid, it betsjut dat jo moatte ôf te kommen fan sokke fersiken. Wy sjitte gewoan sokke transaksjes del. Wy skriuwe oan de ûntwikkelders dat se op ien of oare manier moatte optimalisearje, sadat d'r gjin race foar boarnen is. En dan optimalisearje de ûntwikkelders de applikaasje sadat dit net bart.
En it ekstreme, mar mooglik net-fatale gefal is foarkommen fan deadlocks. Twa transaksjes bywurke twa boarnen, en gongen dêrnei wer tagong ta, dizze kear nei tsjinoerstelde boarnen. Yn dit gefal deadet PostgreSQL de transaksje sels, sadat in oare kin trochgean mei wurkjen. Dit is in deade situaasje en se kin it sels net útfine. Dêrom wurdt PostgreSQL twongen om ekstreme maatregels te nimmen.
En hjir binne twa fragen wêrmei jo blokkearje kinne folgje. Wy brûke werjefte pg_locks, wêrtroch jo swiere slûzen kinne folgje.
En de earste keppeling is de fersyktekst sels. It is nochal lang.
En de twadde link is in artikel oer slûzen. It is nuttich om te lêzen, it is heul ynteressant.
Dus wat sjogge wy? Wy sjogge twa oanfragen. Transaksje mei ALTER TABLE is in blokkearjende transaksje. It begon, mar foltôge net, en de applikaasje dy't dizze transaksje opnaam, docht earne oare dingen. En it twadde fersyk is update. Hy wachtet oant de altertafel einiget foardat hy syn wurk trochgean kin.
Dit is hoe't wy kinne útfine wa't opsletten wa't, wa't fêsthâldt, en kinne wy der fierder mei omgean.
De folgjende module is pg_stat_statements. Lykas ik sei, dit is in module. Om it te brûken, moatte jo de bibleteek yn 'e konfiguraasje lade, PostgreSQL opnij starte, de module ynstallearje (mei ien kommando) en dan sille wy in nije werjefte hawwe.
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;Wat kinne wy dêr wei nimme? As wy prate oer ienfâldige dingen, kinne wy nimme de gemiddelde query útfiering tiid. De tiid groeit, wat betsjut dat PostgreSQL stadich reagearret en wy moatte wat dwaan.
Wy kinne sjen nei de meast aktive skriuwtransaksjes yn 'e databank dy't gegevens feroarje yn dielde buffers. Sjoch wa't dêr gegevens bywurket of wisket.
En wy kinne gewoan nei ferskate statistiken sjen foar dizze oanfragen.
Мы pg_stat_statements Wy brûke it om rapporten te bouwen. Wy sette de statistiken ien kear deis werom. Litte wy it sammelje. Foardat jo de statistiken de folgjende kear weromsette, litte wy in rapport bouwe. Hjir is in keppeling nei it rapport. Jo kinne it besjen.
Wat dogge wy? Wy berekkenje algemiene statistiken foar alle oanfragen. Dan, foar elk fersyk, telle wy syn yndividuele bydrage oan dizze algemiene statistiken.
En wat kinne wy sjen? Wy kinne de totale útfieringstiid fan alle oanfragen fan in bepaald type sjen tsjin 'e eftergrûn fan alle oare oanfragen. Wy kinne sjen nei de CPU en I / O-boarnegebrûk relatyf oan it algemiene byld. En al optimalisearje dizze queries. Wy bouwe de topfragen op basis fan dit rapport en krije al iten foar tinken oer wat te optimalisearjen.
Wat hawwe wy efter de skermen? Der binne noch in pear ynstjoerings oer dy't ik net betocht, om't de tiid beheind is.
der binne pgstattuple is ek in ekstra module út it standert bydragepakket. It lit jo evaluearje bloat tabellen, saneamde tabel fragmintaasje. En as d'r in protte fragmintaasje is, moatte jo it fuortsmite en ferskate ark brûke. En funksje pgstattuple wurket foar in lange tiid. En hoe mear tabellen der binne, hoe langer it sil wurkje.
De folgjende bydrage is pg_buffercache. It lit jo dielde buffers ynspektearje: hoe yntinsyf en foar hokker tabellen buffersiden wurde brûkt. En it lit jo gewoan yn dielde buffers sjen en evaluearje wat der bart.
De folgjende module is pgfincore. It lit tabel operaasjes op leech nivo fia in systeem oprop mincore(), d.w.s. it lit jo in tabel laden yn dielde buffers, of it laden. En it makket it mooglik om ûnder oare de side-cache fan it bestjoeringssysteem te ynspektearjen, dus hoefolle romte nimt de tabel yn 'e side-cache, yn dielde buffers, en lit ús gewoan de wurkdruk fan 'e tabel evaluearje.
Folgjende module - pg_stat_kcache. It brûkt ek in systeemoprop getrusage(). En it útfiert it foar en neidat it fersyk wurdt útfierd. En yn 'e resultearjende statistiken kinne wy skatte hoefolle ús fersyk is bestege oan skiif I / O, d.w.s. operaasjes mei it bestânsysteem en sjocht nei it prosessorgebrûk. De module is lykwols jong (hoest hoest) en foar syn operaasje fereasket it PostgreSQL 9.4 en pg_stat_statements, dy't ik earder neamde.
It is nuttich om te witten hoe't jo statistiken brûke kinne. Jo hawwe gjin programma's fan tredden nedich. Jo kinne ynkomme, sjen, wat dwaan, wat útfiere.
It brûken fan statistiken is net dreech, it is gewoan gewoane SQL. Jo hawwe it fersyk sammele, it gearstald, it ferstjoerd, it besjoen.
Statistiken helpe fragen te beantwurdzjen. As jo fragen hawwe, wikselje jo nei statistiken - sjoch, lûk konklúzjes, analysearje de resultaten.
En eksperimintearje. Der binne in protte oanfragen, in protte gegevens. Jo kinne altyd in besteande query optimalisearje. Jo kinne jo eigen ferzje meitsje fan it fersyk dat jo mear past as it orizjineel en it brûke.
referinsjes
Geskikte keppelings dy't waarden fûn yn it artikel, basearre op materialen, wiene yn it rapport.
Auteur skriuw mear
(eng)
De Statistyk Samler
System Administration Funksjes
Bydrage modules
SQL utils en sql code foarbylden
Tankewol allegear foar jo oandacht!
Boarne: www.habr.com
