LLI Wiki — My Digital Knowledge Base

Collecter des métriques par base pour décider du placement dans les pools élastiques

Quoi mesurer, d'où le lire (DMV, Azure Monitor, Log Analytics), comment l'historiser, noter une base et tenir compte de la saisonnalité.

Déplacer une base d'un pool élastique à un autre est facile. Savoir quelle base déplacer, et où, est la vraie difficulté, et la réponse ne vaut que ce que valent les chiffres. Cet article traite de la mesure : ce que je collecte par base, d'où ça vient, comment je le stocke et comment j'en fais un score. Le déplacement lui-même est traité dans les deux articles suivants.

Flux de données

flowchart LR
    A["Métriques Azure Monitor (par base et par pool)"] --> B["Log Analytics (AzureMetrics)"]
    C["Vue de gestion du pool dans master"] --> D["Contrôles ponctuels"]
    B --> E["Job nocturne (KQL, un jour à la fois)"]
    E --> F["Table d'historique"]
    F --> G["Procédure de scoring"]
    G --> H["Table de scores (par base et par jour)"]
    H --> I["Suggestions de déplacement"]

Quoi collecter

Un pool est un budget partagé : une base compte par la part du budget du pool qu'elle consomme. Voici les signaux que je garde :

Signal Pourquoi Métrique source
CPU Première limite atteinte sur la plupart des pools cpu_percent, cpu_used (vCore)
IO données Pression en lecture, charges à gros scans physical_data_read_percent
Écriture du journal Les traitements lourds en écriture saturent vite log_write_percent
Workers / sessions Beaucoup de requêtes concurrentes, même à CPU faible workers_percent, sessions_percent
Stockage Limite dure du pool, pas de burst possible storage, storage_percent
Part du pool Ramène tout à « ce que cette base coûte à ses voisines » calculée

Les pourcentages d'une base sont relatifs à la limite par base, identique pour toutes les bases d'un pool. Ils sont donc comparables dans un pool, mais pas entre pools de tailles différentes. Je convertis donc toujours en part du pool : vCores utilisés divisés par les vCores du pool, ou pourcentage du pool réparti proportionnellement aux pourcentages des bases.

Où le lire

Contrôle ponctuel d'un pool depuis master :

SELECT TOP (12) end_time, elastic_pool_name,
       avg_cpu_percent, avg_data_io_percent, avg_log_write_percent,
       max_worker_percent, avg_storage_percent
FROM sys.elastic_pool_resource_stats
ORDER BY end_time DESC;

Collecte nocturne en KQL

Chaque nuit, un job récupère la veille, heure par heure, et l'écrit dans une table d'historique. Je garde le maximum et la moyenne par heure, car la moyenne seule cache les pics.

let day = startofday(ago(1d));
AzureMetrics
| where TimeGenerated >= day and TimeGenerated < day + 1d
| where ResourceProvider =~ "MICROSOFT.SQL"
| where MetricName in ("cpu_percent", "log_write_percent", "physical_data_read_percent", "workers_percent")
| extend rid = tolower(_ResourceId)
| where rid has "/databases/"
| extend Server = extract(@"servers/([^/]+)", 1, rid), Db = extract(@"databases/([^/]+)$", 1, rid)
| where Db != "master"
| summarize HourAvg = avg(Average), HourMax = max(Maximum) by bin(TimeGenerated, 1h), Server, Db, MetricName

Avec la géo-réplication, le même nom de base apparaît sur deux serveurs. Je résume d'abord par serveur, puis je garde la copie la plus chargée par heure : c'est le primaire, celui qui coûte de la capacité.

La table d'historique

CREATE TABLE dbo.DbMetricHistory (
    MetricDate  date          NOT NULL,
    HourUtc     tinyint       NOT NULL,
    DbName      sysname       NOT NULL,
    PoolName    sysname       NULL,
    MetricName  varchar(40)   NOT NULL,
    HourAvg     decimal(9,3)  NULL,
    HourMax     decimal(9,3)  NULL,
    CONSTRAINT PK_DbMetricHistory PRIMARY KEY (MetricDate, HourUtc, DbName, MetricName)
);

Deux choix comptent. Le job est idempotent : il supprime le jour qu'il va écrire, donc un rejeu ne crée pas de doublons. Et il purge les anciennes lignes (environ deux mois suffisent), car la table grossit de milliers de lignes par jour.

Noter une base

Un score ramène plusieurs métriques à un seul nombre comparable. Le mien est une somme pondérée de signaux normalisés, calculés sur le maximum journalier :

SELECT DbName, MetricDate,
       0.50 * cpu_p95 + 0.20 * io_p95 + 0.15 * log_p95 + 0.15 * workers_p95 AS Score
FROM (
    SELECT DbName, MetricDate,
           MAX(CASE WHEN MetricName='cpu_percent' THEN HourMax END)                 AS cpu_p95,
           MAX(CASE WHEN MetricName='physical_data_read_percent' THEN HourMax END)  AS io_p95,
           MAX(CASE WHEN MetricName='log_write_percent' THEN HourMax END)           AS log_p95,
           MAX(CASE WHEN MetricName='workers_percent' THEN HourMax END)             AS workers_p95
    FROM dbo.DbMetricHistory
    GROUP BY DbName, MetricDate
) t;

Les poids sont ce que je remets en cause. Nos pools saturaient en CPU, mais la CPU était sous-pondérée : le score composite était surtout de l'IO et classait mal les pools par rapport à la réalité. Je l'ai vérifié en comparant le classement des pools par score à leur classement par CPU réelle (corrélation de Spearman), puis j'ai changé les poids, recalculé tout l'historique et constaté l'amélioration. Une grille de scoring est une hypothèse : on la teste contre la vérité au niveau du pool.

Saisonnalité

Une semaine de données est un piège. La clôture mensuelle, les batchs de nuit et les jobs hebdomadaires créent des pics qu'une fenêtre courte rate. Ce qui a aidé :

Pièges

Mastodon LLIMastodon SocialFosstodon