Monitora Oracle Cloud Infrastructure Database con PostgreSQL utilizzando Prometheus e Grafana

Introduzione

PostgreSQL è uno dei database più popolari tra gli sviluppatori di applicazioni. Oltre all'eliminazione del sovraccarico di gestione offerto da Oracle Cloud Infrastructure Database con PostgreSQL (OCI Database con PostgreSQL), si va anche oltre quando si tratta di implementare una facilità di monitoraggio. Prometheus e Grafana sono una coppia molto popolare quando si tratta di monitorare le prestazioni delle applicazioni per lo storage e la visualizzazione dei dati di serie temporali, rispettivamente.

In questa esercitazione verrà illustrato come monitorare un database OCI con PostgreSQL estraendo metriche a livello di query utilizzando PostgreSQL Exporter, eseguendo lo scraping con Prometheus, visualizzandole in Grafana e impostando avvisi per le metriche di blocco.

Panoramica dell'architettura

Lo schema seguente descrive l'approccio generale. Vengono utilizzati i componenti riportati di seguito.

Database OCI con PostgreSQL.
Esportatore postgres per esporre le metriche.
Prometheus per raschiare le metriche.
Grafana per visualizzare le metriche.

Vista architettura

Nota: mentre l'architettura visualizza tre subnet per maggiore chiarezza, questa esercitazione ne utilizza solo due e l'host di salto è anche la VM utilizzata per distribuire Prometheus e Grafana.

Obiettivi

Estrarre le metriche PostgreSQL con PostgreSQL Exporter per Prometheus.
Includi queste metriche in Prometheus.
Visualizza le metriche utilizzando i dashboard di Grafana.
Impostare avvisi per rilevare il blocco delle tabelle utilizzando metriche esposte tramite PostgreSQL Exporter.

Prerequisiti

Accesso a una tenancy OCI con un database OCI con provisioning eseguito con cluster PostgreSQL. Per ulteriori informazioni, vedere Connettersi a Oracle Cloud Infrastructure Database con PostgreSQL utilizzando DBeaver.
Istanza di computazione OCI (host jump) in una subnet pubblica nella stessa VCN del database OCI con PostgreSQL.
Lista di sicurezza OCI: consentire le porte in entrata 9090, 9187, 3000.

Eseguire i comandi seguenti per impostare il firewall sull'istanza.

sudo firewall-cmd --permanent --add-port=9090/tcp
sudo firewall-cmd --permanent --add-port=9187/tcp
sudo firewall-cmd --permanent --add-port=3000/tcp
sudo firewall-cmd --reload

Task 1: Abilitare le estensioni di monitoraggio sul database OCI con PostgreSQL

Abilitare l'estensione pg_stat_statements.

Aggiungere pg_stat_statements nel database OCI con configurazioni PostgreSQL tramite OCI Console. Per ulteriori informazioni, vedere Enabling Extensions for a Database.
Crea utente di monitoraggio.

Eseguire le query riportate di seguito per creare e connettere l'utente utilizzando DBeaver tramite l'host di collegamento.
```
CREATE USER postgres_exporter WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor TO postgres_exporter;
```
Nota: l'estensione pg_stat_io è disponibile dal database OCI con PostgreSQL v16 in poi.

Task 2: Installazione di PostgreSQL Exporter per Prometheus

Nota: eseguire i comandi riportati di seguito nell'istanza di OCI Compute.

Installare PostgreSQL Exporter.

wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz

tar -xvf postgres_exporter-0.15.0.linux-amd64.tar.gz

sudo mv postgres_exporter-0.15.0.linux-amd64/postgres_exporter /usr/local/bin/

Creare il file queries.yaml personalizzato.

nano queries.yaml

# Custom PostgreSQL Query Metrics for Postgres Exporter

