Cas pratique d'optimisation de PostgreSQL

Configuration de PostgreSQL

La configuration par défaut de PostgreSQL est (en général, peut-être y a-t-il des exceptions dans certaines distributions) paramétrée plutôt pour des serveurs de petite taille. Pour une base à forte volumétrie de données et de requêtes, s'exécutant sur un serveur puissant, il est nécessaire d'effectuer quelques ajustements.

En particulier, il est recommandé de fortement augmenter les paramètres shared_buffers et work_mem qui indiquent la quantité de mémoire maximale à utiliser pour un certain nombre d'opérations. L'utilitaire pg_tune peut être utilisé pour obtenir des indications sur les valeurs à utiliser, suivant la configuration matérielle (même si les valeurs exactes dépendent de l'utilisation qui est faite de la base).

Si la base utilise des index "lourds" (comme des index GIN sur des champs full-text), il est aussi recommandé d'augmenter le délai entre deux checkpoint.

Répartition sur les disques

PostgreSQL peut effectuer des accès disques pour différentes raisons :

1. Pour accéder aux données lors d'une requête.
2. Pour écrire dans le WAL (PostgreSQL écrit toutes les transactions de manière séquentielle dans un journal, nommé WAL (pour write-ahead log), puis ces transactions sont répercutées dans la base elle-même ultérieurement, afin de garantir une constante cohésion des données).
3. Pour écrire les modifications dans la base elle-même (depuis le WAL).
4. Pour matérialiser des tables temporaires, ou effectuer des tris sur un trop grand nombre d'éléments pour la RAM.

Lorsqu'un serveur PostgreSQL sature au niveau des entrées/sorties (voir la commande iostat expliquée dans un article précédent, il est souvent recommandé d'utiliser des disques différents pour certaines de ces opérations. Mettre le WAL sur des disques séparés, et/ou mettre les tables temporaires sur des disques séparés permet d'éviter les effets de bord d'une grosse requête sur les requêtes qui arrivent en parallèle, et globalement d'augmenter la bande passante disque (qui dépend du nombre de disques).

Il faut bien sûr, dans ce cas, se poser la question du type de disque à utiliser (magnétique à faible vitesse -- SATA à 7200RPM --, magnétique à haute haute vitesse -- SAS à 15000RPM -- ou disque flash) suivant la volumétrie de données, le nombre d'accès et le budget disponible, et la réponse peut être différente entre les utilisations.

Mettre assez de RAM pour la base

Une solution radicale, mais pas toujours possible ou abordable, consiste à avoir suffisamment de place en RAM pour que l'ensemble de la base soit dans le cache disque, et que les accès disques soient limités aux écritures et aux éventuelles tables temporaires.

Il faut compter en RAM la taille de la base (que l'on peut obtenir par exemple avec un du -sh /var/lib/postgresql) plus la mémoire utilisée par les applications (qui dépend des réglages de la configuration du PostgreSQL).

Si on souhaite pré-charger toute la base au démarrage de la machine, on peut le faire via une commande du type (attention le cat est nécessaire, sinon tar triche) :

tar c /var/lib/postgresl | cat > /dev/null

Le log des slows queries

PostgreSQL (comme la plupart des moteurs de bases de données) permet de loguer les requêtes ayant plus pris plus d'un certain temps. Cette option se configure via l'entrée log_min_duration_statement du fichier de configuration PostgreSQL (et peut se changer dynamiquement via une commande du type (la valeur étant en milisecondes) :

set log_min_duration_statement=200;

Ce log va permettre de voir quelles sont les requêtes qui coûtent cher, afin de concentrer l'optimisation dessus.

Attention tout de même : écrire ce log demande du temps, si le seuil est trop bas, les performances peuvent s'en trouver dégradées, surtout si le serveur sature au niveau des entrées/sorties.

Il existe des outils pour analyser ces logs, extraire les requêtes les plus longues, compter les fréquences, agréger en ignorant les valeurs mais en ne regardant que le "type" de requêtes, ...

L'un de ces outils est pgfouine, un autre, développé pour nos propres besoins, est disponible ici.

Explain analyze

Une fois les requêtes lentes identifiées, il faut les analyser. PostgreSQL fournit un outil très puissant à cette fin : EXPLAIN ANALYZE.

EXPLAIN ANALYZE va afficher le query plan utilisé pour effectuer la requête, c'est à dire toutes les étapes (chercher dans un index, parcourir une table, trier des résultats), avec à chaque fois une estimation (avant l'exécution) du temps que l'opération prendra, et le temps qu'elle a réellement pris.

Énormément d'informations peuvent être retirées du résultat de cette commande :

• Elle permet souvent de localiser la partie d'une requête complexe qui a coûté le plus cher.
• Elle permet d'essayer différentes formulations de la requête, et voir comment PostgreSQL les traite.
• Elle permet de voir comment PostgreSQL s'y prend pour attaquer la requête. Parfois, il aurait été préférable de s'y prendre autrement (par exemple, si on filtre sur deux champs ayant tous les deux un index, il a choisi un index alors qu'un autre aurait été plus pertinent), et modifier la formulation de la requête l'aide à mieux choisir.
• Elle permet de voir si les estimations faites par PostgreSQL pour choisir un plan sont erronées : le EXPLAIN ANALYZE donne les estimations de temps et de nombre de résultats sur les différentes étapes, ainsi que les valeurs réelles (après avoir exécuter la requête). Si ces valeurs divergent beaucoup, il peut être nécessaire de lancer un VACUUM ANALYZE pour mettre à jour les statistiques utilisées par PostgreSQL, d'incrémenter le seuil de collecte de statistiques, et/ou de modifier les paramètres de la section QUERY TUNING de la configuration.

Index composites

Le premier type d'optimisation, rapide à faire, est de créer des index composites supplémentaires.

La théorie est la suivante : il est possible, mais relativement coûteux, d'utiliser plusieurs index à la fois sur une requête. Prenons une requête du type :

SELECT * FROM table WHERE champ1 = 789 AND champ2 = 42 ORDER BY champ3 LIMIT 5

Si on créé un index sur chacun des champs, PostgreSQL aura plusieurs solutions :

1. Regarder via l'index sur champ3 tous les éléments par champ3 décroissant, jusqu'à en trouver 5 qui correspondent.
2. Regarder via l'index sur champ1 tous les éléments ayant 789 dans le champ, puis filtrer les éléments correspondant (sans index) sur champ2, puis les trier (sans index) sur champ3 et garder les 5 premiers.
3. Comme le 2. mais en inversant champ2 et champ1.
4. Créer, via l'index sur le champ1 une table bitmap des lignes qui correspondent à la valeur 789 de champ1, en créer une autre via l'index pour celles ayant la valeur 42 de champ2, faire un ET bit à bit entre ces deux tables, et ensuite trier les résultats.

Suivant les statistiques que PostgreSQL a collecté sur les index, il va choisir l'un ou l'autre des plans. Il peut parfois choisir le mauvais, mais surtout aucun plan n'est parfait.

Par contre, si on créé un index composite sur les trois champs ainsi :

CREATE INDEX table_champ1_2_3 ON table(champ1, champ2, champ3)

Il pourra alors utiliser le même index pour filtrer à la fois sur les deux champs, et trier sur le troisième.

Attention à l'ordre dans les index composites, il est extrêmement important, en raison du fonctionnement des index (pour les index de type btree, qui sont les plus courants, en tout cas). Les premiers éléments de l'index doivent être fixés à une valeur donnée pour que les éléments suivant puissent servir.

Ainsi, avec un index sur (champ1, champ2) on peut par exemple :

• filtrer sur champ1 avec les opérateurs de type =, <, <=, >, >= ;
• filtrer sur champ1 avec l'opérateur = et sur champ2 avec les opérateurs =, <, <=, >, >=
• filtrer sur champ1 avec l'opérateur = et trier sur champ2
• trier sur champ1
• trier sur champ1 puis champ2.

Mais on ne peut pas par contre :

• filtrer sur champ2 sans filtre exact sur champ1
• filtrer sur champ2 et trier sur champ1
• trier sur champ2 puis champ1.

Attention tout de même à ne pas multiplier les index, qui augmentent l'occupation disque de la base, et ralentissent les requêtes en modification (INSERT, UPDATE, DELETE).

Tri sur l'index de filtre

Ce type d'optimisation découle de ce qui a été expliqué sur les index précédemment. Il est conseillé, autant que possible, d'effectuer le tri sur les mêmes critères que le filtre.

Par exemple, si on demande les articles de la section "informatique" ayant été créés (created_at) entre le 3 juin et le 4 août, triés par date de modification (modified_at), avec un LIMIT 100 parce qu'on est raisonnable, PostgreSQL ne pourra utiliser que l'un des deux critères pour son index (même un index composite ne suffirait pas, là). Par contre, si on utilise la même date pour le tri et le filtre, on peut gagner énormément.

Ce n'est bien sûr pas toujours possible d'un point de vue des fonctionnalités utilisateurs, mais parfois on peut faire en sorte que ce le soit, et le gain peut être important (un facteur 10 voir 100 n'est pas rare).

Jointures inutiles sur les COUNT

Lorsqu'on utilise un ORM (celui de Django en tout cas, mais le cas n'est sans doute pas spécifique), il arrive qu'on soit dans le cas suivant :

• J'ai de l'héritage ou des champs multivalués, donc des jointures (car l'ORM ne sait pas utiliser, hélas, ni l'héritage PostgreSQL ni le type array de PostgreSQL).
• Je veux récupérer des éléments pour les afficher, 50 à la fois, avec une pagination.
• Pour les afficher, j'ai besoin de tous les attributs, des deux tables.
• Pour afficher le décompte des pages, je vais faire un COUNT(*) sur la même requête.

Dans ce cas, suivant les filtres qu'on effectue, il peut ne pas être nécessaire d'avoir la jointure entre les deux tables pour le COUNT(*). L'ORM va avoir tendance à effectuer la jointure même lorsqu'elle n'est pas nécessaire, or, c'est souvent cette jointure qui coûte le plus cher.

Jointures et NOT EXIST

Considérons un cas précis : on a une table d'utilisateurs, et une table des restrictions sur les utilisateurs (par exemple, une fermeture temporaire de compte). On souhaite récupérer les 50 derniers utilisateurs n'ayant pas de restriction active. On peut effectuer la requête de deux manières :

SELECT user.*
FROM user
LEFT OUTER JOIN restrictions ON (restrictions.user_id = user.id AND restrictions.expiration >= NOW())
WHERE restrictions.id IS NULL
ORDER BY user_id DESC LIMIT 50;

Ou :

SELECT user.*
FROM user
WHERE NOT EXISTS (SELECT id FROM restrictions WHERE restrictions.user_id = user.id AND restrictions.expiration >= NOW())
ORDER BY user_id DESC LIMIT 50;

Un ORM (en tout cas, celui de Django) aura tendance à utiliser la première version de la requête, alors que la deuxième version a tendance à être sensiblement plus efficace (faire une jointure et espérer ne pas trouver les données n'est pas le cas "normal" de la jointure).

La clause OFFSET

La clause OFFSET (qui permet de récupérer les valeurs suivantes dans un ORDER) peut coûter très cher sur un grand nombre de résultats. Il faut envisager les solutions suivantes :

1. Utiliser plutôt un filtre sur des valeurs (un id, un rank, une date) du type WHERE id >= 10000 AND id < 11000.
2. Utiliser un curseur, et récupérer les données au fur et à mesure d'une seule "grosse" requête, plutôt que de faire plein de requêtes ayant chacune un OFFSET différent.
3. Récupérer une liste de tous les ids correspondant à la requête initiale, puis utiliser la solution 1. en émulant donc l'OFFSET au niveau de l'applicatif.

Les SELECT DISTINCT

Il s'agit d'un autre type de requêtes à éviter, car très coûteuses pour la base de données. Elles peuvent souvent être éliminées en retravaillant le schéma de la base de données, en particulier, en utilisant des tableaux PostgreSQL et les opérateurs associés au lieu d'une table de jointure pour représenter un champ multivalué.