purge très grosse table (+1téra) [Résolu]

[serge0934]

Bonjour,

j'ai une table dénormalisée pour connecter un BO qui fait + de 1 téra.
Elle historise tout depuis 2002.
Je voudrais purger les années avant 2009 par exemple, sachant que c'est une base répliquée et utilisée dans le monde entier donc 24h/24H.

faire un delete from xx where annee<2009 n'abouti jamais même en pré prod

quelqu'un aurait_il une idée?

merci d'avance

PS: table comprenant 112 champs et un champ date indexé

[Glouferu]

Bonjour,

J'ai aussi des tables dé-normalisé pour BO (mes datamarts qui historisent tout depuis 2008). Pour ma part, j'ai opté pour une solution de partitionnement.

-> J'ai créé autant de nouveau filegroup que j'ai d'années fiscales historisées.

Maintenant, pour votre base répliquée partout dans le monde, il faut nous donner plus d'information sur la nature de la réplication.
Pour ma part, je réplique les données de l'ods à l'aide de snapshots sur un server distant pour éviter de requêter directement les serveur de prod.

-> Et dans les paramètres de réplication, vous pouvez spécifier des filtres sur les données.

-> De plus, un index non clustered sur ces données vous permettrait d'accélérer significativement les temps d'identification des données historisée à répliquer.

-> Une purge va supprimer cet historique. Il est peut-être intéressant de garder ces données sur de gros disques dur de sauvegarde (mais plus lent) et utiliser les fonction de $partition pour interroger ces données qu'en réel nécessité.

En espérant avoir pu vous aider.

[Waldar]

La meilleure méthode c'est de ne pas faire le delete.

Si vraiment vous voulez vous débarrasser des informations, recréez une seconde table avec la même structure, copiez-y les donner à conserver, supprimez la première, renommez et repositionnez les droits.

Une autre solution serait de partitionner votre table, avec une partition par année par exemple.

[StringBuilder]

Code :

DELETE FROM xx WHERE annee<2009

N'abouti pas... Certes.

Avec Oracle, ça n'aboutirait certainement pas non plus et se traduirait par un rollback segment fault...

Normal, outre la lenteur pour effacer les lignes qui plombe tout, le redolog n'est certainement pas assez gros pour récupérer la transaction entière...

Avez-vous testé de regrouper les champs par d'autres critères, et supprimer des plus petits lots ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
declare @annee int;
declare @mois int;
 
SET @annee = 2002;
 
while annee < 2009
begin
   SET @mois = 1;
   while mois < 13
   begin
      DELETE FROM xx WHERE annee = @annee AND mois = @mois;
      SET @mois = @mois + 1;
   end;
   SET @annee = @annee + 1;
end;

Eventuellement, rajoutez aussi des groupes de jours, ou des identifiants (produits, sites, etc.) afin de supprimer moins de lignes à chaque requête.

Vérifiez aussi que autocommit est bien à true.

[serge0934]

merci a vous trois...

en fait je vois que vous tendez vers le même résultat auquel je pensais!

Une purge va supprimer cet historique. Il est peut-être intéressant de garder ces données sur de gros disques dur de sauvegarde (mais plus lent) et utiliser les fonction de $partition pour interroger ces données qu'en réel nécessité

en fait, j'ai restauré un backup sur un gros disque externe et j'ai supprimé toutes les données depuis 2009; d'où jai une base contenant les 3 dernières années, et l'autre celles d'avant. Mes univers BO sont donc sur la forme

Code :

1
2
3
SELECT * FROM SRV1.MaBase..Matable WHERE xxxx
Union
SRVBackup.MaBase..Matable WHERE xxxx

et çà a l'air de tourner en pre prod.

au fait, pour supprimer j'ai fait un job qui supprimait toutes les heures, un mois complet, ce qui est l'équivalent de ton while.

je n'avais pas pensé au filegroup par année mais c'est une bonne idée.

les filtres sur la réplication sont déjà en place et les index sont ok. Pour info, ElSuket (lui-même) a écrit un article top top sur l'indexation il y a 2 ans. Je n'ai plus le lien mais je sais qu'on le vois en cherchant "INDEX INCLUDE SQL SERVER"

Maintenant, il faut que je vois comment faire en prod !!!!!

[serge0934]

StringBuilder, quand tu dis:

Avec Oracle, ça n'aboutirait certainement pas non plus et se traduirait par un rollback segment fault...

j'ai déjà utilisé des segments UNDO de 500Go sous oracle/AIX chez UNExxIC (ceux qui payent le chômage), et des transactions de chargements de fichiers par loader etaient testées, rollbackées puis relancées et commitées sans pb..

