DB2 V8.1 FP4 JDBC-Performance-Issue

[joerg]

Hello,
we reached a quite interesting performance-limit while using JDBC type
4 to a DB2 V8.1 database. The DB2 is running on a Intel-based single
CPU-Server with Linux OS.
What we are doing is an OO-loading-service with about 30 single
select-statements per complex-object. Every statement will be prepared
right before execution and will be close right after as well as the
resultset. All data is accessed by a fully indexed column or in most
cases by a primary key.
In order to optimize the throughput we implemented several scenarios,
every time the base is 10000 complex-objects.

1. scenario

sequential load of complex-objects - result: aprox. 1000
complex-objects per min

2. multi-threaded load

using 5 threads - result: aprox. 1100 complex-objects per min
(overall)
using 10 threads - result: aprox. 1100 complex-objects per min
(overall)

It seems that something is serializing the requests (no synchronized
methods in the code), so that in the end the max.-perfomance is about
1100 complex-objects per min, although the server is not at its
ressource-limit.
The DB2-processes share about 35% CPU in every scenario, e.g. 10
threads -> 3.5% per DB2-process / 5 threads -> 7% per DB2-process.

Has anyone an idea were the bottleneck could be found, is there a
parameter to define the max parallel executions in DB2 ?

Joerg

[Sean McKeough]

Do you have a seperate context for each application? If not, all threads
will be serialized on the single db conneciton. If you're multi context,
look at the server using application snapshots to see if you're locking
your other threads out...

joerg wrote:

Hello,
we reached a quite interesting performance-limit while using JDBC type
4 to a DB2 V8.1 database. The DB2 is running on a Intel-based single
CPU-Server with Linux OS.
What we are doing is an OO-loading-service with about 30 single
select-statements per complex-object. Every statement will be prepared
right before execution and will be close right after as well as the
resultset. All data is accessed by a fully indexed column or in most
cases by a primary key.
In order to optimize the throughput we implemented several scenarios,
every time the base is 10000 complex-objects.

1. scenario

sequential load of complex-objects - result: aprox. 1000
complex-objects per min

2. multi-threaded load

using 5 threads - result: aprox. 1100 complex-objects per min
(overall)
using 10 threads - result: aprox. 1100 complex-objects per min
(overall)

It seems that something is serializing the requests (no synchronized
methods in the code), so that in the end the max.-perfomance is about
1100 complex-objects per min, although the server is not at its
ressource-limit.
The DB2-processes share about 35% CPU in every scenario, e.g. 10
threads -> 3.5% per DB2-process / 5 threads -> 7% per DB2-process.

Has anyone an idea were the bottleneck could be found, is there a
parameter to define the max parallel executions in DB2 ?

Joerg

[joerg]

Hello,
yes, each thread runs withn its own connection. The database snapshots
did not show any severe problem, except the victim-rate in the
bufferpool-snapshot is bit high about 4%.

Thanks

Joerg