Massive Delete
A user needed to delete ~11 million of 20.4 million rows (413-byte rows) without swamping the server with a huge transaction/long rollback. Suggestions: loop in a script (Perl/ESQL-C) deleting in small committed batches with optional sleeps, driven off a list of primary keys (an example ESQL/C cleanup program was posted); during downtime, rename the table, create a new one, set it RAW, insert only the rows to keep (unlogged), then set it back to STANDARD and rebuild indexes/constraints; or fragment the table so obsolete rows can simply be detached as a fragment, which is fast and avoids long transactions. The poster asked for fragmentation examples; no final choice or outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello Again Friends, Thanks for the help you've provided me so far. So... Here are some stats on a table I have: total record count 20,454,537.00 total record count to be moved to DW and deleted 11,000,921.00 row size of 413 bytes. How do I go about deleting this data without rendering my server unusable? I saw a suggestion of deleting records in 500 record batches, but that is a helluva lot of batches to get done what I want. Any other ideas? Thanks in advance for the advice. Tam.
Normally, we have a script(perl,...) to do this, Inside the loop, we can control the number of rows we want to delete in a short transaction. You can also have a couple of seconds sleep for next round if you like. You can just stopt(CTRL-C) it any time and restart it later. thanks, Quman On 9/26/06, Tam OShanter <tam@oshanter.com> wrote: > Hello Again Friends, > Thanks for the help you've provided me so far. > > So... > Here are some stats on a table I have: > > total record count 20,454,537.00 > total record count to be moved to DW and deleted 11,000,921.00 > > row size of 413 bytes. > > How do I go about deleting this data without rendering my server unusable? > > I saw a suggestion of deleting records in 500 record batches, but that is a > helluva lot of batches to get done what I want. > > Any other ideas? > > Thanks in advance for the advice. > > Tam. > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
Okay, So I cannot do this in one transaction without killing my server so... Given my row size, what considerations do I have when trying to decide the optimal number of rows to delete in a given batch? Thoughts? Tam. "Quman" <yquman@gmail.com> wrote in message news:mailman.630.1159295524.20706.informix-list@iiug.org... > Normally, we have a script(perl,...) to do this, > > Inside the loop, we can control the number of rows we want to delete > in a short transaction. You can also have a couple of seconds sleep > for next round if you like. > > You can just stopt(CTRL-C) it any time and restart it later. > > thanks, > Quman > > On 9/26/06, Tam OShanter <tam@oshanter.com> wrote: >> Hello Again Friends, >> Thanks for the help you've provided me so far. >> >> So... >> Here are some stats on a table I have: >> >> total record count 20,454,537.00 >> total record count to be moved to DW and deleted 11,000,921.00 >> >> row size of 413 bytes. >> >> How do I go about deleting this data without rendering my server >> unusable? >> >> I saw a suggestion of deleting records in 500 record batches, but that is >> a >> helluva lot of batches to get done what I want. >> >> Any other ideas? >> >> Thanks in advance for the advice. >> >> Tam. >> >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >>
Quman,
How does the script limit the number of records deleted? There isn't a
command to do "delete first 50 from <table> " is there ?
I did something similar to fix in place alters ( I of course updated a
column that wasn't in an index or trigger, instead of deleting the
record) but to get the records to work on I unloaded the primary key of
the records (because you can't even "select first 50 <pk> from <table>
into temp nibble_<table> with no log;)
If you are going to do this during down time you can do the following:
begin work;
rename <table> to old_<table> ;
create table <table> (...);
alter table <table> type(raw);
insert into <table> select * from old_<table> where <records_to_keep>;
alter table <table> type(standard);
drop table old_<table>;
<create indexes>
<create constraints>
<recreate fk constraints from other tables>
commit work;
Since the inserts are being done to a raw table it won't be logged (of
course it won't be replicated either).
Quman wrote:
> Normally, we have a script(perl,...) to do this,
>
> Inside the loop, we can control the number of rows we want to delete
> in a short transaction. You can also have a couple of seconds sleep
> for next round if you like.
>
> You can just stopt(CTRL-C) it any time and restart it later.
>
> thanks,
> Quman
>
> On 9/26/06, Tam OShanter <tam@oshanter.com> wrote:
> > Hello Again Friends,
> > Thanks for the help you've provided me so far.
> >
> > So...
> > Here are some stats on a table I have:
> >
> > total record count 20,454,537.00
> > total record count to be moved to DW and deleted 11,000,921.00
> >
> > row size of 413 bytes.
> >
> > How do I go about deleting this data without rendering my server unusable?
> >
> > I saw a suggestion of deleting records in 500 record batches, but that is a
> > helluva lot of batches to get done what I want.
> >
> > Any other ideas?
> >
> > Thanks in advance for the advice.
> >
> > Tam.
> >
> >
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org
> > http://www.iiug.org/mailman/listinfo/informix-list
> >
I attached an esql.ec file. That can do our job.
If your table does not have unique ID column, you need modfy it.
I prefer using PERL to do it now. this is our early version.
Thanks,
Quman
/*
* File: CleanupRepTab.ec
*
* CleanupRepTab.ec is an ESQL/C program to cleanup database tables that
* are replicated by Informix Enterprise Replication server between multiple
* sites. For each table to be cleaned, there is a cleanup SQL WHERE clause
* stored in the db_cleanup_rep table. CleanupRepTab.ec will lookup the
* db_cleanup_rep table and delete the rows which satisfy the condition.
* db_cleanup_rep has the following schema,
* create table db_cleanup_rep (
* id serial not null ,
* tabname char(32) not null ,
* colname char(32) not null ,
* delcond varchar(255),
* status char(8),
* primary key (id) constraint db_cleanup_rep_pk );
*
* id: a serial number generated for each row.
* tabname: the name of the table to be cleaned, e.g., arch_recall_reqs,
* file_change_notice, etc.
* colname: a column name of the table, its type must be Integer and Unique.
* delcond: the condition used to delete rows of the table.
* status: "active" means the clean activity is on, "inactive" means off.
* The final results of cleanup are stored in two files: db_cleanup_sum.msg and
* db_cleanup_all.msg. db_cleanup_sum.msg contains the summary about
the deletion
* for each table. db_cleanup_all.msg contains all the ids of cleaned rows for
* each table.
*/
#include <stdio.h>
#include <time.h>
#include <sys/types.h>
#include <stdlib.h>
#include <sqlhdr.h>
#include <sqlca.h>
#define TABLIMIT 100 /*number of tables can be handled */
#define ROWLIMIT 320000 /*max number of rows in a table handled at one
time run*/
void deleteRows(char* tabname, char* colname,
char* delcond, FILE *fp_sum, FILE *fp_all){
int ids[ROWLIMIT];
int thecount=0;
EXEC SQL BEGIN DECLARE SECTION;
int theid;
char buff[255]; char buff2[255];
EXEC SQL END DECLARE SECTION;
/* select the row ids into a array*/
sprintf(buff, " SELECT %s FROM %s WHERE %s INTO TEMP toDelRow
WITH NO LOG \\n",
colname, tabname, delcond);
fputs(buff, fp_sum);
/*printf("stmt1 : %s\\n \\n", buff);*/
EXEC SQL EXECUTE IMMEDIATE :buff;
EXEC SQL PREPARE sele FROM 'select * from toDelRow';
EXEC SQL DECLARE C2 CURSOR FOR sele;
EXEC SQL OPEN C2;
for (; ; ) {
EXEC SQL FETCH C2 into :theid;
if ( sqlca.sqlcode != 0 || thecount > ROWLIMIT) { break; }
ids[thecount]=theid;
/*printf(" i= %d id= %d \\n", thecount,theid);*/
thecount++;
}
EXEC SQL CLOSE C2;
EXEC SQL DROP TABLE toDelRow;
/* delete from target table row by row */
for ( int i=0;i<thecount; i++) {
sprintf(buff2,"DELETE FROM %s WHERE %s = %d \\n",
tabname, colname, ids[i]);
/*printf(" stmt2 = %s \\n", buff2);*/
EXEC SQL begin work without replication;
EXEC SQL EXECUTE IMMEDIATE :buff2;
EXEC SQL commit work;
/* keep a log in the file*/
fputs(buff2, fp_all);
}
/* record summary deletion info*/
sprintf(buff,"===> %s rows deleted: %d \\n \\n", tabname, thecount);
fputs(buff, fp_sum);
}
int main(int argc, char *argv[]) {
static int mycount=0;
char* tname[TABLIMIT];
char* cname[TABLIMIT];
char* cond[TABLIMIT];
FILE *fp_sum, *fp_all;
fp_sum= fopen("db_cleanup_rep.sum","w+");
fp_all= fopen("db_cleanup_rep.all","w+");
time_t current_time; time(¤t_time);
fputs(ctime(¤t_time), fp_sum);
fputs(ctime(¤t_time), fp_all);
EXEC SQL BEGIN DECLARE SECTION;
int id;
char tabname[32];
char colname[32];
char delcond[255];
EXEC SQL END DECLARE SECTION;
/* allocate memory for arraies */
for (int i=0; i<TABLIMIT ; i++){
tname[i]=malloc(32);
cname[i]=malloc(32);
cond[i]=malloc(255);
}
/*select table clean info into arraies*/
EXEC SQL DATABASE 'noaa';
EXEC SQL PREPARE sel FROM
"SELECT * FROM db_cleanup_rep where status='active'";
EXEC SQL DECLARE C1 CURSOR FOR sel;
EXEC SQL OPEN C1;
for ( ;; ) {
EXEC SQL FETCH C1 into :id, :tabname, :colname, :delcond;
if ( sqlca.sqlcode != 0 ){ break; }
sprintf(tname[mycount],"%s",tabname);
sprintf(cname[mycount],"%s",colname);
sprintf(cond[mycount],"%s",delcond);
mycount++;
}
EXEC SQL CLOSE C1;
/*printf(" count= %d \\n", mycount);*/
/*handle it table by table*/
for ( int i=0;i<mycount; i++) {
/*printf("mycount= %d, i=%d, %s, %s\\n \\n", mycount, i,
cname[i], cond[i]);*/
deleteRows(tname[i],cname[i],cond[i], fp_sum, fp_all);
}
fclose(fp_sum); fclose(fp_all);
}
On 26 Sep 2006 12:04:25 -0700, bozon <curtis@crowson1.com> wrote:
> Quman,
> How does the script limit the number of records deleted? There isn't a
> command to do "delete first 50 from <table> " is there ?
> I did something similar to fix in place alters ( I of course updated a
> column that wasn't in an index or trigger, instead of deleting the
> record) but to get the records to work on I unloaded the primary key of
> the records (because you can't even "select first 50 <pk> from <table>
> into temp nibble_<table> with no log;)
>
> If you are going to do this during down time you can do the following:
>
> begin work;
>
> rename <table> to old_<table> ;
>
> create table <table> (...);
>
> alter table <table> type(raw);
> insert into <table> select * from old_<table> where <records_to_keep>;
> alter table <table> type(standard);
> drop table old_<table>;>
> <create indexes>
> <create constraints>
> <recreate fk constraints from other tables>
>
> commit work;
>
> Since the inserts are being done to a raw table it won't be logged (of
> course it won't be replicated either).
> Quman wrote:
> > Normally, we have a script(perl,...) to do this,
> >
> > Inside the loop, we can control the number of rows we want to delete
> > in a short transaction. You can also have a couple of seconds sleep
> > for next round if you like.
> >
> > You can just stopt(CTRL-C) it any time and restart it later.
> >
> > th
...detach the fragment... That sound interesting. J. Superboer escribi': > may be you can design a good fragmentation strategy on this table > so you can detach the fragment with rows you do not need, this will not > cause > long trx rollbacks etc. and is also really fast!!!!!! > check it out. > > Superboer. > > > > Tam OShanter schreef: > > >>Hello Again Friends, >>Thanks for the help you've provided me so far. >> >>So... >>Here are some stats on a table I have: >> >> total record count 20,454,537.00 >> total record count to be moved to DW and deleted 11,000,921.00 >> >>row size of 413 bytes. >> >>How do I go about deleting this data without rendering my server unusable? >> >>I saw a suggestion of deleting records in 500 record batches, but that is a >>helluva lot of batches to get done what I want. >> >>Any other ideas? >> >>Thanks in advance for the advice. >> >>Tam. > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
may be you can design a good fragmentation strategy on this table so you can detach the fragment with rows you do not need, this will not cause long trx rollbacks etc. and is also really fast!!!!!! check it out. Superboer. Tam OShanter schreef: > Hello Again Friends, > Thanks for the help you've provided me so far. > > So... > Here are some stats on a table I have: > > total record count 20,454,537.00 > total record count to be moved to DW and deleted 11,000,921.00 > > row size of 413 bytes. > > How do I go about deleting this data without rendering my server unusable? > > I saw a suggestion of deleting records in 500 record batches, but that is a > helluva lot of batches to get done what I want. > > Any other ideas? > > Thanks in advance for the advice. > > Tam.
Yes. It does sound quite interesting. Can you provide more information, or at least a hint on where to start looking for practical examples? Thanks Friends, Tam. "Jean Sagi" <jeansagi@myrealbox.com> wrote in message news:mailman.646.1159358485.20706.informix-list@iiug.org... > ...detach the fragment... > > That sound interesting. > > > J. > > Superboer escribi': >> may be you can design a good fragmentation strategy on this table >> so you can detach the fragment with rows you do not need, this will not >> cause >> long trx rollbacks etc. and is also really fast!!!!!! >> check it out. >> >> Superboer. >> >> >> >> Tam OShanter schreef: >> >> >>>Hello Again Friends, >>>Thanks for the help you've provided me so far. >>> >>>So... >>>Here are some stats on a table I have: >>> >>> total record count 20,454,537.00 >>> total record count to be moved to DW and deleted 11,000,921.00 >>> >>>row size of 413 bytes. >>> >>>How do I go about deleting this data without rendering my server >>>unusable? >>> >>>I saw a suggestion of deleting records in 500 record batches, but that is >>>a >>>helluva lot of batches to get done what I want. >>> >>>Any other ideas? >>> >>>Thanks in advance for the advice. >>> >>>Tam. >> >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >>
That is similar to my job that fixes in place alters. I would think that the select all records into the temp table might be a performance hit. Don't get me wrong I don't like my method of unloading the records, which is something like "unload to <file> select first <x> <pk_id_col> from <table> where <pk_id_col> > <last_id_selected> order by <pk_id_col>" because you can't do the more natural "select first <x> <pk_id_col> from <table> where <pk_id_col> > <last_id_selected> order by <pk_id_col>" into temp <temp table name> with no log " because it gives you a syntax error, method either. adding first and skip etc. to the temp table select statement would be a nice feature. I of course don't think it would be that hard to implement. I certainly wouldn't have to do anything. ;-) Quman wrote: > I attached an esql.ec file. That can do our job. > If your table does not have unique ID column, you need modfy it. > > I prefer using PERL to do it now. this is our early version. > > Thanks, > Quman > > > /* > * File: CleanupRepTab.ec > * > * CleanupRepTab.ec is an ESQL/C program to cleanup database tables that > * are replicated by Informix Enterprise Replication server between multiple > * sites. For each table to be cleaned, there is a cleanup SQL WHERE clause > * stored in the db_cleanup_rep table. CleanupRepTab.ec will lookup the > * db_cleanup_rep table and delete the rows which satisfy the condition. > * db_cleanup_rep has the following schema, > * create table db_cleanup_rep ( > * id serial not null , > * tabname char(32) not null , > * colname char(32) not null , > * delcond varchar(255), > * status char(8), > * primary key (id) constraint db_cleanup_rep_pk ); > * > * id: a serial number generated for each row. > * tabname: the name of the table to be cleaned, e.g., arch_recall_reqs, > * file_change_notice, etc. > * colname: a column name of the table, its type must be Integer and Unique. > * delcond: the condition used to delete rows of the table. > * status: "active" means the clean activity is on, "inactive" means off. > > * The final results of cleanup are stored in two files: db_cleanup_sum.msg and > * db_cleanup_all.msg. db_cleanup_sum.msg contains the summary about > the deletion > * for each table. db_cleanup_all.msg contains all the ids of cleaned rows for > * each table. > */ > > > #include <stdio.h> > #include <time.h> > #include <sys/types.h> > #include <stdlib.h> > #include <sqlhdr.h> > #include <sqlca.h> > > #define TABLIMIT 100 /*number of tables can be handled */ > #define ROWLIMIT 320000 /*max number of rows in a table handled at one > time run*/ > > void deleteRows(char* tabname, char* colname, > char* delcond, FILE *fp_sum, FILE *fp_all){ > > int ids[ROWLIMIT]; > int thecount=0; > > EXEC SQL BEGIN DECLARE SECTION; > int theid; > char buff[255]; char buff2[255]; > EXEC SQL END DECLARE SECTION; > > /* select the row ids into a array*/ > sprintf(buff, " SELECT %s FROM %s WHERE %s INTO TEMP toDelRow > WITH NO LOG \\n", > colname, tabname, delcond); > fputs(buff, fp_sum); > > /*printf("stmt1 : %s\\n \\n", buff);*/ > EXEC SQL EXECUTE IMMEDIATE :buff; > EXEC SQL PREPARE sele FROM 'select * from toDelRow'; > EXEC SQL DECLARE C2 CURSOR FOR sele; > EXEC SQL OPEN C2; > > for (; ; ) { > > EXEC SQL FETCH C2 into :theid; > if ( sqlca.sqlcode != 0 || thecount > ROWLIMIT) { break; } > ids[thecount]=theid; > /*printf(" i= %d id= %d \\n", thecount,theid);*/ > thecount++; > } > > EXEC SQL CLOSE C2; > EXEC SQL DROP TABLE toDelRow; > > /* delete from target table row by row */ > for ( int i=0;i<thecount; i++) { > > sprintf(buff2,"DELETE FROM %s WHERE %s = %d \\n", > tabname, colname, ids[i]); > /*printf(" stmt2 = %s \\n", buff2);*/ > EXEC SQL begin work without replication; > EXEC SQL EXECUTE IMMEDIATE :buff2; > EXEC SQL commit work; > > /* keep a log in the file*/ > fputs(buff2, fp_all); > } > > /* record summary deletion info*/ > sprintf(buff,"===> %s rows deleted: %d \\n \\n", tabname, thecount); > fputs(buff, fp_sum); > } > > > int main(int argc, char *argv[]) { > > static int mycount=0; > char* tname[TABLIMIT]; > char* cname[TABLIMIT]; > char* cond[TABLIMIT]; > > FILE *fp_sum, *fp_all; > fp_sum= fopen("db_cleanup_rep.sum","w+"); > fp_all= fopen("db_cleanup_rep.all","w+"); > > time_t current_time; time(¤t_time); > fputs(ctime(¤t_time), fp_sum); > fputs(ctime(¤t_time), fp_all); > > EXEC SQL BEGIN DECLARE SECTION; > int id; > char tabname[32]; > char colname[32]; > char delcond[255]; > EXEC SQL END DECLARE SECTION; > > /* allocate memory for arraies */ > for (int i=0; i<TABLIMIT ; i++){ > tname[i]=malloc(32); > cname[i]=malloc(32); > cond[i]=malloc(255); > } > > /*select table clean info into arraies*/ > EXEC SQL DATABASE 'noaa'; > EXEC SQL PREPARE sel FROM > "SELECT * FROM db_cleanup_rep where status='active'"; > EXEC SQL DECLARE C1 CURSOR FOR sel; > EXEC SQL OPEN C1; > > for ( ;; ) { > > EXEC SQL FETCH C1 into :id, :tabname, :colname, :delcond; > if ( sqlca.sqlcode != 0 ){ break; } > sprintf(tname[mycount],"%s",tabname); > sprintf(cname[mycount],"%s",colname); > sprintf(cond[mycount],"%s",delcond); > > mycount++; > } > EXEC SQL CLOSE C1; > > /*printf(" count= %d \\n", mycount);*/ > /*handle it table by table*/ > for ( int i=0;i<mycount; i++) { > > /*printf("mycount= %d, i=%d, %s, %s\\n \\n", mycount, i, > cname[i], cond[i]);*/ > deleteRows(tname[i],cname[i],cond[i], fp_sum, fp_all); > } > > fclose(fp_sum); fclose(fp_all); > > > } > > > > > > > On 26 Sep 2006 12:04:25 -0700, bozon <curtis@crowson1.com> wrote: > > Quman, > > How does the script limit the number of records deleted? There isn't a > > command to do "delete first 50 from <table> " is there ? > > I did something similar to fix in place alters ( I of course updated a > > column that wasn't in an index or trigger, i