Statistiques SQL avec Grafana, InfluxDB et Telegraf

Introduction

Les performances des bases de données sont devenues essentielles à la réussite des environnements applicatifs modernes. Il est donc indispensable d'identifier et d'optimiser les requêtes SQL les plus exigeantes en ressources afin de garantir une expérience utilisateur fluide et la stabilité des applications. 

Cet article présente une approche rapide pour analyser les statistiques d'exécution des requêtes SQL sur une instance InterSystems IRIS afin d'identifier les domaines à optimiser au sein d'une macro-application.

Au lieu de nous concentrer sur la surveillance en temps réel, nous allons mettre en place un système qui collecte et analyse les statistiques précalculées par IRIS une fois par heure.  Cette approche, bien qu'elle ne permette pas de surveillance instantanée, offre un excellent compromis entre la richesse des données disponibles et la simplicité de mise en œuvre. 

Nous utiliserons Grafana pour la visualisation et l'analyse des données, InfluxDB pour le stockage des séries chronologiques et Telegraf pour la collecte des métriques.  Ces outils, reconnus pour leur puissance et leur flexibilité, nous permettront d'obtenir un aperçu clair et exploitable.

Plus précisément, nous détaillerons la configuration de Telegraf pour récupérer les statistiques. Nous configurerons également l'intégration avec InfluxDB pour le stockage et l'analyse des données, et créerons des tableaux de bord personnalisés dans Grafana. Cela nous aidera à identifier rapidement les requêtes nécessitant une attention particulière.

Pour faciliter l'orchestration et le déploiement de ces différents composants, nous utiliserons Docker.

Conditions préalables

Avant de commencer, assurez-vous d'avoir les éléments suivant:

• Git: Git est nécessaire pour cloner le référentiel du projet contenant les fichiers de configuration et les scripts.
• Docker ou Docker Desktop: Docker peut être utilisé pour conteneuriser les applications InfluxDB, Telegraf et Grafana, ce qui facilite leur déploiement et leur gestion.
• Instance InterSystems IRIS:Au moins la version 2022.1, idéalement 2023.1 ou supérieure, avec le package sql-stats-api installé.  Ce package est essentiel pour exposer les statistiques SQL IRIS et permettre à Telegraf de les collecter. [Lien vers OpenExchange]

Bien que nos fichiers docker-compose incluent une instance IRIS, celle-ci ne contiendra aucune donnée statistique SQL, car elle vient d'être créée et démarrée. Elle ne constituera donc pas un choix pratique pour tester le système. C'est pourquoi nous vous recommandons vivement de disposer d'une autre instance IRIS "active" (celle qui contient l'historique des requêtes SQL) afin de pouvoir visualiser des données réelles et tester l'outil d'analyse.

À propos des statistiques

IRIS collecte des statistiques d'exécution des requêtes SQL à une granularité horaire et quotidienne. Les statistiques horaires identifient les variations de performances tout au long de la journée, tandis que les statistiques quotidiennes fournissent une vue d'ensemble de l'activité de la base de données.

Vous trouverez ci-dessous les données que nous collectons pour chaque requête SQL:

• Nombre d'exécutions: indique le nombre de fois où la requête a été exécutée.
• Durée totale d'exécution: mesure la durée totale d'exécution de la requête.
• Variance des temps d'exécution: utilisée pour identifier les variations de performances et les problèmes ponctuels.
• Nombre total de lignes renvoyées (RowCount): disponible pour IRIS 2023.1 et versions ultérieures, cette métrique indique le nombre total de lignes renvoyées par la requête. Elle peut vous aider à identifier les requêtes exigeantes en ressources.
• Nombre total de commandes exécutées: également disponible pour IRIS 2023.1 et versions ultérieures, cette métrique facilite une analyse plus détaillée de l'activité de la base de données et identifie les requêtes qui pourraient être optimisées en réduisant le nombre d'opérations.

Ces informations sont accessibles via les tables suivantes:

• INFORMATION_SCHEMA.STATEMENT_DAILY_STATS
• INFORMATION_SCHEMA.STATEMENT_HOURLY_STATS

Ces tables sont disponibles depuis IRIS 2022.1. Vous trouverez ci-dessous un exemple de requête SQL permettant de récupérer des statistiques:

SELECT ds.* 
FROM INFORMATION_SCHEMA.STATEMENT_DAILY_STATS ds
INNER JOIN INFORMATION_SCHEMA.STATEMENTS st On ds.Statement = st.Hash


SELECT DATEADD('hh',"Hour",$PIECE(hs."Day",'||',2)) As DateTime, hs.*
FROM INFORMATION_SCHEMA.STATEMENT_HOURLY_STATS hs
INNER JOIN INFORMATION_SCHEMA.STATEMENTS st On $PIECE(hs."Day",'||',1) = st.Hash

Pour les versions plus anciennes que IRIS 2022.1, je recommande vivement l'article de David Loveluck, qui explique comment récupérer des statistiques similaires.

Architecture

Le projet repose sur l'interaction de quatre composants clés : IRIS, Grafana, InfluxDB et Telegraf. Le diagramme ci-dessous illustre l'architecture globale du système et le flux de données entre les différents composants:

• InterSystems IRIS: il s'agit de l'instance que nous utiliserons pour récupérer les statistiques.
• Package sql-stats-api: ce package ObjectScript expose les données statistiques IRIS via l'API REST. Il offre deux formats de sortie : JSON pour une utilisation générale et Line Protocol, un format optimisé pour l'ingestion rapide de données de séries chronologiques dans InfluxDB.
• Telegraf: il s'agit d'un agent de collecte de métriques qui fournit le lien entre IRIS et InfluxDB. Dans ce projet, nous utiliserons deux instances de Telegraf:
  • un agent interroge périodiquement l'API REST IRIS pour récupérer les statistiques SQL en temps réel.
  • Un autre agent fonctionne en mode "scan de répertoire". Il surveille un répertoire contenant les fichiers stockés et les transmet à InfluxDB, ce qui permet d'intégrer les données inaccessibles via l'API REST.
• InfluxDB:  cette base de données de séries chronologiques stocke et gère les statistiques SQL collectées par Telegraf, car son architecture est optimisée pour ce type de données. InfluxDB offre également une intégration native avec Grafana, ce qui facilite la visualisation et l'analyse des données. Nous avons préféré InfluxDB à Prometheus, car ce dernier est davantage axé sur la surveillance en temps réel et n'est pas bien adapté au stockage de données agrégées, telles que les sommes ou les moyennes horaire ou journalière, qui sont essentielles pour notre analyse.
• Grafana: il s'agit d'un outil de visualisation qui permet de créer des tableaux de bord personnalisés et interactifs pour analyser les performances SQL. Il récupère les données d'InfluxDB et offre une variété de graphiques et de widgets pour visualiser les statistiques de manière claire et exploitable.

Installation

Commencez par cloner le référentiel:

git clone https://github.com/lscalese/iris-sql-dashboard.git
cd irisiris-sql-dashboard

Configuration de l'environnement

Ce projet utilise Docker pour orchestrer Grafana, InfluxDB, Telegraf et IRIS. Pour des raisons de sécurité, les informations sensibles telles que les clés API et les mots de passe sont stockées dans un fichier .env.

Créez le fichier .env en utilisant l'exemple fourni ci-dessous:

cp .env.example .env

Éditez le fichier .env pour configurer les variables:

Configuration des variables

• TZ: fuseau horaire. Il est recommandé de modifier cette variable en fonction de votre fuseau horaire afin de garantir l'exactitude de l'horodatage des données.
• DOCKER_INFLUXDB_INIT_PASSWORD: mot de passe administrateur permettant d'accéder à InfluxDB.
• IRIS_USER: il s'agit d'un utilisateur IRIS de l'instance Docker IRIS (_system par défaut).
• IRIS_PASSWORD : Il s'agit du mot de passe de l'instance IRIS Docker (SYS par défaut).

Les clés API permettent les connexions suivantes:

