Results 1 to 10 of 10
  1. #1
    Join Date
    May 2011
    Posts
    5

    Unanswered: Confusingly slow simple insert query

    Hi, I've been facing an issue in db2 for some time that has me very confused. We have a large-ish table of around 1.2 million records. Inserts to this table are sometimes quick, on the order of 10ms, and sometimes extremely slow, on the order of 2-5 full seconds. The table has a primary key, several foreign keys, one index on one of the foreign keys, and one check values constraint.

    When queries are slow, they will be slow for quite some time. The server itself is mainly idle, certainly not heavily loaded. Other queries do not seem to show such painfully slow behaviour. We're using DB2 v9.5.

    I'm at a loss as to what could be causing the issue.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The server itself is mainly idle, certainly not heavily loaded. Other queries do not seem to show such painfully slow behaviour.
    Inserts to this table are sometimes quick, ..., and sometimes extremely slow, ...
    When queries are slow, they will be slow for quite some time.
    From these descriptions, the first isuue that came in my mind was "index splitting".
    But, the difference was too big(sometimes extremely slow, on the order of 2-5 full seconds.).
    So, I'm not sure.

    It may be worth to try to ajust PCTFREE(and LEVEL 2 PCTFREE in some environment)
    and to see the number of inserted rows between periods of getting slow.

    Those points may also be analyzed, if index splitting was in doubt.
    - Key distribution of inserted rows in the indexes.
    - Number of index leaf and higher level pages.
    - Muximum and average index entries per page.

  3. #3
    Join Date
    May 2011
    Posts
    5
    I would expect index splitting to incur a cost only on one query, though, whereas my situation is more like slow for one day, quick for a few days, slow for another couple of days.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Once index splitting occured, it is highly possible that index splitting would continue.

    Consider a case with PCTFREE 0.
    If roandom keys were inserted, most keys would be inserted into different leaf pages.
    So, almost every inserts would cause index splitting.
    Then after enough keys inserted, almost every index pages would be splittied,
    The splitted pages have enough spaces for new keys to be inserted.
    Later inserts wouldn't cause index splitting.

    But, if more keys were inserted, splittied pages would be gradualy filled up.
    Then repeat again first scenario.

  5. #5
    Join Date
    May 2011
    Posts
    5
    PCTFREE is listed as -1, which apparently causes the default to apply. I'm not sure what the default is, though.

    I've stepped up the reorg indexes schedule to run every night instead of weekly. We'll see if that helps at all.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Does this table have a clustering index?


    Here is how db2 looks for a page during INSERT:
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  7. #7
    Join Date
    May 2011
    Posts
    5
    It doesn't have a clustering index, no. It does have an ORGANIZE BY clause, on a column for which there are 3 potential values.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    That explaines data page search algorithm, not for the index pages.
    Also, PCTFREE menthioned in the article was for TABLE, not for INDEX.

    Although, some options for table may affect the performance of insert and/or load,
    it is not clear the relationship with the performance getting worse periodicaly.

  9. #9
    Join Date
    Mar 2011
    Posts
    3
    when i analyse ur query...i found that...it might that sometimes more users are connected to ur server or accessing the same table so that it will produce more i/o operations due to which it slows down....and another reason is ofcourse index splitting........it is recommended to make more indexes to improve query performance

  10. #10
    Join Date
    May 2011
    Posts
    5
    I didn't post a query, so I would certainly be impressed if you'd managed to analyse something. It's almost like you're just making things up.

Tags for this Thread

Posting Permissions

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