Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: index creation taking longer in production than QA box

    Hi all,
    Any help is greatly appreciated. Environment is UNIX 7 running on p770's, DB2 V9.7.5.

    I wanted to move a table from the tablespace it was in to a new tablespace. So, I did the following steps.

    1. exported data from prod version of table into .del file(18.5million rows)
    2. dropped the table on QA box.
    3. recreated just the table in the new tablespace(no indexes)
    4. loaded from my .del file (took 4 minutes and change)
    5. created the 6 indexes on the table (took < 25 minutes(multiple runs))

    ran this several times testing it out to get estimated run times, ensure no issues, etc... Step 5 consistently took 25 minutes to complete. I schedule an hour outage in production to perform the same task. I ran over by a couple of hours. Step 5 took 3 hours to complete. Where in my QA environment this was taking the following average for each index:
    ix1 5 minutes 11 seconds
    ix2 4 minutes 42 seconds
    ix3 2 minutes 15 seconds
    ix4 5 minutes 29 seconds
    ix5 3 minutes 10 seconds
    ix6 5 minutes 36 seconds

    In prod took almost 30 minutes per index.

    I have compared the DB CFG in both environments there was one difference other than path names. In QA, (MON_UOW_DATA) = NONE whereas in prod I have (MON_UOW_DATA) = BASE. I don't see that being the cause...

    I compared the DBM CFG for both again I find one difference.
    QA: (SHEAPTHRES) = 20000
    ProdL (SHEAPTHRES) = 192000
    Again, I don't really see that as being cause. We are looking at DISK config and all now, but I would think I would have seen a big difference on the LOAD step if that were the cause. The load ran for 4 minutes and few seconds on both machines.

    Any ideas would be appreciated. Thanks.
    Dave

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Have you compared the complete DDL for this table between the environments (including tablespace(s) and bufferpool(s) , and detailed attributes of these objects ) ?

    Is it a range-partitioned-table? If range-partitioned on production, are the indexes partitioned or not?

    Is the ORGANIZE BY clause for the table identical on both environments?

    Is DPF involved?

    Is DB2-compression involved in either environment for this table/indexes?

    On production, were there any problems logged to db2 diagnostics file(s) during the index creation?

  3. #3
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Just curious, any reason for not using ADMIN_MOVE_TABLE?
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    hi, Dave:
    Are there differences of sortheap and bufferpool settings between the prod env and QA env?
    and also the performance of disks which the temporary tablespace and log files reside in should be checked.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Sorry been out of pocket since I posted this last week.

    @db2mor was the same DDL in both environments/ dropped/recreated/reloaded table. No DPF.

    @Andy Speed. I found in my testing that doing in the order I described above was the fastest way of making the move.

    @fengsun As I said above there is a difference in sortheap, but I don't think it would have contributed to Prod taking 30 additional minutes.
    QA: (SHEAPTHRES) = 20000
    ProdL (SHEAPTHRES) = 192000
    It looks like it may be environmental issues on the hardware. The system guys found a few differences and changed them, I don't have the specifics at the moment but now my index creations in the QA box are taking 10 minutes each, instead of the original 5 minutes each. I have to get back in touch with them today to see if we can account for the other 20 minutes of difference. Then put QA back the way it was and schedule those changes for my prod box.
    Dave

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    you can try to analyze time spent with MON_FORMAT_XML_TIMES_BY_ROW function.
    To analyze sort problems you can try the following during index creation:
    db2pd -sort apphdl=...
    Regards,
    Mark.

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    Hi, dave
    It looks like there is a much more heavy sort overflow ( data spilled to disk) in prod env than in QA env。
    you can use db2top to compare temporary table size between two env when indexes are creating.

Posting Permissions

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