Re: Pb de perf sur une grosse base

Bonjour,

J'ai des problèmes de performance sur une base PG, et je ne sais comment
améliorer les choses. J'ai 2 questions : une au sujet du mode de stockage
des données dans la base, l'autre concernant l'optimisation de requêtes.

Je cherche à comparer Oracle et PG. Toutes nos bases opérationnelles
sont sous oracle depuis environ 15 ans. Maintenant j'essaie de remplacer
Oracle par PG.

J'ai une plateforme de test sous linux (un server Dell, 4 Gb RAM,
bi-processor, Linux Red Hat 9 (2.4.20-31.9)) avec 2 bases, 1 sous
oracle (8i), 1 sous PG 7.4.2. Les 3 bases ont la même structure, même
contenu environ 100 Gb chacune. J'ai écrit des benches, représentatifs
de notre activité sur les bases. Mon problème est que je travaille avec
de très grosses tables, plus de 100 millions de lignes, chaque ligne
a environ 160 colonnes et une taille moyenne de 256 octets.

Sous oracle la SGA est de 500 Mb.
Pour PG le postgresql.conf :
max_connections = 1500
shared_buffers = 30000
sort_mem = 50000
effective_cache_size = 200000
et les valeurs par défaut pour les autres paramètres.

J'ai une table nommée "data" qui ressemble à :
bench=> \d data
Table "public.data"
Column | Type | Modifiers
------------+-----------------------------+-----------
num_poste | numeric(9,0) | not null
dat | timestamp without time zone | not null
datrecu | timestamp without time zone | not null
rr1 | numeric(5,1) |
qrr1 | numeric(2,0) | ...
... all numeric fields
...
Indexes:
"pk_data" primary key, btree (num_poste, dat)
"i_data_dat" btree (dat)

Elle contient 1000 valeurs # de "num_poste" et pour chacune on compte
environ 125000 valeurs # de "dat" (en fait 1 ligne par heure sur 15 ans).

J'ai exécuté un vacuum analyze sur la table.

bench=> select * from tailledb ;
schema | relfilenode | table | index | reltuples | size
--------+-------------+------------------+------------+-------------+----------
public | 125615917 | data | | 1.25113e+08 | 72312040
public | 251139049 | data | i_data_dat | 1.25113e+08 | 2744400
public | 250870177 | data | pk_data | 1.25113e+08 | 4395480

Ma première remarque est que la table occupe beaucoup plus de place
sur PG (70 Gb) que sur oracle (35 Gb).
Le calcul : 125 000 000 rows x 256 b = 32 Gb donne une idée du volume
occupé, pas si mauvais pour oracle. Qu'en est-il pour PG ?
Comment les données sont stockées ?

D'autre part les différentes requêtes du bench sont "simples" : pas
de jointures, sous-interrogation, ... et utilisent les index (testées
avec un explain pour en être sûre) :
Q1 select_court : access to about 700 rows : 1 "num_poste" et 1 mois
(using PK : num_poste=p1 and dat between p2 and p3)
Q2 select_moy : access to about 7000 rows : 10 "num_poste" et 1 mois
(using PK : num_poste between p1 and p1+10 and dat between p2 and p3)
Q3 select_long : about 250 000 rows : 2 "num_poste"
(using PK : num_poste in (p1,p1+2))
Q4 select_tres_long : about 3 millions rows : 25 "num_poste"
(using PK : num_poste between p1 and p1 + 25)

Les requêtes "courtes" (Q1 et Q2) s'exécutent en quelques secondes sur
oracle et PG. Mais les différences deviennent importantes pour Q3 puis Q4 :
Q3 : 8 seconds avec oracle
80 sec avec PG
et Q4 : 28s avec oracle
17m20s avec PG !

Bien sûr lorsque je lance en parallèle 1000 requêtes Q3 ou Q4 les perf
deviennent désastreuses !
Je n'arrive pas à comprendre ce résultat. Le mode d'exécution de toutes
ces requêtes est le même, par index. J'ai lu tous les articles recommendés
sur le site PG.
J'ai essayé avec une table contenant 30 millions de lignes, les résultats
sont similaires.

