[v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

PommePote · 15/05/2013 12:41:25

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 :

postgresql.conf a écrit :

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 :

table_episode a écrit :

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:

La requete a écrit :

SELECT DISTINCT annee_episode FROM episode ;

De même que la requête suivante s'exécute en plus de 1451904ms:

La requete a écrit :

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

rjuju · 15/05/2013 13:01:39

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.

PommePote · 16/05/2013 13:00:29

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 :

Requete SQL a écrit :

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 :

Plan d'execution a écrit :

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 :
VM stat
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)

SAS · 16/05/2013 15:03:39

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.

gleu · 16/05/2013 23:30:27

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.

PommePote · 17/05/2013 10:12:05

SAS a écrit :

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).

gleu a écrit :

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 ?

SQLpro · 24/05/2013 15:33:14

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)

gleu · 24/05/2013 15:46:22

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.

SQLpro · 24/05/2013 16:00:02

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 +

gleu · 24/05/2013 19:38:05

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.

#1 15/05/2013 12:41:25

[v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

#2 15/05/2013 13:01:39

Re : [v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

#3 16/05/2013 13:00:29

Re : [v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

#4 16/05/2013 15:03:39

Re : [v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

#5 16/05/2013 23:30:27

Re : [v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

#6 17/05/2013 10:12:05

Re : [v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

#7 24/05/2013 15:33:14

Re : [v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

#8 24/05/2013 15:46:22

Re : [v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

#9 24/05/2013 16:00:02

Re : [v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

#10 24/05/2013 19:38:05

Re : [v9.2] Performances en lecture faible sur 25M de lignes, étrange ?

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux