By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,640 Members | 1,581 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,640 IT Pros & Developers. It's quick & easy.

Slow insert/logging performance

P: n/a
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 logging. data
tablespace buffer pools are large as well (80000)

Here are my settings:
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000

Tablespaces are SMS instead of DMS.
What kind of % increase can I expect from switching to DMS?
Are there any other parameters I need to focus to improve i/o
performance?

DB2 should be screaming fast on this raid array and it just seems to
crawl.

Note: There is nothing else running on this server/raid array and there
are no errors in the db2diag file.

Jun 28 '06 #1
Share this Question
Share on Google+
46 Replies


P: n/a
dunleav1 wrote:
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 logging. data
tablespace buffer pools are large as well (80000)

Here are my settings:
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000

Tablespaces are SMS instead of DMS.
What kind of % increase can I expect from switching to DMS?
Are there any other parameters I need to focus to improve i/o
performance?

DB2 should be screaming fast on this raid array and it just seems to
crawl.

Note: There is nothing else running on this server/raid array and there
are no errors in the db2diag file.

Some generic recommendations (also for update performance):
* Make sure you have more than one IO server (default is 1) -- You do,OK
Thumbrule is that the number should be equal to the disc-spindles.
* db2 update db cfg using SEQDETECT yes
Enables that db2 detects that the update is doing a table scan and
kicks of prefetching (not relevant for insert of course)
* db2set DB2_PARALLEL_IO=*
Enables parallel IO even in case you only have one container in your
tablespace
* db2set DB2_USE_ALTERNATE_PAGE_CLEANING=yes
Uses the better page cleaner algorithm (always good in OLTP,
not always in BI which is why it's not (yet) the default)

Cheers
Serge

PS: You don't use anything fancy like MDC, right? I've seen poor insert
performance when the MDC dimensions were too fine grained
(explosion of extends).
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 28 '06 #2

P: n/a


Serge Rielau wrote:
dunleav1 wrote:
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 logging. data
tablespace buffer pools are large as well (80000)

Here are my settings:
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000

Tablespaces are SMS instead of DMS.
What kind of % increase can I expect from switching to DMS? Can I expect an increase in performance switching to DMS ? Are there any other parameters I need to focus to improve i/o
performance?

DB2 should be screaming fast on this raid array and it just seems to
crawl.

Note: There is nothing else running on this server/raid array and there
are no errors in the db2diag file.
Some generic recommendations (also for update performance):
* Make sure you have more than one IO server (default is 1) -- You do,OK
Thumbrule is that the number should be equal to the disc-spindles.

I reduced the ioservers to the numbers of disks in the array - 6. * db2 update db cfg using SEQDETECT yes
Enables that db2 detects that the update is doing a table scan and
kicks of prefetching (not relevant for insert of course)
* db2set DB2_PARALLEL_IO=*
Enables parallel IO even in case you only have one container in your
tablespace I'll give the db2_parallel_io=* a shot.

I know to set the pagesize of the tablespace to the stripe size of the
raid array,
but when prefetch automatic is set on the tablespace do the
overhead/extensize,prefetchsize,transferrate for the tablespace matter
?
* db2set DB2_USE_ALTERNATE_PAGE_CLEANING=yes
Uses the better page cleaner algorithm (always good in OLTP,
not always in BI which is why it's not (yet) the default)
DB2_ALTERNATE_PAGE_CLEANING is set to yes.
Cheers
Serge

PS: You don't use anything fancy like MDC, right? I've seen poor insert
performance when the MDC dimensions were too fine grained
(explosion of extends). No MDC is used in this database. --
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Jun 28 '06 #3

P: n/a


Serge Rielau wrote:
dunleav1 wrote:
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 logging. data
tablespace buffer pools are large as well (80000)

Here are my settings:
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000

Tablespaces are SMS instead of DMS.
What kind of % increase can I expect from switching to DMS? Can I expect an increase in performance switching to DMS ? Are there any other parameters I need to focus to improve i/o
performance?

DB2 should be screaming fast on this raid array and it just seems to
crawl.

Note: There is nothing else running on this server/raid array and there
are no errors in the db2diag file.
Some generic recommendations (also for update performance):
* Make sure you have more than one IO server (default is 1) -- You do,OK
Thumbrule is that the number should be equal to the disc-spindles.

I reduced the ioservers to the numbers of disks in the array - 6. * db2 update db cfg using SEQDETECT yes
Enables that db2 detects that the update is doing a table scan and
kicks of prefetching (not relevant for insert of course)
* db2set DB2_PARALLEL_IO=*
Enables parallel IO even in case you only have one container in your
tablespace I'll give the db2_parallel_io=* a shot.

I know to set the pagesize of the tablespace to the stripe size of the
raid array,
but when prefetch automatic is set on the tablespace do the
overhead/extensize,prefetchsize,transferrate for the tablespace matter
?
* db2set DB2_USE_ALTERNATE_PAGE_CLEANING=yes
Uses the better page cleaner algorithm (always good in OLTP,
not always in BI which is why it's not (yet) the default)
DB2_ALTERNATE_PAGE_CLEANING is set to yes.
Cheers
Serge

PS: You don't use anything fancy like MDC, right? I've seen poor insert
performance when the MDC dimensions were too fine grained
(explosion of extends). No MDC is used in this database. --
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Jun 28 '06 #4

P: n/a
Sorry for the double post above.

I have a java program that sends inserts to the database via jdbc.
This java program just sends inserts to the database via a jdbc driver.
I am using:
ibm db2 jdbc universal driver architecture 2.8.46
Java 1.5.0_06-b05
Apache Tomcat/5.5.15
Oracle and Mssql are almost twice as fast as DB2 with the same version
of tomcat and java and java program performing inserts and selects. The
hardware and configuration is the same accross Vendors.
The database server isn't even being stressed because if I look at the
operating system the system is 5% cpu used, 50% memory, 15% i/o.

I set the data tablespace to be DMS and I turne DB2_PARALLEL_IO=*,
turned automatic_prefetch=true for the data tablespace and DB2 is still
dog slow.

It looks like the database isn't sending out the i/o requests fast
enough.

Any more things I can check?

Jun 28 '06 #5

P: n/a
dunleav1 wrote:
Sorry for the double post above.

I have a java program that sends inserts to the database via jdbc.
This java program just sends inserts to the database via a jdbc driver.
I am using:
ibm db2 jdbc universal driver architecture 2.8.46
Java 1.5.0_06-b05
Apache Tomcat/5.5.15

How are these inserts send? Could be you don't get batching in DB2?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 28 '06 #6

P: n/a
The inserts are a single transaction so there should be no batching by
the jdbc driver for this process.

I have a later test that will utilize batching, but I need to get db2
tuned properly for single transactions first :)

Serge Rielau wrote:
dunleav1 wrote:
Sorry for the double post above.

I have a java program that sends inserts to the database via jdbc.
This java program just sends inserts to the database via a jdbc driver.
I am using:
ibm db2 jdbc universal driver architecture 2.8.46
Java 1.5.0_06-b05
Apache Tomcat/5.5.15

How are these inserts send? Could be you don't get batching in DB2?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Jun 28 '06 #7

P: n/a
dunleav1 wrote:
The inserts are a single transaction so there should be no batching by
the jdbc driver for this process.

I have a later test that will utilize batching, but I need to get db2
tuned properly for single transactions first :)

You mean 1 row/transaction? Do you use parameter markers .. VALUES (?,
?, ?) or constants? Any triggers, RI, check constraints?

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 28 '06 #8

P: n/a
Ian
dunleav1 wrote:
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.


What kind of throughput are you getting (rows/s, for DB2 vs. Oracle vs.
MSSQL).

It would be interesting to see a database snapshot that includes the
activity to see what could be causing the slowdown. The DB2 commands
below should be run from the same session

db2 update monitor switches using bufferpool on
db2 reset monitor all
<run your program>
db2 get snapshot for db on <yourdb>
Jun 28 '06 #9

P: n/a
I am using VALUES (?,?,?) etc.

I think I found an issue where the license was not updated for the
multiple processor machine. So I'm retesting.
I'll post the results.
Serge Rielau wrote:
dunleav1 wrote:
The inserts are a single transaction so there should be no batching by
the jdbc driver for this process.

I have a later test that will utilize batching, but I need to get db2
tuned properly for single transactions first :)

You mean 1 row/transaction? Do you use parameter markers .. VALUES (?,
?, ?) or constants? Any triggers, RI, check constraints?

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Jun 28 '06 #10

P: n/a
dunleav1 wrote:
I am using VALUES (?,?,?) etc.

I think I found an issue where the license was not updated for the
multiple processor machine. So I'm retesting.
I'll post the results.

DB2 doesn't care for that. Also there is not much to be parallelized on
a single row insert - at least for the statement itself.
What about triggers, RI and check-constraints? Got any of these on the
table?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 28 '06 #11

P: n/a

You can find an article about DB2 performance at
http://tinyurl.com/kwljd

One thing that will help insert performance is increasing the database
configuration variable LOGBUFSZ. The default is 8 which is way to low
for good performance. The article at developersworks recommends setting
it to at least 256 for OLTP and 128 for OLAP.
Jun 29 '06 #12

P: n/a

Gert van der Kooij wrote:
You can find an article about DB2 performance at
http://tinyurl.com/kwljd

One thing that will help insert performance is increasing the database
configuration variable LOGBUFSZ. The default is 8 which is way to low
for good performance. The article at developersworks recommends setting
it to at least 256 for OLTP and 128 for OLAP.


LOGBUFSZ is set to 4096.

Jun 29 '06 #13

P: n/a
You're right the license stuff made no difference.

There is a trigger on the table I am inserting into but it fires on
update not insert.
There are no check-constraints and the table is the parent in any RI
relationship.

Serge Rielau wrote:
dunleav1 wrote:
I am using VALUES (?,?,?) etc.

I think I found an issue where the license was not updated for the
multiple processor machine. So I'm retesting.
I'll post the results.

DB2 doesn't care for that. Also there is not much to be parallelized on
a single row insert - at least for the statement itself.
What about triggers, RI and check-constraints? Got any of these on the
table?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Jun 29 '06 #14

P: n/a

Ian wrote:
dunleav1 wrote:
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.


What kind of throughput are you getting (rows/s, for DB2 vs. Oracle vs.
MSSQL).

It would be interesting to see a database snapshot that includes the
activity to see what could be causing the slowdown. The DB2 commands
below should be run from the same session

db2 update monitor switches using bufferpool on
db2 reset monitor all
<run your program>
db2 get snapshot for db on <yourdb>


Sample throughput:
oracle - 550 inserts per second
mssql - 500 inserts per second
db2 - 200 inserts per second

I going to try to run a database snapshot later this afternoon and I'll
post up tonight.

Jun 29 '06 #15

P: n/a
dunleav1 wrote:
db2 - 200 inserts per second

That is indeed low. You aren't disconnecting between the inserts or so?
When do you commit?

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 29 '06 #16

P: n/a
"dunleav1" <jm*@dunleavyenterprises.com> wrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
LOGBUFSZ is set to 4096.


Reduce LOGBUFSZ to 256. Making it too large can slow things down. The
LOGBUFSZ memory comes out of memory for DBHEAP, so make sure your DBHEAP is
at 256 larger than you would otherwise need (2048 would probably be fine).

Did you make all the other suggested changes, including number of page
cleaners, I/O servers, log sizes, number of logs, etc?

Please list the db config parms that you used. If you made them too large
(like LOGBUFSZ) then you can adversely affect performance.
Jun 29 '06 #17

P: n/a
Hi!

Transaction log size has a major impact on INSERTs, so find out how big your
transaction logs are (LOGFILSIZ) and how many primary logs there are
(LOGPRIMARY).
The defaults are way too low for high performance.
I've set them like this:
- LOGFILSIZ = 4096
- LOGPRIMARY = 50

But you can play with them.

Best regards,
Kovi

dunleav1 wrote:

Ian wrote:
dunleav1 wrote:
> 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.


What kind of throughput are you getting (rows/s, for DB2 vs. Oracle vs.
MSSQL).

It would be interesting to see a database snapshot that includes the
activity to see what could be causing the slowdown. The DB2 commands
below should be run from the same session

db2 update monitor switches using bufferpool on
db2 reset monitor all
<run your program>
db2 get snapshot for db on <yourdb>


Sample throughput:
oracle - 550 inserts per second
mssql - 500 inserts per second
db2 - 200 inserts per second

I going to try to run a database snapshot later this afternoon and I'll
post up tonight.


--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Jun 30 '06 #18

P: n/a
No there are no disconnects.
Serge Rielau wrote:
dunleav1 wrote:
db2 - 200 inserts per second

That is indeed low. You aren't disconnecting between the inserts or so?
When do you commit?

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Jun 30 '06 #19

P: n/a

Mark A wrote:
"dunleav1" <jm*@dunleavyenterprises.com> wrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
LOGBUFSZ is set to 4096.


Reduce LOGBUFSZ to 256. Making it too large can slow things down. The
LOGBUFSZ memory comes out of memory for DBHEAP, so make sure your DBHEAP is
at 256 larger than you would otherwise need (2048 would probably be fine).

Did you make all the other suggested changes, including number of page
cleaners, I/O servers, log sizes, number of logs, etc?

Please list the db config parms that you used. If you made them too large
(like LOGBUFSZ) then you can adversely affect performance.


How can making LOGBUFSZ set to the max slow things down?
I am allocating a larger section of memory to hold logs until a write
occurs.

Here are my params:
NUM_IOCLEANERS 6
NUM_IOSERVERS 6
LOGBUFSZ 4096
DBHEAP 6000
LOGFILSIZ 60000
LOGPRIMARY 10
LOGSECOND 8
MINCOMMIT 25

Jun 30 '06 #20

P: n/a
Output from a snapshot:
Last reset timestamp = 06/30/2006 10:53:43.698439
Snapshot timestamp = 06/30/2006 10:55:35.462439

Log space available to the database (Bytes)= 4204458230
Log space used by the database (Bytes) = 201941770
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 561835654
Secondary logs allocated currently = 0
Log pages read = 0
Log read time (sec.ns) = 0.000000004
Log pages written = 70397
Log write time (sec.ns) = 28.000000004
Number write log IOs = 70372
Number read log IOs = 0
Number partial page log IOs = 21593
Number log buffer full = 0
Log data found in buffer = 0

The page size of the tablespace where the data is being inserted is
32k.
The tablespace is on an raid 1/0 array that has 32k stripe size.
The disks are 15k rpm disks so they are not slow.
The tablespace has prefetch automatic set to true.
This is about 2 minute snapshot duration - Correct ?
Does the logwrite time look too long for a 2 minutes snapshot ?
What is partial page log IOs?

Jun 30 '06 #21

P: n/a
How large is the bufferpool for the tablespace that you are inserting
into, and what is your softmax and chngpg_thresh settings?

I'm curious how busy those IO_CLEANERS are during the inserts.

I'd also reassert the suggestion made earlier to insert multiple rows
per statement -- it's way faster.....

cheers

dunleav1 wrote:
Mark A wrote:
"dunleav1" <jm*@dunleavyenterprises.com> wrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
LOGBUFSZ is set to 4096.


Reduce LOGBUFSZ to 256. Making it too large can slow things down. The
LOGBUFSZ memory comes out of memory for DBHEAP, so make sure your DBHEAP is
at 256 larger than you would otherwise need (2048 would probably be fine).

Did you make all the other suggested changes, including number of page
cleaners, I/O servers, log sizes, number of logs, etc?

Please list the db config parms that you used. If you made them too large
(like LOGBUFSZ) then you can adversely affect performance.


How can making LOGBUFSZ set to the max slow things down?
I am allocating a larger section of memory to hold logs until a write
occurs.

Here are my params:
NUM_IOCLEANERS 6
NUM_IOSERVERS 6
LOGBUFSZ 4096
DBHEAP 6000
LOGFILSIZ 60000
LOGPRIMARY 10
LOGSECOND 8
MINCOMMIT 25


Jun 30 '06 #22

P: n/a
The tablespace where the inserts are occuring is 32k block size (set to
match the stripe on the raid array).
The buffer pool for the tablespace is 31250 (32k).
softmax = 120
chgpgs_thres = 60

How can tell how busy the IO_CLEANERS are ?

It's winblows operating system.
ts******@gmail.com wrote:
How large is the bufferpool for the tablespace that you are inserting
into, and what is your softmax and chngpg_thresh settings?

I'm curious how busy those IO_CLEANERS are during the inserts.

I'd also reassert the suggestion made earlier to insert multiple rows
per statement -- it's way faster.....

cheers

dunleav1 wrote:
Mark A wrote:
"dunleav1" <jm*@dunleavyenterprises.com> wrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
> LOGBUFSZ is set to 4096.
>

Reduce LOGBUFSZ to 256. Making it too large can slow things down. The
LOGBUFSZ memory comes out of memory for DBHEAP, so make sure your DBHEAP is
at 256 larger than you would otherwise need (2048 would probably be fine).

Did you make all the other suggested changes, including number of page
cleaners, I/O servers, log sizes, number of logs, etc?

Please list the db config parms that you used. If you made them too large
(like LOGBUFSZ) then you can adversely affect performance.


How can making LOGBUFSZ set to the max slow things down?
I am allocating a larger section of memory to hold logs until a write
occurs.

Here are my params:
NUM_IOCLEANERS 6
NUM_IOSERVERS 6
LOGBUFSZ 4096
DBHEAP 6000
LOGFILSIZ 60000
LOGPRIMARY 10
LOGSECOND 8
MINCOMMIT 25


Jun 30 '06 #23

P: n/a
Note: I bumped down the buffer pool setting to 31250 and changed the
blocksize to 32k
from a smaller blocksize and a pool of 80000.

dunleav1 wrote:
The tablespace where the inserts are occuring is 32k block size (set to
match the stripe on the raid array).
The buffer pool for the tablespace is 31250 (32k).
softmax = 120
chgpgs_thres = 60

How can tell how busy the IO_CLEANERS are ?

It's winblows operating system.
ts******@gmail.com wrote:
How large is the bufferpool for the tablespace that you are inserting
into, and what is your softmax and chngpg_thresh settings?

I'm curious how busy those IO_CLEANERS are during the inserts.

I'd also reassert the suggestion made earlier to insert multiple rows
per statement -- it's way faster.....

cheers

dunleav1 wrote:
Mark A wrote:
> "dunleav1" <jm*@dunleavyenterprises.com> wrote in message
> news:11**********************@75g2000cwc.googlegro ups.com...
> > LOGBUFSZ is set to 4096.
> >
>
> Reduce LOGBUFSZ to 256. Making it too large can slow things down. The
> LOGBUFSZ memory comes out of memory for DBHEAP, so make sure your DBHEAP is
> at 256 larger than you would otherwise need (2048 would probably be fine).
>
> Did you make all the other suggested changes, including number of page
> cleaners, I/O servers, log sizes, number of logs, etc?
>
> Please list the db config parms that you used. If you made them too large
> (like LOGBUFSZ) then you can adversely affect performance.

How can making LOGBUFSZ set to the max slow things down?
I am allocating a larger section of memory to hold logs until a write
occurs.

Here are my params:
NUM_IOCLEANERS 6
NUM_IOSERVERS 6
LOGBUFSZ 4096
DBHEAP 6000
LOGFILSIZ 60000
LOGPRIMARY 10
LOGSECOND 8
MINCOMMIT 25


Jun 30 '06 #24

P: n/a
Ian
dunleav1 wrote:
Mark A wrote:
"dunleav1" <jm*@dunleavyenterprises.com> wrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
LOGBUFSZ is set to 4096.

Reduce LOGBUFSZ to 256. Making it too large can slow things down. The
LOGBUFSZ memory comes out of memory for DBHEAP, so make sure your DBHEAP is
at 256 larger than you would otherwise need (2048 would probably be fine).

Did you make all the other suggested changes, including number of page
cleaners, I/O servers, log sizes, number of logs, etc?

Please list the db config parms that you used. If you made them too large
(like LOGBUFSZ) then you can adversely affect performance.


How can making LOGBUFSZ set to the max slow things down?
I am allocating a larger section of memory to hold logs until a write
occurs.

Here are my params:
NUM_IOCLEANERS 6
NUM_IOSERVERS 6
LOGBUFSZ 4096
DBHEAP 6000
LOGFILSIZ 60000
LOGPRIMARY 10
LOGSECOND 8
MINCOMMIT 25

MINCOMMIT 25 is almost certainly the issue. You are telling DB2 to
wait until either:

25 *concurrent* transactions have issued a COMMIT
or
1 second has elapsed
Each individual COMMIT will block waiting for one of the individual
conditions is met, so in your example of having a batch program doing
insert,commit,insert,commit... your batch will be S-L-O-W. Frankly,
I'm surprised you're even getting 200 rows/s. Do you have multiple
threads doing inserts?

Reduce MINCOMMIT to 1 and see what you get. Yes, *1*. I have worked
on HUGE OLTP environments doing 200+ transactions per second that
have MINCOMMIT=1.

Oh, and reduce logbufsz, too, as Mark A suggested. Setting it to 4096
is doing nothing for you (other than wasting some memory that could
potentially be used more effectively), because you're committing very
frequently. (Log records from in the buffer flushed to disk
whenever you issue a commit).

Jun 30 '06 #25

P: n/a
I'll change those two parameters and run another test tommorrow.
Thanks for the suggestions.

Jun 30 '06 #26

P: n/a
I already had logbufsz set to 4096.
I set mincommit to 1 and reran my test and the throughput was horrible.
Ian wrote:
dunleav1 wrote:
Mark A wrote:
"dunleav1" <jm*@dunleavyenterprises.com> wrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
LOGBUFSZ is set to 4096.

Reduce LOGBUFSZ to 256. Making it too large can slow things down. The
LOGBUFSZ memory comes out of memory for DBHEAP, so make sure your DBHEAP is
at 256 larger than you would otherwise need (2048 would probably be fine).

Did you make all the other suggested changes, including number of page
cleaners, I/O servers, log sizes, number of logs, etc?

Please list the db config parms that you used. If you made them too large
(like LOGBUFSZ) then you can adversely affect performance.


How can making LOGBUFSZ set to the max slow things down?
I am allocating a larger section of memory to hold logs until a write
occurs.

Here are my params:
NUM_IOCLEANERS 6
NUM_IOSERVERS 6
LOGBUFSZ 4096
DBHEAP 6000
LOGFILSIZ 60000
LOGPRIMARY 10
LOGSECOND 8
MINCOMMIT 25

MINCOMMIT 25 is almost certainly the issue. You are telling DB2 to
wait until either:

25 *concurrent* transactions have issued a COMMIT
or
1 second has elapsed
Each individual COMMIT will block waiting for one of the individual
conditions is met, so in your example of having a batch program doing
insert,commit,insert,commit... your batch will be S-L-O-W. Frankly,
I'm surprised you're even getting 200 rows/s. Do you have multiple
threads doing inserts?

Reduce MINCOMMIT to 1 and see what you get. Yes, *1*. I have worked
on HUGE OLTP environments doing 200+ transactions per second that
have MINCOMMIT=1.

Oh, and reduce logbufsz, too, as Mark A suggested. Setting it to 4096
is doing nothing for you (other than wasting some memory that could
potentially be used more effectively), because you're committing very
frequently. (Log records from in the buffer flushed to disk
whenever you issue a commit).


Jul 1 '06 #27

P: n/a

"dunleav1" <jm*@dunleavyenterprises.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
>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 logging. data
tablespace buffer pools are large as well (80000)

Here are my settings:
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000

Tablespaces are SMS instead of DMS.
What kind of % increase can I expect from switching to DMS?
Are there any other parameters I need to focus to improve i/o
performance?

DB2 should be screaming fast on this raid array and it just seems to
crawl.

Note: There is nothing else running on this server/raid array and there
are no errors in the db2diag file.
Hi,

one question:
What data types do you are using in your insert, do you're using LOBs or
long varchars?

Regards
Ralph
Jul 1 '06 #28

P: n/a
dunleav1 wrote:
I already had logbufsz set to 4096.
I set mincommit to 1 and reran my test and the throughput was horrible.
Hmm.. where is your log? It should be on separate from your data drives.
That MINCOMMIT 1 gives you horrible performance is suspicious...

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 1 '06 #29

P: n/a
In the original post, you wrote that
the tablespaces were SMS tablespaces.
That would mean that data and indexes
are on the same tablespace, sometimes
undesirable.

Can you post whether the tablespaces
have file-system caching enabled (this
is the default), and if so please
can you post the difference in performance
when file-system caching is disabled
for each tablespace.

Also can you explain how the application
is performing the inserts, that is to say
does it connect,insert, commit, disconnect for each row,
or does it perform a single connect, multiple
inserts, single or multiple commits, and a final disconnect?

Also what is the datatype of the information
being inserted (i.e is it long-varchar or clob or blob etc).

Jul 3 '06 #30

P: n/a
Ian
dunleav1 wrote:
I already had logbufsz set to 4096.
I set mincommit to 1 and reran my test and the throughput was horrible.

This feels like a troll, now.
If you're serious about fixing the problem, then let's see some real
data, full snapshots (not excerpts).

Otherwise, please go away.

Jul 3 '06 #31

P: n/a
The table has approx. 600 columns. The columns are defined a
combincation of decimal or varchar. So, the table is an abnormally
large table.

Ralph Ganszky wrote:
"dunleav1" <jm*@dunleavyenterprises.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
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 logging. data
tablespace buffer pools are large as well (80000)

Here are my settings:
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000

Tablespaces are SMS instead of DMS.
What kind of % increase can I expect from switching to DMS?
Are there any other parameters I need to focus to improve i/o
performance?

DB2 should be screaming fast on this raid array and it just seems to
crawl.

Note: There is nothing else running on this server/raid array and there
are no errors in the db2diag file.

Hi,

one question:
What data types do you are using in your insert, do you're using LOBs or
long varchars?

Regards
Ralph
Jul 5 '06 #32

P: n/a

Ian wrote:
dunleav1 wrote:
I already had logbufsz set to 4096.
I set mincommit to 1 and reran my test and the throughput was horrible.


This feels like a troll, now.
If you're serious about fixing the problem, then let's see some real
data, full snapshots (not excerpts).

Otherwise, please go away.
I'm not a troll.
I thought it would be ugly to post the whole snapshot but here you go.
get snapshot for all databases

Database Snapshot

Database name = B004

Database path = C:\DB2\NODE0000\SQL00002\

Input database alias =

Database status = Active

Catalog database partition number = 0

Catalog network node name =

Operating system running at database server= NT

Location of the database = Local

First database connect timestamp = 07/05/2006 09:26:16.534543

Last reset timestamp = 07/05/2006 09:52:37.897631

Last backup timestamp = 06/23/2006 03:00:01.000000

Snapshot timestamp = 07/05/2006 09:55:00.039582

Number of automatic storage paths = 0

High water mark for connections = 31

Application connects = 5

Secondary connects total = 0

Applications connected currently = 5

Appls. executing in db manager currently = 0

Agents associated with applications = 5

Maximum agents associated with applications= 31

Maximum coordinating agents = 31

Locks held currently = 0

Lock waits = 0

Time database waited on locks (ms) = Not Collected

Lock list memory in use (Bytes) = 3760

Deadlocks detected = 0

Lock escalations = 0

Exclusive lock escalations = 0

Agents currently waiting on locks = 0

Lock Timeouts = 0

Number of indoubt transactions = 0

Total Private Sort heap allocated = 0

Total Shared Sort heap allocated = 0

Shared Sort heap high water mark = 0

Total sorts = 1

Total sort time (ms) = 0

Sort overflows = 0

Active sorts = 0

Buffer pool data logical reads = 49456

Buffer pool data physical reads = 4

Buffer pool temporary data logical reads = 0

Buffer pool temporary data physical reads = 0

Asynchronous pool data page reads = 0

Buffer pool data writes = 4813

Asynchronous pool data page writes = 4643

Buffer pool index logical reads = 283454

Buffer pool index physical reads = 4

Buffer pool temporary index logical reads = 0

Buffer pool temporary index physical reads = 0

Asynchronous pool index page reads = 0

Buffer pool index writes = 899

Asynchronous pool index page writes = 892

Total buffer pool read time (milliseconds) = 25

Total buffer pool write time (milliseconds)= 414

Total elapsed asynchronous read time = 0

Total elapsed asynchronous write time = 363

Asynchronous data read requests = 0

Asynchronous index read requests = 0

No victim buffers available = 0

LSN Gap cleaner triggers = 0

Dirty page steal cleaner triggers = 0

Dirty page threshold cleaner triggers = 0

Time waited for prefetch (ms) = 0

Unread prefetch pages = 0

Direct reads = 812

Direct writes = 1280

Direct read requests = 366

Direct write requests = 5

Direct reads elapsed time (ms) = 50

Direct write elapsed time (ms) = 9

Database files closed = 0

Data pages copied to extended storage = 0

Index pages copied to extended storage = 0

Data pages copied from extended storage = 0

Index pages copied from extended storage = 0

Host execution elapsed time = Not Collected

Commit statements attempted = 28026

Rollback statements attempted = 0

Dynamic statements attempted = 29840

Static statements attempted = 28026

Failed statement operations = 0

Select SQL statements executed = 125

Update/Insert/Delete statements executed = 28988

DDL statements executed = 0

Inactive stmt history memory usage (bytes) = 0

Internal automatic rebinds = 0

Internal rows deleted = 0

Internal rows inserted = 0

Internal rows updated = 0

Internal commits = 1

Internal rollbacks = 0

Internal rollbacks due to deadlock = 0

Rows deleted = 0

Rows inserted = 28750

Rows updated = 238

Rows selected = 150

Rows read = 771

Binds/precompiles attempted = 0

Log space available to the database (Bytes)= 4175848011

Log space used by the database (Bytes) = 230551989

Maximum secondary log space used (Bytes) = 0

Maximum total log space used (Bytes) = 563699214

Secondary logs allocated currently = 0

Log pages read = 0

Log read time (sec.ns) = 0.000000004

Log pages written = 91478

Log write time (sec.ns) = 33.000000004

Number write log IOs = 91421

Number read log IOs = 0

Number partial page log IOs = 28056

Number log buffer full = 0

Log data found in buffer = 0

Appl id holding the oldest transaction = 7

Log to be redone for recovery (Bytes) = 323343002

Log accounted for by dirty pages (Bytes) = 323343002

File number of first active log = 4

File number of last active log = 3

File number of current active log = 5

File number of log being archived = Not applicable

Package cache lookups = 29109

Package cache inserts = 6

Package cache overflows = 0

Package cache high water mark (Bytes) = 859052

Application section lookups = 29840

Application section inserts = 6

Catalog cache lookups = 8

Catalog cache inserts = 2

Catalog cache overflows = 0

Catalog cache high water mark = 0

Workspace Information

Shared high water mark = 0

Corresponding shared overflows = 0

Total shared section inserts = 0

Total shared section lookups = 0

Private high water mark = 345658

Corresponding private overflows = 0

Total private section inserts = 6

Total private section lookups = 29107

Number of hash joins = 0

Number of hash loops = 0

Number of hash join overflows = 0

Number of small hash join overflows = 0



get snapshot for dbm

Database Manager Snapshot

Node name =

Node type = Enterprise Server
Edition with local and remote clients

Instance name = DB2

Number of database partitions in DB2 instance = 1

Database manager status = Active

Product name = DB2 v8.1.11.973

Service level = s060120 (WR21365)

Private Sort heap allocated = 0

Private Sort heap high water mark = 36049

Post threshold sorts = 0

Piped sorts requested = 1

Piped sorts accepted = 1

Start Database Manager timestamp = 07/05/2006
09:26:00.129033

Last reset timestamp = 07/05/2006
09:52:37.897631

Snapshot timestamp = 07/05/2006
09:55:09.818480

Remote connections to db manager = 4

Remote connections executing in db manager = 0

Local connections = 1

Local connections executing in db manager = 0

Active local databases = 1

High water mark for agents registered = 32

High water mark for agents waiting for a token = 0

Agents registered = 32

Agents waiting for a token = 0

Idle agents = 14

Committed private Memory (Bytes) = 136642560

Switch list for db partition number 0

Buffer Pool Activity Information (BUFFERPOOL) = ON 07/05/2006
09:51:29.282932

Lock Information (LOCK) = ON 07/05/2006
09:26:16.999227

Sorting Information (SORT) = ON 07/05/2006
09:52:03.069922

SQL Statement Information (STATEMENT) = OFF

Table Activity Information (TABLE) = OFF

Take Timestamp Information (TIMESTAMP) = ON 07/05/2006
09:26:00.129033

Unit of Work Information (UOW) = OFF

Agents assigned from pool = 209

Agents created from empty pool = 34

Agents stolen from another application = 0

High water mark for coordinating agents = 32

Max agents overflow = 0

Hash joins after heap threshold exceeded = 0

