Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Question Unanswered: Why does DB2 performance worse as application accesses to it with multi thread?

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    14
    IBMer suggested me that don't use DB2's Performance Advisor Wizard,

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Why does DB2 performance worse as application accesses to it with multi thread?

    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.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Oct 2003
    Posts
    87

    Re: Why does DB2 performance worse as application accesses to it with multi thread?

    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.
    Oracle - DB2 - MS Access -

  5. #5
    Join Date
    Dec 2003
    Posts
    5

    UOW waiting

    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?

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: UOW waiting

    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.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Dec 2003
    Location
    Johannesburg, South Africa
    Posts
    18

    Lightbulb DB2 Information

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

  8. #8
    Join Date
    Nov 2003
    Posts
    14

    Re: DB2 Information

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

    lock size can influence,but a little

  9. #9
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Increase Parameters

    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.
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2003
    Posts
    191

    Re: UOW waiting

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •