Optimiser la config pour mettre plus de chose en RAM ?

[Michael REMY]

bonjour/bonsoir,

Je recherche une optimisation miracle avant un changement de matériel plus puissant.

Je voudrais savoir quel paramètre modifier dans la config de postgresql (8.1) pour qu'il consomme plus de mémoire et fasse moins d'accès disque.

En fait j'ai une grosse requête de cette forme :

Code :

1
2
3
SELECT id FROM sale_order_line 
WHERE state IN ('confirmed','confir_wait','manquant')
 AND (id IN (1,2,3....30000))

je n'ai pas écrit la requête entière car c'est impossible ici (elle fait 170Ko dans une fichier texte) mais la partie finale contenant la clause 'ID IN' contient 30000 énumérations (oui je ne plaisante pas).

Le traitement prend 16s, le cpu tourne à 100% et seulement 5% de la mémoire est utilisée). J'ai relevé ces valeurs via la commande TOP sous linux.
donc voilà j'aimerais changer quelques truc dans la config (de base) pour booster cela. Si quelqu'un a une solution miracle (autre que la réécriture du code sql car je n'ai pas la main dessus (c'est un erp)).

merci de votre aide.

infos hardware : pg tourne dans une VM debian etch4, installée sur une machine avec cpu core i7 2.8Ghz, 2Go de RAM accordée à la vm et 2 cores). La VM (fichier 10Go) est sur un ssd bas de gamme.. Pg est installé par défaut, seul le max_stack_deph a été changé et porté à 8096 sinon la requête ne fonctionne pas.

[estofilo]

Par rappport à la config de base qui est prévue pour un tout petit serveur, je dirais d'essayer de changer:
shared_buffers: mettre 512M
work_mem: 10M
effective_cache_size: 1G

Un EXPLAIN ANALYZE sur la requête serait utile aussi pour voir comment il gère le filtrage sur la colonne id.

[punkoff]

Bonjour,

2 choses que je ne comprend pas :
- D'où viennent toutes ces id ? d'une autre requête ?
- A la fin de la requête il y a un joli order by X limit 101 => ne pouvez-vous pas limiter la clause IN ?

[Michael REMY]

Envoyé par punkoff

Bonjour,

2 choses que je ne comprend pas :
1 - D'où viennent toutes ces id ? d'une autre requête ?
2 - A la fin de la requête il y a un joli order by X limit 101 => ne pouvez-vous pas limiter la clause IN ?

1 - demandez donc au concepteur de l'erp (openerp) qui n'avait sans-doute pas prévue 30000 dans une table
2 - 101 est le bombre par défaut défaut de toute recherche faite via l'interface graphique

bonjour,

je reviens ici car depuis, la base a augmenté et les temps de réponses aussi hélas...
j'ai fait quelques tests et prises d'informations.

situation 0 : sur le vieux système non virtualisé (2x xeon 5130 (2.0GHz) 2 Go RAM HD scsi), la grosse requête prend maintenant 32s.
situation 1 : sur une workstation i7-975 (3.3GHz) 2Go RAM HD sata (virtualisé), la grosse requête prend 15s
situation 2 : sur un portable très récent i7 2640M (2.8Ghz) + 2Go rAM + SSD (toujours virtualisé) prend 14s

Sachat qu'il n'y a pas de matériel deux fois plus rapide que celui en situation 1, je commence à me faire des soucis !

En situation 0, la config de pg a été amélioré mais sans miracle.
En situation 1 et 2 : c'est la confif de base (d'installation)

Dans les trois situations, le cpu tourne à 95-100% et seulement 5-10% de ram utilisée seulement ! La base de fait que 150Mo. LA table ne compte que 30000 enregistrement et la requête en sort que 127.

où est le problème ? où est le fameux paramètre à changer ? quel est le goulot d'étranglement ?


merci de votre aide.

ps: j'ai refait un explain analyse sur la situation 1 :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
"Limit  (cost=22272.92..22273.17 rows=101 width=4) (actual time=40.590..40.621 rows=84 loops=1)"
"  ->  Sort  (cost=22272.92..22273.29 rows=149 width=4) (actual time=40.589..40.602 rows=84 loops=1)"
"        Sort Key: id"
"        ->  Bitmap Heap Scan on sale_order_line  (cost=3262.10..22267.54 rows=149 width=4) (actual time=4.669..40.470 rows=84 loops=1)"
"              Recheck Cond: ((((state)::text = 'confirmed'::text) OR ((state)::text = 'confirmed_wait'::text) OR ((state)::text = 'manquant'::text)) AND ((id = 17735) OR (id = 34489) OR (id = 36602) OR (id = 36671) OR (id = 17718) OR (id = 30733) OR (id =  (..)"
"              Filter: ((id = 7841) OR (id = 18141) OR (id = 13652) OR (id = 13651) OR (id = 13650) OR (id = 13597) OR (id = 13521) OR (id = 20416) OR (id = 33657) OR (id = 32359) OR (id = 32358) OR (id = 24557) OR (id = 32441) OR (id = 24556) OR (id = 2455 (..)"
"              ->  BitmapAnd  (cost=3262.10..3262.10 rows=277 width=0) (actual time=3.799..3.799 rows=0 loops=1)"
"                    ->  BitmapOr  (cost=46.23..46.23 rows=5207 width=0) (actual time=0.939..0.939 rows=0 loops=1)"
"                          ->  Bitmap Index Scan on sale_order_line_state  (cost=0.00..40.24 rows=4925 width=0) (actual time=0.868..0.868 rows=4895 loops=1)"
"                                Index Cond: ((state)::text = 'confirmed'::text)"
"                          ->  Bitmap Index Scan on sale_order_line_state  (cost=0.00..2.00 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1)"
"                                Index Cond: ((state)::text = 'confirmed_wait'::text)"
"                          ->  Bitmap Index Scan on sale_order_line_state  (cost=0.00..3.99 rows=282 width=0) (actual time=0.055..0.055 rows=260 loops=1)"
"                                Index Cond: ((state)::text = 'manquant'::text)"
"                    ->  BitmapOr  (cost=3215.62..3215.62 rows=1605 width=0) (actual time=2.658..2.658 rows=0 loops=1)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)"
"                                Index Cond: (id = 17735)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)"
"                                Index Cond: (id = 34489)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)"
"                                Index Cond: (id = 36602)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)"
"                                Index Cond: (id = 36671)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)"
"                                Index Cond: (id = 17718)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)"
"                                Index Cond: (id = 30733)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)"
"                                Index Cond: (id = 18491)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)"
"                                Index Cond: (id = 31810)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)"
"                                Index Cond: (id = 6221)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)"
"                                Index Cond: (id = 37533)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)"
"                                Index Cond: (id = 35502)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)"
"                                Index Cond: (id = 38551)"
"                          ->  Bitmap Index Scan on sale_order_line_pkey  (cost=0.00..2.00 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)"
"                                Index Cond: (id = 38550)"
 
...etc
 
"Total runtime: 61.115 ms"

[estofilo]

En situation 1 tu dis que ça prend 15s mais l'explain analyze montre 61 millisecondes.
Pourquoi ces temps d'exécution n'ont rien à voir entre eux?

[Michael REMY]

Envoyé par estofilo

En situation 1 tu dis que ça prend 15s mais l'explain analyze montre 61 millisecondes.
Pourquoi ces temps d'exécution n'ont rien à voir entre eux?

c'est pas plutôt 61000 ms ? (le point séparateur de milliers).
car j'ai remarqué que c'était encore plus long avec l'explain. une minute me semble la bonne valeur.

peut-être que pgadmin3 ne renvoie pas la bonne valeur du coup. quoi qu'il en soit au chrono c'est bien 15 à 16s !

[estofilo]

C'est bizarre. Le point n'est pas censé être séparateur de milliers, même au format américain soit c'est la virgule, soit pas de séparateur du tout ce qui me semble-t-il est le cas pour les temps d'exécution.
Si tu mets quelque part le contenu zippé de ta table sale_order_line avec les 30k lignes je veux bien essayer ta requête sur les vraies données pour corroborer ou pas tes résultats.

[ymoreau]

Testé à l'intant sur deux serveurs. Sur un postgresql en français la décimale est avec une virgule, sur un postgresql en anglais (je ne sais pas s'il y a une différence anglais/américain) la décimale est avec un point. Les milliers ne sont pas séparés.

Il pourrait être intéressant de lancer la requête dans psql au lieu pgadmin pour voir.