Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: DB2 running very slow

    Hi all,
    I have a DB2 ESE 9.7.4 on Windows running very slow.
    Task manager shows around 75% utilization for both CPU and Disk.
    It's making lots of INSERT operations since there's just one application reading from another DB and writing into it.
    I already stopped once the system to increment dbheap, logfilsz, logbufsz and buffer pool size.

    Which are the first 3 commands that you would run to see the cause of the problem?

    Thanks!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Robert1973 View Post
    Hi all,
    I have a DB2 ESE 9.7.4 on Windows running very slow.
    Task manager shows around 75% utilization for both CPU and Disk.
    It's making lots of INSERT operations since there's just one application reading from another DB and writing into it.
    I already stopped once the system to increment dbheap, logfilsz, logbufsz and buffer pool size.

    Which are the first 3 commands that you would run to see the cause of the problem?

    Thanks!
    1. Hire
    2. A
    3. DB2 DBA
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In general, it is not recommended to try something blindly without monitor/measure detail of system behavior.

    But, I want to doubt first index page splitting, in case of lots of INSERT operations.

    It may be worth to consider enough PCTFREE and LEVEL2 PCTFREE, before reorganization.

    Another option worth to consider for index splitting might be PAGE SPLIT HIGH, if inserted key(of the index) was ascending.

    Also, please see my descriptions in this thread.
    http://www.dbforums.com/db2/1666646-...ert-query.html
    Last edited by tonkuma; 06-20-12 at 21:04. Reason: Add link

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You should read and learn on basic database system tuning techniques in general and for DB2 in particular. Aside from that, you could use the DB2 design advisor and the configuration advisor to get a reasonably good starting point for your system.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    You might consider unloading the "input" data, reformatting for the target, and do a bulk load rather than individual inserts.

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Are both source and target database on the same Windows server machine?

    Are the containers /storage pools for both databases sharing the same physical disks?

    Are the tablespaces on single-containers/pools or multiple?

    Are the active transaction logs using different disks than the containers/storage-pools?

    What you you mean "very slow" - give the insert-rate-per-second (and avgrowlen), and your logbufsiz, logfilsiz.

    If you are using "insert into..select ..from..WHERE...", have you checked the access-plan?

    Give your disk(s) setup - how many controllers/hba, local or remote disks etc.
    Give the #RAM size and number of cores.

    If this is a regular job to copy between databases, why are you using INSERT (instead of load-from-cursor)?

  7. #7
    Join Date
    Apr 2008
    Posts
    39
    Not sure what is your applciation code or what is business logic behind the
    database inserts, you may want to use LOAD instead of inserts. Give more
    info of your issue that will help us in understaing your problem

    Thanks
    Naval K

  8. #8
    Join Date
    Mar 2012
    Posts
    120
    Thank you all for your precious suggestions.
    I'm trying to fix the possible index page splitting, like Tonkuma suggested, and thus I removed indexes for the currently processed table to see if things go better.
    Some of you asked why I didn't use LOAD, this because the source server is not DB2, so my application reads data from there and performs bare inserts on DB2. It's a one shot operation (initialization) that is performing very bad.
    I cannot switch to Federation for reasons that don't depend on me.
    The source server is on a remote Windows machine that doesn't show much overload (CPU and disk usage is under 50%), and at the beginning of the initialization the insertion rate in DB2 was higher, then things got worse....
    (now I have insertion rate of 100.000 records a day)

  9. #9
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Can you (or someone) implement a process on the source server to format sequential records for a subsequent load on the target?

    How many keys/indexes are there on the target? You might run a test with a target with only the primary key and after the data is inserted, then build all of the other index entries if there are several/many.

  10. #10
    Join Date
    Mar 2012
    Posts
    120
    @papadi
    there are just primary keys on the target, and I removed them.
    Why did you say to keep the primary keys?

    Do you think I may add cluster index (now or when the initialization is finished ) and run a reorg, that will speed further inserts?

  11. #11
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Why did you say to keep the primary keys?
    Long-time habit.

    I would pursue getting the data from the source in "loadable" a format. A bulk load will perform exponentially better than the inserts. The more data loaded, the more significant the performance difference.

  12. #12
    Join Date
    Nov 2011
    Posts
    334
    1、 you should make sure the problem is on db2 but not on the remote server.
    2、plz publish the db snapshot, application snapshot here (you need turn on the monitor switch first)
    3、"beginning of the initialization the insertion rate in DB2 was higher, then things got worse.."
    from this , i think maybe you can try :db2set DB2_USE_ALTERNATE_PAGE_CLEANING=on
    4、Are you commit for each insert ? that is very inefficiant ( because each commit will trigger a synchronize logbuffer written ), plz consider to use batch insert(insert serveral rows , commit once )。

Posting Permissions

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