• GRAFANA_INFLUX_API_KEY : Grafana <-> InfluxDB.
• TELEGRAF_INFLUX_API_KEY : Telegraf <-> InfluxDB.

Génération de clés API

Pour des raisons de sécurité, InfluxDB nécessite des clés API pour l'authentification et l'autorisation.  Ces clés sont utilisées pour identifier et autoriser divers composants (Telegraf, Grafana) à accéder à Influx DB.

Le script init-influxdb.sh, inclus dans le référentiel, facilite la génération de ces clés.  Il sera exécuté automatiquement lors du premier démarrage du conteneur infxludb2:

docker compose up -d influxdb2

Après quelques secondes, le fichier .env sera mis à jour avec vos clés API générées.

Remarque: cette étape ne doit être effectuée que lors du premier démarrage du conteneur.

Vérifiez si vous avez accès à l'interface d'administration InfluxDB via l'URL http://localhost:8086/

Connectez-vous avec le nom d'utilisateur "admin" et le mot de passe spécifiés dans la variable d'environnement "DOCKER_INFLUXDB_INIT_PASSWORD" dans le fichier ".env". Lorsque vous naviguez dans "Load Data >> Buckets”, vous devriez découvrir un bucket "IRIS_SQL_STATS" préconfiguré.   

En parcourant "Load Data >> API Tokens", vous devriez trouver nos deux clés API : "Grafana_IRIS_SQL_STATS" et "Telegraf_IRIS_SQL_STATS":

L'environnement est prêt maintenant, et nous pouvons passer à l'étape suivante!

Démarrage

L'environnement étant configuré et les clés API générées, vous pouvez enfin lancer l'ensemble de conteneurs. Pour ce faire, exécutez la commande suivante dans le répertoire racine du projet:

docker compose up -d

Cette commande lancera en arrière-plan tous les services définis dans le fichier docker-compose.yml : InfluxDB, Telegraf, Grafana et l'instance IRIS.

Tableau de bord Grafana

Grafana est désormais disponible à l'adresse http://localhost:3000.

Connexion à Grafana

Ouvrez votre navigateur Web et rendez-vous à l'adresse http://localhost:3000. Le nom d'utilisateur et le mot de passe par défaut sont admin/admin. Cependant, vous serez invité à modifier le mot de passe lors de votre première connexion.

Vérification de la source de données InfluxDB

La source de données InfluxDB est préconfigurée dans Grafana. Il vous suffit de vérifier son bon fonctionnement.

Accédez à “Connections > Data sources (sources de données)”.

Vous devriez voir une source de données nommée “influxdb”.Cliquez dessus pour la modifier.

Cliquez ensuite sur “Save & Test”. Le message “Datasource is working. 1 bucket found” (La source de données fonctionne. 1 bucket trouvé) devrait maintenant s'afficher à l'écran.

Exploration des tableaux de bord

À ce stade, vous avez vérifié que la communication entre Grafana et InfluxDB est établie, ce qui signifie que vous pouvez explorer les tableaux de bord prédéfinis.

Passez à “Dashboards” (tableaux de bord).

Vous trouverez deux tableaux de bord prédéfinis:

• InfluxDB - SQL Stats: ce tableau de bord affiche des statistiques générales sur l'exécution des requêtes SQL, par exemple le nombre d'exécutions, le temps total d'exécution et la variance du temps d'exécution.

• InfluxDB - SQL Stats Details: ce tableau de bord fournit des informations plus détaillées sur chaque requête SQL, par exemple le nombre total de lignes renvoyées ou de commandes exécutées.

Pourquoi les tableaux de bord sont vides

Si vous ouvrez les tableaux de bord, vous verrez qu'ils sont vides.  Cela s'explique par le fait que notre agent Telegraf est actuellement connecté à l'instance IRIS fournie dans le référentiel Docker, dont les tables ne contiennent aucune donnée statistique. Les statistiques SQL ne sont collectées que si l'instance IRIS est active et conserve un historique des requêtes SQL.

Dans la section suivante, nous allons voir comment injecter des données dans l'instance IRIS afin d'afficher les statistiques dans Grafana.

 

Telegraf

Le système de surveillance utilise deux agents Telegraf ayant des rôles spécifiques:

• telegraf-iris.conf:cet agent collecte des données en temps réel à partir d'une instance IRIS active. Il interroge l'API REST IRIS pour récupérer des statistiques SQL afin de les envoyer à InfluxDB.
• telegraf-directory-scan.conf: cet agent intègre les données historiques stockées dans des fichiers. Il surveille le répertoire ./telegraf/in/, lit les fichiers contenant des statistiques SQL et les envoie à InfluxDB.

Pour collecter des données en temps réel, il faut connecter Telegraf à une instance IRIS active sur laquelle le package sql-stats-api est installé.  Ce package expose les statistiques SQL via une API REST, ce qui permet à Telegraf d'y accéder.

Configuration de telegraf-iris.conf

Pour connecter Telegraf à votre instance IRIS, vous devez modifier le fichier ./telegraf/config/telegraf-iris.conf. Vous trouverez ci-dessous un exemple de configuration:

[[inputs.http]]
  ## Une ou plusieurs URL à partir desquelles lire les métriques formatées
  urls = [
    "http://iris:52773/csp/sqlstats/api/daily",
    "http://iris:52773/csp/sqlstats/api/hourly"
  ]
  ## Méthode HTTP
  method = "GET"

  ## En-têtes HTTP facultatifs
  headers = {"Accept" = "text/plain"}
  ## Informations d'identification pour l'authentification HTTP de base (facultatif)
  username = "${IRIS_USER}"
  password = "${IRIS_PASSWORD}"
  data_format = "influx"

Assurez-vous que${IRIS_USER} et ${IRIS_PASSWORD} sont correctement définis dans votre dossier .env file.
Remarque: Vous pouvez copier le fichier et modifier les paramètres pour connecter Telegraf à plusieurs instances IRIS.

Redémarrage de Telegraf:

Après avoir modifié le fichier de configuration, il est nécessaire de redémarrer le conteneur Telegraf pour que les modifications prennent effet:

docker compose up -d telegraf --force-recreate

Récupération des données historiques

Pour récupérer les statistiques SQL historiques, utilisez la méthode ObjectScript CreateInfluxFile sur votre instance IRIS:

; Adaptez le chemin à vos besoins
Set sc = ##class(dc.sqlstats.services.SQLStats).CreateInfluxFile("/home/irisowner/dev/influxdb-lines.txt",,1)

Ce script enregistre l'historique des statistiques SQL dans des fichiers texte dont la longueur maximale est de 10 000 lignes par fichier. Vous pouvez ensuite placer ces fichiers dans le répertoire ./telegraf/in/ afin de les traiter et de les injecter dans InfluxDB.

Vérification de l'injection des données

Vous pouvez vérifier que les données ont été correctement injectées dans InfluxDB à l'aide de l'interface Web. Accédez à "Data Explorer" (Explorateur de données) et vérifiez:

Visualisation des données dans Grafana

Une fois les données injectées, vous pouvez les afficher dans vos tableaux de bord Grafana fournis.

Nous sommes arrivés à la fin de notre article. J'espère qu'il vous a été utile et qu'il vous avez appris à configurer facilement un système de surveillance et d'analyse des statistiques SQL sur vos instances IRIS.

Comme vous l'avez peut-être constaté, cet article a mis l'accent sur les aspects pratiques de la configuration et de l'utilisation de divers outils. Nous n'avons pas étudié en détail le fonctionnement interne d'InfluxDB, du format Line Protocol ou du langage Flux Query, ni examiné la multitude de plugins disponibles pour Telegraf.

Ces sujets, aussi fascinants soient-ils, nécessiteraient un article beaucoup plus long.  Je vous encourage vivement à consulter la documentation officielle d'InfluxDB Get started with InfluxDB (Débuter avec InfluxDB) et Telegraf Plugin Directory (Répertoire des plugins) pour approfondir vos connaissances et découvrir toutes les possibilités offertes par ces outils.

N'hésitez pas à partager vos expériences dans les commentaires.

Merci d'avoir lu cet article, et à bientôt!