Total number of gateway connections = 0

Current number of gateway connections = 0

Gateway connections waiting for host reply = 0

Gateway connections waiting for client request = 0

Gateway connection pool agents stolen = 0

Memory usage for database manager:

Memory Pool Type = Database Monitor Heap

Current size (bytes) = 229376

High water mark (bytes) = 393216

Configured size (bytes) = 491520

Memory Pool Type = Other Memory

Current size (bytes) = 9961472

High water mark (bytes) = 14237696

Configured size (bytes) = 3991928832

Memory Pool Type = Backup/Restore/Util
Heap

Current size (bytes) = 16384

High water mark (bytes) = 16384

Configured size (bytes) = 368984064

Memory Pool Type = Package Cache Heap

Current size (bytes) = 950272

High water mark (bytes) = 950272

Configured size (bytes) = 3991928832

Memory Pool Type = Catalog Cache Heap

Current size (bytes) = 671744

High water mark (bytes) = 671744

Configured size (bytes) = 3991928832

Memory Pool Type = Buffer Pool Heap

Current size (bytes) = 514129920

High water mark (bytes) = 514129920

Configured size (bytes) = 3991928832

Memory Pool Type = Buffer Pool Heap

Current size (bytes) = 1028194304

High water mark (bytes) = 1028194304

Configured size (bytes) = 3991928832

Memory Pool Type = Buffer Pool Heap

Current size (bytes) = 103022592

High water mark (bytes) = 103022592

Configured size (bytes) = 3991928832

Memory Pool Type = Buffer Pool Heap

Current size (bytes) = 655360

High water mark (bytes) = 655360

Configured size (bytes) = 3991928832

Memory Pool Type = Buffer Pool Heap

Current size (bytes) = 393216

High water mark (bytes) = 393216

Configured size (bytes) = 3991928832

Memory Pool Type = Buffer Pool Heap

Current size (bytes) = 262144

High water mark (bytes) = 262144

Configured size (bytes) = 3991928832

Memory Pool Type = Buffer Pool Heap

Current size (bytes) = 196608

High water mark (bytes) = 196608

Configured size (bytes) = 3991928832

Memory Pool Type = Lock Manager Heap

Current size (bytes) = 278528

High water mark (bytes) = 278528

Configured size (bytes) = 294912

Memory Pool Type = Database Heap

Current size (bytes) = 5488640

High water mark (bytes) = 5488640

Configured size (bytes) = 35553280

get snapshot for all bufferpools

Bufferpool Snapshot

Bufferpool name = IBMDEFAULTBP

Database name = B004

Database path = C:\DB2\NODE0000\SQL00002\

Input database alias =

Snapshot timestamp = 07/05/2006 09:55:21.299562

Buffer pool data logical reads = 2695

Buffer pool data physical reads = 4

Buffer pool temporary data logical reads = 0

Buffer pool temporary data physical reads = 0

Buffer pool data writes = 388

Buffer pool index logical reads = 4191

Buffer pool index physical reads = 4

Buffer pool temporary index logical reads = 0

Buffer pool temporary index physical reads = 0

Total buffer pool read time (milliseconds) = 25

Total buffer pool write time (milliseconds)= 58

Asynchronous pool data page reads = 0

Asynchronous pool data page writes = 195

Buffer pool index writes = 14

Asynchronous pool index page reads = 0

Asynchronous pool index page writes = 6

Total elapsed asynchronous read time = 0

Total elapsed asynchronous write time = 0

Asynchronous data read requests = 0

Asynchronous index read requests = 0

No victim buffers available = 0

Direct reads = 914

Direct writes = 1536

Direct read requests = 417

Direct write requests = 6

Direct reads elapsed time (ms) = 51

Direct write elapsed time (ms) = 11

Database files closed = 0

Data pages copied to extended storage = 0

Index pages copied to extended storage = 0

Data pages copied from extended storage = 0

Index pages copied from extended storage = 0

Unread prefetch pages = 0

Vectored IOs = 0

Pages from vectored IOs = 0

Block IOs = 0

Pages from block IOs = 0

Physical page maps = 0

Node number = 0
Tablespaces using bufferpool = 5

Alter bufferpool information:

Pages left to remove = 0

Current size = 24402

Post-alter size = 24402

Bufferpool Snapshot

Bufferpool name = FLINT

Database name = B004

Database path = C:\DB2\NODE0000\SQL00002\

Input database alias =

Snapshot timestamp = 07/05/2006 09:55:21.299562

Buffer pool data logical reads = 54021

Buffer pool data physical reads = 0

Buffer pool temporary data logical reads = 0

Buffer pool temporary data physical reads = 0

Buffer pool data writes = 5401

Buffer pool index logical reads = 320956

Buffer pool index physical reads = 0

Buffer pool temporary index logical reads = 0

Buffer pool temporary index physical reads = 0

Total buffer pool read time (milliseconds) = 0

Total buffer pool write time (milliseconds)= 457

Asynchronous pool data page reads = 0

Asynchronous pool data page writes = 5401

Buffer pool index writes = 966

Asynchronous pool index page reads = 0

Asynchronous pool index page writes = 966

Total elapsed asynchronous read time = 0

Total elapsed asynchronous write time = 457

Asynchronous data read requests = 0

Asynchronous index read requests = 0

No victim buffers available = 0

Direct reads = 0

Direct writes = 0

Direct read requests = 0

Direct write requests = 0

Direct reads elapsed time (ms) = 0

Direct write elapsed time (ms) = 0

Database files closed = 0

Data pages copied to extended storage = 0

Index pages copied to extended storage = 0

Data pages copied from extended storage = 0

Index pages copied from extended storage = 0

Unread prefetch pages = 0

Vectored IOs = 0

Pages from vectored IOs = 0

Block IOs = 0

Pages from block IOs = 0

Physical page maps = 0

Node number = 0
Tablespaces using bufferpool = 1

Alter bufferpool information:

Pages left to remove = 0

Current size = 31250

Post-alter size = 31250

Bufferpool Snapshot

Bufferpool name = TEMP

Database name = B004

Database path = C:\DB2\NODE0000\SQL00002\

Input database alias =

Snapshot timestamp = 07/05/2006 09:55:21.299562

Buffer pool data logical reads = 0

Buffer pool data physical reads = 0

Buffer pool temporary data logical reads = 0

Buffer pool temporary data physical reads = 0

Buffer pool data writes = 0

Buffer pool index logical reads = 0

Buffer pool index physical reads = 0

Buffer pool temporary index logical reads = 0

Buffer pool temporary index physical reads = 0

Total buffer pool read time (milliseconds) = 0

Total buffer pool write time (milliseconds)= 0

Asynchronous pool data page reads = 0

Asynchronous pool data page writes = 0

