Prestaties en queryoptimalisatie in PostgreSQL · Les

Pools afstemmen op het aantal cores

Leid limieten voor pools en max_connections af uit CPU en workload om thrashing te voorkomen.

Les 3 van 413 stappen

Pools afstemmen op het aantal cores is een gratis Prestaties en queryoptimalisatie in PostgreSQL-les op CoddyKit. Dit is les 3 van 4. Je kunt 3 lessen uit dit leerpad gratis volledig lezen — daarna ontgrendelt CoddyKit PRO alle lessen, plus praktische oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject Prestaties en queryoptimalisatie in PostgreSQL. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.

Waarom de poolgrootte niet het aantal verbindingen is

Een veelgemaakte fout is om de connection pool te behandelen als een buffer die je onbeperkt kunt vergroten. Met PgBouncer vóór PostgreSQL heb je eigenlijk twee limieten: hoeveel clients met PgBouncer kunnen praten en hoeveel server-verbindingen PgBouncer openhoudt naar PostgreSQL.

max_client_conn kan groot zijn (duizenden) — dit zijn goedkope sockets die via een proxy lopen.
default_pool_size (en PostgreSQL max_connections) is het dure getal — elke verbinding is een echt backendproces.

Deze les gaat over het kiezen van dat dure getal op basis van het aantal CPU-kernen en de werklast, zodat de database echt werk uitvoert in plaats van voortdurend tussen te veel backends te wisselen.

Eén backend = één proces

Elke PostgreSQL-verbinding wordt ondersteund door een toegewijd OS-proces. Wanneer je meer actieve backends hebt dan CPU-kernen, verdeelt de kernel de processortijd ertussen. Na een bepaald punt levert het toevoegen van verbindingen geen extra doorvoer op — het zorgt voor meer contextwisselingen, lock-concurrentie en geheugendruk.

Je kunt zien hoeveel backends er nu bestaan en hoeveel daarvan daadwerkelijk queries uitvoeren:

SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count(*) DESC;

De startformule

De veelgebruikte basis voor een CPU-gebonden werklast die grotendeels actief is:

connections = (core_count * 2) + effective_spindle_count

De * 2 houdt rekening met backends die kort wachten op I/O of locks terwijl andere de CPU gebruiken. effective_spindle_count benadert hoeveel gelijktijdige I/O-bewerkingen je opslag kan verwerken (denk aan 0 voor een volledig gecachte werklast en een hogere waarde voor arrays met veel schijven).

Voor een server met 8 kernen en een SSD waarop de dataset grotendeels is gecachet, komt dit uit op ongeveer 16-20 serververbindingen — niet 200.

De berekening uitvoeren in SQL

Je hoeft de berekening niet met de hand uit te voeren. PostgreSQL stelt het gedetecteerde aantal CPU-kernen beschikbaar, zodat je rechtstreeks een startgrootte voor de pool kunt berekenen. De onderstaande query is zelfstandig en kan overal worden uitgevoerd:

WITH params AS (
  SELECT 8::int  AS core_count,
         0::int  AS effective_spindles
)
SELECT core_count,
       effective_spindles,
       (core_count * 2) + effective_spindles AS suggested_connections
FROM params;

Actieve tegenover inactieve backends

De formule dimensioneert voor actief werk. In de praktijk mislukken pools door backends die in idle in transaction blijven staan — ze houden een slot (en vaak locks) bezet zonder iets te doen. Deze verbruiken je budget net zo goed als drukke queries.

Controleer ze voordat je de pool groter maakt:

SELECT pid,
       state,
       now() - state_change AS idle_for,
       wait_event_type,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_for DESC;

De poolmodus verandert alles

Hoe agressief PgBouncer serververbindingen hergebruikt, hangt af van pool_mode:

session: een serververbinding is gedurende de hele sessie aan een client gekoppeld. Je hebt ongeveer evenveel serververbindingen nodig als gelijktijdige clients — poolen levert weinig op.
transaction: een serververbinding wordt na elke transactie teruggegeven. Een kleine pool kan veel clients bedienen. Hierdoor kunnen pools ter grootte van (cores*2) duizenden clients verwerken.
statement: de verbinding wordt na elke instructie teruggegeven; dit is het meest agressief, maar transacties met meerdere instructies zijn niet toegestaan.

Dimensionering op basis van het aantal CPU-kernen gaat ervan uit dat voor OLTP-werklasten de modus transaction wordt gebruikt.

Een realistisch PgBouncer-blok

Als je de getallen voor een OLTP-database met 8 cores bij elkaar optelt, ziet een typische pgbouncer.ini er zo uit. Let erop dat max_client_conn enorm groot is, terwijl default_pool_size dicht bij de uitkomst van de formule blijft:

-- pgbouncer.ini (excerpt)
-- pool_mode = transaction
-- max_client_conn = 2000
-- default_pool_size = 20
-- reserve_pool_size = 5
-- reserve_pool_timeout = 3
-- For an 8-core box: (8 * 2) + 0 = 16, rounded to 20.

max_connections moet elke pool afdekken

De max_connections van PostgreSQL is een harde bovengrens voor alle PgBouncer-pools samen, plus gereserveerde slots voor supergebruikers. Als je meerdere databases of gebruikers uitvoert, krijgt elke combinatie een eigen pool van maximaal default_pool_size, en allemaal gebruiken ze hetzelfde budget voor serverprocessen.

Vuistregel: max_connections ≥ de som van alle poolgroottes + reserve_pool_size + superuser_reserved_connections + een marge voor onderhoud en replicatie.

SHOW max_connections;

