Slow execution of Delete Statements

[Michel Esber]

Linux RH 4.0 running DB2 V8 FP 11.

I have a table with ~ 11M rows and running DELETE statements is really
slow. Deleting 1k rows takes more than 3 minutes. If I run select
statements on the same table, I usually fetch rows in a reasonable
time.

The table has the following description:

MACHINE_ID VARCHAR (24)
COLLECT_TIME TIMESTAMP
PROCESS_NAME VARCHAR (64)
PROCESS_PID DECIMAL (10)

These fields are the table PK. There is also and index on (MACHINE_ID,
COLLECT_TIME).

I have run db2exfmt and the explain plan seems to indicate a low
statement cost. Statistics for table and indexes are updated.
I am puzzled by actual very slow execution speeds.

Can anyone guide me trace why and where are the execution constraints?

Thanks in advance,
Original Statement:
------------------
delete
from RTM.TBL_COLLECT_PSSTAT_WIN_RTM
where MACHINE_ID='000423B0F8F61420F1B8111A' and COLLECT_TIME between
'2006-04-17 00:00:55.0' and '2006-04-17 01:00:55.0'
Optimized Statement:
-------------------
DELETE
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q1
WHERE $RID$ IN
(SELECT $RID$
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q2
WHERE (Q2.COLLECT_TIME <= '2006-04-17-01.00.55.000000') AND
('2006-04-17-00.00.55.000000' <= Q2.COLLECT_TIME) AND
(Q2.MACHINE_ID = '000423B0F8F61420F1B8111A'))

Access Plan:
-----------
Total Cost: 41.6416
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
3.50129
DELETE
( 2)
41.6416
6.50129
/----+---\
3.50129 1.33235e+07
IXSCAN TABLE: RTM
( 3) TBL_COLLECT_PSST
19.2224
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN

[Serge Rielau]

The plan looks fine.
Are you deleting all these rows with this one statement.
Or are you calling many delete statements with different literals in the
WHERE clause (high CPU due to compiles, package cache overflows)?

I think you need to look at basic tuning parameters (snapshots) and
sanity checks:
Is this a new system? e.g. could it be your log sits on the same disks
as the table (i/o waits)?
Could it be you're locking with another transaction (lock waits)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Michel Esber]

Serge, thanks for the input.

My application uses parameter markers to run the statements whenever
necessary. That is : delete from table where machine_Id=? and
collect_time between ? and ?.

This server has 4 x Pentium 4 3.GHz with 16Gb. There is no memory /
cpu constraint. Linux sar utility indicates iowait times around 15%,
which is fairly the same threshold as my other servers. Also, I have
taken application snapshots and there is no indication of lock-waits.

My db2diag shows no errors at all. Seems like a lazy system to me ...
but where ...

[Michel Esber]

Also, my logs and tables are on separate disks.

Tables are on RAID 0, and logs are on RAID 1 ...

[ChrisC]

You might want to generate the plan again using parameter markers and
not defined values. In my experience, the plans can be very, very
different when generated each way - mostly depending on what the table
statistics say about the data.

[Michel Esber]

Thanks.

I have generated the plan with parameter markers, and its execution
cost is a little higher than my previous post. It seems to me that it
uses the same access plan:

Original Statement:
------------------
delete
from RTM.TBL_COLLECT_PSSTAT_WIN_RTM
where machine_Id=? and collect_time between ? and ?
Optimized Statement:
-------------------
DELETE
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q1
WHERE $RID$ IN
(SELECT $RID$
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q2
WHERE (:? <= :?) AND (Q2.COLLECT_TIME <= :?) AND (:? <=
Q2.COLLECT_TIME)
AND (Q2.MACHINE_ID = :?))

Access Plan:
-----------
Total Cost: 237.781
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
34.125
DELETE
( 2)
237.781
37.125
/----+---\
34.125 1.33235e+07
IXSCAN TABLE: RTM
( 3) TBL_COLLECT_PSST
19.2742
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN

[Michel Esber]

Thanks.

I have generated the plan with parameter markers, and its execution
cost is a little higher than my previous post. It seems to me that it
uses the same access plan:

Original Statement:
------------------
delete
from RTM.TBL_COLLECT_PSSTAT_WIN_RTM
where machine_Id=? and collect_time between ? and ?
Optimized Statement:
-------------------
DELETE
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q1
WHERE $RID$ IN
(SELECT $RID$
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q2
WHERE (:? <= :?) AND (Q2.COLLECT_TIME <= :?) AND (:? <=
Q2.COLLECT_TIME)
AND (Q2.MACHINE_ID = :?))

Access Plan:
-----------
Total Cost: 237.781
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
34.125
DELETE
( 2)
237.781
37.125
/----+---\
34.125 1.33235e+07
IXSCAN TABLE: RTM
( 3) TBL_COLLECT_PSST
19.2742
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN

[Gert van der Kooij]

In article <11*********************@g10g2000cwb.googlegroups. com>,
mi****@us.automatos.com says...

Thanks.

I have generated the plan with parameter markers, and its execution
cost is a little higher than my previous post. It seems to me that it
uses the same access plan:

Maybe increasing the logbuffer value in the db cfg can speed up the
deletes. The default is 8 which is to low is you want high speed deletes
etc. I've had a significant increaase in insert speed when changing this
value to 32. Increasing it above 32 didn't help in my case.

[Sumanth]

1)The delete statement deletes only 1K rows or a significant amount of rows.
If the rows are significant, the rollback segment being huge cause could
cause slowdown.
Intermediate commits can be done if the transaction can be broken down to
smaller chunks.
2)What is the value of num_io_cleaners?
3)Is this a multiple cpu server?
4)Deletes will also have to update the indexes, your case has only two - so
this should not be a problem?
5)What is the size of your bufferpool?
6) What is the value of your LogBufSz?
7) Can you separate the IO/CPU statistics of your query

-Sumanth


"Michel Esber" <mi****@us.automatos.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...

Thanks.

I have generated the plan with parameter markers, and its execution
cost is a little higher than my previous post. It seems to me that it
uses the same access plan:

Original Statement:
------------------
delete
from RTM.TBL_COLLECT_PSSTAT_WIN_RTM
where machine_Id=? and collect_time between ? and ?
Optimized Statement:
-------------------
DELETE
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q1
WHERE $RID$ IN
(SELECT $RID$
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q2
WHERE (:? <= :?) AND (Q2.COLLECT_TIME <= :?) AND (:? <=
Q2.COLLECT_TIME)
AND (Q2.MACHINE_ID = :?))

Access Plan:
-----------
Total Cost: 237.781
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
34.125
DELETE
( 2)
237.781
37.125
/----+---\
34.125 1.33235e+07
IXSCAN TABLE: RTM
( 3) TBL_COLLECT_PSST
19.2742
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN

[Michel Esber]

1) No, the delete statement deletes a maximum of 2k.
2) NUM_IOCLEANERS = 50. I have no more than 6 physical disks.
3) This is a 4 CPU P4 3GHz server. Also, I have 16GB.
4) Yeah, I belive this is not a problem.
5) The bufferpool for this tablespace has 20000 16k Pages. How do I
check if this number should be higher than it is?
6) LOGBUFSZ = 2048
7) I am sorry. Is this a command line switch that I should use with
db2exfmt ?

Thanks a lot.

[Sumanth]

NUM_IO_CLEANERS seems to be extremely high, I would set it to 6 since u have
6 physical disks

Bufferpool size of urs is 256M which is good enuf. I assume when the delete
is executed, that is the only
active process at this time. (Assuming there are no triggers on the table).
What is size of your record?
Ensure there are no triggers on the table.

I normally set the LOGBUFSZ to fit a single transaction eg: 1000 records is
the limit then LOGBUFSZ would be
1000 * 200 where 200 is the size of each record. so I will set it to 50 4K
pages. This does not explain the
performance u are seeing.

Execute reorg and then runstats on the table and redo the test.

mpstat is a command on the unix platform to see the CPU/IO stats on the
machine. Are u using UNIX/Windows?
I am not sure what is the parallel in windows?

-Sumanth

"Michel Esber" <mi****@us.automatos.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

1) No, the delete statement deletes a maximum of 2k.
2) NUM_IOCLEANERS = 50. I have no more than 6 physical disks.
3) This is a 4 CPU P4 3GHz server. Also, I have 16GB.
4) Yeah, I belive this is not a problem.
5) The bufferpool for this tablespace has 20000 16k Pages. How do I
check if this number should be higher than it is?
6) LOGBUFSZ = 2048
7) I am sorry. Is this a command line switch that I should use with
db2exfmt ?

Thanks a lot.

[Michel Esber]

I raised NUM_IOCLEANERS to a high number because I had several messages
regarding Dirty Pages being held in the Log. I will reduce it now ...
Though, what impact could this high number bring to the system ?

Yes, there are no triggers on the table. Also, my record size is ~ 248.
So I guess LOGBUFSZ is high enough.

I am running Linux. I use sar and see no memory/cpu constraint. IOwait
times are around 15%, which is not different from all my other systems.

Thanks again,

[Mark A]

"Michel Esber" <mi****@us.automatos.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

Linux RH 4.0 running DB2 V8 FP 11.

I have a table with ~ 11M rows and running DELETE statements is really
slow. Deleting 1k rows takes more than 3 minutes. If I run select
statements on the same table, I usually fetch rows in a reasonable
time.

The table has the following description:

MACHINE_ID VARCHAR (24)
COLLECT_TIME TIMESTAMP
PROCESS_NAME VARCHAR (64)
PROCESS_PID DECIMAL (10)

These fields are the table PK. There is also and index on (MACHINE_ID,
COLLECT_TIME).

If your PK is the 4 columns in the order you stated above, and you have an
additional index on (MACHINE_ID, COLLECT_TIME). in that order, then this
index is redundant and you should drop it. Dropping the index will speed up
deletes.

LOGBUFSZ of 512 is usually the max you should use. When it gets higher than
that, performance typically goes down slightly. Make sure your LOGFILSZ is
at least 10,000 and perhaps larger.

To flush your buffer pool more often to disk, set CHNGPS_THRESH to 20. As
mentioned by others, set NUM_IOCLEANERS to something smaller, maybe 4-6.

[Pierre Saint-Jacques]

When you are running the delete, take a snapshot at the db level with
bufferpool switches on.
You are looking for how you data is externalized out of the buffer pool.
Num_iocleaners are the processes launched to clean the buffer pool. Theya
are triggered by:
1) chngpgs_thresh which is defaulted at 60% of any buffer pool is made up
of dirty (committed not externalized) pages.
2) log_gap_cleaners. Triggered when softmax is hit. Externalize dirty pages
whose transactions are contained in the lo gap represented by softmax.
3) dirty page victim steals. Triggered by the fact that there are no pages
to be replaced in the buffer pool and the requesting transaction has to
externalize a page chosen by DB2 before it can bring its page for the
delete. Also known as a synchronous writes.

Your snapshot output will show you the total writes, and the total
asynchronous writes. 1) and 2) above are asynchronous and ok. /) is the
"baddy". You don't want to see any (or as few as possible).
If any show, then it is an indication of too small a buffer pool, as you say
you have high num_iocleaners.

Too high num_iocleaner is something our significant others understand very
well. "Too much cleaning is not good !!!"
This means a page may get cleaned, goes from dirty to clean, then gets
replaced by a request, and almost immediately, that replaced page is needed
for a row in it. Redo the I/O, bad performance.

There may be other issues for your long delete time but look into those to
give you an idea.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Michel Esber" <mi****@us.automatos.com> a écrit dans le message de news:
11*********************@u72g2000cwu.googlegroups.c om...

I raised NUM_IOCLEANERS to a high number because I had several messages
regarding Dirty Pages being held in the Log. I will reduce it now ...
Though, what impact could this high number bring to the system ?

Yes, there are no triggers on the table. Also, my record size is ~ 248.
So I guess LOGBUFSZ is high enough.

I am running Linux. I use sar and see no memory/cpu constraint. IOwait
times are around 15%, which is not different from all my other systems.

Thanks again,

[Michel Esber]

Thank you all for your very helpful comments. They gave me great ideas
to troubleshoot my environment.

Thanks again.