Hoe PostgreSQL te Benchmarken voor Optimale Prestaties

Naarmate de adoptie van PostgreSQL toeneemt, moeten databasebeheerders (DBA’s) en ontwikkelaars vaak de prestaties ervan evalueren om ervoor te zorgen dat hun toepassingen efficiënt werken onder verschillende werkbelastingen. Prestatiebenchmarking is een kritiek proces dat meet hoe goed PostgreSQL omgaat met wisselende belastingen, waardoor knelpunten en optimalisatiegebieden worden geïdentificeerd. Dit artikel verkent tools, metriek en testscenario’s om je te helpen PostgreSQL als een professional te benchmarken.

Waarom PostgreSQL benchmarken?

Benchmarking stelt je in staat om:

1. De doorvoer en latency van je database onder specifieke werkbelastingen te meten.
2. Hardware- of configuratieknelpunten te identificeren.
3. Het effect van optimalisaties zoals indexwijzigingen of query-rewrites te vergelijken.
4. Real-world scenario’s zoals hoge gelijktijdige gebruikersactiviteit of bulk datatoevoegingen te simuleren.

Belangrijke metriek om bij te houden

Tijdens het benchmarken van PostgreSQL, richt je je op deze metriek:

• TPS (Transactions Per Second): Meet hoeveel transacties de database per seconde voltooit.
• IOPS (Input/Output Operations Per Second): Volgt schijfactiviteit.
• Latency: Meet de tijd die nodig is om queries uit te voeren, wat de gebruikerservaring beïnvloedt.
• Resource Utilization: Volgt CPU-, geheugen- en schijfgebruik tijdens de benchmark.

Tools voor PostgreSQL Benchmarking

1. pgbench

Wat is pgbench? 

pgbench is PostgreSQL’s ingebouwde benchmarkingtool. Het simuleert gelijktijdige clients die transacties uitvoeren en meet de prestaties van de database.

Installatie

Het wordt meegeleverd met PostgreSQL-installaties. Om dit te verifiëren, voer uit:

bash
pgbench --version

Aan de slag

1. Initialiseer een benchmarkdatabase:

bash
pgbench -i -s 50 mydb

Hier stelt -s de schaalfactor in, die de grootte van de dataset bepaalt.

2. Voer een eenvoudige benchmark uit:

bash
pgbench -c 10 -j 2 -T 60 mydb

• -c 10: Aantal clientverbindingen.
• -j 2: Aantal threads.
• -T 60: Benchmarkduur in seconden.

Voorbeelduitvoer:

transaction type: TPC-B (sort of)
scaling factor: 50
number of clients: 10
number of threads: 2
duration: 60 s
tps = 1420.123 (excluding connections establishing)

2. Sysbench

Waarom Sysbench gebruiken? 

Sysbench is een veelzijdige benchmarkingtool voor databases en systemen. Het biedt meer flexibiliteit dan pgbench voor aangepaste workloads.

Installatie

Installeer Sysbench met de volgende opdracht:

bash
sudo apt-get install sysbench

Aan de slag

1. Bereid de benchmark voor:

bash
sysbench --db-driver=pgsql --pgsql-db=mydb \
  --pgsql-user=postgres --tables=10 --table-size=1000000 \
  oltp_read_write prepare

2. Voer de benchmark uit:

bash
sysbench --db-driver=pgsql --pgsql-db=mydb \
  --pgsql-user=postgres --threads=4 \
  --time=60 oltp_read_write run

3. pg_stat_statements

Wat is pg_stat_statements?

Een PostgreSQL-extensie die de prestaties en uitvoeringsstatistieken van queries bijhoudt. Hoewel het geen werklasten simuleert, helpt het bij het analyseren van trage queries tijdens benchmarks.

Setup

1. Schakel de extensie in in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

2. Herlaad de configuratie en maak de extensie aan:

CREATE EXTENSION pg_stat_statements;

Gebruik

Voer de volgende query uit om langdurige statements te identificeren:

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC;

Benchmarking Scenario’s

Hieronder is een visuele weergave van de benchmarkresultaten voor drie scenario’s: lees-intensief, schrijf-intensief en gemengde werklasten. Het diagram van transacties per seconde (TPS) toont PostgreSQL’s vermogen om gelijktijdige transacties efficiënt af te handelen, terwijl het latency-diagram de tijd weergeeft die nodig is voor de uitvoering van queries in milliseconden.

PostgreSQL Transacties Per Seconde (TPS)

PostgreSQL Query Latency

Soorten Werklasten

1. Lees-Intensieve Werklasten

Doel: Test de databaseprestaties onder hoge leesactiviteit.

Setup: Gebruik pgbenchmet standaard alleen-lezen transacties:

bash
pgbench -c 50 -T 120 -S mydb

• -S: Voer alleen SELECT-queries uit.
• -c 50: Simuleer 50 gelijktijdige clients.

2. Schrijf-Intensieve Werklasten

Doel: Meet de databaseprestaties met frequente inserts of updates.

Setup: Pas de benchmark aan om schrijfbewerkingen op te nemen:

bash
pgbench -c 20 -j 4 -T 120 -N mydb

• -N: Voer niet-SELECT-query’s uit.

3. Gemengde Lees/Schrijf Workloads

Doel: Simuleer een real-world workload die lees- en schrijfoperaties mengt.

Setup: Gebruik een gebalanceerde configuratie:

bash
pgbench -c 30 -j 4 -T 180 mydb

Optimaliseer PostgreSQL voor Betere Benchmarkresultaten

Tune Geheugeninstellingen

Pas deze parameters aan in postgresql.conf:

shared_buffers = 25% of system memory
work_mem = 4MB
maintenance_work_mem = 64MB

Schakel Parallel Query Execution in

Pas deze parameters aan in postgresql.conf:

max_parallel_workers_per_gather = 4

Optimaliseer Schijf I/O

Gebruik SSD’s voor WAL-bestanden en stem deze instellingen af:

wal_buffers = 16MB
synchronous_commit = off

Voorbeeldresultaten en Interpretatie

Scenario: 50 gelijktijdige clients die een leesintensieve workload uitvoeren gedurende 60 seconden.

Uitvoer:

tps = 2500.456 (excluding connections establishing)

Interpretatie: De database is in staat om 2500 transacties per seconde te verwerken onder deze workload.

Als TPS lager is dan verwacht, analyseer queryplannen met behulp van EXPLAIN ANALYZE om prestatieknelpunten te identificeren.

Conclusie

Benchmarking PostgreSQL is een krachtige manier om prestatielimieten te identificeren en uw database te optimaliseren voor verschillende werklasten. Tools zoals pgbench en sysbench, gecombineerd met inzichten van pg_stat_statements, stellen u in staat om real-world scenario’s te simuleren en PostgreSQL-configuraties fijn af te stemmen.

Door deze tools en technieken te beheersen, kunt u ervoor zorgen dat uw PostgreSQL-instantie hoge prestaties levert voor zowel leesintensieve als schrijfintensieve toepassingen.