Vous n'êtes pas identifié(e).
Bonjour à tous,
A la suite de plusieurs jours de test de PostgreSQL sur une base de données relativement "volumineuse" (25 millions de lignes), j'obtiens des performances assez moyenne en lecture.
J'aurai aimé obtenir de l'aide pour comprendre ces performances relativement faible, sachant que PostgreSQL est en phase de passer au pétabyte (http://www.postgresqlfr.org/pgday2013:v … postgresql) et surtout comment optimiser la configuration de mon PostgreSQL/Machine/Requête/Données pour améliorer ces performances.
La machine utilisée (VM dans le Cloud) est une Linux en 64bits avec 8Go de RAM & 8 cœurs cadencé à 2GHz.
La configuration PostgreSQL a été modifiée comme suit :
effective_cache_size = 5GB
shared_buffers = 300MB
work_mem = 10MB
maintenance_work_mem = 256MB
max_connections = 50
checkpoint_segments = 32
checkpoint_completion_target = 0.9
random_page_cost = 3.0
Pour vous donner un exemple des performances que l'on obtient :
Pour la table suivante, comprenant 25 millions de lignes, dont la structure est :
CREATE TABLE episode
(
id_episode serial NOT NULL,
...
annee_episode integer,
CONSTRAINT episode_pk PRIMARY KEY (id_episode)
)
WITH ( OIDS=FALSE );CREATE INDEX annee_idx ON episode USING btree (annee_episode);
La requête suivante met plus de 772074ms:
SELECT DISTINCT annee_episode FROM episode ;
De même que la requête suivante s'exécute en plus de 1451904ms:
SELECT annee_episode, COUNT (*) AS nombre_episode FROM episode GROUP BY annee_episode ;
Ce qui reste étrange, c'est que lorsque je scrute les processus système, je remarque que le CPU n'est utilisé au maximum qu'à 5% de sa capacité...
Est-ce que ces performances vous semble "normales" vis-à-vis du nombre de lignes ?
(Quand on sait que leboncoin.fr utilise PostgreSQL, je pense que notre volumétrie à côté est minuscule et nos performances bien moindres...)
Peut-être est-ce lié aux performances de la machine (accès au disque ?, ... ?) ?
Merci de votre aide,
PommePote
Hors ligne
Bonjour,
postgres gère sans problème ce genre de volumétrie, pour peu qu'on lui donne assez de ressources. Vous parlez d'une vm dans le cloud, il faut savoir que toutes les bases de données sont très gourmandes en terme de ressources disque. Avez-vous une idée des performances des disques utilisés ? Cela risque d'être votre point le plus pénalisant.
Si votre vm est dédiée à postgres, vous pouvez augmenter le shared_buffers jusqu'à 2GB. Pour votre seconde requête, je suppose que les données sont triées sur disque ce qui est très couteux. Vous pouvez allouer plus de work_mem pour cette requête spécifiquement par exemple.
En ce qui concerne le cpu, c'est sans doute normal si le point de contention est le système disque. Si vous voulez plus d'aide, il faudrait la version de postgres utilisée, les explain analyze des requêtes, plus d'informations sur le système et par exemple un échantillon de la sortie de vmstat durant une des requêtes.
Julien.
https://rjuju.github.io/
Hors ligne
Bonjour,
Tout d'abord, je tiens à vous remercier pour cette réponse.
Il est clair que ces résultats me semblaient étrange.
Je viens d'exécuter un VACUUM FULL sur la table en question. Et les résultats se sont nettement amélioré, bien qu'encore en dessous de ce que j'attendais.
Je comprends bien le principe du VACUUM, mais j'ai du mal à comprendre comment une requête peut passer de 1451904ms à 14794ms suite à un VACUUM FULL ? (c'est un point à étudier aussi de mon côté)
Pour la version de PostgreSQL, c'est un PostgreSQL v9.2.3.
(Plus précisement: PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit)
Concernant la seconde requête :
SELECT annee_episode, COUNT (*) AS nombre_episode FROM episode GROUP BY annee_episode ;
Cette requête s'exécute en ~14794ms.
Le plan d'exécution est le suivant :
HashAggregate (cost=708330.08..708330.23 rows=15 width=4) (actual time=19333.123..19333.130 rows=15 loops=1)
Buffers: shared hit=1944 read=331388
-> Seq Scan on episode (cost=0.00..583330.72 rows=24999872 width=4) (actual time=0.100..6677.270 rows=24999871 loops=1)
Buffers: shared hit=1944 read=331388
Total runtime: 19333.739 ms
Il semblerait que l'index ne soit pas utilisé pour cette requête, il doit être trop couteux pour ce cas de figure.
J'ai également monté le shared_buffer à 2GB, mais il semblerait que ce n'est pas là que se trouve le ralentissement (pour cette requête en tout cas).
Enfin, voici un aperçu du vmstat durant l'exécution de la requête :
Si je ne me trompe pas, le processeur est utilisé à 25% maximum, ça ne swap pas.
Est-t'il possible d'améliorer le temps d'exécution de cette requête ?
Est-ce qu'un paramètre (BDD, système, environnement) limite les performances d'une manière ou d'une autre ?
Merci,
PommePote
Dernière modification par PommePote (16/05/2013 13:01:04)
Hors ligne
Bonjour,
L'index ne sert à rien dans ce cas précis, puisque vous récupérez l'intégralité des données, pour les regrouper par année.
Vous lisez 25M de lignes pour n'en afficher que 15.
Stéphane Schildknecht
Conseil, formations et support PostgreSQL
http://www.loxodata.com
Hors ligne
Cette requête demande deux choses : du CPU (pour le calcul du hachage, noeud HashAggregate) et des IO pour la lecture de la table (SeqScan).
Malgré que la majorité soit lue en dehors du cache de PostgreSQL, cela ne provoque aucune lecture sur disque (colonne bi à 0 dans la sortie du vmstat). Autrement dit, le cache du système contient toute la table. Donc, de ce côté, on ne peut pas faire mieux.
Au niveau CPU, on ne peut rien faire de particulier à ma connaissance. À la rigueur, le hachage peut se faire sur disque mais ça n'a pas l'air d'être le cas ici. D'ailleurs la colonne bo du vmstat est à 0.
Pour cette requête précise, sur votre système, je crois que vous arrivez au mieux de ce que vous pouvez espérer.
Guillaume.
Hors ligne
Bonjour,
L'index ne sert à rien dans ce cas précis, puisque vous récupérez l'intégralité des données, pour les regrouper par année.
Vous lisez 25M de lignes pour n'en afficher que 15.
Bonjour,
Oui effectivement, c'est logique que l'index ne soit pas utilisé.
(J'ai confondu avec la première requête qui effectue un SELECT DISTINCT annee_episode FROM episode, où là l'index n'est pas utilisé. Mais il semblerait, après recherche que le "DISTINCT" chez PostgreSQL ne se contente pas de lire les valeurs de l'index et fonctionne différemment de certains autres SGBD qui peuvent être plus rapide sur ce genre de requêtes).
Cette requête demande deux choses : du CPU (pour le calcul du hachage, noeud HashAggregate) et des IO pour la lecture de la table (SeqScan).
Malgré que la majorité soit lue en dehors du cache de PostgreSQL, cela ne provoque aucune lecture sur disque (colonne bi à 0 dans la sortie du vmstat). Autrement dit, le cache du système contient toute la table. Donc, de ce côté, on ne peut pas faire mieux.
Au niveau CPU, on ne peut rien faire de particulier à ma connaissance. À la rigueur, le hachage peut se faire sur disque mais ça n'a pas l'air d'être le cas ici. D'ailleurs la colonne bo du vmstat est à 0.
Pour cette requête précise, sur votre système, je crois que vous arrivez au mieux de ce que vous pouvez espérer.
Bonjour gleu,
Merci pour ces précisions. Effectivement j'étais assez surpris de voir qu'il n'y ait aucun accès disque, donc tout se fait au niveau du cache.
Quelles seraient les solutions alors pour obtenir de meilleurs résultats ?
Pré-calculer les résultats ?
Hors ligne
Quelles seraient les solutions alors pour obtenir de meilleurs résultats ?
Pré-calculer les résultats ?
Oui, mais comme PostGreSQL ne dispose ni des vues indexées (MS SQL Server) ou des vues matérialisées (Oracle), il faut faire cela à la main par des triggers ce qui est très pénalisant pour les performances et le verrouillage. Pour info SQL Server le fait de manière synchrone, mais en différentiel, ce qui impacte peu les performances. Or reproduire ce type de fonctionnement à la main, par des déclencheurs, est assez complexe...
A +
Dernière modification par SQLpro (24/05/2013 15:33:45)
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Cela peut être pénalisant pour les performances. Mais c'est une piste à suivre. Tout dépend de la charge actuelle, du travail de l'application, etc. Bref, testez par vous-même, vous verrez bien si c'est intéressant ou pas.
Guillaume.
Hors ligne
Les vues matérialisées arrivent dans la version 9.3, mais elle ne sont pas synchrone ce qui revient donc au même que la requête de base dans ce cas précis. De plus elles posent un verrou exclusif sur l'intégralité de la table si j'ai bien compris.... Bref, pas génial ! En pour les rendre synchrone (eager... donc pas tout à fait synchrone, mais "au plus tôt") PG nous dit qu'il faut faire des triggers !!! On tourne en rond....
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Les vues matérialisées de la 9.3 ne sont pas synchrones en effet. Par contre, à ma connaissance, elles ne posent pas de verrous exclusifs.
Guillaume.
Hors ligne
