Come Eseguire il Benchmarking di PostgreSQL per una Prestazione Ottimale

Con l’aumento dell’adozione di PostgreSQL, gli amministratori di database (DBA) e gli sviluppatori spesso devono valutare le sue prestazioni per garantire che le loro applicazioni funzionino in modo efficiente sotto diversi carichi di lavoro. Il benchmarking delle prestazioni è un processo critico che misura quanto bene PostgreSQL gestisce carichi variabili, aiutando a identificare colli di bottiglia e aree per l’ottimizzazione. Questo articolo esplora strumenti, metriche e scenari di test per aiutarti a eseguire il benchmarking di PostgreSQL come un professionista.

Perché eseguire il benchmarking di PostgreSQL?

Il benchmarking ti permette di:

1. Misurare la velocità di trasferimento e la latenza del tuo database sotto specifici carichi di lavoro.
2. Identificare colli di bottiglia hardware o di configurazione.
3. Confrontare l’impatto di ottimizzazioni come cambiamenti agli indici o riscritture delle query.
4. Simulare scenari del mondo reale come alta attività concorrente degli utenti o scritture massive di dati.

Metriche chiave da monitorare

Durante il benchmarking PostgreSQL, concentrati su queste metriche:

• TPS (Transazioni Per Secondo): Misura quante transazioni il database completa in un secondo.
• IOPS (Operazioni di Input/Output Per Secondo): Monitora l’attività del disco.
• Latenza: Misura il tempo necessario per eseguire le query, che influisce sull’esperienza utente.
• Utilizzo delle Risorse: Monitora l’uso della CPU, della memoria e del disco durante il benchmark.

Strumenti per il benchmarking di PostgreSQL

1. pgbench

Cos’è pgbench? 

pgbench è lo strumento di benchmarking integrato di PostgreSQL. Simula client concorrenti che eseguono transazioni e misura le prestazioni del database.

Installazione

È incluso nelle installazioni di PostgreSQL. Per verificare, esegui:

bash
pgbench --version

Iniziare

1. Inizializza un database di benchmark:

bash
pgbench -i -s 50 mydb

Qui, -s imposta il fattore di scala, che determina la dimensione del set di dati.

2. Esegui un benchmark semplice:

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

• -c 10: Numero di connessioni client.
• -j 2: Numero di thread.
• -T 60: Durata del benchmark in secondi.

Esempio di output:

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

Perché Usare Sysbench? 

Sysbench è uno strumento di benchmarking versatile per database e sistemi. Offre maggiore flessibilità rispetto a pgbench per carichi di lavoro personalizzati.

Installazione

Installa Sysbench utilizzando il seguente comando:

bash
sudo apt-get install sysbench

Iniziare

1. Prepara il benchmark:

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

2. Esegui il benchmark:

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

3. pg_stat_statements

Cos’è pg_stat_statements?

Un’estensione di PostgreSQL che traccia le prestazioni delle query e le statistiche di esecuzione. Anche se non simula i carichi di lavoro, aiuta ad analizzare le query lente durante i benchmark.

Configurazione

1. Abilitare l’estensione in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

2. Ricaricare la configurazione e creare l’estensione:

CREATE EXTENSION pg_stat_statements;

Utilizzo

Eseguire la seguente query per identificare le istruzioni a lunga esecuzione:

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

Scenari di Benchmarking

Di seguito è riportata una rappresentazione visiva dei risultati del benchmark per tre scenari: carichi di lavoro prevalentemente in lettura, in scrittura e misti. Il diagramma delle transazioni per secondo (TPS) dimostra la capacità di PostgreSQL di gestire efficientemente le transazioni concorrenti, mentre il diagramma della latenza illustra il tempo impiegato per l’esecuzione delle query in millisecondi.

Transazioni Per Secondo (TPS) di PostgreSQL

Latenza delle Query di PostgreSQL

Tipi di Carichi di Lavoro

1. Carichi di Lavoro Prevalentemente in Lettura

Obiettivo: Testare le prestazioni del database sotto alta attività di lettura.

Configurazione: Utilizzare pgbenchcon transazioni in sola lettura predefinite:

bash
pgbench -c 50 -T 120 -S mydb

• -S: Eseguire solo query SELECT.
• -c 50: Simulare 50 client concorrenti.

2. Carichi di Lavoro Prevalentemente in Scrittura

Obiettivo: Misurare le prestazioni del database con frequenti inserimenti o aggiornamenti.

Configurazione: Modificare il benchmark per includere scritture:

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

• -N: Esegui query non SELECT.

3. Carichi di Lavoro Misto Lettura/Scrittura

Obiettivo: Simulare un carico di lavoro reale che mescola letture e scritture.

Configurazione: Utilizza una configurazione bilanciata:

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

Ottimizzazione di PostgreSQL per Migliori Risultati nei Benchmark

Regola le Impostazioni della Memoria

Regola questi parametri in postgresql.conf:

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

Abilita l’Esecuzione Parallela delle Query

Regola questi parametri in postgresql.conf:

max_parallel_workers_per_gather = 4

Ottimizza l’I/O del Disco

Utilizza SSD per i file WAL e regola queste impostazioni:

wal_buffers = 16MB
synchronous_commit = off

Esempio di Risultati e Interpretazione

Scenario: 50 client.concurrenti eseguono un carico di lavoro prevalentemente di lettura per 60 secondi.

Output:

tps = 2500.456 (excluding connections establishing)

Interpretazione: Il database è in grado di gestire 2500 transazioni al secondo sotto questo carico di lavoro.

Se il TPS è inferiore al previsto, analizza i piani delle query utilizzando EXPLAIN ANALYZE per identificare i colli di bottiglia delle prestazioni.

Conclusione

Benchmarking PostgreSQL è un modo potente per identificare le limitazioni di prestazioni e ottimizzare il tuo database per vari carichi di lavoro. Strumenti come pgbench e sysbench, combinati con le informazioni provenienti da pg_stat_statements, ti permettono di simulare scenari del mondo reale e affinare le configurazioni di PostgreSQL.

Grazie alla padronanza di questi strumenti e tecniche, puoi garantire che la tua istanza di PostgreSQL offra elevate prestazioni sia per applicazioni intensamente orientate alla lettura che per quelle pesantemente orientate alla scrittura.