Buffer pool index writes = 0

Asynchronous pool index page reads = 0

Asynchronous pool index page writes = 0

Total elapsed asynchronous read time = 0

Total elapsed asynchronous write time = 0

Asynchronous data read requests = 0

Asynchronous index read requests = 0

No victim buffers available = 0

Direct reads = 0

Direct writes = 0

Direct read requests = 0

Direct write requests = 0

Direct reads elapsed time (ms) = 0

Direct write elapsed time (ms) = 0

Database files closed = 0

Data pages copied to extended storage = 0

Index pages copied to extended storage = 0

Data pages copied from extended storage = 0

Index pages copied from extended storage = 0

Unread prefetch pages = 0

Vectored IOs = 0

Pages from vectored IOs = 0

Block IOs = 0

Pages from block IOs = 0

Physical page maps = 0

Node number = 0
Tablespaces using bufferpool = 1

Alter bufferpool information:

Pages left to remove = 0

Current size = 15625

Post-alter size = 15625

Jul 5 '06 #33

P: n/a

mike wrote:
In the original post, you wrote that
the tablespaces were SMS tablespaces.
That would mean that data and indexes
are on the same tablespace, sometimes
undesirable.
Yes - in my original tests I was using SMS - I have switched over to
DMS since.
I did not see any real performance gain from SMS to DMS.
>
Can you post whether the tablespaces
have file-system caching enabled (this
is the default), and if so please
can you post the difference in performance
when file-system caching is disabled
for each tablespace.
The tablespaces reside on a DMS tablespace.
The operating system is Windows NT.
The OS is installed on 6 disks configured as RAID1/0 with a stripe
size of 128k.
The disks are HP SCSI Utlra320 - 15k rpm - 320mb transfer rate, 3.8ms
average seek time. All disks are available as 1 LUN to the OS.
>
Also can you explain how the application
is performing the inserts, that is to say
does it connect,insert, commit, disconnect for each row,
or does it perform a single connect, multiple
inserts, single or multiple commits, and a final disconnect.
I have an application that sends a single transactioin to the database
and commits.
Yes - connect,insert, commit, disconnect for each row.
I have five of these applications running to get some volume to the db.
>
Also what is the datatype of the information
being inserted (i.e is it long-varchar or clob or blob etc).
The table be inserted into is a very large table. It is made up of
decimal and varchar.

Jul 5 '06 #34

P: n/a

Serge Rielau wrote:
dunleav1 wrote:
I already had logbufsz set to 4096.
I set mincommit to 1 and reran my test and the throughput was horrible.
Hmm.. where is your log? It should be on separate from your data drives.
That MINCOMMIT 1 gives you horrible performance is suspicious...

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Logfiles and datafiles are all on a shared LUN configured as RAID 1/0.
Yes, to separate logging and data access on separate drives would be
optimal but this server configuration doesn't really allow for it.

"MINCOMMIT =1 is suspicious" - what do I need to investigate further ?

Jul 5 '06 #35

P: n/a
Sorry - The table has 526 columns not 600.
Based on info from dbcc - the average row size is 4797 bytes and the
table has over 3 million rows.
There are four indexes on the table and an update trigger.

As I stated in previous posts - I have the same model and deployment in
Oracle and Mssql.
Same OS - same disk layout - same data - same test application.
I am getting a lot better performance in Oracle and Mssql.

I figure I must have not configured something correctly in my DB2
deployment to not get similar performance in DB2.
Thanks for all suggestions.

Jul 5 '06 #36

P: n/a

"dunleav1" <jm*@dunleavyenterprises.comwrote in message
news:11**********************@j8g2000cwa.googlegro ups.com...
Sorry - The table has 526 columns not 600.
Based on info from dbcc - the average row size is 4797 bytes and the
table has over 3 million rows.
There are four indexes on the table and an update trigger.

As I stated in previous posts - I have the same model and deployment in
Oracle and Mssql.
Same OS - same disk layout - same data - same test application.
I am getting a lot better performance in Oracle and Mssql.

I figure I must have not configured something correctly in my DB2
deployment to not get similar performance in DB2.
Thanks for all suggestions.
Hi,

if you look at the Snapshot you posted to Ian

Buffer pool data physical reads = 4
Buffer pool data writes = 4813
Asynchronous pool data page writes = 4643
Buffer pool index physical reads = 4
Buffer pool index writes = 899
Asynchronous pool index page writes = 892
Total buffer pool read time (milliseconds) = 25
Total buffer pool write time (milliseconds)= 414

and

LSN Gap cleaner triggers = 0
Dirty page steal cleaner triggers = 0
Dirty page threshold cleaner triggers = 0

and

Direct reads = 812
Direct writes = 1280
Direct read requests = 366
Direct write requests = 5
Direct reads elapsed time (ms) = 50
Direct write elapsed time (ms) = 9

and

Log pages written = 91478
Log write time (sec.ns) = 33.000000004
Number write log IOs = 91421

you see that your I/O rates are incredibly good. Your Log write time is 0.3
ms per write request and the Bufferpool write time is below 0.1 ms per write
request. You must have a different problem that slow I/O.

Did you check if you're waiting for something?
Jul 5 '06 #37

P: n/a
Please post the update trigger.

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 5 '06 #38

P: n/a

Serge Rielau wrote:
Please post the update trigger.

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
CREATE TRIGGER updateTrigger NO CASCADE BEFORE UPDATE OF
transStatusTypeId, transSyncInd ON LineItem REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL SET NEWROW.updateId = NEXTVAL FOR update_seq

Jul 5 '06 #39

P: n/a
dunleav1 wrote:
CREATE TRIGGER updateTrigger NO CASCADE BEFORE UPDATE OF
transStatusTypeId, transSyncInd ON LineItem REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL SET NEWROW.updateId = NEXTVAL FOR update_seq
Simple enough..
Anyway it was the INSERT that killed you.
Maybe it's worth summarizing all the data in one post. I lost track...

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 5 '06 #40

P: n/a
So you have five concurrent applications, each of which performs
approximately 600,000 individual
connect+single-row-insert+commit+disconnect, into the same
table (with 4 indexes) that lives in a DMS tablespace.

The overhead of setting-up and taking-down the database-connection
for every row may be high relative to the cost of the insert.

Are the inserts using parameter-markers, or using literal-values?
(is db2 recompiling an access-plan for each insert?)

Are the DMS containers "file" or "device"?
If they are "file" containers, then disable file-system caching for
those tablespaces
to see if avoiding double-buffering will benefit the inserts.

You say that average row-length is 4797 bytes, but what
is the highest width VARCHAR column in the DDL for the table?

Is APPEND MODE enabled for the table?

Jul 6 '06 #41

