Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    6

    Unanswered: DB2 Backup time is too high

    Hi

    We are using DB2 v 9.7 fix pack 4 on z/Linux with OSuse 11, Patch level 1

    Currently we are using the below command to take the backups.

    time db2 backup db <dbname> to <path1>, <path1>, <path1> with 12 buffers buffer 33 parallelism 6 compress without prompting;

    Backup is going fine but the time taken is mentioned below

    real : 71m0.298s
    user : 0m0.025s
    sys : 0m0.025s

    I think the Db2 is taking most of the time in blocking/waiting for something which makes a drastic difference in real vs user or sys time ??

    Could anyone please help me in analyzing the problem why the Real time is lot more than user and system time ?

    Any help is much appreciated

    thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Your command-line suggests an OFFLINE backup.
    Probably you are waiting for I/O, either (or both) on the READS of the tablespaces or the WRITES to (path1, path2, path3) etc.

    Are the containers/storage-pools (for the database) are on the same file-system(s) that host path1,path2,path3 (target directories for the backup files)?

    What is your UTIL_HEAP_SZ?

    Why specify 12 buffers without also specifying a large buffer size ( i cannot understand a buffersize of 33)?

    How does the buffersize compare to the average extent-size of the large tablespaces?

    Did you time the basic backup without any of the parallellism/buffers/buffersize options to get a benchmark ?

    What is the size (in GB/TB) of the compressed backup files (sum of sizes of files in path1,path2,path3)?
    Last edited by db2mor; 05-22-12 at 11:29.

  3. #3
    Join Date
    Apr 2012
    Posts
    6
    Are the containers/storage-pools (for the database) are on the same file-system(s) that host path1,path2,path3 (target directories for the backup files)?
    Ans:
    Path1,Path1,Path1 (All three same locations), Earlier we tried different location on same file system but it didnt showed any improvement.

    Though its same path 3 times , its creating 3 different nodes with varying sizes

    What is your UTIL_HEAP_SZ?
    Ans:
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 73959
    I am also attaching all the cfg parameters, Please check the attachment

    Why specify 12 buffers without also specifying a large buffer size ( i cannot understand a buffersize of 33)?

    If its not 33 what will be the recommended buffer size for 12 buffers

    How does the buffersize compare to the average extent-size of the large tablespaces?

    -- Will get back on this
    Did you time the basic backup without any of the parallellism/buffers/buffersize options to get a benchmark ?

    The improvement was not marginal, it varied only few mins like 2 to 4

    What is the size (in GB/TB) of the compressed backup files (sum of sizes of files in path1,path2,path3)?

    Total size is 27+27+1.7GB, Its creating three files


    Thanks alot
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Keep in mind that I suspect you are waiting on I/O, so you can prove it...

    Can you confirm the total backup size is (27+27+1.7) = 55.7GB ?
    That seems to be around 785 megabytes per minute (55.7 / 71) .

    Time 'cp' of those backup files to other director(ies) on the same file system(s).

    Time 'cp' of those backup files to/from same file-systems used by the backup and its output.

    The idea is to measure your effective I/O speed, then compare to the DB2-backup speed. DB2 will be slower because it has more data to read (the uncompressed data), then compress it, then write.

    The docs suggest the buffersize (of 1 buffer) to be an even multiple of the extent-size (in pages).
    You may need a larger UTIL_HEAP_SZ to fit this if you have multiple buffers.

    Maybe wise to use the extra options (multiple-output-paths, with n buffers buffer xx parallellism z) only if you can *prove* significant benefits by doing that.

  5. #5
    Join Date
    Apr 2012
    Posts
    6
    I am sorry the Backup size was 27*3 = 81 GB , Not 55.7 GB

    So the speed is 1.140 GB per minute. ( 81 GB/71mins ).

    I have cp the the 81GB ( 3 files ) to other directory and it took around 50.50 seconds.. around 1.6 GB per minute

    Currently UTIL_HEAP_SZ = 73959 , How to determine the max value Or recommended value ?

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    So your db2-backup-to-disk gb/minute compares favourably with the 'cp' result.

    You don't appear to have a problem with the backup-to-disk times at all.


    For UTIL_HEAP_SZ study the Infocenter. Each site has different requirements, capabilities so there is no concept of a recommended value to suit everything.

Posting Permissions

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