Geheugen tunen (shared_buffers, work_mem)

Een gebruikelijk startpunt voor shared_buffers is 25% van het totale RAM-geheugen van je systeem. Voor speciale databaseservers kun je tot 40% gaan.

• Te laag? Regelmatig lezen vanaf de schijf.
• Te hoog? Minder RAM voor het besturingssysteem en andere processen, waardoor mogelijk swapping ontstaat.

Vergeet niet PostgreSQL opnieuw te starten nadat je deze instelling in postgresql.conf hebt gewijzigd.

work_mem is geheugen dat wordt toegewezen aan afzonderlijke querybewerkingen, zoals sorteren (ORDER BY, GROUP BY) en hashtabellen (hash-joins).

In tegenstelling tot shared_buffers is dit geheugen privé voor elke bewerking en tijdelijk. Meerdere bewerkingen in één query, of meerdere gelijktijdige query's, kunnen elk hun eigen work_mem gebruiken.

Als een querybewerking de toegewezen work_mem overschrijdt, wordt er naar de schijf 'overgelopen' en worden tijdelijke bestanden gebruikt. Dit is veel trager!

Je kunt work_mem globaal instellen, of zelfs per sessie of per query voor specifieke behoeften. Een goed startpunt is 4-16 MB, maar houd overloop in de gaten met EXPLAIN ANALYZE.

maintenance_work_mem is speciaal gereserveerd geheugen voor onderhoudstaken, met name:

• VACUUM
• CREATE INDEX
• ALTER TABLE (kolommen toevoegen enzovoort)

Deze bewerkingen kunnen zeer veel geheugen gebruiken. Het is gebruikelijk om deze waarde hoger in te stellen dan work_mem, bijvoorbeeld op 64-512 MB, om het onderhoud te versnellen.

effective_cache_size is geen geheugen dat door PostgreSQL wordt toegewezen. In plaats daarvan vertelt deze parameter de queryplanner hoeveel geheugen deze verwacht dat beschikbaar is voor het cachen van gegevens.

Dit omvat shared_buffers EN de bestandssysteemcache van het besturingssysteem. Een hogere waarde moedigt de planner aan om indexscans te gebruiken in plaats van sequentiële scans, omdat de gegevens waarschijnlijk in het geheugen staan.

De meeste van deze instellingen vind je in je bestand postgresql.conf. Je kunt ze tijdens runtime ook wijzigen met ALTER SYSTEM of met ALTER DATABASE/ALTER ROLE voor meer controle.

Vergeet niet de configuratie opnieuw te laden (of PostgreSQL opnieuw te starten voor shared_buffers) nadat je wijzigingen hebt aangebracht in postgresql.conf.

Wanneer je deze geheugenparameters goed afstemt, werken ze samen om:

• schijf-I/O te verminderen: gegevens die vanuit RAM worden geleverd, zijn veel sneller;
• query's te versnellen: sorteringen, joins en aggregaties worden sneller voltooid;
• onderhoud te optimaliseren: VACUUM en het maken van indexen worden sneller uitgevoerd;
• beslissingen van de planner te verbeteren: betere queryplannen leiden tot een betere uitvoering.

We hebben de belangrijkste PostgreSQL-geheugenparameters verkend:

• shared_buffers: hoofdcache voor gegevens, ongeveer 25% van het RAM.
• work_mem: geheugen per bewerking voor sorteringen en hashes.
• maintenance_work_mem: voor VACUUM en CREATE INDEX.
• effective_cache_size: een aanwijzing voor de queryplanner.

Door deze zorgvuldig af te stemmen kun je de prestaties van je database aanzienlijk verbeteren!