Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    Unanswered: backup performance issue.

    Hello all,

    I am using db2 10.1 on RHEL and another DB2 10.5 on AIX.

    I am trying to increase my backup performance considering various factors at a time like:

    buffer,parallelism, UTILITY_HEAP, UTIL_IMPACT_PRIORITY.

    I know that buffer configuration (page and buffer size) is configured somewhere in kernel file.

    1. Please help me to find out that file and crosscheck the config. of Buffer in OS.

    2. Please also share any ideas to increase backup performance in terms of time taken.

    Current scenario:

    command : Full offline backup:

    db2 backup db testdb to /opt/dbbkp with 2 buffers parallelism 1

    UTILITY_HEAP=284666 (4 K page)

    Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10 /* i ignore it as it is offline backup */

    Total backup image size = 49.3 G
    Time taken : 7min 48 s

    OS:

    cpu=2
    RAM = 32G
    H/W IBM Power 740 with AIX 7

    thanks in advance.

    Ssumit
    ssumit

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    some heaps can be set to automatic or default
    util_impact_lim : I ignore it ....
    the reason ? why ignore ??
    remove buffers and paral... and let system decide for you..
    most of the time they do better than your guess
    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

  3. #3
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

    Ignoring this because backup is offline and no other application / utility is running at backup time..

    Do you have any idea about:

    I know that buffer configuration (page and buffer size) is configured somewhere in kernel file.

    1. Please help me to find out that file and crosscheck the config. of Buffer in OS.

    thanks in advance

    ssumit
    ssumit

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    according the doc
    WITH num-buffers BUFFERS
    The number of buffers to be used. If the number of buffers that you specify is not enough to create a successful backup, then the minimum value necessary to complete the backup is automatically chosen for this parameter. If you are backing up to multiple locations, you can specify a larger number of buffers to improve performance. If you specify the COMPRESS parameter, to help improve performance, you can add an extra buffer for each table space that you specify for the PARALLELISM parameter.
    BUFFER buffer-size
    The size, in 4 KB pages, of the buffer used when building the backup image. DB2 will automatically choose an optimal value for this parameter unless you explicitly enter a value. The minimum value for this parameter is 8 pages.
    If using tape with variable block size, reduce the buffer size to within the range that the tape device supports. Otherwise, the backup operation might succeed, but the resulting image might not be recoverable.
    With most versions of Linux, using the default buffer size included with DB2 for backup operations to a SCSI tape device results in error SQL2025N, reason code 75. To prevent the overflow of Linux internal SCSI buffers, use this formula:

    bufferpages <= ST_MAX_BUFFERS * ST_BUFFER_BLOCKS / 4

    where bufferpages is the value you want to use with the BUFFER parameter, and ST_MAX_BUFFERS and ST_BUFFER_BLOCKS are defined in the Linux kernel under the drivers/scsi directory.
    PARALLELISM n
    Determines the number of table spaces which can be read in parallel by the backup utility. DB2 will automatically choose an optimal value for this parameter unless you explicitly enter a value.

    I would not recommend to play with these settings if you don't know what you are doing - leave them to default
    util_impact .. if nothing else is running leave it to 100
    what is the setting of util priority ?
    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

  5. #5
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    its current setting is 10

    Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

    ssumit
    ssumit

  6. #6
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    I have not set UTIL_IMPACT_PRIORITY to any value.
    ssumit

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    UTIL_IMPACT_LIM is not relevant unless a specific utility is run with throttling.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks marcus and przytula_guy,

    I considered the factors you have mentioned.

    But a strange situation faced during backup

    CASE 1 : db2 10.1 on RHEL 5 CASE 2 : db2 10.5 on AIX 7

    both db have same size and architecture.

    same offline backup command give on both servers:


    db2 backup db testdb to /home/db2inst1/db2backup without prompting


    Diag log records:

    in case 1 :
    Autonomic backup/restore - using parallelism = 4.
    Autonomic backup - tuning enabled.
    Using buffer size = 769, number = 4.

    in case 2 :
    Autonomic backup/restore - using parallelism = 1.
    Autonomic backup - tuning enabled.
    Using buffer size = 1024, number = 2.

    I understand the difference in buffers will be due to different OS and memory confifuration.

    BUT WHY PARALLELISM WAS NOT SET TO 4 by DB2 IN BOTH THE CASES ?


    Any clues please.


    thanks in advance

    ssumit
    ssumit

  9. #9
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    db2 takes the #cores and #ram into account (among other things).. did you?
    RHEL is on x64 ? virtualised also? AIX 7 on Power 6/7/8 - lpar also? Difference in #cores?

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    To optimize backup parallelism, use multiple backup paths (even if on the same physical device. I would use at least 4 paths. Obviously it would be best if they were separate physical devices, but that may not be possible in your environment. Also, having more than a couple of tablespaces helps with parallelism.

    As mentioned above, let DB2 chose the parallelism and buffer options for you.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    thanks marcus and db2mor,

    I have review the backup performance in parallelism 1 versus parallelism 4. But in Parrl. 1 Time is 7 min 48 sec and in Parrl. 4 it is 6 min 55 sec.

    Not i am trying to optimizing Buffers . I agree with all that "let Db2 decide the buffer, but our explicit need is to bring down the backup as time as min as we can.

    Backup time is in night and no other app load is there on server. Now...

    my OS version is:
    [db2insts@mstcho scsi]$ cat /proc/version
    Linux version 2.6.32-358.el6.i686 (mockbuild@x86-022.build.eng.bos.redhat.com) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-3) (GCC) ) #1 SMP Tue Jan 29 11:48:01 EST 2013

    I want to know the values of ST_MAX_BUFFERS and ST_BUFFER_BLOCKS which are defined in the Linux kernel under the drivers/scsi.
    I reached to directory "/lib/modules/2.6.32-358.el6.i686/kernel/drivers/scsi"

    but i am not able to find in which file it is defined

    Do any bode has an idea ?

    Thanks.
    ssumit

  12. #12
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    What is your target backup time in seconds , or your target throughput (gigabytes per second) ?

    (you quoted less than 8 minutes to backup 50GB offline to $INSTHOME , but what is your target time ?)

    Have you measured the throughput (e.g. with dd) delivered by your RHEL environment *reading* 50GB of data ?

    Have you measured the actual throughput *writing* 50GB of data to your target file system(s) for the backup?

    Have you timed compressing the db2-backup ( time db2 backup db testdb to /home/db2inst1/db2backup compress without prompting ) ?

    Are you backing-up to the same physical-resources from which you are reading? Do you understand the consequences?

    How virtualised is your i/o?

    With a 2-core RHEL 32GB RAM partition and a tiny database, it is a bit nonsensical to delve into the low level settings: because snails cannot run.

    In simple cases like that, my experience has been that util_heap_sz is significant, as is compressing the backup, but most effective is ensuring that the backup-image is written to different physical resources than the tablespace-containers where that is feasible.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Did you try multiple backup paths (even if on the same physical device)?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks db2mor and marcus,,

    i will relate and try your recommendations..

    Can You tell me what is the recommendation of IBM about no. of buffers to use while backup and what is the upper limit of no. of buffers we can use ?

    thanks in advance
    ssumit

  15. #15
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the doc does not indicate max buffers that can be specified
    IBM always recommends to use automatic - non specified...
    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
  •