Tout un réseau de sites web a ralenti d'un coup, et pas un seul serveur n'était occupé.

Un réseau de sites d'offres d'emploi s'est ramolli au même instant. Pas une page, pas un site : tous, ensemble, cette brume à l'échelle de la ferme qui fait supposer à un opérateur que les serveurs se noient et qu'il est temps d'en acheter de plus gros. Cette supposition coûte cher, elle est fréquente, et dans ce cas elle était fausse. Chaque machine de la ferme s'ennuyait. Toute la ferme attendait la base de données, et la base attendait une seule requête.

Chaque niveau était inactif sauf un

Lors d'un ralentissement de toute une ferme, lisez le taux d'utilisation de chaque niveau avant toute autre chose, et voyez lequel est réellement saturé. Les load balancers tournaient à vide. Les serveurs web s'ennuyaient. Rien dans le chemin de la requête ne travaillait assez dur pour expliquer un site lent.

C'est l'indice qui clôt le débat sur la capacité avant qu'il ne commence. Si chaque niveau est inactif sauf la base de données, un serveur web plus gros vous achète une machine inactive plus rapide. La contrainte n'a jamais été le calcul, et aucune commande de matériel n'allait y remédier.

La réponse coûteuse était d'acheter plus de machines

Plus de matériel aurait mis le gaspillage à l'échelle, pas supprimé. La seule étape honnête était de découvrir à quoi la base de données passait réellement son temps.

Nous avons agrégé l'historique des requêtes par empreinte

Une application écrit rarement la même requête deux fois de la même façon. La même recherche apparaît paramétrée de plusieurs manières différentes, de sorte que compter le texte brut des requêtes cache le coupable dans le bruit. La solution est d'agréger par une empreinte qui survit à cette incohérence, puis de classer chaque instruction selon sa part du temps total de la base. Nous l'avons exécuté sur tout l'historique des requêtes.

-- reconstructed, de-identified rendering of cleared facts. not a real capture.
-- full query history, grouped by query_hash
-- (a fingerprint that survives inconsistent parameterisation)
SELECT TOP 5
  query_hash,
  SUM(execution_count)                AS calls,
  SUM(total_logical_reads)            AS pages_read,
  SUM(total_elapsed_time) * 100.0
    / SUM(SUM(total_elapsed_time)) OVER () AS pct_of_db_time
FROM   sys.dm_exec_query_stats
GROUP  BY query_hash
ORDER  BY pct_of_db_time DESC;

Une ligne ressortait : plus de la moitié de tout ce que faisait la base, dépensée sur une seule instruction. La méthode est volontairement ennuyeuse, elle ne devine pas, et elle est la même à chaque fois.

Une requête représentait plus de la moitié de la base

L'instruction qui en est sortie est du genre qu'on ne regarderait jamais deux fois. Une recherche de ville, deux colonnes, un filtre.

-- reconstructed, de-identified. table/column names renamed from the real schema.
SELECT CityID, CityName
FROM   Cities
WHERE  RoutingPath = @p;

-- Execution plan: Clustered Index Scan (a full scan)
--   Returns:          ONE row
--   Filtered column:  RoutingPath --> no index

La colonne sur laquelle elle filtrait n'avait pas d'index. Chaque appel parcourait donc la table entière pour rendre une seule ligne. En soi, c'est un petit coût ; répété à chaque affichage de page sur tout le réseau, c'est devenu la plus grosse chose que la base faisait.

Une réponse d'une ligne doit lire deux ou trois pages, pas toute la table

“Une réponse d'une ligne doit lire deux ou trois pages, pas des centaines.”

Cette phrase est tout le diagnostic, compressé en quelque chose que vous pouvez dire à la personne qui possède le code. Rien d'autre ne fait lire autant à une recherche d'une seule ligne. Ni un disque lent, ni une CPU occupée, ni une mauvaise nuit. Une réponse d'une ligne qui lit toute la table est un full scan, et un full scan sur une colonne filtrée est une signature sans équivoque : la colonne a besoin d'un index. Nous n'avons pas eu à débattre de matériel, nous avons simplement montré les lectures.

Un index posé en ligne, du scan au seek

-- reconstructed, de-identified. index name illustrative, on the renamed identifiers.
-- Index the column in the WHERE clause; cover the two columns it returns
CREATE NONCLUSTERED INDEX IX_Cities_RoutingPath
  ON Cities (RoutingPath)
  INCLUDE (CityID, CityName)
  WITH (ONLINE = ON);   -- applied without interrupting the site

-- New execution plan: Index Seek

Un index, sur la seule colonne sur laquelle l'application filtrait réellement. Il a été appliqué en ligne, sans interrompre les sites, et la recherche qui avait dominé la base est devenue l'une des choses les moins coûteuses qu'elle faisait.

L'ingénieur du client a enrichi le diagnostic

Le meilleur de cette collaboration n'était pas notre index. C'est ce qui s'est passé quand l'ingénieur base de données du client lui-même s'est joint à la discussion et a enrichi le diagnostic : il a apporté un schéma de blocage lecteur/écrivain de son côté du système. Nous avons intégré son correctif au nôtre, et les deux sont passés ensemble.

“L'ingénieur du client a quitté l'appel en collaborateur.”

C'est la différence entre un ticket de support et un diagnostic. Quand la découverte est vraie et que vous montrez les preuves, les deux côtés cessent de défendre des positions et commencent à améliorer la plateforme ensemble.

Quand chaque niveau est inactif sauf un, arrêtez de regarder la capacité

La leçon est courte et c'est l'inverse du réflexe. Une ferme lente avec des machines inactives n'est pas un problème de capacité, c'est une question logicielle déguisée en problème de capacité. Voici ce qu'il faut vérifier avant de signer une commande de matériel.

Pendant tout ce temps, la machine est restée calme. Les load balancers tournaient à vide, les serveurs web s'ennuyaient, et le correctif était un index sur la colonne sur laquelle l'application filtrait. Le serveur de base de données était sain ; une instruction à l'intérieur avait besoin d'un index.

L'instinct face à une ferme lente est de s'en sortir en achetant. La réponse honnête est presque toujours moins chère : lisez chaque niveau, trouvez celui qui est saturé, et allez lire ses requêtes.