cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

Michael REMY · 13/07/2011 09:15:56

bonjour,

je reprends la maintenance d'un serveur où il y a postgresql 8.1, debian etch et un erp installé dessus : je ne peux pas faire de mise à jour à cause de l'applicatif en prod.

voilà, le problème est qu'une certaine opération dans l'ERP cause une lenteur incroyable pendant 20s, mettant le cpu à 100% et la ram avec seulement 1.5% d'utilisée. J'ai obtenu ces valeurs en faisant un top pendant le traitement.

ya-t-il moyen de modifier un parametre dans la confif permettant à plus de ram d'être utilisée pour optimiser la vitesse. je ne sais pas comment obtenir les infos matériel du serveur. de mémoire,

y-a-t-il un moyen de savoir exactement quelles requêtes SQL sont exécutés pendant cette lenteur ?

je me demande aussi si c'est pas le disque dur qui fait trop de chose au lieu d'utiliser la RAM, mais comment le savoir ?

merci à vous et de votre aide (ou de me mettre sur une piste flagrante)

merci de votre aide.

kenrio · 13/07/2011 10:18:19

Bonjour,

je commencerais par chercher qui fait quoi pour avoir une idée de ce qu'il se passe non ? donc regarde dans ton pg_stat_activity pour connaitre l'utilisateur et ce qu'il fait.
++

cedric · 21/07/2011 19:55:03

Michael REMY a écrit :

bonjour,
je reprends la maintenance d'un serveur où il y a postgresql 8.1, debian etch et un erp installé dessus : je ne peux pas faire de mise à jour à cause de l'applicatif en prod..

Vous êtes conscient que cette version n'est plus maintenue officiellement ? (autrement Red Hat doit encore la distribuer donc la maintenir je suppose, et 2ndQuadrant fournit une extension de 2 ans sur la durée de vie des versions de PostgreSQL)

cedric · 21/07/2011 20:00:42

Michael REMY a écrit :

voilà, le problème est qu'une certaine opération dans l'ERP cause une lenteur incroyable pendant 20s, mettant le cpu à 100% et la ram avec seulement 1.5% d'utilisée. J'ai obtenu ces valeurs en faisant un top pendant le traitement.
ya-t-il moyen de modifier un parametre dans la confif permettant à plus de ram d'être utilisée pour optimiser la vitesse. je ne sais pas comment obtenir les infos matériel du serveur. de mémoire,
y-a-t-il un moyen de savoir exactement quelles requêtes SQL sont exécutés pendant cette lenteur ?
je me demande aussi si c'est pas le disque dur qui fait trop de chose au lieu d'utiliser la RAM, mais comment le savoir ?
merci à vous et de votre aide (ou de me mettre sur une piste flagrante)
merci de votre aide.

Déjà, en ajustant log_min_duration_statement vous pourrez obtenir la requete dans les journaux de postgresql.

Ensuite, pour le matériel, il y a plusieurs solutions, lshw est assez pratique et probablement suffisant. (sinon cat /proc/cpuinfo et cat /proc/meminfo et cat /sys/block/... pour voir du coté matériel disque dur)

Si top montre un cpu a 100% c'est 'normal', reste a voir la répartition, utilisez 'iostat' ou 'vmstat' ou 'sar' (cf paquet 'sysstat') pour tracer l'utilisation des ressources pendant l'exécution de la requete offensante, sar permet d'étudier 'après coup'.

Michael REMY · 21/07/2011 23:05:44

bonjour Cédric,

ça fait plaisir de voir un expert postgresql (car j'en ai bien besoin!).

j'ai récupéré quelques infos :
Le vieux serveur est un bi-xeon 5130 (donc 2x2 cores à 2.Ghz) mais ça ne sert quasiment à rien pour postgresql81.
il y a 2 Go de RAM et 160go de disque dur montés en raid.

La base de données prend moins de 30Mo une fois dumpée ce qui est vraiment minime !

La requête incriminée a été trouvée (grâce au log_duration mis à 1s).
C'est une requête longue-énorme (170Ko copiée-collée dans un fichier texte) mais une requête extrement simple !

Elle est ainsi :

SELECT id
FROM sale_order_line 
WHERE (state IN ('confirmed','confirmed_wait'))
AND (id IN (27403,27405,xxxxxxxxxxxxxxx))
ORDER BY id DESC limit 50

Mais biensûr (ou hélas) , il faut remplacer dans ma citation XXXX par 20000 énumération d'ID comme les 2 (27403,27405) pré-cité. je le répete, je ne peux pas modifier le code de l'ERP, je sais que la requête est stupide.
Elle prend actuellement 15s600 à s'exécuter pendant laquelle le cpu est à 100% et moins de 6% de RAM est utilisé.
Il y a déjà un index sur state et un sur id évidement ! la table et toute la base a été vaccumée completement, et réindexée !
En recopiant la requpete dans pgadmin, j'arrive à reproduire cette lenteur de 15s !

Avant d'envisager un upgrade matériel, je voudrais vraiment faire le tour des paramétres optimizables de postgres (voir du linux) pour gagner en temps si c'est possible !
Pour infos, j'ai réconstruit dans une vm workstation le même système et la requête prend 7s avec 1 core à 3.3Ghz provenant d'un core i7-975 (soit un cpu plus récent de 5 ans par rapport au bi-xeon en prod). Mais même 7s c'est bcp pour une requête exécuté au moins 50fois par jour par le staff.

alors par où commencer ? une idée ? n'hésitez pas à me fournir une option qui augmentera l'occupation mémoire au détriment de l'utilisation du disque dur !

je suis tout-oui !

Dernière modification par Michael REMY (21/07/2011 23:07:17)

gleu · 22/07/2011 00:03:27

Il nous faudrait un EXPLAIN ANALYZE et au moins la taille de la base (la taille d'un dump n'a que peu à voir avec la taille de la base réelle) pour commencer.

Michael REMY · 22/07/2011 09:07:01

pour obtenir la taille de la base j'ai fait cela sous pgadmin : : SELECT pg_database_size('mybase');

et j'ai obtenu : 845 701 816 (800 Mo si je comprends bien !!!!)

et voiçi le explain analyse de la requête problématique et récurrente :

"Limit (cost=0.00..26360.76 rows=50 width=4) (actual time=83.854..645.393 rows=50 loops=1)"
" -> Index Scan Backward using sale_order_line_pkey on sale_order_line (cost=0.00..1209958.84 rows=2295 width=4) (actual time=83.849..645.260 rows=50 loops=1)"
" Filter: ((((state)::text = 'confirmed'::text) OR ((state)::text = 'confirmed_wait'::text) OR ((state)::text = 'manquant'::text)) AND ((id = 27403) OR (id = 27405) OR (id = 27399) OR (id = 16583) OR (id = 27395) OR (id = 19018) OR (id = 27394) OR (i (..)"
"Total runtime: 698.862 ms"

une idée ?

gleu · 22/07/2011 09:57:59

PostgreSQL a donc choisi de faire un parcours inverse d'index pour obtenir le tri. Elle pensait récupérer 2295 lignes d'après le filtre mais le limit a limité l'ensemble des lignes à 50. le tout a mis 600ms à s'exécuter. 600ms pour 50 lignes, c'est beaucoup. Il serait intéressant de voir si la table et l'index ne sont pas fragmentés. Quelle est la taille de la table sale_order_line et de son index sale_order_line_pkey ? Quelque chose qui pourrait être intéressant à faire est un REINDEX de l'index sale_order_line_pkey. Attention, ça va bloquer les écritures sur les tables le temps de son exécution.

cedric · 22/07/2011 10:15:00

Alors il y a de nombreux points.

Tout d'abord vous avez très probablement une configuration d'origine (shared_buffers=32MO, soit 1.5% de 2GO de RAM),
ensuite vous avez un phénomène de 'bloat' important (en gros, la base est complètement fragmentée et contient beaucoup d'espace 'mort', 30MO de dump, meme avec énormément d'index ajouté a cela, il est dur d'obtenir 800MO)
ensuite vous utilisez une version obselete qui ne bénéficie pas de correction (pas fatal dans ce cas précis mais tout de meme important), mais il se trouve que la version 8.2 a eu des optimisations pour gérer les IN (bien que je ne sois pas certain que cela importe beaucoup dans votre cas)

Suggestions:
* Si vous pouvez bloquer la base de données (la nuit par exemple), faites un VACUUM FULL et REINDEX de toute la base (c'est possible en ligne de commande avec 'vacuumdb' et 'reindexdb', ou via une console SQL). Vous pourriez bénéficier de CLUSTER qui est plus rapide mais il vous faudrait pour cela initier le CLUSTER sur chaque table (en général sur la PK, mais pas toujours), peut etre utile pour le cas ci-dessous.

* Si vous ne pouvez pas bloquer la base de données trop longtemps, alors il faut procéder par table, *toujours* faire un reindex apres un vacuum full. (le vacuum fulll réaligne les données et de fait fragmente l'index, d'ou le reindex obligatoire, le cluster, lui, ordonne les données de la table selon un index puis fait le reindex)

* Modifiez votre configuration, postgresql.conf :
- augmenter shared_buffer me semble une bonne idée... en fonction du volume apres vacuum full/reindex, je suppose qu'un 64MO sera pas mal (a voir)
- si la base a bien un volume restreint, il est probable qu'elle soit entièrement en cache (soit dans la shared memory utilisée par postgresql, soit dans le cache dique du système d'exploitation), alors random_page_cost=0.1 et seq_page_cost=0.1. (le but est surtout d'avoir la meme valeur pour les deux paramètres, 0.1 est vraiment bas mais ca peut le faire)
- FSM: si vous restez en 8.1 il faut multiplier par au moins 10 les deux param liés à la FSM, c'est cela qui a causé du bloat dans votre base (plus absence de procédure de maintenance probablement).

L'upgrade du serveur est souhaitable mais il se trouve que passer de la 8.1 a 9.0 peut causer des soucis: possibilité d'avoir des caractères non-valides en 8.1 (fixé en 8.2) donc difficultés pour restaurer le dump. Possibilité d'application mal codée qui ne fonctionnera plus apres 8.1 car le serveur a réduit les CAST automatiques, et donc l'application peut avoir besoin de CASTer explicitement là où c'était inutile en 8.1....
Il faudra upgrader un jour ou l'autre de toute façon ....pensez-y.

cedric · 22/07/2011 10:18:24

EDIT: il est probable que si vous pouvez stopper la base, un pg_dump suivit d'un pg_restore soit plus rapide que de faire un vacuum ful/reindex. (cad on dump la base, on la renomme puis on restaure la base depuis le dump)... le paramètre maintenance_work_mem permet d'augmenter la mémoire utilisable pour la maintenance (cela inclu la construction des index) et l'augmenter considérablement peut etre un vrai plus.

Michael REMY · 22/07/2011 10:38:33

j'ai déjà fait un réindex et un vaccum de cette table.
la taille de la table est 21MB (SELECT pg_size_pretty(pg_total_relation_size('sale_order_line'));)
et la taille des indexes vaut 8kb

En fait j'ai bcp de table à 20mb. n'y-a-t-il pas un paramètre pour dire à posgres de mieu les gérer ?

gleu · 22/07/2011 21:30:41

Comment avez vous récupéré la taille de l'index ?

Michael REMY · 22/07/2011 22:36:26

gleu a écrit :

Comment avez vous récupéré la taille de l'index ?

pgadmin donne cette information et j'ai aussi aussi une commande sql (je l'ai pas sous la main) qui la donne.

gleu · 23/07/2011 08:50:15

Je suis très étonné par la taille de la table et de l'index. Ils sont vraiment petits, ils devraient bien tenir en mémoire. Du coup, je trouve les 600ms un peu cher pour ne récupérer que 50 lignes. Mais si c'est bien leur taille, dans ce cas, il y a peu de choses que vous pouvez faire.

Michael REMY · 22/01/2012 14:29:06

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 Go RAM HD scsi), la grosse requête prend maintenant 32s.
situation 1 : sur une workstation i7-975 2Go HD sata (virtualisé), la grosse requête prend 15s
situation 2 : sur un portable très récent i7 2640M (208Ghz) + 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 :

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

cedric · 22/01/2012 16:07:05

très sincèrement, je vous conseille de considérer de passer du temps sur la mise à jour de PostgreSQL, ce qui est sûrement l'opération la plus rentable que vous pourrez faire.
Se tourner du coté de l'ERP et lui consacrer une mise à jour me semble tout aussi pertinent (quel ERP est-ce donc ?)

gleu · 22/01/2012 16:49:40

Bien d'accord avec Cédric. De toute façon, tourner avec une 8.1 (donc avec une version qui n'est plus corrigée depuis un moment) n'est pas une bonne chose.

Michael REMY · 22/01/2012 17:04:23

Le mieux que je puisse faire est de passer à PG8.2 car j'ai déjà assuré la compatibilité du reste (python, erp..) mais je dois considéré de l'installer sur Windows (donc hors VM) et non plus directement sur le serveur linux (obsolète aussi).

gleu · 22/01/2012 17:06:27

PostgreSQL 8.2 n'est plus maintenu depuis début décembre. Il est préférable de passer à une version plus récente.

Michael REMY · 22/01/2012 19:33:04

re-bonjour,

voilà j'ai testé la version 8.2 installée hors vm (donc soux Win7 x64). Les résultats sont bien meilleur : je passe de 16s à moins d'une seconde pour cette grosse requête. J'ai aussi testé d'autres traitements et on gagne encore plus, surtout dans les recherches.
Pour ceux que ça intéresse, je place là-bas un tab récapitluatif (non terminé) : http://www.michael-remy.fr/forum/postgr … rmance.jpg

Il me reste encore à tester sur le portable SSD et il faudrait que je trouve aussi un moyen de réinstaller un linux etch4 avec pg8.2 mais c'est pas gagné pour ce dernier cas.

Pour infos, en téléchargeant pg8.2 sur le site officiel, il est indiqué que la version Windows est encore maintenant de la 8.2 (mais plus les autres plateformes). Enfin c'est ce que je comprends de cette phrase : "Note: Only PostgreSQL 8.2 and above are supported on Windows."

gleu · 22/01/2012 22:47:57

Non, cette phrase veut dire que les versions antérieures à la 8.2 (donc en fait les versions 8.0 et 8.1) ne sont pas des versions stables pour la plateforme Windows. C'est clair que maintenant que la 8.2 est déclarée obsolète, il serait bon de revoir cette phrase.

gleu · 22/01/2012 22:50:28

Pour info, je viens d'en parler à Magnus Hagander. Une mise à jour de la page devrait être faite rapidement. Merci pour l'info.

gleu · 22/01/2012 23:04:37

Corrigé (http://git.postgresql.org/gitweb/?p=pgw … be514617c8). La modification devrait être apparente sur le site web d'ici quelque temps (le temps que la propagation se fasse).

matrixise · 25/01/2012 11:03:51

Bonjour,

Je connais très bien l'ERP dont tu parles. Mais de quelle version ? Un 4.2 ou un 5.0 ?

Bien à toi

#1 13/07/2011 09:15:56

cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#2 13/07/2011 10:18:19

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#3 21/07/2011 19:55:03

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#4 21/07/2011 20:00:42

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#5 21/07/2011 23:05:44

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#6 22/07/2011 00:03:27

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#7 22/07/2011 09:07:01

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#8 22/07/2011 09:57:59

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#9 22/07/2011 10:15:00

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#10 22/07/2011 10:18:24

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#11 22/07/2011 10:38:33

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#12 22/07/2011 21:30:41

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#13 22/07/2011 22:36:26

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#14 23/07/2011 08:50:15

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#15 22/01/2012 14:29:06

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#16 22/01/2012 16:07:05

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#17 22/01/2012 16:49:40

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#18 22/01/2012 17:04:23

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#19 22/01/2012 17:06:27

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#20 22/01/2012 19:33:04

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#21 22/01/2012 22:47:57

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#22 22/01/2012 22:50:28

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#23 22/01/2012 23:04:37

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

#24 25/01/2012 11:03:51

Re : cpu à 100% avec 1.5% de ram utilisé, comment optimiser la config ?

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux