Monitoraggio di PostgreSQL v.10

Il documento Statistiche prestazionali in PostgreSQL riporta le principali tecniche di monitoraggio del database PostgreSQL. Ma l'evoluzione dei database e' continua e con la versione 10 sono state introdotte alcune variazioni che e' importante segnalare.
Questo documento descrive le novita' presenti nelle versioni PostgreSQL v.10 e successive relative al monitoraggio ed alla statistiche prestazionali in PostgreSQL.

Un documento introduttivo su PostgreSQL e' Introduzione a PostgreSQL, un documento piu' completo e' Qualcosa in piu' su PostgreSQL infine il documento Statistiche prestazionali in PostgreSQL elenca tutti gli strumenti e le tecniche per analizzare le prestazioni con PostgreSQL.

Novita' della versione 10

Una prima novita', anche se non tecnica, della versione 10 e'... il cambio di numerazione!
Fino alla 9.6 una major release era indicata dai primi due numeri della versione, a partire dalla 10 la major release sara' indicata da un solo numero di versione (quindi la 10.1 e' solo una minor release e la prossima major release sara' la 11).

Dal punto di vista delle prestazioni sono significativi il Table Partitioning nativo ed il parallelismo nell'esecuzione delle query. Si tratta di funzionalita' importanti per i VLDB (Very Large Database) o, con un termine piu' alla moda, per i Big Data.

E' stato introdotto il quorum commit per la replica sincrona, che consente di non attendere la risposta di tutti i secondari ma di un sottoinsieme di essi (anche uno solo). Ma sulle funzionalita' di replica la novita' piu' importante e' la Logical Replication.

Per la connessione a Postgres vi sono nuove funzionalita': connessioni Read-only/Read-Write e Multi-host failover. E' stata inoltre implementata l'autenticazione SCRAM-SHA-256 che e' tecnicamente una delle piu' avanzate disponibili.

Importanti sono anche le novita' della 10 sul monitoraggio... continuate a leggere!

Monitoraggio della base dati v.10

Per effettuare il monitoraggio di PostgreSQL e' sempre stato necessario disporre di privilegi da amministratore o comunque molto elevati. Dalla versione 10 sono disponibili ruoli predefiniti per il monitoraggio: pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables, pg_signal_backend oltre a pg_monitor che comprende i diritti di tutti i precedenti.

Gia' nella 9.6 erano raccolte nelle colonne wait_event_type e wait_event della tabella pg_stat_activity una serie di utili eventi. PostgreSQL 10 arricchisce tali informazioni che ora sono distinti in 184 eventi di wait e consentono cosi' un'analisi dettagliata dei bottlenect del database o del sistema.
Sempre sulla vista pg_stat_activity, dalla versione 10 e' disponibile la colonna backend_type.

Sono cambiati i nomi delle directory e delle funzioni che riguardano i WAL per distinguerli meglio dai log. Ad esempio i classici comandi per valutare la quantita' di scritture sui WAL: 

SELECT pg_current_xlog_insert_location();
 pg_current_xlog_insert_location 
---------------------------------
 3A/B2020A58

SELECT pg_sleep(60);

SELECT pg_current_xlog_insert_location();
 pg_current_xlog_insert_location 
---------------------------------
 3C/2D03E0E8

SELECT pg_xlog_location_diff('3A/B2020A58', '3C/2D03E0E8');

Con la 10 diventano: 

SELECT pg_current_wal_insert_lsn(); SELECT pg_sleep(60); SELECT pg_current_wal_insert_lsn();

SELECT pg_wal_lsn_diff('3E/2203E0F8', '3D/B4020A58');

Molto comodo e' anche l'utilizzo di una nuova funzione per elencare i WAL: 

select pg_ls_waldir();

In precedenza per ottenere l'elenco con dimensioni e date di aggiornamento era necessario agire a livello di sistema operativo.

Dal punto di vista del monitoraggio le tipologie di informazioni necessarie sono simili per tutti i database relazionali... tra versioni di PostgreSQL le query di base sono quasi identiche. Quindi facciamo solo un breve riassunto tra le query che riguardano le sessioni connesse ed attive [NdA attenzione alle modifiche sulla pg_stat_activity introdotte nella versione 9.6], i lock presenti e quelli bloccanti, l'utilizzo dello storage, le performance infine, piu' in generale, la struttura del database con utenti, schemata, oggetti, privilegi, ... che sono riportate nel Data Dictionary di PostgreSQL (eg. pg_catalog. pg_class, pg_attribute, pg_roles, ...) e nel Data Dictionary Standard (eg. information_schema. tables, columns, ...). Le query aggiornate per la versione 10? Eccole!

-- Sessioni select pid, datname, usename, client_addr, wait_event is not null as waiting, wait_event, wait_event_type, state, backend_type, query from pg_stat_activity order by state, pid; -- Lock select blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process from pg_catalog.pg_locks blocked_locks join pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid join pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype and blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE and blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation and blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page and blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple and blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid and blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid and blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid and blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid and blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid and blocking_locks.pid != blocked_locks.pid join pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid where not blocked_locks.GRANTED; -- DB size select datname, pg_database_size(datname) dimensione, pg_size_pretty(pg_database_size(datname)) dimensione_leggibile from pg_database where not datistemplate; -- Statistiche prestazionali (operazioni per database) select datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted from pg_stat_database where datname not like 'template%';

GRANTs per il monitoraggio

Con la versione 10 o superiore fornire ad un utente i diritti necessari al monitoraggio della base dati e' terribilmente semplice. Ad esempio per creare un utente grafana, nome di una diffusa Dashboard Open Source, e concedere i necessari permessi i passi sono: 

CREATE USER grafana WITH LOGIN;
ALTER USER grafana PASSWORD 'xxx';
GRANT CONNECT ON DATABASE myDB TO grafana;
GRANT USAGE ON SCHEMA public TO grafana;


GRANT SELECT ON ALL TABLES IN SCHEMA public TO grafana;
GRANT pg_monitor to grafana;

Vi possono essere piccole differenze a seconda delle impostazioni di sicurezza configurate nel database ed a seconda delle versioni di Postgres utilizzate.
Dalla versione PG 14 e' presente il ruolo pg_read_all_data che consente di collegarsi ad ogni database e leggere dati da tutte le tabelle. Dalla versione PG 15 e' stato rimosso il grant di CREATE nello schema PUBLIC come default.

Con le versioni 9.6 e precedenti non e' disponibile il ruolo pg_monitor. Oltre alla necessita' di fornire i GRANT manualmente a tutte le tabelle/schemi deisiderati resta il problema che l'accesso alle viste pg_stat_activity e pg_stat_statements viene, giustamente, ristretto alle sole query dell'utente stesso non superadmin.
E' pero' possibile creare una funzione con SECURITY DEFINER che restituisca i dati desiderati ed una vista che, sfruttando il search_path, si sostituisca alla vista di sistema: 

-- Grants for PG 9.6 
CREATE SCHEMA grafana AUTHORIZATION grafana;
ALTER USER grafana SET SEARCH_PATH TO grafana, pg_catalog;

CREATE OR REPLACE FUNCTION grafana.get_pg_stat_activity() RETURNS SETOF pg_stat_activity AS
  $$ SELECT * FROM pg_catalog.pg_stat_activity; $$
  LANGUAGE sql VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION grafana.get_pg_stat_activity() TO grafana;
 
CREATE OR REPLACE FUNCTION grafana.get_pg_stat_statements() RETURNS SETOF pg_stat_statements AS
  $$ SELECT * FROM public.pg_stat_statements; $$
  LANGUAGE sql VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION grafana.get_pg_stat_statements() TO grafana;

CREATE VIEW grafana.pg_stat_activity AS
  SELECT * FROM grafana.get_pg_stat_activity();
CREATE VIEW grafana.pg_stat_statements AS
  SELECT * FROM grafana.get_pg_stat_statements();
 
GRANT SELECT ON grafana.pg_stat_activity TO grafana;
GRANT SELECT ON grafana.pg_stat_statements TO grafana;

Varie ed eventuali

Un elenco completo delle funzionalita' interne e dei tool esterni per il monitoraggio e' mantenuto sul sito ufficiale PostgreSQL.

Sempre aggiornato sui rilasci delle release PostgreSQL [NdA non solo per PostgreSQL] e' il documento: Your server stinks!

Titolo: Monitoraggio di PostgreSQL v.10
Livello: Avanzato (3/5)
Data: 14 Febbraio 2018
Versione: 1.0.0 - 14 Febbraio 2018
Autore: mail [AT] meo.bogliolo.name