Surveiller une base de données Oracle Cloud Infrastructure Database avec PostgreSQL à l'aide de Prometheus et Grafana

Présentation

PostgreSQL est l'une des bases de données les plus populaires parmi les développeurs d'applications. Au-delà de l'élimination des frais généraux de gestion qu'Oracle Cloud Infrastructure Database avec PostgreSQL (OCI Database with PostgreSQL) offre, elle va également plus loin lorsqu'il s'agit de mettre en oeuvre une facilité de surveillance. Prometheus et Grafana sont une paire très populaire lorsqu'il s'agit de surveiller les performances des applications pour le stockage et la visualisation de données de série chronologique, respectivement.

Dans ce tutoriel, nous allons démontrer comment surveiller une base de données OCI Database with PostgreSQL en extrayant des mesures au niveau de l'interrogation à l'aide de l'exportateur PostgreSQL, en les grattant avec Prometheus, en les visualisant dans Grafana et en configurant des alertes pour les mesures bloat.

Aperçu de l'architecture

Le schéma suivant décrit l'approche globale. Les composants suivants sont utilisés :

Service OCI Database with PostgreSQL.
Exportateur Postgres pour exposer les mesures.
Prometheus pour gratter les mesures.
Grafana pour visualiser les mesures.

Vue d'architecture

Note : Bien que l'architecture affiche trois sous-réseaux pour plus de clarté, ce tutoriel n'en utilise que deux et l'hôte de saut est également la machine virtuelle utilisée pour déployer Prometheus et Grafana.

Objectifs

Extraire les mesures PostgreSQL avec l'exportateur PostgreSQL pour Prometheus.
Ingérez ces métriques dans Prometheus.
Visualisez les mesures à l'aide des tableaux de bord Grafana.
Configurez des alertes pour détecter le gonflement des tables à l'aide des mesures exposées au moyen de l'exportateur PostgreSQL.

Conditions requises

Accès à une location OCI avec une base de données OCI Database with PostgreSQL provisionnée avec une grappe PostgreSQL. Pour plus d'informations, voir Se connecter à Oracle Cloud Infrastructure Database avec PostgreSQL à l'aide de DBeaver.
Instance de calcul OCI (hôte de saut) dans un sous-réseau public du même VCN que la base de données OCI Database with PostgreSQL.
Liste de sécurité OCI : Autorisez les ports entrants 9090, 9187, 3000.

Exécutez les commandes suivantes pour configurer le pare-feu sur l'instance.

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

Tâche 1 : Activer la surveillance des extensions sur OCI Database with PostgreSQL

Activez l'extension pg_stat_statements.

Ajoutez pg_stat_statements dans la base de données OCI Database with PostgreSQL au moyen de la console OCI. Pour plus d'informations, voir Activation des extensions pour une base de données.
Créer un utilisateur du service de surveillance.

Exécutez les interrogations suivantes pour créer et connecter l'utilisateur à l'aide de DBeaver par l'intermédiaire de l'hôte de saut.
```
CREATE USER postgres_exporter WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor TO postgres_exporter;
```
Note : L'extension pg_stat_io est disponible à partir de OCI Database with PostgreSQL v16.

Tâche 2 : Installer l'exportateur PostgreSQL pour Prometheus

Note : Exécutez les commandes suivantes sur votre instance de calcul OCI.

Installer l'exportateur PostgreSQL.

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/

Créez queries.yaml personnalisé.

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"

Définir la variable d'environnement de la source de données.
```
export DATA_SOURCE_NAME="postgresql://postgres_exporter:<password>@<POSTGRES_HOST>:5432/postgres?sslmode=require"
```
Note : <POSTGRES_HOST> est le nom de domaine complet du point d'extrémité principal. Vous pouvez le trouver dans la page des détails de la grappe PostgreSQL dans la console OCI.
Démarrez l'exportateur PostgreSQL.

Exécutez l'exportateur PostgreSQL avec le fichier queries.yaml personnalisé à l'aide de la commande suivante. Cela démarre l'exportateur sur le port 9187 et enregistre la sortie dans exporter.log.
```
nohup /usr/local/bin/postgres_exporter --extend.query-path=/home/opc/queries.yaml --web.listen-address=":9187" > exporter.log 2>&1 &
```
Note : Pour que les opérations s'exécutent après le redémarrage, envisagez de configurer le service à l'aide de systemd
Vérifier l'exportateur.
```
curl http://localhost:9187/metrics
```

Tâche 3 : Installer et configurer Prometheus

Installer 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/

Créer la configuration Prometheus.

Créez le fichier /etc/prometheus/prometheus.yml avec le contenu suivant.

global:
scrape_interval: 15s

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

Démarrer Prometheus.
```
nohup prometheus --config.file=/etc/prometheus/prometheus.yml > prometheus.log 2>&1 &
```
Note : Pour que les opérations s'exécutent après le redémarrage, envisagez de configurer le service à l'aide de systemd
Vérifier les cibles.

Visitez ici : http://<instance_ip>:9090/targets.

Tâche 4 : Installer et configurer Grafana

Installer 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

Connectez-vous à Grafana.

Allez à l'URL suivante : http://<instance_ip>:3000 et entrez Nom d'utilisateur comme admin et Mot de passe comme admin.
Ajouter la source de données dans Grafana.
1. Allez à Connexions, Sources de données et cliquez sur Ajouter une source de données.
2. Sélectionnez Prometheus.
3. Entrez URL comme http://localhost:9090.
Importer le tableau de bord PostgreSQL.
1. Sélectionnez Importer un tableau de bord.
2. Importer l'ID tableau de bord 9628 à partir des tableaux de bord Grafana.

Tâche 5 : Explorer les mesures de la base de données OCI Database with PostgreSQL dans Grafana

Les images suivantes montrent les tableaux de bord finaux.

Exemple de tableau de bord

Consultez les mesures exposées à partir de votre fichier queries.yaml personnalisé dans l'onglet Explorer de Grafana.
Ajouter des visualisations pour :
- Principales instructions SQL les plus lentes.
- Meilleurs utilisateurs.
- Interrogations actives.
- Principales adresses IP de client.
- Fréquence des interrogations (les plus appelées interrogations).

Tâche 6 : Configurer l'alerte pour la base de données OCI Database with PostgreSQL dans Grafana

Collecter la mesure Bloat.
- Une interrogation SQL personnalisée a été utilisée pour estimer la saturation de la table, en fonction des vues du catalogue système PostgreSQL telles que pg_class, pg_namespace, pg_attribute et pg_stats.
- Cette interrogation a été exposée en tant que mesure personnalisée au moyen d'un exportateur PostgreSQL en l'ajoutant au fichier queries.yaml, comme indiqué dans la tâche 2.2, et a ensuite été balayée par Prometheus.
Configurer une règle d'alerte.
1. Naviguez jusqu'à Alerte et cliquez sur Règles d'alerte dans Grafana.
2. Cliquez sur + Nouvelle règle d'alerte.
3. Entrez un nom pour votre règle d'alerte.
4. Sélectionnez Prometheus comme source de données.
5. Dans la section Définir une interrogation et une condition d'alerte, entrez les informations suivantes.
  - Recherchez la mesure table_bloat_bloat_size_percent.
  - Dans le générateur de condition d'alerte, entrez les informations suivantes.
    - Définir l'entrée : Sélectionnez A.
    - Condition : Sélectionnez IS ABOVE 30.
    Note : Vous pouvez cliquer sur Prévisualiser pour vérifier si l'alerte est déclenchée dans les conditions courantes.
6. Dans la section Définir le comportement de l'évaluation, entrez les informations suivantes.
  1. Cliquez sur + Nouveau groupe d'évaluation.
  2. Groupe d'évaluations : Entrez postgres-bloat-alerts.
  3. Intervalle d'évaluation : Sélectionnez 5m (qui définit la fréquence à laquelle Grafana évalue la règle d'alerte).
  4. Période en attente : Réglez à 5m (l'alerte doit rester vraie pendant 5 minutes continues avant qu'elle ne se déclenche. Cette valeur doit être égale ou supérieure à l'intervalle d'évaluation).
7. Cliquez sur Enregistrer la règle et quitter pour activer votre alerte.
Configurer l'alerte par courriel au moyen de SMTP (Gmail).

Pour activer les avis par courriel pour les alertes dans Grafana, configurez la section smtp de votre fichier grafana.ini.
1. Modifiez le fichier de configuration Grafana dans l'instance de calcul OCI où vous avez installé Grafana.
```
sudo nano /etc/grafana/grafana.ini
```
2. Annulez la mise en commentaire et mettez à jour la section smtp avec le contenu suivant.
```
[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
```
  Note : Pour utiliser Gmail, vous devez activer la vérification en 2 étapes et générer un mot de passe d'application à partir des paramètres de votre compte Google. N'utilisez pas votre mot de passe Gmail habituel.
