Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 10 sur 10
  1. #1
    Expert Confirmé Sénior
    Homme Profil pro
    Inscrit en
    mai 2002
    Messages
    3 006
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : mai 2002
    Messages : 3 006
    Points : 4 555
    Points
    4 555

    Par défaut Maintenance de table

    Bonjour,


    Je me posais des questions concernant la maintenance des tables d'une base pgsql.


    Et plus en particulier sur les manières de gérer l'espace / la ré-organisation des tables / index.


    Prenons une table avec 3.000.000 de lignes assez simple :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    CREATE TABLE t_telephone_tel (
    cli_id REFERENCES t_client_cli (cli_id),
    ttl_id REFERENCES t_tel_type_ttl (ttl_id),
    tel_num varchar(10),
    PRIMARY KEY (cli_id, ttl_id));
     
     
    CREATE INDEX idx_tel_1
      ON t_telephone_tel
      USING btree
      (tel_num COLLATE pg_catalog."default" varchar_pattern_ops, cli_id );

    Mon test est le suivant :
    Suppression de 300.000 lignes
    vacuum analyze de la table.

    Insertion de 300.000 lignes =>
    la table n'a pas grossis => les lignes supprimées ont été réutilisées.
    Mon index idx_tel_1 n'a pas grossis (j'en déduis qu'il a bien été ré-indéxé ?)
    Par contre la taille de ma primary key a presque doublée 64Mo=> 101Mo !


    Comment celà ce fait ?
    Quelle possibilité ai-je à ce stade là ? Ré-org mes tables selon un cluster ?
    Code :
    1
    2
     
    cluster ma_table USING ma_pk
    Merci.

  2. #2
    Expert Confirmé
    Profil pro
    Inscrit en
    octobre 2008
    Messages
    1 818
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : octobre 2008
    Messages : 1 818
    Points : 2 507
    Points
    2 507

    Par défaut

    La commande CLUSTER réécrirait la table dans l'ordre de l'index, mais pas l'index lui-même.
    Pour remettre l'index de la clef primaire à sa taille minimale (=supprimer les trous) il faudrait faudrait lancer un REINDEX dessus.

  3. #3
    Expert Confirmé Sénior
    Homme Profil pro
    Inscrit en
    mai 2002
    Messages
    3 006
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : mai 2002
    Messages : 3 006
    Points : 4 555
    Points
    4 555

    Par défaut

    pourtant le fait de faire
    Code :
    1
    2
     
    CLUSTER ma_table USING ma_pk;
    ré-org bien mon index sur la pk, il passe de 101Mo à 64Mo (taille initiale).


    Mais sinon ok pour le REINDEX ca marche bien aussi, merci.


    Restera le fait que je ne comprend pourquoi l'index associé à la pk grossis dans ce cas de figure alors que l'autre index lui ne bouge pas (tous les deux sont des btree pourtant)


    edit :
    Pour le cluster :
    delete de 300k lignes.
    pas de vacuum analyze.
    cluster => la table et les index sont réorganisées.

    J'en déduit qu'il fait un vacuum avant de réécrire la table + un reindex.

  4. #4
    Expert Confirmé
    Profil pro
    Inscrit en
    octobre 2008
    Messages
    1 818
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : octobre 2008
    Messages : 1 818
    Points : 2 507
    Points
    2 507

    Par défaut

    Etonnant car je ne vois de mention de ça dans la doc de cluster. Si ça réécrit l'index on s'attendrait à ce que ça soit mentionné, je trouve. Ou alors j'ai pas lu assez attentivement.

    Pour les index en b-tree dont l'un augmente et l'autre pas, l'explication est peut-être sur le contenu des données. En principe l'espace libre dans un b-tree a plus de chances d'être réutilisé si on insère les mêmes valeurs qu'avant. Si par contre on insère des valeurs totalement différentes (ce qui est le cas avec les clefs auto-incrémentées) les trous dans b-tree ont plus de chances de rester tels quels. Mais c'est juste une théorie je ne sais pas ce qu'elle vaut dans le cas présent.

  5. #5
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 294
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 294
    Points : 27 316
    Points
    27 316

    Par défaut

    Contrairement à Oracle ou MS SQL Server, la maintenance par VACUUM ne défragmente pas physiquement les données. C'est pourquoi il y a toujours une fragmentation résiduelle des tables.
    Pour en avoir le cœur net, il suffit de recréer la table sous un autre nom et ajouter les données dedans, puis mesurer le volume de la table.
    Cela fait partit des points négatifs de PostGreSQL....
    Comme je l'indiquais dans ce post : http://www.developpez.net/forums/d11...s/#post6302371
    A la suite de cet article : http://www.developpez.net/forums/d11...resql-ecueils/

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  6. #6
    Expert Confirmé
    Profil pro
    Inscrit en
    octobre 2008
    Messages
    1 818
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : octobre 2008
    Messages : 1 818
    Points : 2 507
    Points
    2 507

    Par défaut

    En effet VACUUM ne défragmente pas, puisque c'est le rôle d'autres commandes que sont VACUUM FULL ou CLUSTER.

    C'est sûr que si on se trompe de commande on n'obtient pas le résultat escompté, que ce soit sous postgresql ou n'importe quel autre SGBD.

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 294
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 294
    Points : 27 316
    Points
    27 316

    Par défaut

    CLUSTER n'est pas à proprement parler une commande de défragmentation, mais d'organisation des lignes d'une table sous forme de CLUSTER (tri physique des lignes) ce qui libère les espaces mort des lignes, la table pouvant être à nouveau fragmentée par de nouvelles mises à jour. CLUSTER nécessite un verrou exclusif pendant toute l'opération.
    Cela correspond à la création d'un index CLUSTER puis sa destruction pour MS SQL Server.
    VACUUM FULL reconstruit la table et permet de libérer les espaces morts. Il ne concerne pas les index et nécessite un verrou exclusif pendant toute l'opération.
    Cela correspond à DBCC CLEANTABLE sous MS SQL Server.
    Pour défragmenter un index sous PG il faut le reconstruire. C'est à nouveau une opération bloquante (verrou exclusif).

    Il n'existe pas d'opération de défragmentation sous PostGreSQL comme :
    • ALTER INDEX ... REORGANIZE
    • DBCC INDEXDEFRAG ....

    qui permettent de défragmenter les index ou les tables sans poser de verrou...

    Comme je l'ai dit, cela fait partit des limitations de PG.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  8. #8
    Membre Expert Avatar de scheu
    Inscrit en
    juin 2007
    Messages
    1 503
    Détails du profil
    Informations forums :
    Inscription : juin 2007
    Messages : 1 503
    Points : 1 495
    Points
    1 495

    Par défaut

    J'ai également le même problème d'index qui grossit dans une table à nombre de lignes constant mais dans laquelle je fais des delete puis insert tous les jours
    J'ai dû mettre en place un reindex, mais qui a pour contrainte de locker la table le temps de l'opération (1h30 quand-même !)

    Sous Postgresql actuellement il est possible de "défragmenter" un index sans verrou sur la table avec l'option CREATE INDEX CONCURRENTLY. En gros on créé un 2ème index, sans locker la table, sur les mêmes colonnes, puis on supprime l'ancien et on renomme le nouveau

    Mais pour les indexes des PK ce n'est pas encore possible, dans la version actuelle de Postgresql (9.1), de recréer une PK avec "using index" pour spécifier un index déjà existant qu'on aurait créé avec l'option "concurrently"

    C'est donc une contrainte de la version actuelle, le seul moyen étant de planifier la tâche de maintenance (le reindex par exemple) dans un créneau horaire où la table n'est pas utilisée

    Pour le VACUUM FULL, c'est aussi contraignant car il locke la table le temps de l'opération. Dans les versions 8.x c'était tellement long qu'un export/import de la table était plus rapide. Là dans les dernières versions c'est apparemment plus rapide d'après la doc (je n'ai pas testé personnellement).
    A toi de voir en fonction du temps que ça prend sur ta table si c'est acceptable ou pas et si tu arrives à trouver un créneau horaire pour le faire, sans que personne n'accède à la table
    La théorie, c'est quand on sait tout mais que rien ne fonctionne.
    La pratique, c'est quand tout fonctionne mais que personne ne sait pourquoi.
    Ici, nous avons réuni théorie et pratique : Rien ne fonctionne ... et personne ne sait pourquoi !

    Réplication de base avec Postgresql : http://scheu.developpez.com/tutoriel.../log-shipping/

  9. #9
    Expert Confirmé Sénior
    Homme Profil pro
    Inscrit en
    mai 2002
    Messages
    3 006
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : mai 2002
    Messages : 3 006
    Points : 4 555
    Points
    4 555

    Par défaut

    Citation Envoyé par scheu Voir le message
    J'ai également le même problème d'index qui grossit dans une table à nombre de lignes constant mais dans laquelle je fais des delete puis insert tous les jours
    J'ai dû mettre en place un reindex, mais qui a pour contrainte de locker la table le temps de l'opération (1h30 quand-même !)

    Sous Postgresql actuellement il est possible de "défragmenter" un index sans verrou sur la table avec l'option CREATE INDEX CONCURRENTLY. En gros on créé un 2ème index, sans locker la table, sur les mêmes colonnes, puis on supprime l'ancien et on renomme le nouveau

    Mais pour les indexes des PK ce n'est pas encore possible, dans la version actuelle de Postgresql (9.1), de recréer une PK avec "using index" pour spécifier un index déjà existant qu'on aurait créé avec l'option "concurrently"

    C'est donc une contrainte de la version actuelle, le seul moyen étant de planifier la tâche de maintenance (le reindex par exemple) dans un créneau horaire où la table n'est pas utilisée

    Pour le VACUUM FULL, c'est aussi contraignant car il locke la table le temps de l'opération. Dans les versions 8.x c'était tellement long qu'un export/import de la table était plus rapide. Là dans les dernières versions c'est apparemment plus rapide d'après la doc (je n'ai pas testé personnellement).
    A toi de voir en fonction du temps que ça prend sur ta table si c'est acceptable ou pas et si tu arrives à trouver un créneau horaire pour le faire, sans que personne n'accède à la table
    Bleh, j'ai pas testé avec des volumes plus concéquent (combien de Go ta table ?)

    Du coup, duplication de la dites table c'est pas plus rapide ?
    Et as-tu testé avec l'option cluster ?

  10. #10
    Membre Expert Avatar de scheu
    Inscrit en
    juin 2007
    Messages
    1 503
    Détails du profil
    Informations forums :
    Inscription : juin 2007
    Messages : 1 503
    Points : 1 495
    Points
    1 495

    Par défaut

    20 Go ma table
    La duplication de table ou l'export/import c'est faisable, mais c'est long et encore faut-il que ta table ne soit pas utilisée pendant l'opération, ce qui n'est pas toujours le cas

    C'est notamment une des limitations actuelles de Postgresql qui me fait penser que pour les grosses volumétries et haute-dispo (tables dispo 24h/24 et sans verrous), je conseille un SGBD payant comme Oracle ou SQL Server
    La théorie, c'est quand on sait tout mais que rien ne fonctionne.
    La pratique, c'est quand tout fonctionne mais que personne ne sait pourquoi.
    Ici, nous avons réuni théorie et pratique : Rien ne fonctionne ... et personne ne sait pourquoi !

    Réplication de base avec Postgresql : http://scheu.developpez.com/tutoriel.../log-shipping/

+ Répondre à la discussion
Cette discussion est résolue.

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •