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='000 423B0F8F61420F1 B8111A' 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_TIM E <= '2006-04-17-01.00.55.000000 ') AND
('2006-04-17-00.00.55.000000 ' <= Q2.COLLECT_TIME ) AND
(Q2.MACHINE_ID = '000423B0F8F614 20F1B8111A'))

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_PSS T
19.2224
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN

[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.auto matos.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.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.auto matos.com> wrote in message
news:11******** **************@ e56g2000cwe.goo glegroups.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_cleaner s. 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.auto matos.com> a écrit dans le message de news:
11************* ********@u72g20 00...legro ups.com...

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.