SELECT current_setting('max_connections')::int            AS max_conn,
       current_setting('superuser_reserved_connections')::int AS reserved,
       current_setting('max_connections')::int
         - current_setting('superuser_reserved_connections')::int AS usable;

Geheugen is het andere budget

Cores begrenzen de nuttige gelijktijdigheid, maar RAM bepaalt hoe hoog je max_connections veilig kunt instellen. Elk serverproces kan per sorteer- of hashknooppunt maximaal work_mem toewijzen, en één query kan dit meerdere keren gebruiken.

Slechtste geval ≈ max_connections * work_mem * (nodes per query).
Stel work_mem in met de werkelijke verbindingsbovengrens in gedachten — met een kleine pool kun je je een grotere work_mem veroorloven.

Dit is een sterk argument voor pooling: minder serverprocessen betekent meer geheugen per query.

SELECT current_setting('work_mem')                       AS work_mem,
       current_setting('max_connections')::int           AS max_conn,
       pg_size_pretty(
         current_setting('work_mem')::bigint
         * current_setting('max_connections')::int
       ) AS naive_worst_case;

Valideer tegen echte verzadiging

De formule is een startpunt, geen absolute waarheid. Controleer na het implementeren of serverprocessen CPU-beperkt zijn (goed — de cores vormen de limiet) of vastzitten op wachttijden voor LWLock/Lock (een teken dat de pool te groot is en de concurrentie toeneemt).

Neem tijdens belasting steekproeven van de wachtgebeurtenissen:

SELECT coalesce(wait_event_type, 'Running') AS wait_type,
       coalesce(wait_event, 'on_cpu')       AS wait_event,
       count(*)
FROM pg_stat_activity
WHERE state = 'active'
  AND backend_type = 'client backend'
GROUP BY 1, 2
ORDER BY count(*) DESC;

De afstemmingslus in de praktijk

Gebruik een korte feedbacklus in plaats van te gokken:

Begin bij (cores * 2) voor default_pool_size.
Voer een belastingstest uit. Als de verwerkingscapaciteit vlak blijft en de latentie toeneemt terwijl de CPU's volledig worden belast, is de pool al groot genoeg — verklein hem.
Als de CPU's niets doen terwijl clients in PgBouncer in de wachtrij staan (een stijgende cl_waiting), kan de pool te klein zijn of zijn de queries I/O-gebonden — verhoog effective_spindle_count en test opnieuw.

Controleer met de beheerconsole hoe PgBouncer zelf de druk weergeeft:

-- Connect to the special 'pgbouncer' admin database, then:
SHOW POOLS;
-- Watch cl_active, cl_waiting, sv_active, sv_idle.
-- Persistent cl_waiting > 0 with idle CPUs => pool too small.

Korte controle

Je hebt een PostgreSQL-server met 16 cores, NVMe-opslag en een werkset die volledig in RAM past (feitelijk nul schijven). De applicatie opent momenteel 800 rechtstreekse verbindingen en de CPU's worden volledig belast, terwijl de wachttijden op vergrendelingen toenemen. Wat is bij de standaardbenadering met PgBouncer in transactiemodus de beste beginwaarde voor default_pool_size?

Beantwoord dit in de interactieve les

Samenvatting

Belangrijkste punten voor het afstemmen van pools op het aantal cores:

Scheid de goedkope limiet (max_client_conn) van de dure limiet (default_pool_size / max_connections).
Begin met (cores * 2) + effective_spindle_count — meestal tientallen verbindingen, geen honderden.
De formule gaat uit van de poolmodus transaction; de sessiemodus heeft veel meer serververbindingen nodig.
Zorg dat max_connections de som van alle pools plus gereserveerde slots afdekt, en reserveer RAM via work_mem * max_connections.
Spoor serverprocessen met idle in transaction op en valideer met echte gegevens over wachtgebeurtenissen en SHOW POOLS — verklein de pool bij CPU-belasting en vergroot hem alleen wanneer de CPU's niets doen en clients in de wachtrij staan.

Gratis beginnen

Leer SQL met een AI-tutor — gratis

Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.

Cursussen: 22
Lessen: 88

Gratis beginnen met leren Bekijk de volledige cursus

Veelgestelde vragen

Is de les “Pools afstemmen op het aantal cores” gratis?

Ja — je kunt hier op het web alle 3 lessen van het leerpad Prestaties en queryoptimalisatie in PostgreSQL, waaronder “Pools afstemmen op het aantal cores”, gratis volledig lezen. Daarna ontgrendelt CoddyKit PRO alle lessen, plus interactieve oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.

Wat leer ik in “Pools afstemmen op het aantal cores”?

Leid limieten voor pools en max_connections af uit CPU en workload om thrashing te voorkomen. Je oefent met Prestaties en queryoptimalisatie in PostgreSQL door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.

Heb ik ervaring nodig om met Prestaties en queryoptimalisatie in PostgreSQL te beginnen?

Ervaring vooraf is niet nodig. Prestaties en queryoptimalisatie in PostgreSQL op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 3 van 4.

Hoe lang duurt de les “Pools afstemmen op het aantal cores”?

De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.

Kan ik code schrijven en uitvoeren in deze les over Prestaties en queryoptimalisatie in PostgreSQL?

Ja. Elke les over Prestaties en queryoptimalisatie in PostgreSQL bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.

Alle lessen in deze cursus

Waarom verbindingen duur zijn in PostgreSQL
Transaction- versus session-poolingmodi
Pools afstemmen op het aantal cores
Verzadiging en wachtrijen in pools diagnosticeren

← Terug naar Prestaties en queryoptimalisatie in PostgreSQL