Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2012
    Posts
    20

    Unanswered: Reorg table/index worst performance ever

    Hi!

    We are getting performance issues about reorging huge tables in our database.

    Details:

    • Db2 9.7
    • 4 TB database.
    • + 100GB Physical memory.
    • Nice disk write and read ratios
    • 20% CPU use.


    Last weekend after feeling ourselves unable to rebuild the biggest tables, we tried to reorg a 1GB table in a 2,3TB tablespace, it was working hard for 2 hours to reorg that simple table.

    It seems that there is a db2 bottleneck but we dont know how to find it.

    Could anyone help us? how can i improve directly the reorg performance? How can i know if there is any memory bottleneck?

    Thanks in advance for ur help.

    Revu.

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    what is the exact reorg syntax you used? How many rows in the table? Enough temp-space with the required pagesize?
    how about db2diag output? numer of logfiles? numer of indexes?
    Nice read/write ratios? And the individual disks? Not 1 getting all the I/O?

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    that's why partitioning is invented so one would not have to reorg/runstats entire huge table.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Jan 2012
    Posts
    20
    First of all:

    We have already tried to rebuild/reorg another 20 tables (between 1 GB and 100GB sized) and the performance has became as bad as the first one.

    That's a bit suspicious, it seems like it was a bottleneck during our reorg operations.

    ---

    what is the exact reorg syntax you used?
    offline reorg without using tempspace, using longlobdata option.

    All the sap applications were stopped while data was in movement.



    How many rows in the table?

    14KK

    Enough temp-space with the required pagesize?

    Yes. There were enough space but we havent used tempspace option

    how about db2diag output?

    Nothing

    numer of logfiles?

    Logretaint recovery, 256 logfiles, about 200GB of total log transaction capacity

    numer of indexes?

    3

    Nice read/write ratios?

    There were no finding reported during the last storage review (2 weeks ago).

    And the individual disks? Not 1 getting all the I/O?

    there are 7 different disks, 1 container per disk for each tablespace.Tablespaces are DMS.

    All the containers's i/o were similar.




    Thanks a lot for u quick answer=)

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    Do you reorg table offline? There are four phases in a offline table reorgnization.
    sort ,build ,replace , index_recreate .
    Have you used db2pd or other monitor tools to see the runing time for each phase?

  6. #6
    Join Date
    Jan 2012
    Posts
    20
    Quote Originally Posted by MarkhamDBA View Post
    that's why partitioning is invented so one would not have to reorg/runstats entire huge table.
    Im in agreement with you, but is impossible to fight against SAP developers...

  7. #7
    Join Date
    Jan 2012
    Posts
    20
    Quote Originally Posted by fengsun2 View Post
    Do you reorg table offline? There are four phases in a offline table reorgnization.
    sort ,build ,replace , index_recreate .
    Have you used db2pd or other monitor tools to see the runing time for each phase?
    Yes, we have db2pd'ed all the 4 phases.

    There were no difference between them, all of them were reeeeeally slow.

    Thx.

  8. #8
    Join Date
    Nov 2011
    Posts
    334
    and what the io capacity is (10M/s、50M/s or 100M/s ?) when the table reorgnization is runing...

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    longlobdata :Long field and LOB data are to be reorganized.
    this has nothing todo with/without tempspace as I can read
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Jan 2012
    Posts
    20
    Quote Originally Posted by Revu View Post
    Yes, we have db2pd'ed all the 4 phases.

    There were no difference between them, all of them were reeeeeally slow.

    Thx.
    Perhaps the "build" phase was the slowest one.

    If we split the time spent to reorg the table we could seen something like these

    sort 15%
    build 35%
    replace 20%
    index_recreate 30%

  11. #11
    Join Date
    Jan 2012
    Posts
    20
    Quote Originally Posted by przytula_guy View Post
    longlobdata :Long field and LOB data are to be reorganized.
    this has nothing todo with/without tempspace as I can read
    przytula_guy, are you saying that's impossible to reorg longlobdata without using tempspace, or are you suggesting that this could be the bottleneck?

    thanks a lot!!!

  12. #12
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the option is to force the reorg of long data (if any)
    for offline reorg the tempspace is always used, and the tbspace for this can be specified : use tablespacename
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  13. #13
    Join Date
    Jan 2012
    Posts
    20
    Thanks for your answer, but i dont understand you.

    Are you trying to say that we need to force the use of tempspace?

    Is there any bottleneck using the longlobdata without specifing the temporary tablespace?

    Is there a lot of wasted time moving the lobs?
    Last edited by Revu; 01-30-12 at 11:07.

  14. #14
    Join Date
    Jan 2012
    Posts
    20
    I just need to know any way of monitoring my database in order to find the bottleneck.

    Which are the memory pools affected by tables and indexes reorganizations?

    Is there any relationship between reorgs and prefetchsize? and util_heap_size? and num_io_servers?

    Thx for ur help.

  15. #15
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    how about reading some doc in
    REORG INDEXES/TABLE
    and developerswork ...
    instead of requesting "spoon feeding"
    try first to understand what db2 is doing and request specific questions instead of ..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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