Bonjour, Khabrovites! J'attire votre attention sur une traduction de l'article
«Comment une seule modification de configuration PostgreSQL a amélioré les performances des requêtes lentes de 50x» par Pavan Patibandla. Cela m'a beaucoup aidé à améliorer les performances de PostgreSQL.
Chez Amplitude, notre objectif est de fournir des analyses de produits interactives faciles à utiliser afin que chacun puisse trouver des réponses à ses questions sur le produit. Pour garantir la convivialité, Amplitude doit fournir ces réponses rapidement. Par conséquent, lorsqu'un de nos clients s'est plaint du temps qu'il a fallu pour charger la liste déroulante des propriétés d'événement dans l'interface utilisateur d'Amplitude, nous avons commencé une étude détaillée du problème.
En suivant le retard à différents niveaux, nous avons réalisé qu'il a fallu 20 secondes pour qu'une requête PostgreSQL particulière se termine. Cela nous a surpris, car les deux tables ont des index dans la colonne join.
Demande lente
Le plan d'exécution de PostgreSQL pour cette requête était inattendu pour nous. Malgré le fait que les deux tables aient des index, PostgreSQL a décidé d'effectuer une jointure par hachage avec l'analyse séquentielle d'une grande table. L'analyse séquentielle d'une grande table a pris la plupart du temps de requête.
Plan d'exécution de requête lente
J'ai d'abord pensé que cela pourrait être dû à la fragmentation. Mais après avoir vérifié les données, j'ai réalisé que les données ne sont ajoutées qu'à ce tableau et pratiquement pas supprimées de là. Étant donné que le nettoyage de l'endroit avec VACUUM n'aidera pas beaucoup ici, j'ai commencé à creuser plus loin. J'ai ensuite essayé la même requête sur un autre client avec un bon temps de réponse. À ma grande surprise, le plan d'exécution des requêtes était complètement différent!
Plan d'exécution pour la même requête sur un autre client
Fait intéressant, l'application A n'a eu accès qu'à 10 fois plus de données que l'application B, mais le temps de réponse a été 3 000 fois plus long.
Pour voir d'autres plans de requête PostgreSQL, j'ai désactivé la connexion de hachage et redémarré la requête.
Plan d'exécution alternatif pour les requêtes lentes
Eh bien ici! La même demande se termine 50 fois plus rapidement lors de l'utilisation d'une boucle imbriquée au lieu d'une jointure de hachage. Alors pourquoi PostgreSQL a-t-il choisi le pire plan pour l'application A?
En examinant de plus près le coût estimé et le délai réel pour les deux plans, les ratios estimés du coût et du délai réel étaient très différents. Le principal coupable de cet écart était l'estimation des coûts du balayage séquentiel. PostgreSQL estime que les analyses séquentielles seraient meilleures que plus de 4000 analyses d'index, mais en fait, les analyses d'index étaient 50 fois plus rapides.
Cela m'a conduit aux
options de configuration
random_page_cost et
seq_page_cost . Les valeurs PostgreSQL par défaut sont
4 et
1 pour
random_page_cost ,
seq_page_cost , qui sont configurés pour le disque dur, où l'accès aléatoire au disque est plus cher que l'accès séquentiel. Cependant, ces coûts étaient inexacts pour notre déploiement à l'aide du volume
gp2 EBS , qui sont des disques
SSD . Pour notre déploiement, l'accès aléatoire et séquentiel est presque le même.
J'ai changé la valeur de
random_page_cost à
1 et
réessayé la demande. Cette fois, PostgreSQL a utilisé la boucle imbriquée et la requête s'est exécutée 50 fois plus rapidement. Après le changement, nous avons également remarqué une diminution significative du temps de réponse maximum de PostgreSQL.
Les performances globales d'une requête lente se sont considérablement améliorées.
Si vous utilisez SSD et utilisez PostgreSQL avec la configuration par défaut, je vous conseille d'essayer de
définir random_page_cost et
seq_page_cost . Vous pourriez être surpris de l'amélioration spectaculaire des performances.
Pour ma part, j'ajouterai que j'ai défini les paramètres minimum
seq_page_cost = random_page_cost = 0.1 pour donner la priorité aux données en mémoire (cache) sur les opérations du processeur, car j'ai alloué une grande quantité de RAM pour PostgreSQL (la taille de la RAM dépasse la taille de la base de données sur le disque). Il n'est pas très clair pourquoi la communauté postgres utilise toujours les paramètres par défaut qui sont pertinents pour un serveur avec une petite quantité de RAM et de disques durs, et pas pour les serveurs modernes. Espérons que cela sera bientôt corrigé.