# Top 5 slowest SQL statements
top_sql_statements:
query: >
	SELECT query, calls, total_exec_time + total_plan_time AS total_time
	FROM pg_stat_statements
	ORDER BY total_time DESC
	LIMIT 5;
metrics:
	- query:
		usage: "LABEL"
		description: "SQL query text"
	- calls:
		usage: "COUNTER"
		description: "Number of executions"
	- total_time:
		usage: "GAUGE"
		description: "Total execution + plan time"

# 2. Top users by total execution time
top_sql_users:
query: >
	SELECT usename, SUM(total_exec_time + total_plan_time) AS total_exec_time
	FROM pg_stat_statements
	JOIN pg_user ON pg_stat_statements.userid = pg_user.usesysid
	GROUP BY usename
	ORDER BY total_exec_time DESC
	LIMIT 5;
metrics:
	- usename:
		usage: "LABEL"
		description: "PostgreSQL user"
	- total_exec_time:
		usage: "GAUGE"
		description: "Total execution time by user"

# 3. Top client IPs by connection count
top_clients:
query: >
	SELECT client_addr, COUNT(*) AS connections
	FROM pg_stat_activity
	WHERE client_addr IS NOT NULL
	GROUP BY client_addr
	ORDER BY connections DESC
	LIMIT 5;
metrics:
	- client_addr:
		usage: "LABEL"
		description: "Client IP address"
	- connections:
		usage: "GAUGE"
		description: "Number of active connections"

# 4. Currently running queries by user and IP
active_queries_by_user_ip:
query: >
	SELECT usename, client_addr, COUNT(*) as active_queries
	FROM pg_stat_activity
	WHERE state = 'active'
	GROUP BY usename, client_addr;
metrics:
	- usename:
		usage: "LABEL"
		description: "User running queries"
	- client_addr:
		usage: "LABEL"
		description: "Client IP address"
	- active_queries:
		usage: "GAUGE"
		description: "Number of currently active queries"

# 5. Query frequency (most called)
frequent_sql:
query: >
	SELECT query, calls
	FROM pg_stat_statements
	ORDER BY calls DESC
	LIMIT 5;
metrics:
	- query:
		usage: "LABEL"
		description: "SQL query text"
	- calls:
		usage: "COUNTER"
		description: "Number of executions"

# 6. Table bloat statistics
table_bloat:
query: >
	SELECT
	current_database() AS current_db,
	schemaname AS schema_name,
	tblname AS table_name,
	(bs * tblpages) / 1024 AS actual_table_size_kb,
	((tblpages - est_tblpages) * bs) / 1024 AS wasted_space_kb,
	CASE
		WHEN tblpages > 0 AND tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages) / tblpages::float
		ELSE 0
	END AS wasted_space_percent,
	fillfactor AS table_fill_factor,
	CASE
		WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff) * bs / 1024
		ELSE 0
	END AS bloat_size_kb,
	CASE
		WHEN tblpages > 0 AND tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float
		ELSE 0
	END AS bloat_size_percent
	FROM (
	SELECT
		ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages,
		ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff,
		tblpages,
		fillfactor,
		bs,
		schemaname,
		tblname
	FROM (
		SELECT
		(4 + tpl_hdr_size + tpl_data_size + (2 * ma)
		- CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END
		- CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END
		) AS tpl_size,
		(heappages + toastpages) AS tblpages,
		reltuples,
		toasttuples,
		bs,
		page_hdr,
		schemaname,
		tblname,
		fillfactor,
		ma,
		tpl_hdr_size,
		tpl_data_size
		FROM (
		SELECT
			ns.nspname AS schemaname,
			tbl.relname AS tblname,
			tbl.reltuples,
			tbl.relpages AS heappages,
			COALESCE(toast.relpages, 0) AS toastpages,
			COALESCE(toast.reltuples, 0) AS toasttuples,
			COALESCE(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
			current_setting('block_size')::numeric AS bs,
			CASE
			WHEN version() ~ 'mingw32|64-bit|x86_64|ppc64|ia64|amd64' THEN 8
			ELSE 4
			END AS ma,
			24 AS page_hdr,
			23 +
			CASE WHEN MAX(COALESCE(s.null_frac, 0)) > 0 THEN (7 + count(s.attname)) / 8 ELSE 0 END +
			CASE WHEN bool_or(att.attname = 'oid' AND att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
			SUM((1 - COALESCE(s.null_frac, 0)) * COALESCE(s.avg_width, 0)) AS tpl_data_size
		FROM pg_attribute AS att
		JOIN pg_class AS tbl ON att.attrelid = tbl.oid
		JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
		LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname AND s.tablename = tbl.relname AND s.attname = att.attname
		LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
		WHERE NOT att.attisdropped
			AND tbl.relkind IN ('r', 'm')
		GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
		) AS inner_subquery
	) AS mid_subquery
	) AS bloat_subquery
	WHERE schemaname = 'public'
	ORDER BY schema_name, table_name;
metrics:
	- schema_name:
		usage: "LABEL"
		description: "Schema name"
	- table_name:
		usage: "LABEL"
		description: "Table name"
	- bloat_size_kb:
		usage: "GAUGE"
		description: "Bloat size in KB"
	- bloat_size_percent:
		usage: "GAUGE"
		description: "Bloat size as a percentage"

Impostare la variabile di ambiente dell'origine dati.
```
export DATA_SOURCE_NAME="postgresql://postgres_exporter:<password>@<POSTGRES_HOST>:5432/postgres?sslmode=require"
```
Nota: <POSTGRES_HOST> è il nome FQDN dell'endpoint primario. È possibile trovarlo nella pagina dei dettagli del cluster PostgreSQL in OCI Console.
Avviare PostgreSQL Exporter.

Eseguire PostgreSQL Exporter con il file queries.yaml personalizzato utilizzando il comando seguente. Viene avviato l'esportatore sulla porta 9187 e viene registrato l'output in exporter.log.
```
nohup /usr/local/bin/postgres_exporter --extend.query-path=/home/opc/queries.yaml --web.listen-address=":9187" > exporter.log 2>&1 &
```
Nota: per mantenere gli elementi in esecuzione dopo il riavvio, considerare la possibilità di impostare il servizio utilizzando systemd
Verificare l'esportatore.
```
curl http://localhost:9187/metrics
```

Task 3: Installazione e configurazione di Prometheus

Installa Prometheus.

wget https://github.com/prometheus/prometheus/releases/download/v2.52.0/prometheus-2.52.0.linux-amd64.tar.gz

tar -xvf prometheus-2.52.0.linux-amd64.tar.gz

sudo mv prometheus-2.52.0.linux-amd64/prometheus /usr/local/bin/
sudo mv prometheus-2.52.0.linux-amd64/promtool /usr/local/bin/

Crea configurazione Prometheus.

Creare un file /etc/prometheus/prometheus.yml con il contenuto seguente.

global:
scrape_interval: 15s

scrape_configs:
- job_name: 'postgres_exporter'
	static_configs:
	- targets: ['127.0.0.1:9187']

Avvia Prometheus.
```
nohup prometheus --config.file=/etc/prometheus/prometheus.yml > prometheus.log 2>&1 &
```
Nota: per mantenere gli elementi in esecuzione dopo il riavvio, considerare la possibilità di impostare il servizio utilizzando systemd
Verifica destinazioni.

Visita qui: http://<instance_ip>:9090/targets.

Task 4: Installazione e configurazione di Grafana

Installa Grafana.

sudo yum install -y https://dl.grafana.com/oss/release/grafana-10.4.2-1.x86_64.rpm
sudo systemctl enable grafana-server
sudo systemctl start grafana-server

Esegui il login a Grafana.

Andare all'URL seguente: http://<instance_ip>:3000 e immettere Nome utente come admin e Password come admin.
Aggiungere l'origine dati in Grafana.
1. Andare a Connessioni, Origini dati e fare clic su Aggiungi origine dati.
2. Selezionare Prometheus.
3. Immettere URL come http://localhost:9090.
Importa dashboard PostgreSQL.
1. Selezionare Importa un dashboard.
2. Importare l'ID dashboard 9628 dai dashboard Grafana.

Task 5: Esplora OCI Database con le metriche PostgreSQL in Grafana

Le immagini seguenti mostrano i dashboard finali.

Esempio di dashboard

Visualizzare le metriche esposte dal file queries.yaml personalizzato dalla scheda Esplora in Grafana.
Aggiungi visualizzazioni per:
- Prime istruzioni SQL più lente.
- Utenti principali.
- Query attive.
- Primi IP client.
- Frequenza di query (query più chiamate).

Task 6: Impostazione dell'avviso per il database OCI con metriche di blocco PostgreSQL in Grafana

Raccolta metrica blocco.
- Una query SQL personalizzata è stata utilizzata per stimare il blocco delle tabelle in base alle viste del catalogo di sistema PostgreSQL, ad esempio pg_class, pg_namespace, pg_attribute e pg_stats.
- Questa query è stata esposta come metrica personalizzata tramite un esportatore PostgreSQL aggiungendola al file queries.yaml, come mostrato nel task 2.2, e successivamente è stata raschiata da Prometheus.
Configura regola di avviso.
1. Andare a Avviso e fare clic su Regole avviso in Grafana.
2. Fare clic su + Nuova regola di avviso.
3. Immettere un nome per la regola dell'avviso.
4. Selezionare Prometheus come Origine dati.
5. Nella sezione Definisci condizione query e avviso, immettere le informazioni riportate di seguito.
  - Cercare la metrica table_bloat_bloat_size_percent.
  - Nella Costruzione guidata Condizione di avviso immettere le seguenti informazioni.
    - Imposta input: selezionare A.
    - Condizione: selezionare IS ABOVE 30.
    Nota: è possibile fare clic su Anteprima per verificare se l'avviso verrà attivato nelle condizioni correnti.
6. Nella sezione Imposta comportamento valutazione immettere le seguenti informazioni.
  1. Fare clic su + Nuovo gruppo di valutazione.
  2. Gruppo di valutazione: inserire postgres-bloat-alerts.
  3. Intervallo di valutazione: selezionare 5m (definisce la frequenza con cui Grafana valuta la regola di avviso).
  4. Periodo in sospeso: impostare su 5m (l'avviso deve rimanere true per 5 minuti continui prima che venga attivato). Deve essere uguale o superiore all'intervallo di valutazione).
7. Fare clic su Salva regola ed esci per attivare l'avviso.
Configura avvisi e-mail tramite SMTP (Gmail).

Per abilitare le notifiche di posta elettronica per gli avvisi in Grafana, configurare la sezione smtp del file grafana.ini.
1. Modificare il file di configurazione Grafana nell'istanza di OCI Compute in cui è stato installato Grafana.
```
sudo nano /etc/grafana/grafana.ini
```
2. Annulla il commento e aggiorna la sezione smtp con il seguente contenuto.
```
[smtp]
enabled = true
host = smtp.gmail.com:587
user = your_email@gmail.com
password = ***************
skip_verify = true
from_address = your_email@gmail.com
from_name = Grafana
ehlo_identity = localhost
startTLS_policy = OpportunisticStartTLS
enable_tracing = false
```
  Nota: per utilizzare Gmail, è necessario abilitare la verifica a 2 fasi e generare una password dell'applicazione dalle impostazioni dell'account Google. Non utilizzare la normale password Gmail.
3. Dopo aver salvato la configurazione, riavviare Grafana per applicare le modifiche utilizzando il comando seguente.
```
sudo systemctl restart grafana-server
```
Impostazione dei punti di contatto per le notifiche di avviso.

I punti di contatto definiscono dove e come Grafana invia avvisi quando viene attivata una regola, ad esempio e-mail, Slack, webhook e così via.
1. Andare a Avviso e fare clic su Punti di contatto in Grafana.
2. Fare clic su + Nuovo punto di contatto per crearne uno oppure selezionarne e modificarne uno esistente.
3. Per configurare un punto di contatto e-mail, immettere le informazioni riportate di seguito.
  - Nome: immettere il nome del punto di contatto, ad esempio grafana-default-email.
  - Integrazione: selezionare Posta elettronica.
  - Indirizzi: immettere uno o più indirizzi e-mail.
4. Fare clic su Test per verificare la consegna delle e-mail.
5. Fare clic su Salva punto di contatto.
Configura criterio di notifica.

I criteri di notifica in Grafana controllano come e quando gli avvisi vengono instradati ai punti di contatto. Dopo aver impostato un punto di contatto, è necessario creare un criterio di notifica per connettere gli avvisi a tale punto di contatto.
1. Passare a Avviso e fare clic su Criteri di notifica in Grafana.
2. Fare clic su Modifica nel criterio predefinito o su + Nuovo criterio.
3. In Punto di contatto predefinito, selezionare il contatto (grafana-default-email).
4. In Raggruppa per è possibile raggruppare gli avvisi in base ai valori delle etichette, ad esempio:
  - grafana_folder
  - alertname
5. Fare clic su Opzioni tempificazione e immettere le informazioni riportate di seguito.
  - Attesa gruppo: selezionare 30s, questo è il momento di attendere prima di inviare il primo avviso in un gruppo.
  - Intervallo di gruppo: selezionare 5m, ovvero il periodo di tempo tra batch di avvisi raggruppati.
  - Intervallo ripetizione: selezionare 30m, ovvero il periodo di tempo dopo il quale lo stesso avviso viene inviato nuovamente se viene ancora attivato.
6. Fare clic su Aggiorna criterio predefinito per salvare.
Attivazione e verifica degli avvisi.
1. Simulare una violazione della soglia (facoltativo).
  
  Per eseguire il test dell'attivazione degli avvisi, è possibile effettuare una delle seguenti operazioni:
  - Ridurre temporaneamente la soglia di avviso (ad esempio, impostare bloat_size_percent > 0).
    
    oppure
  - Introdurre il blocco eseguendo ripetute operazioni UPDATE o DELETE su una tabella di test di grandi dimensioni.
2. Attendere la valutazione.
  
  Grafana controlla le condizioni di avviso in base all'intervallo di valutazione definito (ad esempio, ogni 5 minuti). Se la condizione è vera per il periodo in sospeso (ad esempio, 5 minuti), l'avviso verrà attivato.
3. Verifica consegna e-mail.
  
  Quando viene attivato, l'avviso verrà inviato tramite il punto di contatto SMTP configurato (ad esempio, Gmail). Selezionare la posta in arrivo dell'indirizzo del destinatario per un messaggio di posta elettronica di notifica da Grafana.
4. Feedback interfaccia utente Grafana.
  
  Andare a Avviso e fare clic su Regole di avviso per visualizzare lo stato dell'avviso. Nella pagina Regole di avviso verranno visualizzati indicatori di stato come Assunzione.

Conferme

Autore: Irine Benoy (Open-source Data Black Belt)

Altre risorse di apprendimento

Esplora altri laboratori su docs.oracle.com/learn o accedi a più contenuti di formazione gratuiti sul canale YouTube di Oracle Learning. Inoltre, visitare education.oracle.com/learning-explorer per diventare Oracle Learning Explorer.

Per la documentazione del prodotto, visitare Oracle Help Center.

Titolo e informazioni sul copyright

Monitor an OCI Database with PostgreSQL using Prometheus and Grafana

G39868-01