P: n/a
Correct - the update trigger has nothing to do with the amount of
records I can insert into the table.
Serge Rielau wrote:
dunleav1 wrote:
CREATE TRIGGER updateTrigger NO CASCADE BEFORE UPDATE OF
transStatusTypeId, transSyncInd ON LineItem REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL SET NEWROW.updateId = NEXTVAL FOR update_seq
Simple enough..
Anyway it was the INSERT that killed you.
Maybe it's worth summarizing all the data in one post. I lost track...

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 6 '06 #42

P: n/a

mike wrote:
So you have five concurrent applications, each of which performs
approximately 600,000 individual
connect+single-row-insert+commit+disconnect, into the same
table (with 4 indexes) that lives in a DMS tablespace.
Kind of - as a stress test I kick of 5 current apps that perform a some
number of inserts into a large table that do a connect, single row
insert, commit, disconnect that is in a DMS tablespace.
The overhead of setting-up and taking-down the database-connection
for every row may be high relative to the cost of the insert.
How do I measure this ?
>
Are the inserts using parameter-markers, or using literal-values?
(is db2 recompiling an access-plan for each insert?)
The use parameter markers.
>
Are the DMS containers "file" or "device"?
If they are "file" containers, then disable file-system caching for
those tablespaces
to see if avoiding double-buffering will benefit the inserts.
They are a file container. It's windows not unix or linux. How do I
disabale filesystem caching ?
>
You say that average row-length is 4797 bytes, but what
is the highest width VARCHAR column in the DDL for the table?
Yes the average is 4797 - the highest width varchar is 100 in the ddl.
>
Is APPEND MODE enabled for the table?
I don't see it in the ddl from dbcc.

I did notice that the tablespace has an overhead and a transferrate
setup in the definition. Can that effect performance of inserts of
objects in that tablespace?

The numbers were calculated by dbcc when I created the tablespace - I
entered the hard drive values from the manufacturer. Why wouldn't that
be 0 instead 5.8 and .01 ?

CREATE REGULAR TABLESPACE FLINT IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE
USING (FILE 'C:\DB2\NODE0000\flint01'1280000)
EXTENTSIZE 16
PREFETCHSIZE AUTOMATIC
BUFFERPOOL FLINT
OVERHEAD 5.800000
TRANSFERRATE 0.010000;

Jul 6 '06 #43

P: n/a
dunleav1 wrote:
Kind of - as a stress test I kick of 5 current apps that perform a some
number of inserts into a large table that do a connect, single row
insert, commit, disconnect that is in a DMS tablespace.
>The overhead of setting-up and taking-down the database-connection
for every row may be high relative to the cost of the insert.
How do I measure this ?
Easiest way: Don't disconnect and compare...
You can also create a statement event monitor and sum up the
execution times. The delta between the last commit and the next insert
is an approximation.

What are the values for these DBM CFG?
MAXAGENTS
NUM_INITAGENTS
MAX_COORDAGENTS
MAX_CONNECTIONS

Especially make sure that NUMINITAGENTS is 5 (or more).

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 6 '06 #44

P: n/a
dunleav1 wrote:
The overhead of setting-up and taking-down the database-connection
for every row may be high relative to the cost of the insert.
How do I measure this ?
Refer to advice from Serge.

They are a file container. It's windows not unix or linux. How do I
disabale filesystem caching ?
In your case: before running your tests, close all
connections to the database, connect to the
database with suitable rights, and use a command
like this (ensure the tablespace name is correct):
"db2 alter tablespace flint no file system caching"
This just prevents double-buffering, which sometimes
is helpful.

Is APPEND MODE enabled for the table?
I don't see it in the ddl from dbcc.
This non-default setting will influence the speed of bulk
inserts and you would enable it as follows:
db2 "alter table Table-Name append on ".
I use this for bulk populating tables that are initially empty
and then disable it for regular oltp access.
You might not want that setting to be permanent,
unless you perform regular reorgs, because it will
use more disk space (it will always use new
pages instead of finding free space in existing
pages).

>
I did notice that the tablespace has an overhead and a transferrate
setup in the definition. Can that effect performance of inserts of
objects in that tablespace?
I would not bother about those because I'm guessing they have
nothing to do with your symptom, instead I'm guessing that
if you made a test whereby you kept the database-connection
opened, and performed the inserts but only commit every
thousand rows (or some other high figure) , and disconnect
at the end , then you will see quite a different result.
This is based on the theory that the
frequent connect/disconnect has a relatively
high cost in db2 compared to the insert, and the
relatively high cost of frequent commits for bulk inserts.

Jul 7 '06 #45

P: n/a
Serge here are the values you requested.
MAXAGENTS
400
NUM_INITAGENTS
5
MAX_COORDAGENTS
(MAXAGENTS - NUM_INITAGENTS)
MAX_CONNECTIONS
MAX_COORDAGENTS

Jul 10 '06 #46

P: n/a

mike wrote:
dunleav1 wrote:
The overhead of setting-up and taking-down the database-connection
for every row may be high relative to the cost of the insert.
How do I measure this ?
I'm trying to setup an event monitor to analyze.
Refer to advice from Serge.

They are a file container. It's windows not unix or linux. How do I
disabale filesystem caching ?

In your case: before running your tests, close all
connections to the database, connect to the
database with suitable rights, and use a command
like this (ensure the tablespace name is correct):
"db2 alter tablespace flint no file system caching"
This just prevents double-buffering, which sometimes
is helpful.
I turned off the file system caching and it didn't make a difference.
>
Is APPEND MODE enabled for the table?
I don't see it in the ddl from dbcc.

This non-default setting will influence the speed of bulk
inserts and you would enable it as follows:
db2 "alter table Table-Name append on ".
I use this for bulk populating tables that are initially empty
and then disable it for regular oltp access.
You might not want that setting to be permanent,
unless you perform regular reorgs, because it will
use more disk space (it will always use new
pages instead of finding free space in existing
pages).
Making this change did make a difference of about 10% improvement.
>

I did notice that the tablespace has an overhead and a transferrate
setup in the definition. Can that effect performance of inserts of
objects in that tablespace?

I would not bother about those because I'm guessing they have
nothing to do with your symptom, instead I'm guessing that
if you made a test whereby you kept the database-connection
opened, and performed the inserts but only commit every
thousand rows (or some other high figure) , and disconnect
at the end , then you will see quite a different result.
This is based on the theory that the
frequent connect/disconnect has a relatively
high cost in db2 compared to the insert, and the
relatively high cost of frequent commits for bulk inserts.
It's more that DB2 may have a higher cost of connect/disconnect
compared to Oracle and SqlServer. ( I'm still hoping I have something
misconfigured on the DB2 side )

Jul 10 '06 #47

This discussion thread is closed

Replies have been disabled for this discussion.