[elsuket]

Bonjour,

Effectivement le problème de la purge de tables très grandes est un problème, et vous auriez du penser à partitionner à la création.
Cela vous aurait permis de scinder une partition, le la faire basculer dans une table dans le même groupe de fichiers en quelques secondes, puisqu'il ne 'agit que d'un changement de métadonnées, et enfin de supprimer ou d'archiver la table.

Maintenant comme le dit StringBuilder, le mieux sera pour vous de procéder par lots, de sorte que le fichier du journal des transactions ne soit pas impacté.
Pour faire cela :

- stockez les valeurs de la clé primaire de la table dans une table à part, que j'appelle T_PK dans la suite
- créer un index cluster dessus
- dans une boucle qui teste s'il reste des lignes dans T_PK, faites le DELETE sur le TOP (tailleDuLot) ORDER BY PK par équi-jointure entre T_PK et la table à purger
- supprimez le TOP(tailleDuLot) ORDER BY PK de T_PK
- surveillez l'occupation de votre fichier du journal des transactions en utilisant cette requête et DBCC SQLPERF(logspace)
- Une fois terminé, supprimez la table T_PK

Enjoy

@++

[StringBuilder]

Envoyé par serge0934

StringBuilder, quand tu dis:


j'ai déjà utilisé des segments UNDO de 500Go sous oracle/AIX chez UNExxIC (ceux qui payent le chômage), et des transactions de chargements de fichiers par loader etaient testées, rollbackées puis relancées et commitées sans pb..

Oui, y'a toujours moyen de moyenner.
Mais quand il s'agit de one shot, c'est pas forcément très malin de devoir racheter des disques juste pour avoir assez de place pour faire un delete de 1 To, alors qu'on peut faire n delete à la place.

Quand il s'agit de faire une grosse transaction comme c'était le cas lors du passage à l'euro, oui, là, la question se pose. Mais quand il s'agit de purger (pour gagner de la place) c'est bête d'en arriver là (je pense).

[serge0934]

merci elsuket,

il y a déjà un petit moment que j'utilise le dbcc loginfo que j'avais trouvé sur ton blog

[Glouferu]

Envoyé par serge0934

Code :

Je vais peut-être dire une bétise mais de mémoire j'avais fais un test il y a quelques semaines et stacker trop de UNION dans une même requête pourri les perfs.
Je ne sais pas si quelqu'un de plus expérimenté que moi peux nous infirmer ou confirmer cela ?

[mikedavem]

Effectivement procéder par petit lot permettrait de ne pas surcharger le journal des transactions (au passage à voir s'il est possible de passer en mode simple pendant votre opération .. cela garantira que le journal ne grossira pas de manière exagérée) et de ne pas engendrer de lock escalation sur votre table si celle-ci est utilisé par d'autres utilisateurs en même temps.

Voir également si des index non cluster existent sur votre table. Cela peut être utile de les désactiver à ce moment. Cela évitera de mettre à jour l'index cluster et les index non cluster associés.

Maintenant il peut être nécessaire de remettre à jour les statistiques pendant votre procédure si un grand nombre de lignes est supprimé .. j'ai déjà eu le cas sur une table d'archive où les premières suppressions se passaient relativement bien mais les performances des suivantes se dégradaient au fur et à mesure.

++

[elsuket]

Envoyé par mikedavem

Maintenant il peut être nécessaire de remettre à jour les statistiques pendant votre procédure si un grand nombre de lignes est supprimé .. j'ai déjà eu le cas sur une table d'archive où les premières suppressions se passaient relativement bien mais les performances des suivantes se dégradaient au fur et à mesure.

Idem pour moi.
J'avais donc mis un UPDATE STATISTICS maTable si le nombre de lignes affectées était un multiple de 10% du nombre de lignes de la table.

Envoyé par Glouferu

j'avais fais un test il y a quelques semaines et stacker trop de UNION dans une même requête pourri les perfs.

Encore une fois : ça dépend
Il est des cas où passer par un UNION pour se passer d'un OR ou d'un IN et rendre "recherchable" le prédicat fonctionne mieux.
Le problème avec le UNION, c'est qu'il fait le DISTINCT automatiquement.
Donc si on est sûr que chaque ensemble qui compose l'UNION est mutuellement exclusif, il faut utiliser UNION ALL.

@++

[Glouferu]

Envoyé par elsuket

Le problème avec le UNION, c'est qu'il fait le DISTINCT automatiquement.
Donc si on est sûr que chaque ensemble qui compose l'UNION est mutuellement exclusif, il faut utiliser UNION ALL.

Oki, je comprends mieux la distinction.
Merci Elsuket