Vous n'êtes pas identifié(e).
Bonjour,
Suite à la migration d'une base de données Oracle 10g vers PostgreSQL 9.2.2.1, je rencontre quelques problèmes de performances sur un enchainement de procédures stockées.
Suite à différents tests est à l'activation des statistiques (pg_stat) j'ai pu identifier que le problème viens des updates. Ces updates sont relativement simples mais "très" nombreux. Chose étonnante, le nombre d'updates executés par seconde diminue au fur et à mesure du traitement...
J'ai tout d'abord accusé un problème de buffer et j'ai donc boosté les paramètres suivants (volontairement excessif) :
shared_buffers = 1GB
work_mem = 512MB
effective_cache_size = 2GB
temp_buffers = 512MBsans succès...
Pour rentrer dans le détail voici le hiérarchie d'appel des procédures concernées :
-- point d'entrée, executé en 70 min
procedureA
dos_encours CURSOR FOR
SELECT *
FROM table1
WHERE colA <= date AND TRIM(colB) IS NOT NULL AND (colB) <> ' ' ORDER BY colA;
FOR l_dos IN dos_encours LOOP
PERFORM procedureB('E',l_dos.colA,l_dos.colB,...);
END LOOP;
-- appelé 28 000 fois
procedureB
...
IF ancien > 0 THEN
PERFORM procedureC(...);
PERFORM procedureD(...);
PERFORM procedureE(...);
PERFORM procedureD(...);
PERFORM procedureE(...);
END IF;
-- appelé 28 000 fois
procedureC
WHILE
...
IF
...
FOR
...
procedureF(...);
ELSE IF
...
...
procedureD(...);
procedureE(...);
-- appelé entre 28 000 et 84 000
procedureD et procedureE
IF
PERFORM procedureF(...);
ELSE
PERFORM procedureF(...);
-- appelé 335000 fois
procedureF
IF quel = 1 THEN
PERFORM procedureG(...);
ELSIF quel = 2 THEN
...
-- appelé au total 112 000 fois, self_time : 65 min
procedureG
IF moy>0 THEN
UPDATE table2
SET colA=to_char(to_number_arob(rtrim(colA),'9999990.99')+moy,'9999990.99')
WHERE colB=param1 AND colC=param2;
END IF;
UPDATE wingescph.f_suivi
SET colA=to_char(moy,'9999990.99')
WHERE colB=param1 AND colC=param2 AND to_number_arob(TRIM(colA),'9999990.99')<moy;La table 2 est une table de 60 lignes disposant de 42 colonnes de type « character » avec un index composite de type btree sur les colonnes colB, colC.
Quelqu'un aurait-il une explication sur ce comportement qui reste étonnant pour de simples update, surtout quand oracle s'en sort très bien (moins de 3 minutes de traitement) ?
Hors ligne
Non, pas d'explication. Même si votre message est très détaillé, cela donne paradoxalement assez peu d'informations.
Par exemple, vous donnez un bon de configuration mais sans indiquer quoi que ce soit au niveau matériel (notamment la RAM vu que vous parlez uniquement de paramètres de mémoire). Cela étant donné, ça n'est pas forcément le problème qui vous touche.
Si je comprends bien, la procédure A est la procédure principale et sa durée d'exécution est de 70 minutes. Et sur ces 70 minutes, la procédure G est responsable de 65 minutes, c'est bien ça ?
Guillaume.
Hors ligne
Oui c'est bien cela, la procédure G prend 65/70 du temps de traitement.
Niveau configuration matériel je suis sur un windows server 2003, avec 8Go de RAM, et un quad-core 2.4Ghz. La base de données utilise 500Mo sur disque et les index 200Mo (car le modèle utilise beaucoup de colonne de type character)
Pour les performance, le processeur est utilisé à 25% durant le traitement et côté RAM pas de monté flagrante (500Mo / 1Go consommé).
Pour les paramètres Postgres j'ai aussi essayé de jouer avec le vaccum et la synchronisation des commit, sans trouver de valeurs intéressante.
Hors ligne
Un truc sur lequel je n'avais pas tilté. La procédure est 112000 fois et va donc faire potentiellement 112000 UPDATE dans une table de 60 lignes ? vu la façon différente dont PostgreSQL et Oracle gère MVCC, je pense que PostgreSQL est à son désavantage ici et que c'est certainement fortement à l'avantage d'Oracle, d'où les meilleures performances que vous voyez sur Oracle.
Guillaume.
Hors ligne
C'est en effet ma crainte...
Je vais faire quelques tests en posant dans la session des LOCK sur la table 2 et en changeant le niveau d'isolation de la transaction (READ UNCOMMITED). On verra bien.
Hors ligne
Pas la peine d'essayer read uncommited, ce n'est pas implémenté sous PostgreSQL. De toute façon, je ne vois pas bien le lien avec le problème.
Guillaume.
Hors ligne
Salut,
Est-il logique que 120000 updates sur une seule table aussi petite et dans une seule transaction prenne plus de 1 heure ?
Quelles sont les différences majeures dans le système MVCC de Oracle et Postgresql qui pourraient expliquer un tel écart, à savoir moins de 3 min pour Oracle et plus de 60 sous Postgresql ?
Hors ligne
Est-il logique que 120000 updates sur une seule table aussi petite et dans une seule transaction prenne plus de 1 heure ?
C'est ridiculement lent. Maintenant, le contexte joue bien sûr. Mais tout de même, c'est très lent.
Sur une très petit table, sans contrainte, un UPDATE d'un million de lignes prend 5 secondes sur mon poste de bureau (ie, pas sur un serveur méga blindé).
Quelles sont les différences majeures dans le système MVCC de Oracle et Postgresql qui pourraient expliquer un tel écart, à savoir moins de 3 min pour Oracle et plus de 60 sous Postgresql ?
Oracle met à jour directement la ligne de la table et enregistre l'ancienne valeur dans un UNDO LOG. PostgreSQL marque la ligne comme supprimée et en crée une autre, ce qui nécessite notamment de mettre à jour les index.
Guillaume.
Hors ligne
Merci gleu pour tes réponses qui nous on permis de creuser le sujet.
Nous avons finalement fait plusieurs tests avec les éléments suivants :
- Utilisation de table temporaire (en mémoire car work_mem et temp_mem supérieur à la volumétrie de la table)
- utilisation de l'option UNLOGGED lors du create de la table 2
- utilisation de LOCK explicite au début du traitement
- utilisation d'isolation de transaction de type serializable (d'autres forums semblaient y croire...)
toutes ces pistes n'ont rien données.
Finalement nous avons retourné le problème et avons décidé de changer le comportement de notre procedureA. Ainsi le curseur dos_encours prend maintenant en compte un offset et un limit afin de découper en sous ensemble les données à traiter.
De cette façon la procédure est appelée plusieurs fois à la suite au lieu d'une seule. Ceci a l'avantage de fermer et reouvrir une transaction à chaque fois et ainsi laisser le temps au autovaccum de passer entre les deux appels (wait forcé de 50ms entre chaque appel) afin de supprimer les lignes mortes dans la table.
Après plusieurs tests il s'avère que le traitement de 1000 dossiers à la fois reste le plus performant. Nous avons ainsi pu réduire le temps et passer à 2 min de traitement.
Hors ligne