Postgres productietuning die de latentie verkort

postgresql.confis in de standaardstatus ontworpen om een installatie nooit te laten mislukken, en niet om uw verkeer te absorberen. Dankzij de historische waarde van shared_buffers, 128 MB, kan Postgres op een minimale machine starten zonder kritieke bronnen te reserveren. max_connections op 100 past op een kleine gedeelde server. Geen van beide is gekozen vanwege uw werkelijke belasting.

Het probleem is dus niet dat Postgres standaard slecht is ingesteld, maar dat het nooit voor u is ingesteld. In de volgende secties worden de instellingen doorlopen in de volgorde waarin ze het meeste opleveren, van het meest voorkomende knelpunt (de verbindingen) tot het langzaamst optredende (de WAL).

Het eerste project is geen geheugen, het zijn verbindingen. Elke Postgres-verbinding opent een speciaal serverproces dat RAM en context-CPU-tijd verbruikt, zelfs als het niet actief is. Het verhogen van max_connections om fouten van het type "te veel verbindingen" te voorkomen, verschuift het probleem: boven een bepaald aantal gelijktijdige actieve verbindingen verslechtert CPU-conflict de latentie van alle verzoeken, inclusief de snelste.

De juiste aanpak keert de gebruikelijke volgorde om: schaal max_connections op de daadwerkelijke gelijktijdigheid van de server en absorbeer vervolgens de gelijktijdigheid aan de applicatiezijde met een pooler zoals PgBouncer in pool_mode=transaction. De pooler multiplext honderden clientverbindingen op een handvol feitelijk actieve serververbindingen. Ons speciale artikel beschrijft het transactiemodusmechanisme en zijn limieten (opgestelde verklaringen, LISTEN/NOTIFY), en een vergelijking tussen PgBouncer, Supavisor en PgCat om de implementatie te kiezen.

max_connections is een "postmaster"-contextparameter: voor het wijzigen ervan is een volledige herstart van de server vereist, geen eenvoudig herladen. Op speciale Postgres-instanties van Aurabase (Pro- en Enterprise-abonnementen, één CNPG-cluster per project) wordt deze instelling per planlaag geconfigureerd in plaats van op de standaardwaarde te blijven staan. Een herstart is geen triviale handeling die in de productie moet worden herhaald, wat deze keuze in fasen rechtvaardigt in plaats van een vaste waarde. De formule voor het kiezen van uw eigen waarde, met zijn limieten, is het onderwerp van een apart artikel: size max_connections.

Vier geheugeninstellingen wegen meer dan alle andere samen: shared_buffers, effective_cache_size, work_mem en maintenance_work_mem. De eerste drie bepalen hoeveel gegevens Postgres in het geheugen bewaart voordat hij terugkeert naar de schijf; de vierde bepaalt de snelheid van een VACUUM- of indexcreatie.

shared_buffers stelt de interne cache in die door alle verbindingen wordt gedeeld. De benchmark die gewoonlijk wordt gedocumenteerd door het PostgreSQL-project is ongeveer 25% van het RAM-geheugen dat beschikbaar is op een speciale databaseserver. Daarnaast nemen de winsten af ​​en neemt de schijfcache van het besturingssysteem het over. effective_cache_size wijst niets toe: het is een schatting, gegeven aan de queryplanner, van het totale beschikbare geheugen voor de cache (Postgres en OS gecombineerd). Door het te klein te maken, wordt de planner in de richting van sequentiële scans geduwd, terwijl een index grotendeels in de cache zou worden opgeslagen; de huidige benchmark ligt tussen 50 en 75% RAM.

work_mem is de meest voorkomende valstrik. Dit is geen mondiale limiet. Elke sorteer- of hashbewerking in een query kan een eigen deel ervan in beslag nemen, en een query met meerdere joins kan zijn eigen deel meerdere keren reserveren. Een te royale waarde in combinatie met een hoge max_connections kan het RAM-geheugen van de server uitputten onder gelijktijdige belasting, zelfs als elk afzonderlijk verzoek redelijk lijkt. maintenance_work_memkan daarentegen aanzienlijk genereuzer blijven: het is alleen van toepassing op onderhoudswerkzaamheden (VACUUM, CREATE INDEX), die zelden gelijktijdig plaatsvinden.

Alleen shared_buffers vereist opnieuw opstarten. De andere drie worden hot herladen, ook voor een geïsoleerde sessie: SET work_mem = '64MB'; voor de duur van een enkel hebzuchtig verzoek, zonder de algemene instelling te beïnvloeden.

Schakel autovacuüm nooit uit in de productie, zelfs niet tijdelijk om "bronnen vrij te maken" tijdens een piekbelasting. Postgres gebruikt MVCC: elke UPDATE en elke DELETE laat een deadline achter die alleen vacuüm kan herstellen. Zonder dit systeem zwellen de tabellen op, verslechteren de indexen en verslechteren de uitvoeringsplannen geleidelijk, zonder dat er fouten zichtbaar zijn totdat het laat is.

De standaardwaardeautovacuum_vacuum_scale_factor (20% dode rijen vóór activering) is geschikt voor een kleine tabel, niet voor een schrijfintensieve tabel met meerdere miljoenen rijen. Op een tafel van 10 miljoen rijen vertegenwoordigt deze 20% 2 miljoen dode rijen verzameld vóór de eerste doorgang. Verlaag deze drempel tabel voor tabel in plaats van de algehele waarde van de gehele database te wijzigen.

-- Verlaag de drempel voor een tabel die veel schrijft, niet in het algemeen
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

De in Aurabase Studio geïntegreerde Advisor controleert deze configuratie bij elke projectanalyse, op dezelfde manier als tabellen zonder primaire sleutels of niet-geïndexeerde externe sleutels. Dit is eerder een expliciet signaal dan een stille degradatie die te laat ontdekt wordt.

De meeste latentieproblemen in de productie komen noch van de CPU, noch van het RAM: ze komen van een afwezige of slecht gekozen index. Voordat een enkele parameter van postgresql.confwordt aangeraakt, blijft EXPLAIN (ANALYZE, BUFFERS) op de betreffende zoekopdracht de meest betrouwbare diagnose. Het vermindert de latentie van Postgres-verzoeken ruim voordat bronnen worden toegevoegd.

-- Stel een trage query vast voordat u de configuratie aanraakt
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

Een Seq Scan in een tabel met meerdere miljoenen rijen, waar een Index Scan werd verwacht, duidt bijna altijd op een indexprobleem. Drie oorzaken komen het vaakst naar voren: een ontbrekende index, een kolomtype dat niet compatibel is met de bestaande index, of verouderde statistieken na een massale import zonder ANALYZE. Door RAM toe te voegen of work_mem te vergroten, verbergt u dit symptoom soms bij een kleine hoeveelheid gegevens; het probleem verschijnt opnieuw zodra de tafel groter wordt.

Om deze verzoeken te lokaliseren zonder ze één voor één te doorzoeken, verzamelt pg_stat_statements de uitvoeringsstatistieken van alle verzoeken van de server. Een veel voorkomende valkuil: de extensie moet eerst worden vermeld in shared_preload_libraries, een "postmaster" contextparameter die opnieuw opstarten vereist. Zonder deze stap slaagt CREATE EXTENSION pg_stat_statements; stilletjes, maar verzamelt niets.

# Vereist een volledige herstart ("postmaster" parameter)
shared_preload_libraries = 'pg_stat_statements'

-- Zodra de server opnieuw is opgestart, doet u in elke te controleren database het volgende:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Dit is precies de fout die de Aurabase-backend retourneert als deze extensie afwezig is: een expliciet bericht in plaats van een stille lege lijst, die verward zou kunnen worden met "geen langzame query". De Studio Advisor gaat nog verder: hij classificeert automatisch elk verzoek met een gemiddelde tijd langer dan 150 ms als waarschuwing en langer dan 500 ms als kritiek. Deze drempelwaarden zijn gebaseerd op dezelfde pg_stat_statements-statistieken.

Een controlepunt dwingt Postgres om alle pagina's die sinds de vorige in het geheugen zijn gewijzigd naar schijf te schrijven. Standaard kan dit schrijven zich richten op een te kort tijdsbestek. Het resultaat is een merkbare piek in de schijflatentie aan de applicatiekant, het soort periodieke vertraging dat moeilijk in verband kan worden gebracht met een specifiek verzoek.

checkpoint_completion_target regelt de spreiding van dit schrijven over het interval tussen twee controlepunten. Een detail dat ontbreekt in oudere checklists: PostgreSQL 14, uitgebracht in 2021, veranderde de standaardwaarde van 0,5 in 0,9. Op een PostgreSQL 16-instantie, zoals speciale Aurabase-tenantclusters, is deze instelling daarom standaard al correct; het handmatig aanpassen heeft alleen zin op een versie ouder dan 14. Zie onze vergelijking PostgreSQL 16 versus 17 versus 18 voor andere versiewijzigingen die van invloed zijn op het afstemmen.

max_wal_size werkt in dezelfde richting: een te lage waarde activeert vaker controlepunten dan verwacht, zelfs als checkpoint_timeout nog niet is bereikt. Door dit te verhogen, wordt de frequentie van controlepunten verminderd, ten koste van een langere hersteltijd na een crash, omdat er meer WAL's zijn om opnieuw af te spelen. Een compromis dat moet worden bepaald op basis van uw tolerantie voor onbeschikbaarheid, geen universele waarde.

Deze checklist is geen eenmalige audit die één keer moet worden afgevinkt voordat deze in productie gaat. Een basis die in volume verdubbelt of verkeer dat verdrievoudigt, maakt de bij het opstarten gekozen benchmarks overbodig, vaak zonder expliciete fouten, slechts een progressieve verslechtering van de p95-latentie.

Drie signalen verdienen voortdurende monitoring. pg_stat_statements identificeert verzoeken die na verloop van tijd verslechteren. pg_stat_activity rapporteert geblokkeerde of abnormaal langlopende zoekopdrachten, en de verhouding tussen actieve verbindingen en max_connections anticipeert op verzadiging voordat er fouten aan de applicatiezijde ontstaan.

Het tabblad Waarneembaarheid van Studio omvat een deel van deze basis voor elk Aurabase-project, zonder dat er tools van derden hoeven te worden geïnstalleerd. Het geeft een overzicht van langzame verzoeken, stelt u in staat een actief verzoek via PID te annuleren of beëindigen, en geeft een poolverzadigingsindicator weer die waarschuwt boven een gebruik van 80%. Op een zelf-gehoste instantie wordt dezelfde monitoring met de hand gebouwd, waarbij pg_stat_statements is geactiveerd en er een externe monitoringtool op is aangesloten.

Acht instellingen, in de volgorde waarin ze het meeste opleveren, met wat u moet weten voordat u ze aanraakt.

De hier genoemde drempels, zoals de 150 ms en 80% poolverzadiging die de Aurabase Studio Advisor bewaakt, zijn een door de code geverifieerd uitgangspunt en geen universele waarheid. Uw werkelijke kosten blijven de enige uiteindelijke rechter. Om max_connections precies op maat te maken in plaats van een algemene regel te volgen, beschrijft het speciale artikel de formule en de limieten ervan.

Drie vragen die systematisch naar voren komen als de checklist voor de eerste keer wordt toegepast.