224 lines
4.6 KiB
Markdown
224 lines
4.6 KiB
Markdown
# tuning-postgres
|
||
|
||
Script Bash per applicare un **tuning automatico di PostgreSQL** (solo configurazione, niente installazione o spostamento dati), pensato in particolare per ambienti ERP (es. Zucchetti Infinity/AdHoc), ma utilizzabile in qualsiasi contesto.
|
||
|
||
Supporta:
|
||
|
||
- PostgreSQL **14 / 15 / 16**
|
||
- Sistemi **Debian/Ubuntu** (layout `postgresql-common`, es. `/etc/postgresql/16/main`)
|
||
- Profili di carico:
|
||
- `PRIVATO`
|
||
- `SHARED`
|
||
- `ANALYTICS`
|
||
|
||
> ⚠️ Lo script **deve essere eseguito come root**.
|
||
|
||
---
|
||
|
||
## Cosa fa lo script
|
||
|
||
1. Rileva automaticamente le versioni PostgreSQL installate in:
|
||
```text
|
||
/etc/postgresql/14/main
|
||
/etc/postgresql/15/main
|
||
/etc/postgresql/16/main
|
||
e ti chiede, tramite whiptail, quale versione vuoi tunare.
|
||
|
||
Chiede il profilo di utilizzo del DB:
|
||
|
||
PRIVATO → istanza singola, poche connessioni (max_connections contenuto)
|
||
|
||
SHARED → istanza multi-tenant, molte connessioni
|
||
|
||
ANALYTICS → carichi analitici pesanti, parallelismo più aggressivo
|
||
|
||
Rileva in automatico:
|
||
|
||
RAM (MB) dalla macchina (da /proc/meminfo)
|
||
|
||
Numero core CPU (da nproc o getconf)
|
||
|
||
Calcola i parametri di tuning in base a:
|
||
|
||
RAM totale
|
||
|
||
Numero core CPU
|
||
|
||
Profilo scelto
|
||
|
||
max_connections associato al profilo
|
||
|
||
Scrive (o sovrascrive) il file:
|
||
|
||
/etc/postgresql/<VERSIONE>/main/zucchetti.conf
|
||
con tutti i parametri di tuning (memoria, cache, WAL, autovacuum, parallel query, ecc.).
|
||
|
||
Si assicura che in:
|
||
|
||
|
||
/etc/postgresql/<VERSIONE>/main/postgresql.conf
|
||
sia presente:
|
||
|
||
|
||
include_if_exists = 'zucchetti.conf'
|
||
in modo che il tuning venga caricato automaticamente all’avvio.
|
||
|
||
Riavvia il servizio:
|
||
|
||
systemctl restart postgresql@<VERSIONE>-main
|
||
Parametri configurati
|
||
Connessioni
|
||
max_connections
|
||
|
||
PRIVATO → 100
|
||
|
||
SHARED → 10000
|
||
|
||
ANALYTICS → 10000
|
||
|
||
Memoria
|
||
Calcolati in modo proporzionale alla RAM totale:
|
||
|
||
shared_buffers ≈ 25% della RAM
|
||
|
||
effective_cache_size ≈ 70% della RAM
|
||
|
||
maintenance_work_mem ≈ 5% della RAM (max 4096MB)
|
||
|
||
work_mem ≈ (25% RAM) / (max_connections × 2), con limiti:
|
||
|
||
PRIVATO: max 64MB
|
||
|
||
SHARED: max 8MB
|
||
|
||
ANALYTICS: max 128MB
|
||
|
||
WAL e Checkpoint
|
||
checkpoint_completion_target = 0.9
|
||
|
||
wal_buffers = -1
|
||
|
||
min_wal_size = 1GB
|
||
|
||
max_wal_size = 4GB
|
||
|
||
Planner & IO
|
||
default_statistics_target
|
||
|
||
ANALYTICS: 2000
|
||
|
||
altri profili: 1000
|
||
|
||
random_page_cost = 1.1
|
||
|
||
effective_io_concurrency = 200
|
||
|
||
Autovacuum
|
||
Parametri pensati per ambienti ERP:
|
||
|
||
autovacuum = on
|
||
|
||
autovacuum_analyze_scale_factor = 0.1
|
||
|
||
autovacuum_analyze_threshold = 500
|
||
|
||
autovacuum_vacuum_scale_factor = 0.2
|
||
|
||
autovacuum_vacuum_threshold = 1500
|
||
|
||
autovacuum_freeze_max_age = 200000000
|
||
|
||
autovacuum_multixact_freeze_max_age = 400000000
|
||
|
||
autovacuum_naptime = 5s
|
||
|
||
autovacuum_vacuum_cost_delay = 20ms
|
||
|
||
autovacuum_vacuum_cost_limit = -1
|
||
|
||
autovacuum_work_mem = -1
|
||
|
||
Parallel Query
|
||
Tutti i profili abilitano il parallelismo, con intensità diversa:
|
||
|
||
max_worker_processes
|
||
|
||
max_parallel_workers
|
||
|
||
max_parallel_workers_per_gather
|
||
|
||
max_parallel_maintenance_workers
|
||
|
||
parallel_setup_cost
|
||
|
||
parallel_tuple_cost
|
||
|
||
min_parallel_table_scan_size
|
||
|
||
min_parallel_index_scan_size
|
||
|
||
Nel profilo ANALYTICS questi valori sono più aggressivi (maggiore parallelismo, costi minori, soglie più basse).
|
||
|
||
Requisiti
|
||
Sistema operativo: Debian/Ubuntu (o derivati con layout analogo)
|
||
|
||
PostgreSQL 14/15/16 installato con struttura:
|
||
|
||
/etc/postgresql/<VERSIONE>/main/postgresql.conf
|
||
|
||
Gestione servizi tramite systemd:
|
||
|
||
postgresql@<VERSIONE>-main
|
||
|
||
Pacchetti:
|
||
|
||
whiptail (opzionale, ma consigliato per l’interfaccia testuale)
|
||
|
||
Installazione
|
||
Copia lo script nel server (ad esempio in /root):
|
||
|
||
scp tuning-postgres.sh root@server:/root/
|
||
chmod +x /root/tuning-postgres.sh
|
||
Utilizzo
|
||
Esegui lo script come root:
|
||
|
||
|
||
sudo /root/tuning-postgres.sh
|
||
Flusso tipico:
|
||
|
||
Seleziona la versione PostgreSQL (14/15/16) tramite menù whiptail.
|
||
|
||
Seleziona il profilo (PRIVATO, SHARED, ANALYTICS).
|
||
|
||
Lo script calcola il tuning in base alle risorse della VM.
|
||
|
||
Viene generato / aggiornato zucchetti.conf.
|
||
|
||
Il servizio PostgreSQL della versione scelta viene riavviato.
|
||
|
||
Verifiche consigliate
|
||
Dopo l’esecuzione:
|
||
|
||
cat /etc/postgresql/16/main/zucchetti.conf
|
||
Controlla che contenga:
|
||
|
||
il profilo corretto (Profilo: PRIVATO/SHARED/ANALYTICS)
|
||
|
||
i valori di RAM/CPU attesi
|
||
|
||
la sezione # ===== Parallel Query =====
|
||
|
||
Verifica anche che il postgresql.conf includa:
|
||
|
||
|
||
include_if_exists = 'zucchetti.conf'
|
||
e che il servizio sia attivo:
|
||
|
||
|
||
systemctl status postgresql@16-main
|
||
Note operative
|
||
Lo script non modifica i dati del cluster, né utenti né password.
|
||
|
||
Puoi rieseguirlo in qualsiasi momento (ad es. dopo upgrade di RAM/CPU o cambio profilo).
|
||
|
||
È pensato come tuning “base intelligente” da cui partire: in casi particolari (tabelle enormi, query molto specifiche) può essere comunque utile ottimizzare manualmente indici o parametri aggiuntivi. |