[AlmostCrazy]

Hello Guys,

I profile my application with JProbe. I observe that as I config my application to use one thread to access to DB2, 670 transactions get commited within 6890 ms. As I configure the application with two threads to access to DB2, I just get 532 transactions commited within 36588 ms. Even after I run DB2's Performance Advisor Wizard, there is not obvious improvement. Is there anyone have idea? Here is some infomation:

OS: Red Hat Linux release 7.3
DB2: DB2 V8.1.0.0 without patch


Cheers!

Stephen

[richardluopeng]

IBMer suggested me that don't use DB2's Performance Advisor Wizard,

[n_i]

Originally posted by AlmostCrazy

I profile my application with JProbe. I observe that as I config my application to use one thread to access to DB2, 670 transactions get commited within 6890 ms. As I configure the application with two threads to access to DB2, I just get 532 transactions commited within 36588 ms. Even after I run DB2's Performance Advisor Wizard, there is not obvious improvement. Is there anyone have idea? Here is some infomation:

An application has to be designed specifically for concurrent access to the database in order to benefit from multithreaded execution. When you increase the number of threads they all attempt to access the same tables and even possibly the same rows, therefore introducing lock waits. I would start with looking at the application tuning rather than database. Look at isolation levels that your statements use, see if 'SELECT... FOR READ ONLY' helps. Check to see if the transactions are unnecessarily long.

[N-ary]

Originally posted by AlmostCrazy
Hello Guys,

I profile my application with JProbe. I observe that as I config my application to use one thread to access to DB2, 670 transactions get commited within 6890 ms. As I configure the application with two threads to access to DB2, I just get 532 transactions commited within 36588 ms. Even after I run DB2's Performance Advisor Wizard, there is not obvious improvement. Is there anyone have idea? Here is some infomation:

OS: Red Hat Linux release 7.3
DB2: DB2 V8.1.0.0 without patch


Cheers!

Stephen

An application has to be designed with multi-threading in mind, as does the table and index definitions to support it. Check commit scope. Do different functions/paths through the app force contention on certain tables/indexes.

[AlmostCrazy]

Actually I take several snapshot, and there is not any connection that is in "Lock Waiting" state, but lots of connection are in "UOW waiting" states. I am not sure what they are waiting for?

[n_i]

Originally posted by AlmostCrazy
Actually I take several snapshot, and there is not any connection that is in "Lock Waiting" state, but lots of connection are in "UOW waiting" states. I am not sure what they are waiting for?

"UOW waiting" means a transaction is in progress and has not committed yet.

[tupiex]

Check you Isolation level and also the lock size of your tablespace?

[richardluopeng]

Originally posted by tupiex
Check you Isolation level and also the lock size of your tablespace?

lock size can influence,but a little

[nitingm]

Hi,

Please go ahead and increase the LockList, and the Max Locks.

Please also remember that the MaxLocks is a percentage of the locklist that upon being reached causes row level locks to be escalated to table level locks. So the balance is a bit tricky.

Please do a reading on a document by Wilkins on Locking. I've attached it here.
Regards

Nitin.

[jsander]

Hi,

not quite, it means the database is idle waiting for the application to proceed.

If UOW waiting is the dominant state of the connections, the threads are just not doing much.

In other words, UOW waiting is an indication that the application may have performance problems with its threads.

Johann

Originally posted by n_i
"UOW waiting" means a transaction is in progress and has not committed yet.