[DB2] Supprimer plusieurs millions de lignes

Supprimer plusieurs millions de lignes [DB2] - SQL/NoSQL - Programmation

Marsh Posté le 21-11-2006 à 16:08:06    

Salut à tous,
 
Voici mon problème :
 
J'ai une table qui contient plus de 20 millions de lignes par mois.
 
Je dois supprimer les 6 derniers mois.
 
Quand je passe par un DELETE, la requête met TRES longtemps à aboutir.
 
J'essaie de multiplier les DELETE en trouvant des clés d'éclatement. J'arrive à limiter à 6 millions par DELETE, mais les requêtes mettent toujours 10 plombes à aboutir. Parfois j'obtiens un "log full"...
 
Connaîtriez-vous une méthode qui me permettrai de passer facilement ces DELETE ?
 
Merci
Robbie

Reply

Marsh Posté le 21-11-2006 à 16:08:06   

Reply

Marsh Posté le 21-11-2006 à 16:13:04    

selon ton SGBD, tu peux désactiver les transactions durant le DELETE. Il va alors se comporter comme un TRUNCATE.
Il est très important par contre de demander un lock exclusif durant le delete si tu ne travailles pas en transaction.
 
Une autre solution, à tester, même si je doute qu'elle marche mieux :
- LOCK sur la table
- CREATE TABLE AS SELECT de lignes à conserver dans une nouvelle table
- DROP de ta table
- RENAME de la table temporaire
 
C'est typiquement ce que fait SQL Server par exemple, lorsqu'on change la structure d'une table.


Message édité par MagicBuzz le 21-11-2006 à 16:13:25
Reply

Marsh Posté le 21-11-2006 à 17:42:57    

Pourquoi pas la solution de désactiver les transactions... mais que se passe t-il s'il y a un problème lors du DELETE ?
 
Ma table risque d'être endommagée, non ?

Reply

Marsh Posté le 21-11-2006 à 17:45:54    

begin
delete
delete
delete
commit

Reply

Marsh Posté le 21-11-2006 à 17:55:36    

C'est pas mieux de faire :
begin  
delete  
commit
delete  
commit
delete  
commit
 
çà libère le journal entre chaque DELETE, non ?

Message cité 1 fois
Reply

Marsh Posté le 21-11-2006 à 17:57:01    

Reply

Marsh Posté le 21-11-2006 à 18:04:35    

robirob444 a écrit :

C'est pas mieux de faire :
begin  
delete  
commit
delete  
commit
delete  
commit
 
çà libère le journal entre chaque DELETE, non ?


ben si t'as delete qui échoue ? et puis le commit, ça bouffe beaucoup. paye toi un sgbd qui fasse pas **** avec la taille du journal

Reply

Marsh Posté le 21-11-2006 à 18:08:20    

anapajari a écrit :

try this : http://www.webservertalk.com/archi [...] 26768.html


 
Ha ouais, pas mal... :) , j'avais jamais pensé à utiliser le FETCH FIRST x ROWS ONLY avec un DELETE...
 
Merci beaucoup, je vais essayer çà !

Reply

Marsh Posté le 21-11-2006 à 18:16:23    

c'est bizarre quand même, je ne vois pourquoi delete serait lent, ça devrait même être une transaction des plus rapides. au garbage collector ensuite de faire son boulot.

Message cité 1 fois
Reply

Marsh Posté le 21-11-2006 à 18:20:33    

Taz a écrit :

c'est bizarre quand même, je ne vois pourquoi delete serait lent, ça devrait même être une transaction des plus rapides. au garbage collector ensuite de faire son boulot.


 
Je te le fais pas dire...
 
La table fait plus de 170 millions de lignes, et dès que je veux en supprimer 6 millions çà rame comme pas possible...
Le truc étrange est que parfois çà passe en 40 minutes et des fois çà mets 8 heures...incompréhensible...
 
Peut-être que le SGBD a du mal à trouver les lignes à supprimer... (je passe pourtant par un index)


Message édité par robirob444 le 21-11-2006 à 18:21:46
Reply

Marsh Posté le 21-11-2006 à 18:20:33   

Reply

Marsh Posté le 21-11-2006 à 18:39:35    

je connais pas DB2, c'est peut-être bof niveau ACID et ça lock comme un goret ...

Reply

Marsh Posté le 21-11-2006 à 18:40:31    

si c'est une table et un schema que tu maitrises parfaitement et que tu n'as pas de users dessus tu peux dropper les index, les cles etrangeres etc avant et tout recreer par la suite.
 
ou trouver une astuce pour descendre bien au dessous des 6 millions de ligne.
 
montre une fois la tronche de ton delete au fait

Reply

Marsh Posté le 21-11-2006 à 19:26:14    

y'a aussi la solution du partitionnement.
tu partitionnes pour chaque mois.
et zou, tu shootes une partition, ça dure dans les 2 ms.


Message édité par MagicBuzz le 21-11-2006 à 19:26:28
Reply

Marsh Posté le 21-11-2006 à 19:26:42    

t'as essayé un db2explain pour voir ce qu'il manquait et les optimisations possibles sur ta requête?

Reply

Marsh Posté le 22-11-2006 à 20:40:10    

Les DELETE prennent généralement du temps à cause des indexes existants, ils sont mis à jour à chaque ligne effacée ... :)

Reply

Marsh Posté le 22-11-2006 à 22:47:45    

c'est pas faux ça

Reply

Marsh Posté le 23-11-2006 à 00:40:33    

Par contre, plutôt que de dropper les index (ce qui peut être catastrophique si d'autres personnes utilisent la table en même temps), Il vaut mieux
- soit les désactiver lors de la mise à jour s'il n'y a pas de PrimaryKey sur la table (uniquement des index unique ou simple, clusterés ou non)
- soit désactiver leur mise à jour (plus lent, mais ça marche sur les PK)
 
Voir la doc de "ALTER INDEX" pour plus d'infos.
 
A noter que là j'ai voulu faire des petits tests sous SQL Server 2005 et... C'est très étrange pour les résultats !
La piste des index pourrait bien être à écarter sérieusement ! En effet, sous SQL Server 2005 Express tout du moins, les résultats sont totalement en contradiction avec l'idée reçue des index qui ralentissent les requêtes de INS/DEL
 


create table test (id numeric, label varchar(50));
 
create unique index uix_test on test (id);
 
declare @i numeric
set @i = 1
 
while @i < 10000
begin
  insert into test (id, label) values (@i, 'Label ' + cast(@i as varchar))
  select @i = @i + 1
end;


=> 1 minute pile
 
Sans index :


create table test (id numeric, label varchar(50));
 
declare @i numeric
set @i = 1
 
while @i <= 10000
begin
  insert into test (id, label) values (@i, 'Label ' + cast(@i as varchar))
  select @i = @i + 1
end;


=> 1 minute pile
 
Avec une PK active


drop table test
 
create table test (id numeric primary key, label varchar(50));
 
declare @i numeric
set @i = 1
 
while @i <= 10000
begin
  insert into test (id, label) values (@i, 'Label ' + cast(@i as varchar))
  select @i = @i + 1
end;


=> 1 minute pile
 
Cette partie du test est à mettre de côté, car il semblerait que ce soit le T-SQL qui soit très lent.
 
Ensuite :


insert into test select id + 10000, label from test;
insert into test select id + 20000, label from test;
insert into test select id + 40000, label from test;
insert into test select id + 80000, label from test;
insert into test select id + 160000, label from test;
insert into test select id + 320000, label from test;
insert into test select id + 640000, label from test;
insert into test select id + 1280000, label from test;
insert into test select id + 2560000, label from test;
insert into test select id + 5120000, label from test;
insert into test select id + 10240000, label from test;


=> environ 2x plus rapide avec la PK active que si je désactive la PK puis que je la recompute ! (30 minutes contre 60 minutes -temps estimé, j'ai arrêté en plein milieu ça me gonflait-)
Truc marrant aussi : avec ce type de requête, j'ai pu insérer 20 millions de lignes en quelques minutes alors que le T-SQL mettait un temps pas possible pour en insérer 10k :pt1cable:
 
Test des DELETE donc :


begin transaction;
 
delete test where id between 1000000 and 5000000;
 
rollback;


=> 3 minutes 1 seconde
 
Je désactive la mise à jour de la PK ici, plutôt que de la supprimer : en effet, il sera plus facile de retrouver des lignes mises à jour dans un index qui ne se met plus à jour (mais non marqué comme pollué) plutôt que sans index du tout !


begin transaction;
 
alter index PK__test__72C60C4A on test set(STATISTICS_NORECOMPUTE=on);
 
delete test where id between 1000000 and 5000000;
 
alter index PK__test__72C60C4A on test set(STATISTICS_NORECOMPUTE=off);
alter index PK__test__72C60C4A on test rebuild;
 
rollback;


=> 5 minutes 1 seconde
 
Bref, c'est sans appel : c'est bien plus rapide avec ne serait-ce que la PK activée !
 
Test avec un second index :


create index ix_test on test (label);


10 minutes 26 secondes :sleep:
=> Hors de question de le droper donc, puis de le recréer... A moins que le DELETE ne mette substanciellement plus de 10 minutes !
 


begin transaction;
 
delete test where id between 1000000 and 5000000;
 
rollback;


=> 9 minutes 54 secondes
Donc il est impensable de gagner du temps en supprimant l'index puis en le recréant !
 
Bon, un test plus probant, qui utilise l'index en question...


begin transaction;
 
delete test where label like 'Label 100%';
 
rollback;


=> 3 secondes
 


begin transaction;
 
alter index ix_test on test set(STATISTICS_NORECOMPUTE=on);
 
delete test where label like 'Label 100%';
 
alter index ix_test on test set(STATISTICS_NORECOMPUTE=off);
alter index ix_test on test rebuild;
 
rollback;


=> 15 minutes 23 secondes (lol)
 


begin transaction;
 
alter index ix_test on test disable;
 
delete test where label like 'Label 100%';
 
alter index ix_test on test rebuild;
 
rollback;


=> J'ai stoppé au bout de 1 minute 30 secondes (c'est vraiment plus lent, ça sert à rien de se faire du mal)
 


drop table test;


=> 0 secondes :love:
 
A noter que le temps final doit être pas mal faussé par la transaction, mais ça me gavait de réinsérer les lignes effacées :spamafote:
Dans les deux cas, le temps lié à la transaction doit être le même pour les deux cas.
 
A noter aussi que mon serveur, c'est un peu Agecanonix. Donc faut pas se fier à sa lenteur pour tirer des conclusions quant à la rapidité globale des traîtements, avec un serveur récent, ça doit même pas être le 10°... (et dire que je l'ai payé la peau du cul mon joli serveur :cry:)
J'aime bien la tronche de mon log des transactions aussi maintenant : près de 4 Go :o (disque physique séparé, donc aucune incidence sur les temps de traîtements)
 
PS : Je serais plutôt curieux d'avoir l'avis d'un expert (DBA) sur mon test, parceque c'est vrai que les résultats vont à l'encontre totale de ce à quoi on a toujours été habitué ! Je serais curieux aussi de voir ce que ça donne avec d'autres SGBD, parceque là... Moi je vote et je dis que c'est chelou :o
 
DANS TOUS LES CAS :
DB2 supporte très certainement les PARTITIONS, d'autant plus qu'il est souvent utilisé pour des datawarehouse énormes (plusieurs To par table), et donc où le partitionnement semble inévitable.
Sous SQL Server 2005, le partitionnement accélère les insertions/mises à jours/select/delete (possibilité de //ser les traîtements d'une même requête entre les partitions).
Mais surtout, une partition met moins de 1 seconde à être virée d'une table. Donc des partitions changant avec le critère qui te permet d'isoler les 6 millions de lignes (je suppose, le mois d'une date par exemple) et zou ! Ton problème est résolu : quand tu fais le ménage, tu shootes simplement les partitions qui contiennent les lignes à supprimer ! Le temps d'éxécution devrait être très proche de celui d'un "drop table", c'est à dire plus proche de 0 secondes que d'autrechose :spamafote:


Message édité par MagicBuzz le 23-11-2006 à 00:42:46
Reply

Marsh Posté le 15-10-2008 à 10:47:12    

Salut, j'ai eu le même problème en début de semaine.
 
As-tu essayé de faire TRUNCATE table nom_de_ta_table?
 
Ca te vide ta table presque instantanément!

Reply

Marsh Posté le 15-10-2008 à 11:02:46    

sauf qu'il ne veut pas vider la table, mais supprimer les lignes selon un filtre...
 
sinon, évidement, un truncate est LA solution pour vider une table, puisque c'est non transactionnel.

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed