Optimisation des performances de PostgreSQL pour une exécution plus rapide des requêtes
Objectif
Notre objectif est d’accélérer l’exécution d’une requête factice sur la base de données PostgreSQL en utilisant uniquement les outils intégrés disponibles
dans la base de données.
Système d’exploitation et versions logicielles
- Système d’exploitation: Red Hat Enterprise Linux 7.5
- logiciel: Serveur PostgreSQL 9.2
Exigences
Installation de base de PostgreSQL Server. Accès à l’outil en ligne de commande psql et propriété de la base de données exemple.
Conventions
- # – nécessite que les commandes Linux données soient exécutées avec les privilèges root, soit directement en tant qu’utilisateur root, soit en utilisant la commande
sudo - $ – commandes linux données à exécuter en tant qu’utilisateur régulier non privilégié
Introduction
PostgreSQL est une base de données open source fiable disponible dans le dépôt de nombreuses distributions modernes. La facilité d’utilisation, la possibilité d’utiliser des extensions et la stabilité qu’elles offrent contribuent à sa popularité.
Tout en fournissant les fonctionnalités de base, comme répondre aux requêtes SQL, stocker les données insérées de manière cohérente, gérer les transactions, etc., la plupart des solutions de base de données matures fournissent des outils et des savoir-faire sur la façon de
ajuster la base de données, identifier les éventuels goulets d’étranglement, et être capable de résoudre les problèmes de performance qui ne manqueront pas de se produire au fur et à mesure que le système alimenté par la solution donnée se développe.
PostgreSQL ne fait pas exception, et dans ce guide
, nous utiliserons l’outil intégré explain pour accélérer la complétion d’une requête lente. C’est loin d’être une base de données du monde réel, mais on peut se faire une idée de l’utilisation des outils intégrés. Nous utiliserons une version 9.2 du serveur PostgreSQL sur Red Hat Linux 7.5, mais les outils présentés dans ce guide sont également présents dans des versions de base de données et de système d’exploitation beaucoup plus anciennes.
Le problème à résoudre
Considérez ce tableau simple (les noms des colonnes sont explicites) :
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
Has OIDs: no
Avec des records comme :
foobardb=# select * from employees limit 2;
emp_id | first_name | last_name | birth_year | birth_month | birth_dayofmonth
--------+------------+-----------+------------+-------------+------------------
1 | Emily | James | 1983 | 3 | 20
2 | John | Smith | 1990 | 8 | 12
Dans cet exemple, nous sommes l’entreprise Nice et avons déployé une application appelée HBapp qui envoie un e-mail de « joyeux anniversaire » à l’employé le jour de son anniversaire. L’application interroge la base de données tous les matins pour trouver les destinataires de la journée (avant les heures de travail, nous ne voulons pas tuer notre base de données RH par gentillesse).
L’application exécute la requête suivante pour trouver les destinataires :
foobardb=# select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
emp_id | first_name | last_name
--------+------------+-----------
1 | Emily | James
Tout fonctionne bien, les utilisateurs reçoivent leur courrier. De nombreuses autres applications utilisent la base de données et la table des employés qu’elle contient, comme la comptabilité et la BI. L’entreprise Nice grandit, et donc le tableau des employés. Avec le temps, l’application s’exécute trop longtemps et l’exécution se chevauche avec le début des heures de travail, ce qui ralentit le temps de réponse de la base de données dans les applications critiques. Nous devons faire quelque chose pour que cette requête s’exécute plus rapidement, sinon l’application ne sera pas déployée, et avec elle il y aura moins de gentillesse dans Nice Company.
Pour cet exemple, nous n’utiliserons pas d’outils avancés pour résoudre le problème, mais seulement un fourni par l’installation de base. Voyons comment le planificateur de base de données exécute la requête avec explain.
Nous ne faisons pas de tests en production ; Nous créons une base de données pour les tests, créons la table et y insérons deux employés mentionnés ci-dessus. Nous utilisons les mêmes valeurs pour la requête tout au long de ce tutoriel,
donc à n’importe quelle exécution, un seul enregistrement correspondra à la requête : Emily James. Ensuite, nous exécutons la requête avec explain analyze qui précède pour voir comment elle est exécutée avec un minimum de données dans la table :
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..15.40 rows=1 width=96) (actual time=0.023..0.025 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 1
Total runtime: 0.076 ms
(4 rows)
C’est très rapide. Peut-être aussi rapide qu’au moment où l’entreprise a déployé pour la première fois HBapp. Imitons l’état du foobardb de production actuel en chargeant autant de (faux) employés dans la base de données que nous en avons en production (note : nous aurons besoin de la même taille de stockage sous la base de données de test qu’en production).
Nous utiliserons simplement bash pour remplir la base de données de test (en supposant que nous avons 500.000 employés en production) :
for j in {1..500000} ; do echo "insert into employees (first_name, last_name, birth_year, birth_month, birth_dayofmonth) values ('user$j','Test',1900,01,01);"; done | psql -d foobardb
Aujourd’hui, nous avons 500002 employés :
foobardb=# select count(*) from employees;
count
--------
500002
(1 row)
Exécutons à nouveau la requête explain :
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..11667.63 rows=1 width=22) (actual time=0.012..150.998 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 500001
Total runtime: 151.059 ms
Nous n’avons toujours qu’une seule correspondance, mais la requête est nettement plus lente. Nous devrions remarquer le premier nœud du planificateur : Seq Scan qui signifie balayage séquentiel – la base de données lit la table entière
, alors que nous n’avons besoin que d’un seul enregistrement, comme le ferait un grep dans bash. En fait, il peut être plus lent que grep. Si nous exportons la table dans un fichier csv appelé /tmp/exp500k.csv :
foobardb=# copy employees to '/tmp/exp500k.csv' delimiter ',' CSV HEADER;
COPY 500002
Et récupérez les informations dont nous avons besoin (nous recherchons le 20ème jour du 3ème mois, les deux dernières valeurs du fichier csv dans toutes les lignes
) :
time grep ",3,20" /tmp/exp500k.csv
1,Emily,James,1983,3,20
real 0m0.067s
user 0m0.018s
sys 0m0.010s
Mis à part la mise en cache, on considère qu’elle est de plus en plus lente à mesure que la table grandit.
La solution est l’indexation des causes. Aucun employé ne peut avoir plus d’une date de naissance, qui se compose exactement d’un birth_year, d’un birth_month et d’un birth_dayofmonth - ces trois champs fournissent donc une valeur unique pour cet utilisateur particulier. Et un utilisateur est identifié par son emp_id (il peut y avoir plus d’un employé dans l’entreprise portant le même nom). Si nous déclarons une contrainte sur ces quatre champs, un index implicite sera également créé :
foobardb=# alter table employees add constraint birth_uniq unique (emp_id, birth_year, birth_month, birth_dayofmonth);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq" for table "employees"
Nous avons donc obtenu un index pour les quatre champs, voyons comment fonctionne notre requête :
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..11667.19 rows=1 width=22) (actual time=103.131..151.084 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 500001
Total runtime: 151.103 ms
(4 rows)
C’est identique au précédent, et on peut voir que le plan est le même, l’index n’est pas utilisé. Créons un autre index par une contrainte unique sur emp_id, birth_month et birth_dayofmonth uniquement (après tout, nous n’interrogeons pas birth_year dans HBapp) :
foobardb=# alter table employees add constraint birth_uniq_m_dom unique (emp_id, birth_month, birth_dayofmonth);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_m_dom" for table "employees"
Voyons le résultat de notre réglage :
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..11667.19 rows=1 width=22) (actual time=97.187..139.858 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 500001
Total runtime: 139.879 ms
(4 rows)
Rien. La différence ci-dessus provient de l’utilisation de caches, mais le plan est le même. Allons plus loin. Ensuite, nous allons créer un autre index sur emp_id et birth_month :
foobardb=# alter table employees add constraint birth_uniq_m unique (emp_id, birth_month);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_m" for table "employees"
Et exécutez à nouveau la requête :
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using birth_uniq_m on employees (cost=0.00..11464.19 rows=1 width=22) (actual time=0.089..95.605 rows=1 loops=1)
Index Cond: (birth_month = 3::numeric)
Filter: (birth_dayofmonth = 20::numeric)
Total runtime: 95.630 ms
(4 rows)
Succès! La requête est 40 % plus rapide, et on peut voir que le plan a changé : la base de données ne scanne plus toute la table, mais utilise l’index sur birth_month et emp_id. Nous avons créé tous les mixages des quatre champs, il n’en reste qu’un. Vaut la peine d’essayer :
foobardb=# alter table employees add constraint birth_uniq_dom unique (emp_id, birth_dayofmonth);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_dom" for table "employees"
Le dernier index est créé sur les champs emp_id et birth_dayofmonth. Et le résultat est :
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using birth_uniq_dom on employees (cost=0.00..11464.19 rows=1 width=22) (actual time=0.025..72.394 rows=1 loops=1)
Index Cond: (birth_dayofmonth = 20::numeric)
Filter: (birth_month = 3::numeric)
Total runtime: 72.421 ms
(4 rows)
Désormais, notre requête est environ 49 % plus rapide, en utilisant le dernier (et seulement le dernier) index créé. Notre tableau et les index associés se présentent comme suit :
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
"birth_uniq" UNIQUE CONSTRAINT, btree (emp_id, birth_year, birth_month, birth_dayofmonth)
"birth_uniq_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_dayofmonth)
"birth_uniq_m" UNIQUE CONSTRAINT, btree (emp_id, birth_month)
"birth_uniq_m_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_month, birth_dayofmonth)
Has OIDs: no
Nous n’avons pas besoin de créer les index intermédiaires, le plan indique clairement qu’il ne les utilisera pas, nous les laissons donc tomber :
foobardb=# alter table employees drop constraint birth_uniq;
ALTER TABLE
foobardb=# alter table employees drop constraint birth_uniq_m;
ALTER TABLE
foobardb=# alter table employees drop constraint birth_uniq_m_dom;
ALTER TABLE
En fin de compte, notre table ne gagne qu’un seul indice supplémentaire, ce qui est peu coûteux pour une vitesse presque double de HBapp :
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
"birth_uniq_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_dayofmonth)
Has OIDs: no
Et nous pouvons introduire notre réglage en production en ajoutant l’index que nous avons vu être le plus utile :
alter table employees add constraint birth_uniq_dom unique (emp_id, birth_dayofmonth);Conclusion
Inutile de dire qu’il ne s’agit que d’un exemple factice. Il est peu probable que vous stockiez la date de naissance de votre employé dans trois champs distincts alors que vous pourriez utiliser un champ de type date, ce qui permettrait d’effectuer des opérations liées à la date de manière beaucoup plus facile que de comparer des valeurs de mois et de jour sous forme d’entiers. Notez également que les quelques requêtes d’explication ci-dessus ne sont pas adaptées à des tests excessifs. Dans un scénario réel, vous devez tester l’impact du nouvel objet de base de données sur toute autre application qui utilise la base de données, ainsi que sur les composants de votre système qui interagissent avec HBapp.
Par exemple, dans ce cas, si nous pouvons traiter la table pour les destinataires dans 50 % du temps de réponse initial, nous pouvons virtuellement produire 200 % des e-mails à l’autre bout de l’application (disons que la HBapp fonctionne en séquence pour toutes les 500 filiales de Nice Company), ce qui peut entraîner un pic de charge ailleurs – peut-être que les serveurs de messagerie recevront beaucoup de « Joyeux anniversaire » e-mails à relayer juste avant qu’ils n’envoient les rapports quotidiens à la direction, ce qui entraîne des retards de livraison. Il est également un peu loin de la réalité que quelqu’un qui ajuste une base de données crée des index avec des essais et des erreurs aveugles – ou du moins, espérons que c’est le cas dans une entreprise employant autant de personnes.
Notez toutefois que nous avons obtenu une augmentation de 50 % des performances de la requête uniquement en utilisant la fonction d’explication PostgreSQL intégrée pour identifier un seul index qui pourrait être utile dans une situation donnée. Nous avons également montré que toute base de données relationnelle n’est pas meilleure qu’une recherche en texte clair si nous ne les utilisons pas comme elles sont censées être utilisées.