Re: Pb de perf sur une grosse base

Bonjour,
J'ai recréé ma table avec des types integer et real plutôt
que numeric.
Tout se trouve amélioré :
schema | relfilenode | table | index | reltuples | size
--------+-------------+------------------+------------+-------------+----------
public | 253442696 | data | | 1.25113e+08 | 29760016
public | 378639579 | data | i_data_dat | 1.25113e+08 | 2744400
public | 378555698 | data | pk_data | 1.25113e+08 | 3295584
La table occupe 28 Gb au lieu de 68 Gb !

Pour mes différentes requêtes, c'est nettement mieux mais pas encore ça :
oracle PG hier (numeric) PG aujourd'hui (integer/real)
Q1 <1s <1s <1s
Q2 3s 8s 4s
Q3 8s 1m20s 27s
Q4 28s 17m20s 6m47s

Le résultat du EXPLAIN ANALYZE :

Q1 :bench=> explain analyze select 'Q1',min(td),max(u) from data where
num_poste=1000 and dat between
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '2000
days'))::timestamp and
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '2000 days') +
interval '1 month' - interval '1 hour')::timestamp;

QUERY PLAN
--------------------------------------------------------------------------------
Aggregate (cost=2466.47..2466.47 rows=1 width=8) (actual time=261.777..261.778
rows=1 loops=1)
-> Index Scan using pk_data on data (cost=0.00..2463.41 rows=611 width=8)
(actual time=20.106..259.924 rows=744 loops=1)
Index Cond: ((num_poste = 1000) AND (dat >= (date_trunc('month'::text,
(to_timestamp('31012004'::text, 'ddmmyyyy'::text) - '2000
days'::interval)))::timestamp without time zone) AND (dat <=
(((date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) -
'2000 days'::interval)) + '1 mon'::interval) - '01:00:00'::interval))::timestamp
without time zone))
Total runtime: 262.145 ms
(4 rows)

Q2 : bench=> explain analyze select 'Q2',count(*) from data where num_poste
between 100 and 100+10 and dat between
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '3000
days'))::timestamp and
(date_trunc('month',to_timestamp('31012004','ddmmyyyy')-interval '3000 days') +
interval '1 month' - interval '1 hour')::timestamp;

QUERY PLAN
--------------------------------------------------------------------------------
Aggregate (cost=24777.68..24777.68 rows=1 width=0) (actual
time=4253.977..4253.978 rows=1 loops=1)
-> Index Scan using pk_data on data (cost=0.00..24762.34 rows=6138 width=0)
(actual time=46.602..4244.984 rows=7920 loops=1)
Index Cond: ((num_poste >= 100) AND (num_poste <= 110) AND (dat >=
(date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) -
'3000 days'::interval)))::timestamp without time zone) AND (dat <=
(((date_trunc('month'::text, (to_timestamp('31012004'::text, 'ddmmyyyy'::text) -
'3000 days'::interval)) + '1 mon'::interval) - '01:00:00'::interval))::timestamp
without time zone))
Total runtime: 4254.233 ms
(4 rows)

Q3 : bench=> explain analyze select 'Q3',sum(rr1),count(ff) from data where
num_poste in (50,50+2);
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate (cost=963455.87..963455.87 rows=1 width=8) (actual
time=27668.666..27668.667 rows=1 loops=1)
-> Index Scan using pk_data, pk_data on data (cost=0.00..962236.31
rows=243910 width=8) (actual time=16.251..27275.468 rows=250226 loops=1)
Index Cond: ((num_poste = 50) OR (num_poste = 52))
Total runtime: 27673.837 ms
(4 rows)

Q4 : bench=> explain analyze select 'Q4',count(*) from data where num_poste
between 600 and 625;
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate (cost=14086174.57..14086174.57 rows=1 width=0) (actual
time=428235.024..428235.025 rows=1 loops=1)
-> Index Scan using pk_data on data (cost=0.00..14076910.99 rows=3705431
width=0) (actual time=45.283..424634.826 rows=3252938 loops=1)
Index Cond: ((num_poste >= 600) AND (num_poste <= 625))
Total runtime: 428235.224 ms
(4 rows)

Concernant les paramètres de postgresql.conf :

# - Memory -

shared_buffers = 30000 # min 16, at least max_connections*2, 8KB each
#sort_mem = 1024 # min 64, size in KB
sort_mem = 5000 # min 64, size in KB
#vacuum_mem = 8192 # min 1024, size in KB

# - Free Space Map -

#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~50 bytes each

# - Planner Cost Constants -

#effective_cache_size = 1000 # typically 8KB each
effective_cache_size = 200000 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)

Merci pour votre aide, Valérie.

>From: Hervé Piedvache <herve(at)elma(dot)fr>
>To: pgsql-fr-generale(at)postgresql(dot)org, Valerie Schneider DSI/DEV
<Valerie(dot)Schneider(at)meteo(dot)fr>
>Subject: Re: [pgsql-fr-generale] Pb de perf sur une grosse base
>Date: Wed, 4 Aug 2004 17:12:05 +0200
>User-Agent: KMail/1.6.2
>MIME-Version: 1.0
>Content-Disposition: inline
>Content-Transfer-Encoding: 8bit
>
>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)

Oui, je n'ai cité que qq requêtes, d'autres nécessitent cet index.

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

********************************************************************
* 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 *
********************************************************************