3. Après avoir enregistré la configuration, redémarrez Grafana pour appliquer les modifications à l'aide de la commande suivante.
```
sudo systemctl restart grafana-server
```
Configurer des points de contact pour les avis d'alerte.

Les points de contact définissent où et comment Grafana envoie des alertes lorsqu'une règle est déclenchée, comme le courriel, Slack, le webhook, etc.
1. Naviguez jusqu'à Alerte et cliquez sur Points de contact dans Grafana.
2. Cliquez sur + Nouveau point de contact pour en créer un, ou sélectionnez et modifiez un point existant.
3. Pour configurer un point de contact par courriel, entrez les informations suivantes.
  - Nom : Entrez le nom du point de contact (par exemple, grafana-default-email).
  - Intégration : Sélectionnez Courriel.
  - Adresses : Entrez une ou plusieurs adresses de courriel.
4. Cliquez sur Tester pour vérifier la transmission de messages.
5. Cliquez sur Enregistrer le point de contact.
Configurer une politique d'avis.

Les politiques de notification dans Grafana contrôlent comment et quand les alertes sont acheminées vers les points de contact. Après avoir configuré un point de contact, vous devez créer une politique de notification pour connecter des alertes à ce point de contact.
1. Naviguez jusqu'à Alerte et cliquez sur Politiques d'avis dans Grafana.
2. Cliquez sur Modifier dans la politique par défaut ou sur + Nouvelle politique.
3. Sous Point de contact par défaut, sélectionnez votre contact (grafana-default-email).
4. Sous Regrouper par, vous pouvez regrouper les alertes en fonction de valeurs d'étiquette telles que :
  - grafana_folder
  - alertname
5. Cliquez sur Options de programmation et entrez les informations suivantes.
  - Attente de groupe : Sélectionnez 30s, c'est le temps d'attente avant l'envoi de la première alerte d'un groupe.
  - Intervalle de groupe : Sélectionnez 5m, c'est le temps entre les lots d'alertes regroupées.
  - Intervalle de répétition : Sélectionnez 30m, c'est l'heure après laquelle la même alerte est renvoyée si elle se déclenche toujours.
6. Cliquez sur Mettre à jour la politique par défaut pour enregistrer.
Déclencher et vérifier l'alerte.
1. Simuler une violation de seuil (facultatif).
  
  Pour tester le déclenchement d'alerte, vous pouvez :
  - Réduisez temporairement le seuil d'alerte (par exemple, définissez bloat_size_percent > 0).
    
    Ou,
  - Introduisez des ballonnements en effectuant des opérations UPDATE ou DELETE répétées sur une grande table de test.
2. Attendez l'évaluation.
  
  Grafana vérifie les conditions d'alerte à l'intervalle d'évaluation défini (par exemple, toutes les 5 minutes). Si la condition est vraie pour la période en attente (par exemple, 5 minutes), l'alerte se déclenche.
3. Vérification du service de transmission de messages.
  
  Lorsqu'elle est déclenchée, l'alerte est envoyée par le point de contact SMTP configuré (par exemple, Gmail). Vérifiez la boîte de réception de l'adresse du destinataire pour un courriel de notification envoyé par Grafana.
4. Rétroaction sur l'interface utilisateur Grafana.
  
  Naviguez jusqu'à Alerte et cliquez sur Règles d'alerte pour voir le statut de l'alerte. Vous verrez des indicateurs de statut tels que Démarrage dans la page Règles d'alerte.

Remerciements

Auteur - Irine Benoy (Ceinture noire de données à code source libre)

Ressources d'apprentissage supplémentaires

Explorez d'autres laboratoires sur le site docs.oracle.com/learn ou accédez à plus de contenu d'apprentissage gratuit sur le canal Oracle Learning YouTube. De plus, visitez education.oracle.com/learning-explorer pour devenir un explorateur Oracle Learning.

Pour obtenir la documentation sur le produit, visitez Oracle Help Center.

Informations sur le titre et les droits d'auteur

Monitor an OCI Database with PostgreSQL using Prometheus and Grafana

G39866-01