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
14  9652 
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
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 ...
Also, my logs and tables are on separate disks.
Tables are on RAID 0, and logs are on RAID 1 ...
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.
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_TIM E <= :?) 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_PSS T
19.2742
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN
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_TIM E <= :?) 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_PSS T
19.2742
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN
In article <11************ *********@g10g2 000cwb.googlegr oups.com>, mi****@us.autom atos.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.
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.auto matos.com> wrote in message
news:11******** *************@g 10g2000cwb.goog legroups.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_TIM E <= :?) 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_PSS T
19.2742
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Jason Kirk |
last post by:
I've just installed PHP 5 under Fedora Core 2 and I've been having
speed problems with xsl and the dom functions. Previously I used
the sablotron extension functions under PHP 4 with script execution
times of under 5 seconds ( max of 0,2M xml files, with a fair
number of xsl:key statements). Under PHP 5 execution times almost
seem random and never seem to use the full system resources
(memory-limit wasn't included as a compile option) --...
|
by: mamo74 |
last post by:
Hello.
I am administering a SQL Server (Enterprise Edition on Windows 2003)
from some month and can't understand what is going on in the latest
week (when the db grow a lot).
The DB is around 250G, and has one table with 1 billion rows. It is
performing in a decent way, but can't understand why a particolar table
has strong performance problem.
|
by: Ian Murphy |
last post by:
Hopefully someone else has seen something similar.
We are experiencing an odd problem with aspnet, iis6 and w2k3. I have
written a small site which works fine during the day. Each morning however
the initial page load can take anything from 10 minutes to over an hour.
Nothing seems to occur during this time. The first page load is simply
incredibly slow. It seems to 'hang' until someone else tried to load the
default.aspx, though there...
|
by: MadMan2004 |
last post by:
Hello all!
I'm having a problem with a project I'm working on and I'd like to ask
for anyone's input that might be helpful. I'm building a rather large
front-end application connecting to an AS400 for the back end database
and I'm experiencing slow response times when executing sql statements.
Some select statement response times are bad. Not all, but some. And
there doesn't seem to be a consistent factor in any of the sql
statements...
|
by: Cornelius Buschka |
last post by:
Hi,
we saw the following problem:
We deleted all rows from a table B referencing table A (~500000 records). No
problem, but the following try to delete all records from table A (~180000) lead
to a "never ending" statement. We found out, that vacuuming table B after delete
did the trick.
It seems to us the database has to do scan thru deleted records on B while
| | |
by: Ryan |
last post by:
We've got as slightly unusual scenario happening whereby a statement is
passed to SQL which consists of two parts.
BEGIN TRANSACTION
DELETE * FROM Whatever
BULK INSERT INTO Whatever...(etc)
COMMIT TRANSACTION
The first is a deletion of the data and the second is the bulk insert
of replacement data into that table. The error that we see is a
|
by: romixnews |
last post by:
Hi,
I'm facing the problem of analyzing a memory allocation dynamic and
object creation dynamics of a very big C++ application with a goal of
optimizing its performance and eventually also identifying memory
leaks. The application in question is the Mozilla Web Browser. I also
have had similar tasks before in the compiler construction area. And
it is easy to come up with many more examples, where such kind of
statistics can be very...
|
by: dunleav1 |
last post by:
I have a process that does inserts that runs 50% slower that Oracle and
Mssql.
Queries normally take 50% slower than normal.
DB2, Oracle, Mssql all are configured on same os, same disk array (raid
1/0 - 15k disks) but DB2 I/O seems to be significantly slower.
Tablespaces are SMS with automatic prefetch set.
My thought was to create the log buffer array as large as possible so I
don't have to switch to disk much. I'm running circular...
|
by: Raj |
last post by:
We have a batch process that deletes about 7-8 million records every
day
& takes about 30-35 mins to complete. Is there a way to optimize
deletes? Below is the statement
delete from fact where (key,dt) in (select key,dt from dim1 union
select key,dt from dim2)
Fact table has composite index on key,someothecolumn,dt.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
