Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Nov 2010
    Posts
    99

    Unanswered: Slowing response on increasing workload

    I have an after insert trigger on a table that calls a stored procedure that inserts records into a table. If I run a simple SQL statement to insert say 100K rows the response time is around 400 records per second. However if I increase this to 200K-1M then the response time gradually slows down to around 50-21 records per second.

    It seems it is able to rip through the first 100K then gradual slowdown.

    I run the insert with +c and explicitly execute the commit at the end. The memory, CPU, logs, file system are not heavily impacted. Also I don't see any output from the diag log - looking for adjusting bufferpools or heapsizes, etc. There are no victim pages or steals that I can see.

    I thought this would be easy to fix but I can't seem to figure out the bottleneck.

    Thanks in advance,

    James

  2. #2
    Join Date
    Nov 2010
    Posts
    99
    FYI I am running DB2 AESE LUW 9.7 fp5 LINUX RE 5.3

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Have you tried using db2top to narrow the search? How are the disk i/o during the inserts?

    Andy

  4. #4
    Join Date
    Nov 2010
    Posts
    99
    Yeah I was looking at db2top for a few stats. I am using 98% of the system i/o but there is not much i/o wait , maybe 5% tops (top).

    I think this may have to do with other traffic on the system, because now I can't get the response time over 30 records per second no matter how many or few records I insert. This and when I rerun the same query I get different response times - earlier was 150 per second now just 33 per second.

    I am surprised that the response time can vary so wildly from 400+ per second down to 30+ per second.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Are you doing the inserts locally or remotely from the DB2 server? If remotely, then the network can be the problem. If the server is not dedicated to DB2, then other activity can affect performance.

    Andy

  6. #6
    Join Date
    Nov 2010
    Posts
    99
    Inserts are directly on the db server

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What else is running on the server?

    Andy

  8. #8
    Join Date
    Nov 2010
    Posts
    99
    A COGNOS user, but they are off now. Just me. The head-scratching continues because now I am rerunning, and the response times have slowed to around 10 per sec. with no one else on the system...

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Check you lock list, lock escalations, and lock waits. You are doing a lot of inserts within a single UOW and it may be a locking issue. Try looking there.

    Andy

  10. #10
    Join Date
    Nov 2010
    Posts
    99
    There are no lock escalations. LOCKLIST is set to AUTOMATIC. I commit after every 10K rows.

  11. #11
    Join Date
    Nov 2010
    Posts
    99
    db2pd -locks wait -db<my db> - comes up blank

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Post a couple of outputs from MONREPORT.DBSUMMARY, produced at the beginning and at the end of your test run.
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Things that I can think of that will slow down inserts are:

    1) lots of indexes
    2) Foreign keys

    Maybe take a look at the access plan for the insert and see if anything jumps at you.

    You can also explore APPEND mode for the table.

    Andy

  14. #14
    Join Date
    Nov 2010
    Posts
    99
    Thanks. I already had APPEND mode applied for the tables involved in the transaction, and that did help the performance a lot. I understand the indexing tax but the issue I can't seems to understand is one moment today it's processing at 400+ per second, then 100+ , now down to 17 per sec. I have maybe inserted 1M records in the last 24 hours. I should be inserting 10M+ records per day. It's pretty frustrating as you can imagine .

  15. #15
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Does the process just keep getting slower, or does it pick speed sometimes?

    How are you inserting the data?

    Andy

Posting Permissions

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