IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PostgreSQL Discussion :

CDC sous PostgreSQL


Sujet :

PostgreSQL

  1. #1
    Futur Membre du Club
    Inscrit en
    Novembre 2010
    Messages
    24
    Détails du profil
    Informations forums :
    Inscription : Novembre 2010
    Messages : 24
    Points : 8
    Points
    8
    Par défaut CDC sous PostgreSQL
    Bonjour,

    Je suis actuellement sur un projet de mise en place d'un journal qui tient en temps réel toutes les modifications, insertions et suppressions de certaines tables dans la base de données.
    J'ai commencé par utiliser un outils d'ETL (Talend) qui possède des composants CDC (Change Data Capture) et qui permet de gérer ce genre de mécanismes mais après plusieurs tests cela ne me convenait pas vraiment.
    J'ai donc décidé de gérer cela moi même au sein du SGBD (ici postgre) et donc j'ai créé une table 'journal' qui s'alimente en fonction des Insert, Update et Delete d'une table source.
    Cette table contient donc tous les champs de la table source et en plus un op_id (l'id de la table) une date_debut (date d'insertion ou de modifs) et une date_fin (date de modifs ou date de suppression) et en fait si j'ai par exemple 1 insertion, 4 modifs sur le même enregistrement, puis une suppression, mon journal aura 5 lignes (voir procédure pour l'aspect du journal)
    J'ai donc créé des triggers pour 'catcher' mes insert, update et delete qui vont appeler chacun une procédure précise qui va gérer le journal.

    J'en arrive à mon problème : pour l'insertion tout va bien, mes traitements conservent une rapidité correcte mais lorsque je lance un update en masse (par exemple passer en UpperCase 2 colonnes de toute la table) le temps d'exécution est multiplié par 100. Pareil pour le Delete.
    Je sais que mes procédures lance des update sur le journal dans le cas de l'update et du delete sur la source, ça doit avoir un rapport avec ça. Je cherche désespérément comment arranger tout ça mais je ne trouve pas.

    J'ai mis ma procédure en fichier attachée.

    Merci d'avance pour mes sauveurs !
    Fichiers attachés Fichiers attachés

  2. #2
    Membre émérite
    Profil pro
    Inscrit en
    Octobre 2008
    Messages
    1 874
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : Octobre 2008
    Messages : 1 874
    Points : 2 890
    Points
    2 890
    Par défaut
    Il n'y a pas d'index sur table_log? Il en faudrait certainement pour accéler les UPDATE dessus.

  3. #3
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Faites vous des triggers ensembliste ou FOR EACH ROW ?

    D'autre part les mécanismes de CDC fait par l'intermédiaire de déclencheurs sont toujours très consommateurs en ressources et allongent par nature la durée des transactions, donc le verrouillage. C'est pourquoi lorsqu'ils sont intégrés dans le SGBDR ils ne reposent pas sur des déclencheurs, mais sur une lecture asynchrone du journal de transaction par exemple, comme c'est le cas de CDC et Change Tracking de MS SQL Server !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  4. #4
    Futur Membre du Club
    Inscrit en
    Novembre 2010
    Messages
    24
    Détails du profil
    Informations forums :
    Inscription : Novembre 2010
    Messages : 24
    Points : 8
    Points
    8
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Faites vous des triggers ensembliste ou FOR EACH ROW ?
    A +
    Je le fait 'FOR EACH ROW' comme noté dans mon fichier .sql attaché
    Par contre, vous dites qu'il est possible de mettre en place un CDC sans passer par des déclencheurs ?
    Comment faire sous postgreSQL ?

    Citation Envoyé par estofilo Voir le message
    Il n'y a pas d'index sur table_log? Il en faudrait certainement pour accéler les UPDATE dessus.
    Je vais vérifier pour les index sur la table 'journal'. Merci.

  5. #5
    Futur Membre du Club
    Inscrit en
    Novembre 2010
    Messages
    24
    Détails du profil
    Informations forums :
    Inscription : Novembre 2010
    Messages : 24
    Points : 8
    Points
    8
    Par défaut
    Bon,
    J'ai ajouté un index sur la table_log sur les champs qui forment la lé primaire de la table source (en gros les champs dont j'ai besoin dans la clause where de mon update) et c'est parfait !
    Je passe de 25 lignes updatées / sec à 1232 lignes updatées / sec...Il n'y a pas photo !

    Bon après je suis conscient que c'est plus lents que lorsqu'il n'y a pas de journalisation branchée mais c'est logique puisque je fais 1 update et 1 insert sur la table_log supplémentaire pour chaque update sur la table source.
    Encore merci pour tout.

  6. #6
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    C'est idiot de faire des déclencheurs ligne à ligne alors que vous pouvez les faire ensembliste donc optimisés !!!
    Remplacez ce genre de lignes par :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
        UPDATE TABLE_LOG SET date_fin = current_timestamp WHERE <id> = OLD.<id> AND date_fin is null;
    Par :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
        UPDATE TABLE_LOG 
           SET date_fin = current_timestamp 
           WHERE <id> IN (SELECT id
                          FROM   OLD
                          WHERE  date_fin IS NULL);
    Et faites des triggers FOR EACH STATEMENT !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  7. #7
    Membre émérite
    Profil pro
    Inscrit en
    Octobre 2008
    Messages
    1 874
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : Octobre 2008
    Messages : 1 874
    Points : 2 890
    Points
    2 890
    Par défaut
    Non ça ne fonctionnera pas avec postgresql.

Discussions similaires

  1. 'SHOW TABLES' marche pas sous postgresql !?
    Par fet dans le forum PostgreSQL
    Réponses: 4
    Dernier message: 13/05/2004, 10h28
  2. select dans un trigger sous Postgresql
    Par kastor_grog dans le forum Requêtes
    Réponses: 1
    Dernier message: 03/09/2003, 18h00
  3. Comment entrer des lettres accentuées sous postgresql ?
    Par Chihuahua dans le forum Requêtes
    Réponses: 11
    Dernier message: 28/08/2003, 09h04
  4. Triggers sous PostGreSQL
    Par Phaf dans le forum Requêtes
    Réponses: 4
    Dernier message: 05/08/2003, 15h22
  5. Création d'utilisateur sous PostgreSQL 7.3.2 avec PHP
    Par duongkhang dans le forum PostgreSQL
    Réponses: 3
    Dernier message: 06/06/2003, 14h10

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo