Rendre une requête Postgres 1 000 fois plus rapide

• Mattermost utilise Elasticsearch pour réduire la charge sur la base de données et fournir des résultats de recherche bien plus rapides
  • Pour qu’Elasticsearch fonctionne correctement, il faut indexer toutes les données à rechercher
  • Une fois les données déjà indexées, l’indexation ultérieure des nouveaux messages et fichiers est assez rapide
  • En revanche, indexer entièrement depuis zéro une très grande base de données (100 millions de posts) est extrêmement lent (au bout de 18 heures, le processus n’avait même pas atteint la moitié et continuait à ralentir)
• Un graphique du temps passé par appel à la base de données a permis d’identifier la requête SQL de la méthode PostStore.GetPostsBatchForIndexing comme étant le problème
  • Cette requête trie essentiellement les posts par timestamp de création et renvoie les N posts plus récents qu’un timestamp donné
  • Le job d’indexation exécute cette requête en boucle jusqu’à ce que tous les posts soient indexés
• Le plan d’exécution de la requête a été analysé avec EXPLAIN (ANALYZE, BUFFERS) :
  • Lors du scan d’index sur la table Posts, 40 millions de blocs étaient traités pour appliquer la condition Filter (309 GB)
  • Le JOIN avec la table Channels n’était pas le problème
  • En n’appliquant que la partie Posts.CreateAt > ?1 de la clause OR dans WHERE, c’était bien plus rapide (30 ms)
  • En appliquant ensuite la condition Posts.CreateAt = ?1 AND Posts.Id > ?2, c’était extrêmement rapide (0,047 ms)
• Cause identifiée :
  • La requête d’origine parcourait toutes les lignes de Posts avant de les filtrer, alors que la requête modifiée ne vérifiait que l’index et n’extrayait que les lignes nécessaires
  • Si la requête devenait de plus en plus lente avec le temps, c’est parce qu’elle devait filtrer un nombre croissant de lignes
• Solution :
  • Utiliser la fonctionnalité de comparaison de constructeurs de lignes de PostgreSQL et remplacer la condition par (Posts.CreateAt, Posts.Id) > (?1, ?2)
  • Avec ce changement, le temps d’exécution de la requête est tombé à 34 ms
  • En revanche, sur MySQL, la requête modifiée était encore plus lente. Comme la requête d’origine y était plus rapide, le code a été bifurqué pour utiliser une requête différente selon la base
• Enseignements :
  • Toujours utiliser l’option BUFFERS avec EXPLAIN
  • Chercher à exploiter Index Cond plutôt que Filter
  • Partir du principe que PostgreSQL et MySQL se comportent presque toujours différemment
• Conclusion
  • Ce travail d’optimisation a permis de réduire le temps d’exécution de la requête de plus de 1 000 fois
  • Cette optimisation a été intégrée dans Mattermost v9.7.0 et v9.5 ESR
  • Ce travail d’optimisation a aussi permis d’apprendre beaucoup de choses

[dontcryme]

Comme indiqué tout à la fin de l’article, le titre de cet article a un petit côté accrocheur... si on voulait le reformuler de manière plus concrète :

« Cas d’usage de PostgreSQL appris à travers des erreurs »

peut-être..?

[vwjdalsgkv]

Hum... personnellement, si un article de ce niveau est écrit en s’appuyant sur une entreprise ou un produit en particulier, j’aurais plutôt tendance à perdre beaucoup en confiance envers ce produit.
La présentation est claire et bien structurée, mais c’est dommage qu’elle semble un peu manquer de valeur technique sur le fond.

[savvykang]

Moi aussi, après avoir lu cet article, j’ai au contraire eu moins confiance. Cela signifie qu’ils ont lancé une fonctionnalité sans même tester le traitement à grande échelle pour un produit qu’ils vendent contre rémunération. J’ai l’impression qu’un index aussi simple aurait dû être mis en place dès l’étape de développement de la fonctionnalité. On dirait que beaucoup d’étapes de la procédure de développement logiciel ont été omises.