🥇Diepe duik in de interne statistieken van PostgreSQL. Alexey Lesovsky

Transcriptie van het rapport uit 2015 door Alexey Lesovsky "Deep dive into PostgreSQL internal statistics"

Disclaimer van de auteur van het rapport: Ik merk op dat dit rapport dateert van november 2015 - er zijn meer dan 4 jaar verstreken en er is veel tijd verstreken. Versie 9.4 die in het rapport wordt besproken, wordt niet langer ondersteund. In de afgelopen 4 jaar zijn er 5 nieuwe releases uitgebracht waarin veel innovaties, verbeteringen en veranderingen met betrekking tot statistieken zijn verschenen, en een deel van het materiaal is verouderd en niet relevant. Terwijl ik het bekeek, heb ik geprobeerd deze plaatsen te markeren om u, de lezer, niet te misleiden. Ik heb deze plaatsen niet herschreven, er zijn er veel, en als resultaat zal er een heel ander rapport uitkomen.

Het PostgreSQL DBMS is een enorm mechanisme en dit mechanisme bestaat uit veel subsystemen, waarvan het gecoördineerde werk rechtstreeks van invloed is op de prestaties van het DBMS. Tijdens de werking worden statistieken en informatie over de werking van componenten verzameld, waarmee u de effectiviteit van PostgreSQL kunt evalueren en maatregelen kunt nemen om de prestaties te verbeteren. Er is echter veel van deze informatie en deze wordt gepresenteerd in een vrij vereenvoudigde vorm. Het verwerken en interpreteren van deze informatie is soms een volkomen niet-triviale taak, en de "dierentuin" van tools en hulpprogramma's kan zelfs een geavanceerde DBA gemakkelijk in de war brengen.

Goedemiddag Mijn naam is Alexey. Zoals Ilya zei, ik zal het hebben over PostgreSQL-statistieken.

PostgreSQL-activiteitsstatistieken. PostgreSQL heeft twee statistieken. Activiteitsstatistieken, die zullen worden besproken. En plannerstatistieken over gegevensdistributie. Ik zal het specifiek hebben over PostgreSQL-activiteitsstatistieken, waarmee we de prestaties kunnen beoordelen en op de een of andere manier kunnen verbeteren.

Ik zal je vertellen hoe je statistieken effectief kunt gebruiken om verschillende problemen op te lossen die je hebt of zou kunnen hebben.

Wat komt er niet in het rapport? In het rapport ga ik niet in op de statistieken van de planner, omdat. dit is een apart onderwerp voor een apart rapport over hoe gegevens in de database worden opgeslagen en hoe de queryplanner een idee krijgt over de kwalitatieve en kwantitatieve kenmerken van deze gegevens.

En er zullen geen toolreviews zijn, ik zal het ene product niet met het andere vergelijken. Er zal geen reclame zijn. Laten we dit laten vallen.

Ik wil je laten zien dat het nuttig is om statistieken te gebruiken. Het is nodig. Gebruik het onbevreesd. Alles wat we nodig hebben is gewone SQL en een basiskennis van SQL.

En we zullen praten over welke statistieken we moeten kiezen om problemen op te lossen.

Als we naar PostgreSQL kijken en een opdracht uitvoeren op het besturingssysteem om de processen te bekijken, zien we een "black box". We zullen enkele processen zien die iets doen, en bij naam kunnen we ons grofweg voorstellen wat ze daar doen, wat ze doen. Maar in feite is dit een zwarte doos, we kunnen niet naar binnen kijken.

We kunnen kijken naar de CPU-belasting top, kunnen we het geheugengebruik van sommige systeemhulpprogramma's zien, maar we kunnen niet in PostgreSQL kijken. Hiervoor hebben we andere tools nodig.

En als we verder gaan, zal ik je vertellen waar de tijd wordt besteed. Als we PostgreSQL vertegenwoordigen in de vorm van een dergelijk schema, dan is het mogelijk om te antwoorden waar de tijd wordt besteed. Dit zijn twee dingen: het is de verwerking van clientverzoeken van applicaties en de achtergrondtaken die PostgreSQL uitvoert om het draaiende te houden.

Als we naar de linkerbovenhoek kijken, kunnen we zien hoe verzoeken van klanten worden verwerkt. Het verzoek komt van de applicatie en er wordt een clientsessie geopend voor verder werk. Het verzoek wordt doorgegeven aan de planner. De planner bouwt een bevragingsplan. Stuurt het verder voor uitvoering. Er is een soort blok-I / O-gegevens gekoppeld aan tabellen en indexen. De benodigde gegevens worden van schijven in het geheugen gelezen in een speciaal gebied dat "gedeelde buffers" wordt genoemd. De queryresultaten, als het gaat om updates of verwijderingen, worden vastgelegd in het transactielogboek in WAL. Sommige statistische informatie gaat naar het logboek of de statistiekenverzamelaar. En het resultaat van het verzoek wordt teruggegeven aan de klant. Daarna kan de klant alles herhalen met een nieuwe aanvraag.

Wat hebben we met achtergrondtaken en achtergrondprocessen? We hebben verschillende processen die ervoor zorgen dat de database in een normale bedrijfsmodus actief blijft. Deze processen komen ook aan bod in het rapport: dit zijn autovacuüm, checkpointer, processen gerelateerd aan replicatie, achtergrondschrijver. Ik zal elk van hen bespreken terwijl ik rapporteer.

Wat zijn de problemen met statistiek?

Veel informatie. PostgreSQL 9.4 biedt 109 statistieken voor het bekijken van statistische gegevens. Als de database echter veel tabellen, schema's en databases opslaat, moeten al deze statistieken worden vermenigvuldigd met het overeenkomstige aantal tabellen en databases. Dat wil zeggen, er is nog meer informatie. En het is heel gemakkelijk om erin te verdrinken.
Het volgende probleem is dat statistieken worden weergegeven door tellers. Als we naar deze statistieken kijken, zien we constant stijgende tellers. En als er veel tijd is verstreken sinds de statistieken zijn gereset, zullen we miljarden waarden zien. En ze vertellen ons niets.
Er is geen geschiedenis. Als je een storing hebt, iets is 15-30 minuten geleden gevallen, dan kun je de statistieken niet gebruiken om te zien wat er 15-30 minuten geleden is gebeurd. Dit is het probleem.
Het ontbreken van een tool ingebouwd in PostgreSQL is een probleem. De kernelontwikkelaars bieden geen hulpprogramma. Zoiets hebben ze niet. Ze geven alleen statistieken in de database. Gebruik het, doe er een verzoek aan, wat je maar wilt, en doe het dan.
Aangezien er geen tool is ingebouwd in PostgreSQL, veroorzaakt dit een ander probleem. Veel tools van derden. Elk bedrijf dat min of meer directe handen heeft, probeert zijn eigen programma te schrijven. En daardoor heeft de community veel tools die je kunt gebruiken om met statistieken te werken. En in sommige tools zijn er enkele functies, in andere tools zijn er geen andere functies, of er zijn enkele nieuwe functies. En er doet zich een situatie voor dat u twee, drie of vier tools moet gebruiken die elkaar overlappen en verschillende functies hebben. Dit is erg onaangenaam.

Wat volgt hieruit? Het is belangrijk om direct statistieken te kunnen gebruiken om niet afhankelijk te zijn van programma's, of deze programma's op de een of andere manier zelf te verbeteren: voeg enkele functies toe om uw voordeel te behalen.

En je hebt basiskennis van SQL nodig. Om wat gegevens uit statistieken te halen, moet u SQL-query's maken, d.w.z. u moet weten hoe selectie, deelname wordt gemaakt.

Statistieken vertellen ons verschillende dingen. Ze kunnen worden onderverdeeld in categorieën.

De eerste categorie zijn de gebeurtenissen die plaatsvinden in de database. Dit is wanneer er een gebeurtenis plaatsvindt in de database: een query, een tabeltoegang, autovacuüm, commits, dan zijn dit allemaal gebeurtenissen. De tellers die overeenkomen met deze gebeurtenissen worden opgehoogd. En we kunnen deze gebeurtenissen volgen.
De tweede categorie zijn de eigenschappen van objecten zoals tabellen, databases. Ze hebben eigenschappen. Dit is de maat van de tafels. We kunnen de groei van tabellen volgen, de groei van indexen. We zien veranderingen in de dynamiek.
En de derde categorie is de tijd besteed aan het evenement. Verzoek is een evenement. Het heeft zijn eigen specifieke duurmaat. Hier begonnen, hier geëindigd. We kunnen het volgen. Ofwel de tijd van het lezen van een blok van schijf of schrijven. Deze dingen worden ook bijgehouden.

Bronnen van statistieken worden als volgt gepresenteerd:

In gedeeld geheugen (gedeelde buffers) is er een segment om daar statische gegevens te plaatsen, er zijn ook tellers die constant worden opgehoogd wanneer bepaalde gebeurtenissen plaatsvinden, of wanneer er momenten zijn in de werking van de database.
Al deze tellers zijn niet beschikbaar voor de gebruiker en zijn zelfs niet beschikbaar voor de beheerder. Dit zijn dingen van laag niveau. Om toegang te krijgen, biedt PostgreSQL een interface in de vorm van SQL-functies. We kunnen geselecteerde selecties maken met behulp van deze functies en een soort metriek (of een reeks metrieken) krijgen.
Het is echter niet altijd handig om deze functies te gebruiken, dus functies vormen de basis voor views (VIEWs). Dit zijn virtuele tabellen die statistieken geven over een specifiek subsysteem of over een reeks gebeurtenissen in de database.
Deze ingebouwde weergaven (VIEW's) zijn de belangrijkste gebruikersinterface voor het werken met statistieken. Ze zijn standaard beschikbaar zonder extra instellingen, je kunt ze meteen gebruiken, bekijken, informatie daaruit halen. En er zijn ook bijdragen. Bijdragen zijn officieel. U kunt het pakket postgresql-contrib installeren (bijvoorbeeld postgresql94-contrib), de benodigde module in de configuratie laden, er parameters voor specificeren, PostgreSQL opnieuw opstarten en u kunt het gebruiken. (Opmerking. Afhankelijk van de distributie maakt het pakket in recente versies van contrib deel uit van het hoofdpakket).
En er zijn niet-officiële bijdragen. Ze worden niet geleverd met de standaard PostgreSQL-distributie. Ze moeten worden gecompileerd of als een bibliotheek worden geïnstalleerd. Opties kunnen heel verschillend zijn, afhankelijk van wat de ontwikkelaar van deze niet-officiële bijdrage heeft bedacht.

Deze dia toont al die weergaven (VIEW's) en enkele van die functies die beschikbaar zijn in PostgreSQL 9.4. Zoals we kunnen zien, zijn dat er veel. En het is vrij gemakkelijk om in de war te raken als je het voor de eerste keer ervaart.

Als we echter de vorige foto nemen Как тратится время на PostgreSQL en compatibel met deze lijst, krijgen we deze foto. Elke weergave (VIEW) of elke functie kunnen we voor een of ander doel gebruiken om de juiste statistieken te verkrijgen wanneer we PostgreSQL hebben draaien. En we kunnen al wat informatie krijgen over de werking van het subsysteem.

Het eerste waar we naar gaan kijken is pg_stat_database. Zoals we kunnen zien, is dit een weergave. Het bevat veel informatie. De meest uiteenlopende informatie. En het geeft zeer nuttige kennis van wat er gaande is in de database.

Wat kunnen we daaruit halen? Laten we beginnen met de eenvoudigste dingen.

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

Het eerste waar we naar kunnen kijken is het percentage cachehits. Cache hit percentage is een nuttige statistiek. Hiermee kunt u inschatten hoeveel gegevens uit de gedeelde buffercache worden gehaald en hoeveel er van de schijf wordt gelezen.

Het is duidelijk dat hoe meer cachehit we hebben, hoe beter. We evalueren deze statistiek als een percentage. En als we bijvoorbeeld een percentage van deze cachehits hebben dat groter is dan 90%, dan is dat goed. Als het onder de 90% zakt, hebben we niet genoeg geheugen om de hete kop aan gegevens in het geheugen te houden. En om deze gegevens te gebruiken, wordt PostgreSQL gedwongen toegang te krijgen tot de schijf en dit is langzamer dan wanneer de gegevens uit het geheugen worden gelezen. En je moet nadenken over het vergroten van het geheugen: ofwel verhoog je gedeelde buffers, ofwel verhoog je het ijzeren geheugen (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 valt er nog meer uit deze voorstelling te halen? U kunt de afwijkingen in de database zien. Wat wordt hier getoond? Er zijn commits, rollbacks, creatie van tijdelijke bestanden, hun grootte, impasses en conflicten.

We kunnen dit verzoek gebruiken. Deze SQL is vrij eenvoudig. En we kunnen deze gegevens zelf inzien.

En hier zijn de drempelwaarden. We kijken naar de verhouding tussen commits en rollbacks. Commits is een succesvolle bevestiging van de transactie. Rollbacks is een rollback, d.w.z. de transactie deed wat werk, belastte de database, overwoog iets, en toen deed zich een fout voor en werden de resultaten van de transactie weggegooid. d.w.z. het aantal rollbacks dat voortdurend toeneemt, is slecht. En je moet ze op de een of andere manier vermijden en de code bewerken zodat dit niet gebeurt.

Conflicten zijn gerelateerd aan replicatie. En die moeten ook vermeden worden. Als u enkele query's hebt die op de replica worden uitgevoerd en er zich conflicten voordoen, moet u deze conflicten analyseren en kijken wat er gebeurt. Details zijn te vinden in de logboeken. En los conflicten op zodat applicatieverzoeken foutloos werken.

Deadlocks is ook een slechte situatie. Wanneer verzoeken strijden om bronnen, heeft één verzoek toegang gekregen tot één bron en de vergrendeling ingenomen, het tweede verzoek heeft toegang gekregen tot de tweede bron en heeft ook de vergrendeling ingenomen, en vervolgens hebben beide verzoeken toegang gekregen tot elkaars bronnen en geblokkeerd in afwachting van de buurman om de vergrendeling vrij te geven. Ook dit is een problematische situatie. Ze moeten worden aangepakt op het niveau van het herschrijven van applicaties en het serialiseren van toegang tot bronnen. En als u ziet dat uw impasses voortdurend toenemen, moet u naar de details in de logboeken kijken, de ontstane situaties analyseren en kijken wat het probleem is.

Tijdelijke bestanden (temp_files) zijn ook slecht. Wanneer een gebruikersverzoek onvoldoende geheugen heeft voor de operationele, tijdelijke gegevens, wordt er een bestand op schijf gemaakt. En alle bewerkingen die hij zou kunnen uitvoeren in een tijdelijke buffer in het geheugen, begint hij al op de schijf uit te voeren. Het is langzaam. Dit verhoogt de uitvoeringstijd van de query. En de client die een verzoek naar PostgreSQL heeft gestuurd, ontvangt iets later een reactie. Als al deze bewerkingen in het geheugen worden uitgevoerd, reageert Postgres veel sneller en wacht de client minder.

pg_stat_bgwriter - Deze weergave beschrijft de werking van twee PostgreSQL-achtergrondsubsystemen: checkpointer и background writer.

Laten we om te beginnen de controlepunten analyseren, de zogenaamde. checkpoints. Wat zijn checkpoints? Een controlepunt is een positie in het transactielogboek die aangeeft dat alle gegevenswijzigingen die in het logboek zijn vastgelegd, met succes zijn gesynchroniseerd met de gegevens op schijf. Het proces kan, afhankelijk van de werkdruk en instellingen, lang duren en bestaat meestal uit het synchroniseren van vuile pagina's in gedeelde buffers met gegevensbestanden op schijf. Waar is het voor? Als PostgreSQL de hele tijd toegang tot de schijf zou hebben en daar gegevens van zou halen en bij elke toegang gegevens zou schrijven, zou het traag zijn. Daarom heeft PostgreSQL een geheugensegment waarvan de grootte afhangt van de parameters in de configuratie. Postgres wijst operationele gegevens in dit geheugen toe voor verdere verwerking of opvraging. In het geval van verzoeken tot wijziging van gegevens worden deze gewijzigd. En we krijgen twee versies van de gegevens. De ene staat in het geheugen, de andere op schijf. En periodiek moet u deze gegevens synchroniseren. Wat er in het geheugen is gewijzigd, moet worden gesynchroniseerd met schijf. Hiervoor is een controlepost nodig.

Checkpoint gaat door gedeelde buffers, markeert vuile pagina's dat ze nodig zijn voor checkpoint. Daarna begint het de tweede doorgang door gedeelde buffers. En de pagina's die zijn gemarkeerd voor checkpoint, synchroniseert hij al. De gegevens zijn dus al gesynchroniseerd met de schijf.

Er zijn twee soorten controlepunten. Eén controlepunt wordt uitgevoerd na een time-out. Dit ijkpunt is nuttig en goed - checkpoint_timed. En er zijn checkpoints op aanvraag - checkpoint required. Zo'n controlepunt doet zich voor wanneer we een zeer groot gegevensrecord hebben. We hebben veel transactielogboeken vastgelegd. En PostgreSQL is van mening dat het dit allemaal zo snel mogelijk moet synchroniseren, een checkpoint moet maken en verder moet gaan.

En als je naar de statistieken kijkt pg_stat_bgwriter en kijk wat je hebt checkpoint_req veel groter is dan checkpoint_timed, dan is dit slecht. Waarom slecht? Dit betekent dat PostgreSQL constant onder druk staat wanneer het gegevens naar schijf moet schrijven. Checkpoint by timeout is minder stressvol en wordt volgens het interne schema uitgevoerd en als het ware uitgerekt in de tijd. PostgreSQL heeft de mogelijkheid om het werk te pauzeren en het schijfsubsysteem niet te belasten. Dit is handig voor PostgreSQL. En verzoeken die tijdens het controlepunt worden uitgevoerd, zullen geen stress ondervinden van het feit dat het schijfsubsysteem bezig is.

En er zijn drie parameters om het ijkpunt aan te passen:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Hiermee kunt u de werking van controlepunten regelen. Maar ik zal er niet bij stilstaan. Hun invloed is een apart probleem.

Opmerking: Versie 9.4 die in het rapport wordt overwogen, is niet langer relevant. In moderne versies van PostgreSQL is de parameter checkpoint_segments vervangen door parameters min_wal_size и max_wal_size.

Het volgende subsysteem is de achtergrondschrijver − background writer. Wat is hij aan het doen? Het draait constant in een eindeloze lus. Het scant pagina's naar gedeelde buffers en spoelt de vuile pagina's die het vindt naar schijf. Op deze manier helpt het de checkpointer om minder werk te doen tijdens het checkpointen.

Waar is hij anders voor nodig? Het voorziet in de behoefte aan schone pagina's in gedeelde buffers als ze plotseling (in grote hoeveelheden en onmiddellijk) nodig zijn om gegevens op te nemen. Stel dat er zich een situatie voordoet waarin het verzoek schone pagina's vereiste en deze zich al in gedeelde buffers bevinden. Postgres backend hij pakt ze gewoon en gebruikt ze, hij hoeft zelf niets schoon te maken. Maar als er plotseling geen dergelijke pagina's zijn, pauzeert de backend en begint te zoeken naar pagina's om ze naar schijf te spoelen en voor eigen behoeften te gebruiken - wat een negatieve invloed heeft op de tijd van het momenteel uitgevoerde verzoek. Als je ziet dat je een parameter maxwritten_clean groot, betekent dit dat de achtergrondschrijver zijn werk niet doet en dat u de parameters moet verhogen bgwriter_lru_maxpageszodat hij meer werk in één cyclus kan doen, meer pagina's kan wissen.

En een andere zeer nuttige indicator is buffers_backend_fsync. Backends doen geen fsync omdat het traag is. Ze geven fsync door aan de IO stack checkpointer. De checkpointer heeft zijn eigen wachtrij, het verwerkt periodiek fsync en synchroniseert pagina's in het geheugen met bestanden op schijf. Als de wachtrij van de checkpointer groot en vol is, wordt de backend gedwongen om zelf fsync te doen en dit vertraagt de backend, d.w.z. de klant ontvangt later een reactie dan mogelijk. Als je ziet dat je deze waarde groter dan nul hebt, dan is dit al een probleem en u moet aandacht besteden aan de instellingen van de achtergrondschrijver en ook de prestaties van het schijfsubsysteem evalueren.

Opmerking: _De volgende tekst beschrijft de statistische weergaven die verband houden met replicatie. De meeste weergave- en functienamen zijn hernoemd in Postgres 10. De essentie van de hernoemingen was om te vervangen xlog op wal и location op lsn in functie-/weergavenamen, enz. Bijzonder voorbeeld, functie pg_xlog_location_diff() is hernoemd naar pg_wal_lsn_diff()._

We hebben hier ook veel. Maar we hebben alleen items nodig die verband houden met de locatie.

Als we zien dat alle waarden gelijk zijn, dan is dit ideaal en blijft de replica niet achter bij de master.

Deze hexadecimale positie hier is de positie in het transactielogboek. Het neemt constant toe als er enige activiteit in de database is: inserts, deletes, etc.

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

Als deze dingen anders zijn, is er een soort vertraging. Vertraging is de vertraging van de replica van de master, d.w.z. de gegevens verschillen tussen servers.

Er zijn drie redenen voor de vertraging:

Het is het schijfsubsysteem dat schrijfbewerkingen voor bestandssynchronisatie niet aankan.
Dit zijn mogelijke netwerkfouten of netwerkoverbelasting, wanneer de gegevens geen tijd hebben om de replica te bereiken en deze niet kunnen reproduceren.
En de verwerker. De processor is een zeer zeldzaam geval. En dat heb ik twee of drie keer gezien, maar dat kan ook gebeuren.

En hier zijn drie vragen waarmee we statistieken kunnen gebruiken. We kunnen inschatten hoeveel er wordt geregistreerd in ons transactielogboek. Er is zo'n functie pg_xlog_location_diff en we kunnen de replicatievertraging schatten in bytes en seconden. Ook hiervoor gebruiken we de waarde uit deze view (VIEWs).

Opmerking: _In plaats van pg_xlog_locationdiff() kunt u de aftrekoperator gebruiken en de ene locatie van de andere aftrekken. Comfortabel.

Met een vertraging, die in seconden is, is er één moment. Als er geen activiteit op de master is, was de transactie er ongeveer 15 minuten geleden en is er geen activiteit, en als we naar deze vertraging op de replica kijken, zien we een vertraging van 15 minuten. Dit is het waard om te onthouden. En het kan tot een verdoving leiden als je naar deze vertraging kijkt.

pg_stat_all_tables is een andere handige weergave. Het toont statistieken op tabellen. Als we tabellen in de database hebben, is er wat activiteit mee, sommige acties, we kunnen deze informatie uit deze weergave halen.

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;

Het eerste waar we naar kunnen kijken, zijn sequentiële tabelscans. Het nummer zelf na deze passages is niet per se slecht en geeft niet aan dat we al iets moeten doen.

Er is echter een tweede statistiek - seq_tup_read. Dit is het aantal rijen dat is geretourneerd uit de sequentiële scan. Als het gemiddelde aantal hoger is dan 1, 000, 10, 000, dan is dit al een indicator dat u mogelijk ergens een index moet bouwen zodat de toegangen per index zijn, of het is mogelijk om query's te optimaliseren die dergelijke sequentiële scans gebruiken, zodat dit gebeurt niet. was.

Een eenvoudig voorbeeld - laten we zeggen dat een aanvraag met een grote OFFSET en LIMIT de moeite waard is. Er worden bijvoorbeeld 100 rijen in een tabel gescand en daarna worden 000 vereiste rijen gebruikt en worden de eerder gescande rijen weggegooid. Dit is ook een slecht geval. En dergelijke verzoeken moeten worden geoptimaliseerd. En hier is zo'n eenvoudige SQL-query waarop u deze kunt zien en de ontvangen cijfers kunt evalueren.

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;

Tabelgroottes kunnen ook worden verkregen met behulp van deze tabel en met behulp van extra functies pg_total_relation_size(), pg_relation_size().

Over het algemeen zijn er metacommando's dt и di, die u kunt gebruiken in PSQL en ook tabel- en indexgroottes kunt bekijken.

Het gebruik van functies helpt ons echter om naar de grootte van tabellen te kijken, zelfs rekening houdend met indexen, of zonder rekening te houden met indexen, en al enkele schattingen te maken op basis van de groei van de database, d.w.z. hoe deze met ons meegroeit, met welke intensiteit, en trek al enkele conclusies over maatoptimalisatie.

Schrijf activiteit. Wat is een record? Laten we eens kijken naar de operatie UPDATE – de werking van het bijwerken van rijen in de tabel. In feite bestaat update uit twee bewerkingen (of zelfs meer). Dit is het invoegen van een nieuwe rijversie en het markeren van de oude rijversie als verouderd. Later zal autovacuüm deze verouderde versies van de lijnen komen opruimen, deze plek markeren als beschikbaar voor hergebruik.

Bij update gaat het ook niet alleen om het bijwerken van een tabel. Het is nog steeds een indexupdate. Als u veel indexen op de tabel heeft, moeten bij update ook alle indexen worden bijgewerkt waaraan de velden die in de query zijn bijgewerkt, deelnemen. Deze indexen hebben ook verouderde rijversies die moeten worden opgeschoond.

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 vanwege het ontwerp is UPDATE een zware operatie. Maar ze kunnen gemakkelijker worden gemaakt. Eten hot updates. Ze verschenen in PostgreSQL versie 8.3. En wat is dit? Dit is een lichtgewicht update die er niet voor zorgt dat indexen opnieuw worden opgebouwd. Dat wil zeggen, we hebben het record bijgewerkt, maar alleen het record op de pagina (dat bij de tabel hoort) is bijgewerkt en de indexen verwijzen nog steeds naar hetzelfde record op de pagina. Er is een beetje zo'n interessante logica van werk, als er een vacuüm komt, dan heeft het deze kettingen hot herbouwt en alles blijft werken zonder de indexen bij te werken, en alles gebeurt met minder verspilling van middelen.

En wanneer je hebt n_tup_hot_upd groot, het is heel goed. Dit betekent dat lichtgewicht updates de boventoon voeren en dit is voor ons goedkoper in termen van middelen en alles is in orde.

ALTER TABLE table_name SET (fillfactor = 70);

Hoe het volume te verhogen hot updateov? We kunnen gebruiken fillfactor. Het bepaalt de grootte van de gereserveerde vrije ruimte bij het vullen van een pagina in een tabel met behulp van INSERT's. Wanneer inserts naar de tafel gaan, vullen ze de pagina volledig, laten er geen lege ruimte in achter. Vervolgens wordt een nieuwe pagina gemarkeerd. De gegevens worden opnieuw ingevuld. En dit is het standaardgedrag, vulfactor = 100%.

We kunnen de vulfactor instellen op 70%. Dat wil zeggen, met inserts werd een nieuwe pagina toegewezen, maar slechts 70% van de pagina was gevuld. En we hebben nog 30% in reserve. Wanneer u een update moet uitvoeren, gebeurt dit hoogstwaarschijnlijk op dezelfde pagina en past de nieuwe versie van de rij op dezelfde pagina. En hot_update zal worden gedaan. Dit maakt het gemakkelijker om op tabellen te schrijven.

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

Autovacuüm wachtrij. Autovacuum is zo'n subsysteem waarvoor er maar heel weinig statistieken in PostgreSQL zijn. We kunnen alleen in de tabellen in pg_stat_activity zien hoeveel stofzuigers we op dit moment hebben. Het is echter erg moeilijk om te begrijpen hoeveel tafels er onderweg in de rij staan.

Opmerking: _Sinds Postgres 10 is de situatie met het volgen van het vacuümvacuüm veel verbeterd - de weergave pg_stat_progress is verschenenvacuüm, wat de kwestie van autovacuümbewaking aanzienlijk vereenvoudigt.

We kunnen deze vereenvoudigde query gebruiken. En we kunnen zien wanneer het vacuüm moet worden gemaakt. Maar hoe en wanneer moet het vacuüm beginnen? Dit zijn de oude versies van de snaren waar ik het eerder over had. Update heeft plaatsgevonden, de nieuwe versie van de rij is ingevoegd. Er is een verouderde versie van de string verschenen. Tafel pg_stat_user_tables er is zo'n parameter n_dead_tup. Het toont het aantal "dode" rijen. En zodra het aantal dode rijen boven een bepaalde drempel komt, komt er een autovacuüm op tafel.

En hoe wordt deze drempel berekend? Dit is een heel specifiek percentage van het totale aantal rijen in de tabel. Er is een parameter autovacuum_vacuum_scale_factor. Het definieert het percentage. Laten we zeggen 10% + er is een extra basisdrempel van 50 lijnen. En wat gebeurt er? Wanneer we meer dode rijen hebben dan "10% + 50" van alle rijen in de tabel, zetten we de tabel op autovacuüm.

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

Er is echter één punt. Basisdrempels voor parameters av_base_thresh и av_scale_factor kunnen individueel worden toegewezen. En dienovereenkomstig zal de drempel niet globaal zijn, maar individueel voor de tafel. Daarom moet je daar trucs en trucs gebruiken om te berekenen. En als je geïnteresseerd bent, kun je kijken naar de ervaring van onze collega's van Avito (de link op de dia is ongeldig en is bijgewerkt in de tekst).

Ze schreven voor Munin-plug-indie rekening houdt met deze zaken. Er is een voetendoek op twee lakens. Maar hij denkt correct en heel effectief stelt ons in staat om te beoordelen waar we veel vacuüm nodig hebben voor tafels waar er weinig is.

Wat kunnen we eraan doen? Als we een lange rij hebben en de autovacuüm kan het niet aan, dan kunnen we het aantal vacuümwerkers vergroten of de stofzuiger gewoon agressiever makenzodat het eerder triggert, verwerkt de tabel in kleine stukjes. En zo zal de wachtrij afnemen. - Het belangrijkste hier is om de belasting van de schijven te controleren, omdat. Het vacuümding is niet gratis, hoewel met de komst van SSD / NVMe-apparaten het probleem minder opvalt.

pg_stat_all_indexes zijn statistieken over indexen. Ze is niet groot. En we kunnen er informatie over het gebruik van indexen uit halen. En we kunnen bijvoorbeeld bepalen welke indexen we extra hebben.

Zoals ik al zei, update is niet alleen het bijwerken van tabellen, het is ook het bijwerken van indexen. Dienovereenkomstig, als we veel indexen in de tabel hebben, moeten bij het bijwerken van de rijen in de tabel ook de indexen van de geïndexeerde velden worden bijgewerkt, en als we ongebruikte indexen hebben waarvoor geen indexscans zijn, dan hangen ze bij ons als ballast. En je moet ze kwijtraken. Hiervoor hebben we een veld nodig idx_scan. We kijken alleen naar het aantal indexscans. Als de indexen nul scans hebben gedurende een relatief lange periode van opslag van statistieken (minstens 2-3 weken), dan zijn dit hoogstwaarschijnlijk slechte indexen, we moeten ze verwijderen.

Opmerking: Bij het zoeken naar ongebruikte indexen in het geval van streaming replicatieclusters, moet u alle knooppunten van het cluster controleren, omdat statistieken zijn niet globaal en als de index niet op de master wordt gebruikt, kan deze op replica's worden gebruikt (als er een belasting is).

Twee koppelingen:

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

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Dit zijn meer geavanceerde queryvoorbeelden voor het opzoeken van ongebruikte indexen.

De tweede link is een nogal interessante vraag. Er zit een zeer niet-triviale logica in. Ik beveel het aan voor beoordeling.

Wat moet er nog meer worden samengevat door indexen?

Ongebruikte indexen zijn slecht.
Ze nemen ruimte in beslag.
Vertraag de updatebewerkingen.
Extra werk voor de stofzuiger.

Als we ongebruikte indexen verwijderen, maken we de database alleen maar beter.

Het volgende uitzicht is pg_stat_activity. Dit is een analoog van het hulpprogramma ps, alleen in PostgreSQL. Als ps'ohm, kijk dan naar de processen in het besturingssysteem pg_stat_activity zal je de activiteit binnen PostgreSQL laten zien.

Wat kunnen we daaruit halen?

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

We kunnen de algehele activiteit zien die plaatsvindt in de database. We kunnen een nieuwe inzet maken. Alles explodeerde daar, nieuwe verbindingen worden niet geaccepteerd, fouten stromen de applicatie binnen.

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

We kunnen zo'n query uitvoeren en het totale percentage verbindingen zien ten opzichte van de maximale verbindingslimiet en zien wie we de meeste verbindingen hebben. En in dit gegeven geval zien we die gebruiker cron_role 508 verbindingen geopend. En er gebeurde iets met hem. Je moet ermee dealen en zien. En het is heel goed mogelijk dat dit een soort abnormaal aantal verbindingen is.

Als we een OLTP-belasting hebben, moeten zoekopdrachten snel, zeer snel zijn en mogen er geen lange zoekopdrachten zijn. Als er echter lange verzoeken zijn, is er op korte termijn niets aan de hand, maar op de lange termijn beschadigen lange query's de database, ze vergroten het opgeblazen effect van tabellen wanneer tabelfragmentatie optreedt. Zowel bloat als lange vragen moeten worden weggegooid.

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;

Let op: bij een dergelijk verzoek kunnen we lange verzoeken en transacties definiëren. We gebruiken de functie clock_timestamp() om de werktijd te bepalen. Lange verzoeken die we hebben gevonden, kunnen we onthouden en uitvoeren explain, bekijk plannen en optimaliseer op de een of andere manier. We schieten de huidige lange verzoeken en leven voort.

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

Slechte transacties zijn inactief in transactie en inactief in transactie (afgebroken) transacties.

Wat betekent het? Transacties hebben meerdere statussen. En een van deze staten kan op elk moment in beslag nemen. Er is een veld om staten te definiëren state in deze weergave. En we gebruiken het om de staat te bepalen.

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

En, zoals ik hierboven al zei, deze twee staten inactief in transactie en inactief in transactie (afgebroken) zijn slecht. Wat het is? Dit is wanneer de applicatie een transactie opende, enkele acties deed en zijn gang ging. De transactie blijft openstaan. Het blijft hangen, er gebeurt niets, er is een verbinding voor nodig, het vergrendelt op gewijzigde rijen en verhoogt mogelijk nog steeds de zwelling van andere tabellen, vanwege de architectuur van de Postrges-transactie-engine. En dergelijke transacties moeten ook worden geschoten, omdat ze in het algemeen sowieso schadelijk zijn.

Als je ziet dat je er meer dan 5-10-20 in je database hebt, dan moet je je zorgen gaan maken en er iets mee gaan doen.

Hier gebruiken we ook voor de rekentijd clock_timestamp(). We schieten transacties, we optimaliseren de applicatie.

Zoals ik hierboven al zei, zijn sloten wanneer twee of meer transacties strijden om een of een groep bronnen. Hiervoor hebben we een veld waiting met booleaanse waarde true of false.

Toegegeven - dit betekent dat het proces wacht, er moet iets worden gedaan. Wanneer een proces wacht, wacht de client die het proces heeft gestart ook. De client in de browser zit en wacht ook.

Opmerking: _Vanaf Postgres 9.6 is het veld waiting verwijderd en vervangen door twee meer informatieve velden wait_event_type и wait_event._

Wat te doen? Als u lange tijd waar ziet, moet u dergelijke verzoeken verwijderen. Dergelijke transacties schieten we gewoon. We schrijven naar ontwikkelaars wat er op de een of andere manier moet worden geoptimaliseerd, zodat er geen race om middelen is. En dan optimaliseren de ontwikkelaars de applicatie zodat dit niet gebeurt.

En een extreem, maar potentieel niet dodelijk geval is optreden van impasses. Twee transacties hebben twee bronnen geüpdatet, waarna ze deze opnieuw openen, al naar tegenovergestelde bronnen. PostgreSQL neemt en schiet in dit geval de transactie zelf weg, zodat de andere kan blijven werken. Dit is een doodlopende situatie en ze begrijpt zichzelf niet. Daarom moet PostgreSQL extreme maatregelen nemen.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

En hier zijn twee vragen waarmee u sloten kunt volgen. We gebruiken het uitzicht pg_locks, waarmee u zware sloten kunt volgen.

En de eerste link is de verzoektekst zelf. Het is vrij lang.

En de tweede link is een artikel over sloten. Het is nuttig om te lezen, het is erg interessant.

Dus wat zien we? We zien twee verzoeken. Transactie met ALTER TABLE is een blokkerende transactie. Het begon, maar eindigde niet, en de applicatie die deze transactie heeft gepost, doet ergens andere dingen. En het tweede verzoek is update. Het wacht tot de alter-tabel klaar is voordat het verder gaat met zijn werk.

Zo kunnen we achterhalen wie wie heeft opgesloten, wie wie vasthoudt en kunnen we dit verder afhandelen.

De volgende module is pg_stat_statements. Zoals ik al zei, het is een module. Om het te gebruiken, moet je de bibliotheek in de configuratie laden, PostgreSQL opnieuw opstarten, de module installeren (met één opdracht) en dan hebben we een nieuwe weergave.

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 kunnen we daaruit halen? Als we het over eenvoudige dingen hebben, kunnen we de gemiddelde uitvoeringstijd van query's nemen. De tijd groeit, wat betekent dat PostgreSQL traag reageert en dat er iets moet gebeuren.

We kunnen de meest actieve schrijftransacties in de database zien die gegevens in gedeelde buffers wijzigen. Kijk wie daar gegevens bijwerkt of verwijdert.

En we kunnen gewoon naar verschillende statistieken voor deze verzoeken kijken.

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

Wij pg_stat_statements gebruikt om rapporten op te bouwen. We resetten de statistieken één keer per dag. Laten we het verzamelen. Voordat we de volgende keer statistieken resetten, maken we een rapport. Hier is een link naar het rapport. Je kunt het bekijken.

Wat doen we? We berekenen de algemene statistieken voor alle zoekopdrachten. Vervolgens tellen we voor elke zoekopdracht de individuele bijdrage aan deze algemene statistiek.

En wat kunnen we zien? We kunnen de totale uitvoeringstijd van alle verzoeken van een bepaald type zien tegen de achtergrond van alle andere verzoeken. We kunnen CPU- en I/O-gebruik bekijken in relatie tot het totaalbeeld. En nu al om deze aanvragen te optimaliseren. We bouwen topquery's op basis van dit rapport en krijgen al stof tot nadenken over wat we kunnen optimaliseren.

Wat hebben we achter de schermen? Er zijn nog een paar inzendingen die ik niet heb overwogen, omdat de tijd beperkt is.

Er is pgstattuple is ook een extra module uit het standaard contribs-pakket. Het stelt je in staat om te evalueren bloat tafels, zgn. tafel fragmentatie. En als de fragmentatie groot is, moet u deze verwijderen, verschillende tools gebruiken. En functie pgstattuple werkt lang. En hoe meer tafels, hoe langer het werkt.

De volgende bijdrage is pg_buffercache. Hiermee kunt u gedeelde buffers inspecteren: hoe intensief en voor welke tabellen bufferpagina's worden gebruikt. En het stelt je gewoon in staat om in gedeelde buffers te kijken en te evalueren wat daar gebeurt.

De volgende module is pgfincore. Hiermee kunt u tafelbewerkingen op laag niveau uitvoeren via een systeemaanroep mincore(), d.w.z. hiermee kunt u de tabel in gedeelde buffers laden of ontladen. En het maakt het onder andere mogelijk om de paginacache van het besturingssysteem te inspecteren, dat wil zeggen hoeveel de tabel inneemt in de paginacache, in gedeelde buffers, en stelt u eenvoudig in staat om de belasting van de tafel te evalueren.

De volgende module is pg_stat_kcache. Het gebruikt ook de systeemoproep getrusage(). En het voert het uit voor en nadat het verzoek is uitgevoerd. En in de verkregen statistieken kunnen we inschatten hoeveel ons verzoek heeft uitgegeven aan schijf-I / O, d.w.z. bewerkingen met het bestandssysteem, en wordt gekeken naar het processorgebruik. De module is echter jong (khe-khe) en vereist voor zijn werk PostgreSQL 9.4 en pg_stat_statements, die ik eerder noemde.

De mogelijkheid om statistieken te gebruiken is handig. U hebt geen software van derden nodig. Je kunt kijken, zien, iets doen, presteren.
Statistieken gebruiken is eenvoudig, het is gewone SQL. Je verzamelde een verzoek, stelde het samen, stuurde het, bekeek het.
Statistieken helpen bij het beantwoorden van vragen. Als je vragen hebt, wend je je tot statistieken - kijk, trek conclusies, analyseer de resultaten.
En experimenteer. Veel aanvragen, veel data. U kunt altijd een bestaande zoekopdracht optimaliseren. U kunt uw eigen versie van het verzoek maken die beter bij u past dan het origineel en deze gebruiken.