473,703 Members | 2,393 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Slow insert/logging performance

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
46 13134
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_ALTERNA TE_PAGE_CLEANIN G=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


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,prefe tchsize,transfe rrate for the tablespace matter
?
* db2set DB2_USE_ALTERNA TE_PAGE_CLEANIN G=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_P AGE_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


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,prefe tchsize,transfe rrate for the tablespace matter
?
* db2set DB2_USE_ALTERNA TE_PAGE_CLEANIN G=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_P AGE_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
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_prefe tch=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
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
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.