Transkripsie van die 2015-verslag deur Alexey Lesovsky "Diep duik in PostgreSQL interne statistieke"
Vrywaring van die skrywer van die verslag: Ek neem kennis dat hierdie verslag gedateer is November 2015 - meer as 4 jaar het verloop en baie tyd het verloop. Weergawe 9.4 wat in die verslag bespreek word, word nie meer ondersteun nie. Oor die afgelope 4 jaar is 5 nuwe vrystellings vrygestel waarin baie innovasies, verbeterings en veranderinge aangaande statistiek verskyn het, en van die materiaal is verouderd en nie relevant nie. Soos ek geresenseer het, het ek probeer om hierdie plekke te merk om nie u die leser te mislei nie. Ek het nie hierdie plekke herskryf nie, daar is baie van hulle, en gevolglik sal 'n heeltemal ander verslag uitdraai.
Die PostgreSQL DBBS is 'n groot meganisme, en hierdie meganisme bestaan uit baie substelsels, waarvan die gekoördineerde werk die werkverrigting van die DBBS direk beïnvloed. Tydens die werking word statistieke en inligting oor die werking van komponente ingesamel, wat jou toelaat om die doeltreffendheid van PostgreSQL te evalueer en maatreëls te tref om prestasie te verbeter. Daar is egter baie van hierdie inligting en dit word in 'n taamlik vereenvoudigde vorm aangebied. Die verwerking van hierdie inligting en die interpretasie daarvan is soms 'n heeltemal nie-triviale taak, en die "dieretuin" van gereedskap en nutsprogramme kan selfs 'n gevorderde DBA maklik verwar.
Goeie middag My naam is Aleksey. Soos Ilya gesê het, ek sal praat oor PostgreSQL-statistieke.
PostgreSQL-aktiwiteitstatistieke. PostgreSQL het twee statistieke. Aktiwiteitstatistiek, wat bespreek sal word. En skeduleerderstatistieke oor dataverspreiding. Ek sal spesifiek praat oor PostgreSQL-aktiwiteitstatistieke, wat ons in staat stel om prestasie te beoordeel en op een of ander manier te verbeter.
Ek sal jou vertel hoe om statistiek effektief te gebruik om 'n verskeidenheid probleme op te los wat jy het of mag hê.
Wat sal nie in die verslag wees nie? In die berig sal ek nie die statistieke van die skeduleerder aanraak nie, want. hierdie is 'n aparte onderwerp vir 'n aparte verslag oor hoe data in die databasis gestoor word en hoe die navraagbeplanner 'n idee kry oor die kwalitatiewe en kwantitatiewe kenmerke van hierdie data.
En daar sal geen gereedskapresensies wees nie, ek sal nie een produk met 'n ander vergelyk nie. Daar sal geen advertensies wees nie. Kom ons laat vaar dit.
Ek wil jou wys dat die gebruik van statistiek nuttig is. Dit is nodig. Gebruik dit vreesloos. Al wat ons nodig het is gewone SQL en 'n basiese kennis van SQL.
En ons sal praat oor watter statistieke om te kies om probleme op te los.
As ons na PostgreSQL kyk en 'n opdrag op die bedryfstelsel uitvoer om die prosesse te sien, sal ons 'n "swart boks" sien. Ons sal 'n paar prosesse sien wat iets doen, en by die naam kan ons ons rofweg voorstel wat hulle daar doen, wat hulle doen. Maar in werklikheid is dit 'n swart boks, ons kan nie binne kyk nie.
Ons kan kyk na die SVE-lading in top, kan ons die geheuebenutting deur sommige stelselhulpmiddels sien, maar ons sal nie binne PostgreSQL kan kyk nie. Hiervoor het ons ander gereedskap nodig.
En as ek verder gaan, sal ek jou vertel waar die tyd spandeer word. As ons PostgreSQL verteenwoordig in die vorm van so 'n skema, dan sal dit moontlik wees om te antwoord waar die tyd spandeer word. Dit is twee dinge: dit is die verwerking van kliëntversoeke van toepassings en die agtergrondtake wat PostgreSQL verrig om dit aan die gang te hou.
As ons na die boonste linkerhoek begin kyk, kan ons sien hoe kliëntversoeke verwerk word. Die versoek kom van die toepassing en 'n kliëntsessie word oopgemaak vir verdere werk. Die versoek word aan die skeduleerder deurgegee. Die beplanner bou 'n navraagplan. Stuur dit verder vir uitvoering. Daar is 'n soort van blok I / O data wat verband hou met tabelle en indekse. Die nodige data word vanaf skywe in die geheue gelees in 'n spesiale area genaamd "gedeelde buffers". Die navraagresultate, as dit opdaterings, verwyderings is, word in die transaksielogboek in WAL aangeteken. Sommige statistiese inligting gaan in die log of statistiek versamelaar. En die resultaat van die versoek word aan die kliënt teruggegee. Daarna kan die kliënt alles herhaal met 'n nuwe versoek.
Wat het ons met agtergrondtake en agtergrondprosesse? Ons het verskeie prosesse wat die databasis in 'n normale bedryfsmodus aan die gang hou. Hierdie prosesse sal ook in die verslag gedek word: dit is outovakuum, kontrolewyser, prosesse wat verband hou met replikasie, agtergrondskrywer. Ek sal elkeen van hulle aanraak terwyl ek verslag doen.
Wat is die probleme met statistiek?
- Baie inligting. PostgreSQL 9.4 bied 109 maatstawwe om statistiekdata te bekyk. As die databasis egter baie tabelle, skemas, databasisse stoor, sal al hierdie metrieke vermenigvuldig moet word met die ooreenstemmende aantal tabelle, databasisse. Dit wil sê, daar is selfs meer inligting. En dit is baie maklik om daarin te verdrink.
- Die volgende probleem is dat statistieke deur tellers voorgestel word. As ons na hierdie statistieke kyk, sal ons voortdurend toenemende tellers sien. En as daar baie tyd verloop het sedert die statistieke teruggestel is, sal ons miljarde waardes sien. En hulle vertel ons niks nie.
- Daar is geen geskiedenis nie. As jy 'n soort mislukking het, iets het 15-30 minute gelede geval, jy sal nie die statistieke kan gebruik en sien wat 15-30 minute gelede gebeur het nie. Dit is 'n probleem.
- Die gebrek aan 'n instrument wat in PostgreSQL ingebou is, is 'n probleem. Die kernontwikkelaars verskaf geen nut nie. Hulle het nie so iets nie. Hulle gee net statistieke in die databasis. Gebruik dit, maak 'n versoek daarvoor, wat jy ook al wil hê, doen dit dan.
- Aangesien daar geen instrument in PostgreSQL ingebou is nie, veroorsaak dit nog 'n probleem. Baie derdeparty-instrumente. Elke maatskappy wat min of meer direkte hande het, probeer om sy eie program te skryf. En gevolglik het die gemeenskap baie instrumente wat jy kan gebruik om met statistiek te werk. En in sommige instrumente is daar 'n paar kenmerke, in ander nutsgoed is daar geen ander kenmerke nie, of daar is 'n paar nuwe kenmerke. En 'n situasie ontstaan dat jy twee, drie of vier gereedskap moet gebruik wat mekaar oorvleuel en verskillende funksies het. Dit is baie irriterend.
Wat volg hieruit? Dit is belangrik om statistieke direk te kan neem om nie van programme afhanklik te wees nie, of op een of ander manier self hierdie programme te verbeter: voeg 'n paar funksies by om jou voordeel te trek.
En jy benodig basiese kennis van SQL. Om data uit statistiek te kry, moet jy SQL-navrae maak, d.w.s. jy moet weet hoe kies, aansluit gemaak word.
Statistieke vertel ons verskeie dinge. Hulle kan in kategorieë verdeel word.
- Die eerste kategorie is die gebeure wat in die databasis plaasvind. Dit is wanneer een of ander gebeurtenis in die databasis plaasvind: 'n navraag, 'n tafeltoegang, outovakuum, commits, dan is dit alles gebeurtenisse. Die tellers wat met hierdie gebeurtenisse ooreenstem, word verhoog. En ons kan hierdie gebeure dop.
- Die tweede kategorie is die eienskappe van voorwerpe soos tabelle, databasisse. Hulle het eienskappe. Dit is die grootte van die tafels. Ons kan die groei van tabelle, die groei van indekse dop. Ons kan veranderinge in dinamika sien.
- En die derde kategorie is die tyd wat aan die geleentheid spandeer word. Versoek is 'n gebeurtenis. Dit het sy eie spesifieke maatstaf van duur. Hier begin, hier geëindig. Ons kan dit dop. Óf die tyd van lees van 'n blok vanaf skyf of skryf. Hierdie dinge word ook opgespoor.
Bronne van statistiek word soos volg aangebied:
- In gedeelde geheue (gedeelde buffers) is daar 'n segment vir die plasing van statiese data daar, daar is ook daardie tellers wat voortdurend verhoog word wanneer sekere gebeurtenisse plaasvind, of sommige oomblikke in die werking van die databasis ontstaan.
- Al hierdie tellers is nie vir die gebruiker beskikbaar nie en is nie eers vir die administrateur beskikbaar nie. Dit is lae vlak dinge. Om toegang tot hulle te verkry, bied PostgreSQL 'n koppelvlak in die vorm van SQL-funksies. Ons kan uitgesoekte keuses maak deur hierdie funksies te gebruik en 'n soort metrieke (of stel metrieke) kry.
- Dit is egter nie altyd gerieflik om hierdie funksies te gebruik nie, so funksies is die basis vir aansigte (VIEWS). Dit is virtuele tabelle wat statistieke verskaf oor 'n spesifieke substelsel, of oor 'n stel gebeurtenisse in die databasis.
- Hierdie ingeboude aansigte (VIEWs) is die hoofgebruikerskoppelvlak om met statistiek te werk. Hulle is by verstek beskikbaar sonder enige bykomende instellings, jy kan dit dadelik gebruik, kyk, inligting van daar af neem. En daar is ook bydraes. Bydraes is amptelik. U kan die postgresql-contrib-pakket installeer (byvoorbeeld, postgresql94-contrib), die nodige module in die konfigurasie laai, parameters daarvoor spesifiseer, PostgreSQL herbegin en u kan dit gebruik. (Let wel. Afhangende van die verspreiding, is die pakket in onlangse weergawes van bydrae deel van die hoofpakket).
- En daar is nie-amptelike bydraes. Hulle word nie saam met die standaard PostgreSQL-verspreiding voorsien nie. Hulle moet óf saamgestel óf as 'n biblioteek geïnstalleer word. Opsies kan baie verskil, afhangende van waarmee die ontwikkelaar van hierdie nie-amptelike bydrae vorendag gekom het.
Hierdie skyfie wys al daardie aansigte (VIEWs) en sommige van daardie funksies wat beskikbaar is in PostgreSQL 9.4. Soos ons kan sien, is daar baie van hulle. En dit is redelik maklik om deurmekaar te raak as jy dit vir die eerste keer ervaar.
As ons egter die vorige foto neem Как тратится время на PostgreSQL en versoenbaar is met hierdie lys, kry ons hierdie prentjie. Elke aansig (VIEWs), of elke funksie, kan ons vir een of ander doel gebruik om die toepaslike statistieke te verkry wanneer ons PostgreSQL loop. En ons kan reeds inligting kry oor die werking van die substelsel.
Die eerste ding waarna ons sal kyk, is pg_stat_database. Soos ons kan sien, is dit 'n voorstelling. Dit bevat baie inligting. Die mees uiteenlopende inligting. En dit gee baie nuttige kennis van wat ons in die databasis aangaan.
Wat kan ons daarvandaan neem? Kom ons begin met die eenvoudigste dinge.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Die eerste ding waarna ons kan kyk, is die kas-trefferpersentasie. Kas-trefferpersentasie is 'n nuttige maatstaf. Dit laat jou toe om te skat hoeveel data uit die gedeelde bufferkas geneem word, en hoeveel van die skyf gelees word.
Dit is duidelik dat hoe meer kastreffer ons het, hoe beter. Ons evalueer hierdie maatstaf as 'n persentasie. En, byvoorbeeld, as ons 'n persentasie van hierdie kastreffers groter as 90% het, dan is dit goed. As dit tot onder 90% daal, het ons nie genoeg geheue om die warm kop van data in die geheue te hou nie. En om hierdie data te gebruik, word PostgreSQL gedwing om toegang tot die skyf te verkry en dit is stadiger as wanneer die data uit die geheue gelees is. En jy moet daaraan dink om geheue te verhoog: óf verhoog gedeelde buffers, óf verhoog ystergeheue (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 kan nog uit hierdie aanbieding geneem word? U kan die onreëlmatighede sien wat in die databasis voorkom. Wat word hier gewys? Daar is commits, rollbacks, skepping van tydelike lêers, hul grootte, dooiepunte en konflikte.
Ons kan hierdie versoek gebruik. Hierdie SQL is redelik eenvoudig. En ons kan hierdie data self sien.
En hier is die drempelwaardes. Ons kyk na die verhouding van commits en rollbacks. Commits is 'n suksesvolle bevestiging van die transaksie. Terugskrywings is 'n terugrol, dit wil sê die transaksie het 'n bietjie werk gedoen, die databasis gespanne, iets oorweeg, en toe het 'n mislukking plaasgevind, en die resultate van die transaksie word weggegooi. d.w.s. die aantal terugskrywings wat voortdurend toeneem, is sleg. En jy moet hulle op een of ander manier vermy en die kode wysig sodat dit nie gebeur nie.
Konflikte hou verband met replikasie. En hulle moet ook vermy word. As jy 'n paar navrae het wat op die replika uitgevoer word en konflikte ontstaan, dan moet jy hierdie konflikte ontleed en kyk wat gebeur. Besonderhede kan in die logs gevind word. En los konflikte op sodat toepassingsversoeke sonder foute werk.
Dooiepunte is ook 'n slegte situasie. Wanneer versoeke om hulpbronne meeding, het een versoek toegang tot een hulpbron verkry en die slot geneem, die tweede versoek het toegang tot die tweede hulpbron verkry en ook die slot geneem, en dan het albei versoeke toegang tot mekaar se hulpbronne verkry en geblokkeer en gewag vir die buurman om die slot vry te stel. Dit is ook 'n problematiese situasie. Hulle moet aangespreek word op die vlak van herskryf van toepassings en serialisering van toegang tot hulpbronne. En as jy sien dat jou dooiepunte voortdurend toeneem, moet jy na die besonderhede in die logboeke kyk, die situasies wat ontstaan het ontleed en kyk wat die probleem is.
Tydelike lêers (temp_files) is ook sleg. Wanneer 'n gebruikerversoek nie genoeg geheue het om die operasionele, tydelike data te akkommodeer nie, skep dit 'n lêer op skyf. En al die bewerkings wat hy in 'n tydelike buffer in die geheue kon uitvoer, begin hy reeds op die skyf uitvoer. Dit is stadig. Dit verhoog die navraaguitvoeringstyd. En die kliënt wat 'n versoek na PostgreSQL gestuur het, sal 'n bietjie later 'n antwoord ontvang. As al hierdie bewerkings in die geheue uitgevoer word, sal Postgres baie vinniger reageer en die kliënt sal minder wag.
pg_stat_bgwriter - Hierdie aansig beskryf die werking van twee PostgreSQL agtergrond substelsels: checkpointer и background writer.
Om mee te begin, kom ons ontleed die beheerpunte, die sg. checkpoints. Wat is kontrolepunte? 'n Kontrolepunt is 'n posisie in die transaksielog wat aandui dat alle dataveranderings wat in die logboek aangebring is, suksesvol gesinchroniseer is met die data op skyf. Die proses, afhangende van die werklading en instellings, kan lank wees en bestaan meestal uit die sinchronisering van vuil bladsye in gedeelde buffers met datalêers op skyf. Waarvoor is dit? As PostgreSQL die hele tyd toegang tot die skyf gehad het en data van daar af geneem het, en data op elke toegang geskryf het, sou dit stadig wees. Daarom het PostgreSQL 'n geheuesegment waarvan die grootte afhang van die parameters in die konfigurasie. Postgres ken operasionele data in hierdie geheue toe vir verdere verwerking of navrae. In die geval van dataveranderingsversoeke word dit verander. En ons kry twee weergawes van die data. Een is in die geheue, die ander is op skyf. En periodiek moet jy hierdie data sinchroniseer. Ons het nodig wat in die geheue verander is om met skyf gesinchroniseer te word. Dit vereis 'n kontrolepunt.
Kontrolepunt gaan deur gedeelde buffers, merk vuil bladsye dat dit nodig is vir kontrolepunt. Dan begin dit die tweede deurgang deur gedeelde buffers. En die bladsye wat gemerk is vir kontrolepunt, hy sinchroniseer hulle reeds. Die data is dus reeds met die skyf gesinchroniseer.
Daar is twee tipes beheerpunte. Een kontrolepunt word uitgevoer met time-out. Hierdie kontrolepunt is nuttig en goed - checkpoint_timed. En daar is kontrolepunte op aanvraag - checkpoint required. So 'n kontrolepunt vind plaas wanneer ons 'n baie groot datarekord het. Ons het baie transaksielogboeke aangeteken. En PostgreSQL glo dat dit dit alles so vinnig as moontlik moet sinchroniseer, 'n kontrolepunt moet maak en aanbeweeg.
En as jy na die statistieke gekyk het pg_stat_bgwriter en kyk wat jy het checkpoint_req is baie groter as checkpoint_timed, dan is dit sleg. Hoekom sleg? Dit beteken dat PostgreSQL voortdurend onder spanning verkeer wanneer dit data na skyf moet skryf. Kontrolepunt vir uitteltyd is minder stresvol en word volgens die interne skedule uitgevoer en as 't ware oor tyd uitgerek. PostgreSQL het die vermoë om werk te onderbreek en nie die skyfsubstelsel te bemoei nie. Dit is nuttig vir PostgreSQL. En versoeke wat tydens kontrolepunt uitgevoer word, sal nie stres ervaar as gevolg van die feit dat die skyfsubstelsel besig is nie.
En daar is drie parameters om die kontrolepunt aan te pas:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Hulle laat jou toe om die werking van beheerpunte te beheer. Maar ek sal nie by hulle stilstaan nie. Hul invloed is 'n aparte kwessie.
waarskuwing: Weergawe 9.4 wat in die verslag oorweeg word, is nie meer relevant nie. In moderne weergawes van PostgreSQL, die parameter checkpoint_segments vervang deur parameters min_wal_size и max_wal_size.
Die volgende subsisteem is die agtergrondskrywer − background writer. Wat doen hy? Dit loop voortdurend in 'n eindelose lus. Dit skandeer bladsye in gedeelde buffers en spoel die vuil bladsye wat dit vind na skyf. Op hierdie manier help dit die kontrolewyser om minder werk te doen tydens kontrole.
Waarvoor is hy nog nodig? Dit maak voorsiening vir die behoefte aan skoon bladsye in gedeelde buffers as dit skielik (in groot hoeveelhede en onmiddellik) benodig word om data te akkommodeer. Gestel 'n situasie het ontstaan toe die versoek skoon bladsye vereis het en hulle is reeds in gedeelde buffers. Postgres backend hy vat hulle net en gebruik hulle, hy hoef niks self skoon te maak nie. Maar as daar skielik nie sulke bladsye is nie, breek die agterkant en begin soek na bladsye om hulle na skyf te spoel en dit vir sy eie behoeftes te neem - wat die tyd van die tans uitvoerende versoek negatief beïnvloed. As jy sien dat jy 'n parameter het maxwritten_clean groot, beteken dit dat die agtergrondskrywer nie sy werk doen nie en jy moet die parameters verhoog bgwriter_lru_maxpagessodat hy meer werk in een siklus kan doen, maak meer bladsye skoon.
En nog 'n baie nuttige aanwyser is buffers_backend_fsync. Backends doen nie fsync nie, want dit is stadig. Hulle gee fsync deur die IO-stapelkontrolewyser. Die kontrolewyser het sy eie tou, dit verwerk periodiek fsync en sinchroniseer bladsye in die geheue met lêers op skyf. As die kontrolewyser-tou groot en vol is, word die agterkant gedwing om self fsync te doen en dit vertraag die agterkant, dit wil sê die kliënt sal later 'n antwoord ontvang as wat dit kon. As jy sien dat jy hierdie waarde groter as nul het, dan is dit reeds 'n probleem en jy moet aandag gee aan die instellings van die agtergrondskrywer en ook die werkverrigting van die skyfsubstelsel evalueer.
waarskuwing: _Die volgende teks beskryf die statistiese sienings wat met replikasie geassosieer word. Die meeste van die aansig- en funksiename is hernoem in Postgres 10. Die essensie van die hernames was om te vervang xlog op wal и location op lsn in funksie/aansig name, ens. Besondere voorbeeld, funksie pg_xlog_location_diff() is herdoop na pg_wal_lsn_diff()._
Ons het ook baie hier. Maar ons benodig net items wat verband hou met ligging.
As ons sien dat alle waardes gelyk is, dan is dit ideaal en die replika bly nie agter die meester nie.
Hierdie heksadesimale posisie hier is die posisie in die transaksielogboek. Dit neem voortdurend toe as daar 'n mate van aktiwiteit in die databasis is: invoegings, skraps, ens.
сколько записано 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 hierdie dinge anders is, dan is daar 'n soort vertraging. Lag is die vertraging van die replika vanaf die meester, dit wil sê die data verskil tussen bedieners.
Daar is drie redes vir die vertraging:
- Dit is die skyf-substelsel wat nie lêersinkronisering kan hanteer nie.
- Dit is moontlike netwerkfoute, of netwerkoorlading, wanneer die data nie tyd het om die replika te bereik nie en dit nie kan reproduseer nie.
- En die verwerker. Die verwerker is 'n baie seldsame geval. En ek het dit al twee of drie keer gesien, maar dit kan ook gebeur.
En hier is drie navrae wat ons in staat stel om statistieke te gebruik. Ons kan skat hoeveel in ons transaksielog aangeteken is. Daar is so 'n funksie pg_xlog_location_diff en ons kan die replikasievertraging in grepe en sekondes skat. Ons gebruik ook die waarde van hierdie aansig (VIEWs) hiervoor.
Let wel: _In plaas van pg_xlog_locationdiff() funksie, kan jy die aftrekoperateur gebruik en een ligging van 'n ander aftrek. Gerieflik.
Met 'n vertraging, wat in sekondes is, is daar een oomblik. As daar geen aktiwiteit op die meester is nie, was die transaksie sowat 15 minute gelede daar en daar is geen aktiwiteit nie, en as ons na hierdie vertraging op die replika kyk, sal ons 'n vertraging van 15 minute sien. Dit is die moeite werd om te onthou. En dit kan lei tot 'n stupor wanneer jy hierdie vertraging gekyk het.
pg_stat_all_tables is nog 'n nuttige aansig. Dit wys statistieke op tabelle. Wanneer ons tabelle in die databasis het, is daar 'n aktiwiteit daarmee, 'n paar aksies, ons kan hierdie inligting uit hierdie aansig kry.
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;Die eerste ding waarna ons kan kyk, is opeenvolgende tabelskanderings. Die nommer self na hierdie gedeeltes is nie noodwendig sleg nie en dui nie aan dat ons reeds iets moet doen nie.
Daar is egter 'n tweede maatstaf - seq_tup_read. Dit is die aantal rye wat van die opeenvolgende skandering teruggestuur is. As die gemiddelde getal 1 000, 10 000, 50 000, 100 000 oorskry, dan is dit reeds 'n aanduiding dat jy dalk iewers 'n indeks moet bou sodat die toegange volgens indeks is, of dit is moontlik om navrae wat sulke opeenvolgende skanderings gebruik, te optimaliseer sodat dit gebeur nie. was.
'n Eenvoudige voorbeeld - kom ons sê 'n versoek met 'n groot OFFSET en LIMIT is die moeite werd. Byvoorbeeld, 100 000 rye in 'n tabel word geskandeer en daarna word 50 000 vereiste rye geneem, en die vorige geskandeerde rye word weggegooi. Dit is ook 'n slegte saak. En sulke versoeke moet geoptimaliseer word. En hier is so 'n eenvoudige SQL-navraag waarop jy dit kan sien en die ontvangde getalle kan evalueer.
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;Tafelgroottes kan ook verkry word deur hierdie tabel te gebruik en bykomende funksies te gebruik pg_total_relation_size(), pg_relation_size().
Oor die algemeen is daar meta-opdragte dt и di, wat jy in PSQL kan gebruik en ook tabel- en indeksgroottes sien.
Die gebruik van funksies help ons egter om na die groottes van tabelle te kyk, selfs met inagneming van indekse, of sonder om indekse in ag te neem, en reeds 'n paar skattings te maak gebaseer op die groei van die databasis, dit wil sê hoe dit saam met ons groei, met watter intensiteit, en maak reeds 'n paar gevolgtrekkings oor die optimalisering van grootte.
Skryf aktiwiteit. Wat is 'n rekord? Kom ons kyk na die operasie UPDATE – die werking van die opdatering van rye in die tabel. Trouens, opdatering is twee bewerkings (of selfs meer). Dit is die invoeging van 'n nuwe ry weergawe en merk die ou ry weergawe as verouderd. Later sal outovacuum kom en hierdie verouderde weergawes van die lyne skoonmaak, merk hierdie plek as beskikbaar vir hergebruik.
Bywerking gaan ook nie net oor die opdatering van 'n tabel nie. Dit is steeds 'n indeksopdatering. As jy baie indekse op die tafel het, dan sal alle indekse waarin die velde wat in die navraag opgedateer is deelneem met opdatering ook opgedateer moet word. Hierdie indekse sal ook verouderde ry weergawes hê wat skoongemaak sal moet word.
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 as gevolg van sy ontwerp, is UPDATE 'n swaargewig-operasie. Maar hulle kan makliker gemaak word. Eet hot updates. Hulle het verskyn in PostgreSQL weergawe 8.3. En wat is dit? Dit is 'n liggewig-opdatering wat nie veroorsaak dat indekse herbou word nie. Dit wil sê, ons het die rekord opgedateer, maar net die rekord in die bladsy (wat aan die tabel behoort) is opgedateer, en die indekse wys steeds na dieselfde rekord in die bladsy. Daar is 'n bietjie van so 'n interessante logika van werk, wanneer 'n vakuum kom, dan het dit hierdie kettings hot herbou en alles gaan voort om te werk sonder om die indekse op te dateer, en alles gebeur met minder vermorsing van hulpbronne.
En wanneer jy het n_tup_hot_upd groot, dit is baie goed. Dit beteken dat ligte opdaterings seëvier en dit is goedkoper vir ons in terme van hulpbronne en alles is in orde.
ALTER TABLE table_name SET (fillfactor = 70);Hoe om volume te verhoog hot updateov? Ons kan gebruik fillfactor. Dit bepaal die grootte van die gereserveerde vrye spasie wanneer 'n bladsy in 'n tabel gevul word deur INSERT's te gebruik. Wanneer insetsels na die tabel gaan, vul hulle die bladsy heeltemal, moenie leë spasie daarin laat nie. Dan word 'n nuwe bladsy uitgelig. Die data word weer ingevul. En dit is die standaardgedrag, fillfactor = 100%.
Ons kan die vulfaktor op 70% stel. Dit wil sê, met insetsels is 'n nuwe bladsy toegeken, maar slegs 70% van die bladsy is gevul. En ons het 30% oor in reserwe. Wanneer jy 'n opdatering moet doen, sal dit heel waarskynlik op dieselfde bladsy gebeur, en die nuwe weergawe van die ry sal op dieselfde bladsy pas. En hot_update sal gedoen word. Dit maak dit makliker om op tabelle te skryf.
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));Outovakuum tou. Autovacuum is so 'n substelsel waarvoor daar baie min statistieke in PostgreSQL is. Ons kan net in die tabelle in pg_stat_activity sien hoeveel vakuums ons op die oomblik het. Dit is egter baie moeilik om te verstaan hoeveel tafels in die tou dit onderweg het.
Let wel: _Sedert Postgres 10 het die situasie met die dop van die vakuumvakuum baie verbeter - die pg_stat_progress-aansig het verskynvakuum, wat die kwessie van outovakuummonitering aansienlik vergemaklik.
Ons kan hierdie vereenvoudigde navraag gebruik. En ons kan sien wanneer die vakuum gemaak moet word. Maar hoe en wanneer moet die vakuum begin? Dit is die ou weergawes van die snare waaroor ek vroeër gepraat het. Opdatering het plaasgevind, die nuwe weergawe van die ry is ingevoeg. 'n Verouderde weergawe van die string het verskyn. Tafel pg_stat_user_tables daar is so 'n parameter n_dead_tup. Dit wys die aantal "dooie" rye. En sodra die aantal dooie rye meer as 'n sekere drempel geword het, sal 'n outovakuum na die tafel kom.
En hoe word hierdie drempel bereken? Dit is 'n baie spesifieke persentasie van die totale aantal rye in die tabel. Daar is 'n parameter autovacuum_vacuum_scale_factor. Dit definieer die persentasie. Kom ons sê 10% + daar is 'n bykomende basisdrempel van 50 reëls. En wat gebeur? Wanneer ons meer dooie rye as "10% + 50" van alle rye in die tabel het, plaas ons die tabel op outovakuum.
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));Daar is egter een punt. Basiese drempels vir parameters av_base_thresh и av_scale_factor individueel toegewys kan word. En dienooreenkomstig sal die drempel nie wêreldwyd wees nie, maar individueel vir die tafel. Daarom, om te bereken, daar moet jy truuks en truuks gebruik. En as jy belangstel, kan jy kyk na die ervaring van ons kollegas van Avito (die skakel op die skyfie is ongeldig en is in die teks opgedateer).
Hulle het geskryf vir wat hierdie dinge in ag neem. Daar is 'n voetlap op twee lakens. Maar hy oorweeg korrek en redelik effektief stel ons in staat om te bepaal waar ons baie vakuum nodig het vir tafels, waar daar min is.
Wat kan ons daaromtrent doen? As ons 'n lang tou het en die outovakuum kan dit nie hanteer nie, dan kan ons die aantal vakuumwerkers vermeerder, of bloot die vakuum meer aggressief maaksodat dit vroeër aktiveer, verwerk die tabel in klein stukkies. En dus sal die tou afneem. - Die belangrikste ding hier is om die lading op die skywe te monitor, want. Die vakuum-ding is nie gratis nie, hoewel die probleem met die koms van SSD / NVMe-toestelle minder opvallend geword het.
pg_stat_all_indexes is statistieke oor indekse. Sy is nie groot nie. En ons kan inligting oor die gebruik van indekse daaruit kry. En ons kan byvoorbeeld bepaal watter indekse ons ekstra het.
Soos ek reeds gesê het, opdatering is nie net die opdatering van tabelle nie, dit is ook die opdatering van indekse. Gevolglik, as ons baie indekse op die tabel het, moet die indekse van die geïndekseerde velde ook bygewerk word wanneer die rye in die tabel opgedateer word, en as ons ongebruikte indekse het waarvoor daar geen indeksskanderings is nie, dan hang dit saam met ons as ballas. En jy moet van hulle ontslae raak. Hiervoor het ons 'n veld nodig idx_scan. Ons kyk net na die aantal indeksskanderings. As die indekse geen skanderings het oor 'n relatief lang tydperk van statistiekberging (ten minste 2-3 weke), dan is dit heel waarskynlik slegte indekse, ons moet daarvan ontslae raak.
Let wel: As u na ongebruikte indekse soek in die geval van stroomreplikasie-klusters, moet u alle nodusse van die groep nagaan, want statistieke is nie globaal nie, en as die indeks nie op die meester gebruik word nie, kan dit op replikas gebruik word (as daar 'n vrag is).
Twee skakels:
Hierdie is meer gevorderde navraagvoorbeelde vir hoe om ongebruikte indekse op te soek.
Die tweede skakel is 'n taamlik interessante navraag. Daar is 'n baie nie-triviale logika daarin. Ek beveel dit aan vir hersiening.
Wat anders moet deur indekse opgesom word?
-
Ongebruikte indekse is sleg.
-
Hulle neem ruimte op.
-
Vertraag opdateringsbewerkings.
-
Ekstra werk vir die vakuum.
As ons ongebruikte indekse verwyder, sal ons die databasis net beter maak.
Die volgende aansig is pg_stat_activity. Dit is 'n analoog van die nut ps, slegs in PostgreSQL. As ps'ohm jy kyk dan na die prosesse in die bedryfstelsel pg_stat_activity sal jou die aktiwiteit binne PostgreSQL wys.
Wat kan ons daarvandaan neem?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Ons kan die algehele aktiwiteit wat in die databasis plaasvind, sien. Ons kan 'n nuwe ontplooiing maak. Alles het daar ontplof, nuwe verbindings word nie aanvaar nie, foute stroom in die toepassing.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Ons kan 'n navraag soos hierdie uitvoer en die totale persentasie verbindings relatief tot die maksimum verbindingslimiet sien en sien wie ons die meeste verbindings het. En in hierdie gegewe geval sien ons daardie gebruiker cron_role 508 verbindings oopgemaak. En iets het met hom gebeur. Jy moet dit hanteer en sien. En dit is heel moontlik dat dit 'n soort abnormale aantal verbindings is.
As ons 'n OLTP-lading het, moet navrae vinnig, baie vinnig wees, en daar moet nie lang navrae wees nie. As daar egter lang versoeke is, dan is daar op die kort termyn niks om oor bekommerd te wees nie, maar op die lang termyn benadeel lang navrae die databasis, dit verhoog die opblaas-effek van tabelle wanneer tabelfragmentasie plaasvind. Beide opblaas en lang navrae moet van die hand gewys word.
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;Neem asseblief kennis: met so 'n versoek kan ons lang versoeke en transaksies definieer. Ons gebruik die funksie clock_timestamp() om die werkstyd te bepaal. Lang versoeke wat ons gevind het, ons kan dit onthou, dit uitvoer explain, kyk na planne en op een of ander manier optimaliseer. Ons skiet die huidige lang versoeke en leef voort.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Slegte transaksies is ledig in transaksie en ledig in transaksie (geaborteerde) transaksies.
Wat beteken dit? Transaksies het verskeie state. En een van hierdie state kan enige tyd neem. Daar is 'n veld om state te definieer state in hierdie siening. En ons gebruik dit om die staat te bepaal.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';En, soos ek hierbo gesê het, hierdie twee state ledig in transaksie en ledig in transaksie (geaborteer) is sleg. Wat dit is? Dit is wanneer die toepassing 'n transaksie oopgemaak het, 'n paar aksies gedoen het en oor sy besigheid gegaan het. Die transaksie bly oop. Dit hang, niks gebeur daarin nie, dit neem 'n verbinding, sluit op veranderde rye en verhoog moontlik steeds die opblaas van ander tabelle, as gevolg van die argitektuur van die Postrges-transaksieenjin. En sulke transaksies moet ook geskiet word, want dit is in elk geval skadelik in die algemeen.
As jy sien dat jy meer as 5-10-20 van hulle in jou databasis het, dan moet jy bekommerd raak en iets met hulle begin doen.
Hier gebruik ons ook vir die berekeningstyd clock_timestamp(). Ons skiet transaksies, ons optimaliseer die toepassing.
Soos ek hierbo gesê het, is slotte wanneer twee of meer transaksies om een of 'n groep hulpbronne meeding. Hiervoor het ons 'n veld waiting met Boolese waarde true of false.
Dit is waar - dit beteken dat die proses wag, iets moet gedoen word. Wanneer 'n proses wag, dan wag die kliënt wat die proses begin het ook. Die kliënt in die blaaier sit en wag ook.
waarskuwing: _Begin vanaf Postgres 9.6, die veld waiting verwyder en vervang deur twee meer insiggewende velde wait_event_type и wait_event._
Wat om te doen? As jy vir 'n lang tyd waar sien, moet jy van sulke versoeke ontslae raak. Ons skiet net sulke transaksies. Ons skryf aan ontwikkelaars wat op een of ander manier geoptimaliseer moet word sodat daar geen wedloop vir hulpbronne is nie. En dan optimaliseer die ontwikkelaars die toepassing sodat dit nie gebeur nie.
En 'n uiterste, maar potensieel nie noodlottige geval is voorkoms van dooiepunte. Twee transaksies het twee hulpbronne opgedateer, dan kry hulle weer toegang tot hulle, reeds na teenoorgestelde hulpbronne. PostgreSQL in hierdie geval neem en skiet die transaksie self af sodat die ander een kan aanhou werk. Dit is 'n doodloopsituasie en sy verstaan haarself nie. Daarom word PostgreSQL gedwing om uiterste maatreëls te tref.
En hier is twee navrae wat jou toelaat om slotte op te spoor. Ons gebruik die uitsig pg_locks, wat jou toelaat om swaar slotte op te spoor.
En die eerste skakel is die versoekteks self. Dis nogal lank.
En die tweede skakel is 'n artikel oor slotte. Dit is nuttig om te lees, dit is baie interessant.
So wat sien ons? Ons sien twee versoeke. Transaksie met ALTER TABLE is 'n blokkerende transaksie. Dit het begin, maar nie geëindig nie, en die toepassing wat hierdie transaksie geplaas het, doen iewers ander dinge. En die tweede versoek is opdatering. Dit wag vir die altertafel om klaar te maak voordat dit voortgaan met sy werk.
Dit is hoe ons kan uitvind wie wie toegesluit het, wie vir wie vashou, en ons kan dit verder hanteer.
Die volgende module is pg_stat_statements. Soos ek gesê het, dit is 'n module. Om dit te gebruik, moet jy sy biblioteek in die konfigurasie laai, PostgreSQL herbegin, die module installeer (met een opdrag), en dan sal ons 'n nuwe aansig hê.
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 kan ons daarvandaan neem? As ons oor eenvoudige dinge praat, kan ons die gemiddelde navraaguitvoeringstyd neem. Tyd groei, wat beteken dat PostgreSQL stadig reageer en iets gedoen moet word.
Ons kan die mees aktiewe skryftransaksies in die databasis sien wat data in gedeelde buffers verander. Kyk wie data daar opdateer of uitvee.
En ons kan net kyk na verskillende statistieke vir hierdie versoeke.
Ons is pg_stat_statements gebruik om verslae te bou. Ons stel die statistieke een keer per dag terug. Kom ons versamel dit. Voordat ons volgende keer statistieke terugstel, bou ons 'n verslag. Hier is 'n skakel na die berig. Jy kan dit kyk.
Wat doen ons? Ons bereken die algehele statistieke vir alle versoeke. Dan, vir elke navraag, tel ons sy individuele bydrae tot hierdie algehele statistiek.
En wat kan ons sien? Ons kan die totale uitvoeringstyd van alle versoeke van 'n spesifieke tipe teen die agtergrond van alle ander versoeke sien. Ons kan kyk na SVE en I/O gebruik in verhouding tot die algehele prentjie. En reeds om hierdie versoeke te optimaliseer. Ons bou topnavrae op grond van hierdie verslag en kry reeds stof tot nadenke oor wat om te optimaliseer.
Wat het ons agter die skerms? Daar is nog 'n paar voorleggings wat ek nie oorweeg het nie, want tyd is beperk.
Daar is pgstattuple is ook 'n bykomende module uit die standaardbydraespakket. Dit laat jou toe om te evalueer bloat tafels, sg. tabel fragmentasie. En as die fragmentasie groot is, moet u dit verwyder, verskillende gereedskap gebruik. En funksie pgstattuple werk vir 'n lang tyd. En hoe meer tafels, hoe langer sal dit werk.
Die volgende bydrae is pg_buffercache. Dit laat jou toe om gedeelde buffers te inspekteer: hoe intensief en vir watter tabelle bufferbladsye gebruik word. En dit laat jou net toe om na gedeelde buffers te kyk en te evalueer wat daar gebeur.
Die volgende module is pgfincore. Dit laat jou toe om lae-vlak tabelbewerkings uit te voer deur 'n stelseloproep mincore(), dit wil sê dit laat jou toe om die tabel in gedeelde buffers te laai, of dit af te laai. En dit laat onder meer toe om die bladsykas van die bedryfstelsel te inspekteer, dit wil sê hoeveel die tabel in die bladsykas beslaan, in gedeelde buffers, en laat jou eenvoudig toe om die lading op die tabel te evalueer.
Die volgende module is pg_stat_kcache. Dit gebruik ook die stelseloproep getrusage(). En dit voer dit uit voor en nadat die versoek uitgevoer is. En in die statistieke wat verkry is, stel dit ons in staat om te skat hoeveel ons versoek op skyf I / O spandeer het, dit wil sê, bedrywighede met die lêerstelsel en kyk na die verwerkergebruik. Die module is egter jonk (khe-khe) en vir sy werk vereis dit PostgreSQL 9.4 en pg_stat_statements, wat ek vroeër genoem het.
-
Die vermoë om statistiek te gebruik is nuttig. Jy het nie derdeparty sagteware nodig nie. Jy kan kyk, sien, iets doen, optree.
-
Die gebruik van statistieke is maklik, dit is gewone SQL. Jy het 'n versoek versamel, dit saamgestel, dit gestuur, daarna gekyk.
-
Statistiek help om vrae te beantwoord. As jy vrae het, gaan jy na statistieke - kyk, maak gevolgtrekkings, ontleed die resultate.
-
En eksperimenteer. Baie versoeke, baie data. U kan altyd 'n bestaande navraag optimaliseer. Jy kan jou eie weergawe van die versoek maak wat jou beter pas as die oorspronklike en dit gebruik.
verwysings
Geldige skakels wat in die artikel gevind is, op grond waarvan, in die verslag was.
Skrywer skryf meer
(eng)
Die Statistiekversamelaar
Stelseladministrasiefunksies
Bydrae modules
SQL-nutsprogramme en SQL-kode voorbeelde
Dankie almal vir julle aandag!
Bron: will.com