Metriche di sistema PostgreSQL

Immagina che il tuo server PostgreSQL sia un ristorante e noi siamo i revisori. Per far funzionare il ristorante senza intoppi, dobbiamo tenere d'occhio quanti ingredienti (CPU, memoria, disco) vengono usati, quanto spesso finiscono e chi li consuma. Se ci sfugge qualcosa, il nostro "ristorante" potrebbe ricevere troppi ordini e andare in tilt, oppure semplicemente crollare nel bel mezzo della giornata. Ecco perché capire le metriche di sistema è fondamentale.

Metriche chiave da monitorare in PostgreSQL:

1. CPU: indica quanto tempo di processore viene speso per eseguire le query.
2. Memoria (RAM): mostra come PostgreSQL usa la RAM, incluso il caching delle query.
3. Spazio su disco: forse la più prevedibile: non puoi salvare più dati di quanto il disco ti permette.

Il nostro obiettivo è imparare a controllare e interpretare le metriche di sistema di PostgreSQL per evitare problemi di performance e carenza di risorse.

Monitoraggio dell'utilizzo della CPU

Il processore è il cuore del tuo server. PostgreSQL può consumare CPU sia per query complesse che per task in background, come autoanalisi e auto-cleanup del database (autovacuum). Se il tuo database sembra un mangione insaziabile che "divora" la CPU, è ora di intervenire.

1. Uso degli strumenti di sistema.
   Per cominciare, scopri quanto tempo di CPU PostgreSQL consuma a livello di sistema. Su Linux puoi usare i comandi top o htop.

Trova il processo PostgreSQL (di solito contiene il nome del tuo database). Ad esempio, postgres: postgres [your_query].

Fai attenzione alla colonna %CPU. Se è sempre "al massimo", è un segnale d'allarme.

2. Analisi del carico CPU da PostgreSQL.

PostgreSQL offre viste integrate per monitorare il carico. La più utile qui è pg_stat_activity, che mostra le query attive.

Esempio di query:

SELECT pid, usename, query, state, now() - query_start AS durata
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY durata DESC;

Cosa conta qui?

• state = 'active' mostra solo le query che sono in esecuzione in questo momento.
• durata mostra da quanto tempo le query stanno caricando la CPU.

Consiglio pratico:

Se vedi una query lunga che non dovrebbe impiegare così tanto, ricontrolla quali indici vengono usati. Puoi anche terminare il processo problematico con pg_terminate_backend.

Esempio di terminazione:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active' AND durata > interval '10 minutes';

Monitoraggio dell'utilizzo della memoria

La memoria è la seconda risorsa più importante per PostgreSQL, soprattutto quando si tratta di caching dei dati. PostgreSQL usa attivamente la RAM (tramite i parametri work_mem e shared_buffers) per velocizzare le operazioni.

1. Parametri principali di PostgreSQL per l'uso della memoria:

• shared_buffers: Questo è il blocco principale di memoria riservato a PostgreSQL. Di solito occupa il 25-40% di tutta la RAM del server.
• work_mem: Memoria per operazioni di ordinamento e hash in una singola query. Più alto è il valore, più operazioni temporanee possono essere fatte in RAM (e non su disco).

2. Controllo delle impostazioni di memoria attive.

Per vedere quali impostazioni di memoria sono attive in PostgreSQL, esegui:

SHOW shared_buffers;
SHOW work_mem;

Esempio di output:

1GB
4MB

Questo significa che il server ha 1 GB di RAM riservato per shared_buffers e 4 MB per ogni operazione di ordinamento/hash.

3. Monitoraggio della memoria con pg_stat_activity

Puoi controllare quanta memoria viene usata dalle connessioni attive. Ecco una query comoda:

SELECT pid, usename, state, backend_start, pg_size_pretty(pg_backend_memory_contexts_size(pid)) AS memoria_usata
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY memoria_usata DESC;

Questa query mostra quanta RAM viene usata da ogni connessione attiva.

Consiglio: se una connessione consuma troppa memoria, controlla se nella query ci sono operazioni di ordinamento o aggregazione che possono essere ottimizzate.

Monitoraggio dello spazio su disco

Il disco è la riserva finale di risorse per PostgreSQL. Anche se hai abbastanza RAM e CPU, PostgreSQL ha bisogno di spazio su disco per salvare dati, log delle transazioni (WAL) e file temporanei.

1. Controllo della dimensione del database.

Partiamo dal base: vedere quanto è grande il database.

SELECT pg_size_pretty(pg_database_size(current_database())) AS dimensione_db;

Cosa fa questa query?

Mostra la dimensione totale del database corrente in un formato leggibile (MB, GB).

2. Controllo della dimensione di tabelle e indici.

Per trovare i "pesanti" nel database, usa:

SELECT relname AS nome_tabella, pg_size_pretty(pg_total_relation_size(relid)) AS dimensione_totale
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Esempio di output:

3. Monitoraggio dei WAL (log delle transazioni).

Se hai un database attivo, i log delle transazioni possono crescere in fretta. Per controllare la loro dimensione:

SELECT pg_size_pretty(pg_xlog_location_diff(pg_current_wal_lsn(), '0/0')) AS dimensione_wal;

Conclusioni

Ora hai strumenti e conoscenze per tenere d'occhio le metriche di sistema di PostgreSQL:

1. Usa htop o pg_stat_activity per monitorare il carico sulla CPU.
2. Configura i parametri shared_buffers e work_mem per usare al meglio la memoria.
3. Controlla regolarmente la dimensione del database, delle tabelle e degli indici per evitare di restare senza spazio su disco.

Queste skill ti torneranno utili per evitare crash improvvisi e mantenere il server PostgreSQL in forma smagliante. Quando pensi al server, ricorda: risorse organizzate bene rendono il tuo prodotto il preferito dai clienti (utenti).