Qu'est-ce que je peux faire ?
Merci pour votre aide !

********************************************************************
* Les points de vue exprimes sont strictement personnels et *
* n'engagent pas la responsabilite de METEO-FRANCE. *
********************************************************************
* Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
* METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
* 42, avenue G. Coriolis Email : Valerie(dot)Schneider(at)meteo(dot)fr *
* 31057 TOULOUSE Cedex - FRANCE http://www.meteo.fr *
********************************************************************

Valerie Schneider DSI/DEV wrote:

> Bonjour,
>
> J'ai des problèmes de performance sur une base PG, et je ne sais comment
> améliorer les choses. J'ai 2 questions : une au sujet du mode de stockage
> des données dans la base, l'autre concernant l'optimisation de requêtes.
>
> Je cherche à comparer Oracle et PG. Toutes nos bases opérationnelles
> sont sous oracle depuis environ 15 ans. Maintenant j'essaie de remplacer
> Oracle par PG.
>
> J'ai une plateforme de test sous linux (un server Dell, 4 Gb RAM,
> bi-processor, Linux Red Hat 9 (2.4.20-31.9)) avec 2 bases, 1 sous
> oracle (8i), 1 sous PG 7.4.2. Les 3 bases ont la même structure, même
> contenu environ 100 Gb chacune. J'ai écrit des benches, représentatifs
> de notre activité sur les bases. Mon problème est que je travaille avec
> de très grosses tables, plus de 100 millions de lignes, chaque ligne
> a environ 160 colonnes et une taille moyenne de 256 octets.
>
> Sous oracle la SGA est de 500 Mb.
> Pour PG le postgresql.conf :
> max_connections = 1500
> shared_buffers = 30000
> sort_mem = 50000
> effective_cache_size = 200000
> et les valeurs par défaut pour les autres paramètres.
>
>
> J'ai une table nommée "data" qui ressemble à :
> bench=> \d data
> Table "public.data"
> Column | Type | Modifiers
> ------------+-----------------------------+-----------
> num_poste | numeric(9,0) | not null
> dat | timestamp without time zone | not null
> datrecu | timestamp without time zone | not null
> rr1 | numeric(5,1) |
> qrr1 | numeric(2,0) | ...
> ... all numeric fields
> ...
> Indexes:
> "pk_data" primary key, btree (num_poste, dat)
> "i_data_dat" btree (dat)
>
> Elle contient 1000 valeurs # de "num_poste" et pour chacune on compte
> environ 125000 valeurs # de "dat" (en fait 1 ligne par heure sur 15 ans).
>
> J'ai exécuté un vacuum analyze sur la table.
>
> bench=> select * from tailledb ;
> schema | relfilenode | table | index | reltuples | size
> --------+-------------+------------------+------------+-------------+----------
> public | 125615917 | data | | 1.25113e+08 | 72312040
> public | 251139049 | data | i_data_dat | 1.25113e+08 | 2744400
> public | 250870177 | data | pk_data | 1.25113e+08 | 4395480
>
> Ma première remarque est que la table occupe beaucoup plus de place
> sur PG (70 Gb) que sur oracle (35 Gb).
> Le calcul : 125 000 000 rows x 256 b = 32 Gb donne une idée du volume
> occupé, pas si mauvais pour oracle. Qu'en est-il pour PG ?
> Comment les données sont stockées ?
>
> D'autre part les différentes requêtes du bench sont "simples" : pas
> de jointures, sous-interrogation, ... et utilisent les index (testées
> avec un explain pour en être sûre) :
> Q1 select_court : access to about 700 rows : 1 "num_poste" et 1 mois
> (using PK : num_poste=p1 and dat between p2 and p3)
> Q2 select_moy : access to about 7000 rows : 10 "num_poste" et 1 mois
> (using PK : num_poste between p1 and p1+10 and dat between p2 and p3)
> Q3 select_long : about 250 000 rows : 2 "num_poste"
> (using PK : num_poste in (p1,p1+2))
> Q4 select_tres_long : about 3 millions rows : 25 "num_poste"
> (using PK : num_poste between p1 and p1 + 25)
>
> Les requêtes "courtes" (Q1 et Q2) s'exécutent en quelques secondes sur
> oracle et PG. Mais les différences deviennent importantes pour Q3 puis Q4 :
> Q3 : 8 seconds avec oracle
> 80 sec avec PG
> et Q4 : 28s avec oracle
> 17m20s avec PG !
>
> Bien sûr lorsque je lance en parallèle 1000 requêtes Q3 ou Q4 les perf
> deviennent désastreuses !
> Je n'arrive pas à comprendre ce résultat. Le mode d'exécution de toutes
> ces requêtes est le même, par index. J'ai lu tous les articles recommendés
> sur le site PG.
> J'ai essayé avec une table contenant 30 millions de lignes, les résultats
> sont similaires.
>
> Qu'est-ce que je peux faire ?
> Merci pour votre aide !
>
> ********************************************************************
> * Les points de vue exprimes sont strictement personnels et *
> * n'engagent pas la responsabilite de METEO-FRANCE. *
> ********************************************************************
> * Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
> * METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 *
> * 42, avenue G. Coriolis Email : Valerie(dot)Schneider(at)meteo(dot)fr *
> * 31057 TOULOUSE Cedex - FRANCE http://www.meteo.fr *
> ********************************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>

--
Jean-Max Reymond
dernière éruption de l'Etna: http://jmreymond.free.fr/Etna2002

Bonjour,

Le mercredi 4 Août 2004 15:09, Valerie Schneider DSI/DEV a écrit :
> Sous oracle la SGA est de 500 Mb.
> Pour PG le postgresql.conf :
> max_connections = 1500
> shared_buffers = 30000
> sort_mem = 50000

Ca me paraît beaucoup ... essayez de diviser par deux ... ou pas dix.
C'est aloué par process ... donc ca risque de vous pénaliser d'avoir une
valeur trop importante ...

> effective_cache_size = 200000
> et les valeurs par défaut pour les autres paramètres.

Quelles sont les valeurs de max_fsm_pages et max_fsm_relations ?
Que disent les deux dernières lignes du vacuum full verbose analyze; ?
Quelles sont les valeurs de random_page_cost, cpu_tuple_cost,
cpu_index_tuple_cost, cpu_operator_cost ?
Si vous êtes sur les paramètres par défaut vous pouvez déjà passer le
random_page_cost = 2 ou 3 ... la machine est rapide ...

> J'ai une table nommée "data" qui ressemble à :
> bench=> \d data
> Table "public.data"
> Column | Type | Modifiers
> ------------+-----------------------------+-----------
> num_poste | numeric(9,0) | not null
> dat | timestamp without time zone | not null
> datrecu | timestamp without time zone | not null
> rr1 | numeric(5,1) |
> qrr1 | numeric(2,0) | ...
> ... all numeric fields

Pourquoi utilisez-vous des Numeric ? Est-ce véritablement une obligation ? Ou
est-ce du au portage Oracle->PostgreSQL ?
Les Integers sont mieux gérés pour les CAST de l'optimiseur, et prennent moins
de place sur le disque.

> Indexes:
> "pk_data" primary key, btree (num_poste, dat)
> "i_data_dat" btree (dat)

A la vue des quatres requêtes ... je ne vois pas trop l'intérêt de l'index
i_data_dat ... mais bon c'est juste une histoire de place et de rapidité des
INSERT ... et puis vous faites peut-être des requêtes juste sur le
date ... :o)

> Ma première remarque est que la table occupe beaucoup plus de place
> sur PG (70 Gb) que sur oracle (35 Gb).
> Le calcul : 125 000 000 rows x 256 b = 32 Gb donne une idée du volume
> occupé, pas si mauvais pour oracle. Qu'en est-il pour PG ?
> Comment les données sont stockées ?

Il est clair qu'avec des Integer la place que le disque serait grandement
réduite ... et donc les accès en lecture seront aussi optimisés !

> Bien sûr lorsque je lance en parallèle 1000 requêtes Q3 ou Q4 les perf
> deviennent désastreuses !

La machine doit bien ramer en lecture ... c'est clair, il faut impérativement
optimiser la structure de la table ...

Cordialement,
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902