Topic
  • 5 replies
  • Latest Post - ‏2012-05-28T15:57:50Z by SystemAdmin
Rakeshtechie
Rakeshtechie
5 Posts

Pinned topic db2 backup to multiple drives on z/linux environment

‏2012-04-04T11:56:53Z |
Hi

We are currently on a z/Linux machine with DB2 running. Currently we are taking the backup using
Code:

db2 backup db <databasename> to <location> PARALLELISM 10 compress WITHOUT PROMPTING

our database is huge and the above script is taking about 180 to 190 mins to backup. We found some solution to backup to
multiple drives instead of one to enhance the speed of operation.

Could anyone please explain or eloborate how to take make the backup to multiple drives on linux machine.

What other tuning can be done to make it fast.

Any help is much appreciated

thanks
  • mor
    mor
    577 Posts

    Re: db2 backup to multiple drives on z/linux environment

    ‏2012-04-04T14:56:33Z  
    Examine your UTIL_HEAP_SZ , make it as large as you can afford within your constraints and then
    measure the impact of the following options (they are available on LUW but I do not know if they are available on the Z/linux platform):

    db2 backup .... WITH XXX BUFFERS BUFFER YYYYY PARALLELISM ZZ compress UTIL_IMPACT_PRIORITY www

    where XXX is a integer number of buffers, each of which is YYYY 4KB-pages large
    and ZZ is the number of tablespaces it should process concurrently
    and WWW is a number between 1 and 100 with 100 being the highest priority.

    You need to experiment in your environment to determine the values of XXX, YYYY, ZZ and WWW that work best in your context and your constraints.

    If backing up to disk (not tape), For fastest speeds of backup, try to ensure that the target location for the backup-file lives on different file-systems (preferably different physical resources) from the database-containers themselves. Additionally when backing up to disk, it helps if you specify multiple output directories (each of which is on a different filesystem, preferably with different sets of underlying spindles). However, you must then manage the multiple files that the backup comprises (one file per output directory specified
    on the command-line to Backup, per database).

    If your database is physically partitioned(dpf) different considerations apply.

    Your sysadmin will show you how to find the bottleneck: is it on the READ side, or on the WRITE side, or both.
  • Rakeshtechie
    Rakeshtechie
    5 Posts

    Re: db2 backup to multiple drives on z/linux environment

    ‏2012-04-06T06:22:22Z  
    • mor
    • ‏2012-04-04T14:56:33Z
    Examine your UTIL_HEAP_SZ , make it as large as you can afford within your constraints and then
    measure the impact of the following options (they are available on LUW but I do not know if they are available on the Z/linux platform):

    db2 backup .... WITH XXX BUFFERS BUFFER YYYYY PARALLELISM ZZ compress UTIL_IMPACT_PRIORITY www

    where XXX is a integer number of buffers, each of which is YYYY 4KB-pages large
    and ZZ is the number of tablespaces it should process concurrently
    and WWW is a number between 1 and 100 with 100 being the highest priority.

    You need to experiment in your environment to determine the values of XXX, YYYY, ZZ and WWW that work best in your context and your constraints.

    If backing up to disk (not tape), For fastest speeds of backup, try to ensure that the target location for the backup-file lives on different file-systems (preferably different physical resources) from the database-containers themselves. Additionally when backing up to disk, it helps if you specify multiple output directories (each of which is on a different filesystem, preferably with different sets of underlying spindles). However, you must then manage the multiple files that the backup comprises (one file per output directory specified
    on the command-line to Backup, per database).

    If your database is physically partitioned(dpf) different considerations apply.

    Your sysadmin will show you how to find the bottleneck: is it on the READ side, or on the WRITE side, or both.
    Thanks mor. It gives a lot of info

    We are using DB2 v9.7.0.4 with Fixpack 4. Does this product has any recommended settings or like any issues like bugs or fixes ?

    Thanks alot once again
  • Rakeshtechie
    Rakeshtechie
    5 Posts

    Re: db2 backup to multiple drives on z/linux environment

    ‏2012-05-23T05:53:55Z  
    Thanks mor. It gives a lot of info

    We are using DB2 v9.7.0.4 with Fixpack 4. Does this product has any recommended settings or like any issues like bugs or fixes ?

    Thanks alot once again
    Thanks mor

    Currently the below setting showed good. Initially it was around 180 mins now its 71 mins.

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

    The time its taking as below

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

    Does the DB2 spends most of the time in waiting or blocking , is this a normal scenario ?

    The size of the files after backup is 81 GB. ( 27,27,27)

    Db2 taking 71mins for 81 GB backup is a good time ? is there any benchmarking available( of course it depends on hardware as well)
  • mor
    mor
    577 Posts

    Re: db2 backup to multiple drives on z/linux environment

    ‏2012-05-23T06:31:42Z  
    Thanks mor

    Currently the below setting showed good. Initially it was around 180 mins now its 71 mins.

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

    The time its taking as below

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

    Does the DB2 spends most of the time in waiting or blocking , is this a normal scenario ?

    The size of the files after backup is 81 GB. ( 27,27,27)

    Db2 taking 71mins for 81 GB backup is a good time ? is there any benchmarking available( of course it depends on hardware as well)
    near duplicate of recent dbforums post
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: db2 backup to multiple drives on z/linux environment

    ‏2012-05-28T15:57:50Z  
    Thanks mor

    Currently the below setting showed good. Initially it was around 180 mins now its 71 mins.

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

    The time its taking as below

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

    Does the DB2 spends most of the time in waiting or blocking , is this a normal scenario ?

    The size of the files after backup is 81 GB. ( 27,27,27)

    Db2 taking 71mins for 81 GB backup is a good time ? is there any benchmarking available( of course it depends on hardware as well)
    Unless you're doing so for a specific reason, the typical best-practice is to not specify the WITH <x> BUFFERS, BUFFER <y>, or PARALLELISM <z> parameters, and instead allow DB2's own tuning algorithms to optimize those. The only parameter that ought to be interesting, that you could and probably should control is the size of the utilities memory heap -- UTIL_HEAP_SZ.

    Do you know how long backup takes if you do not COMPRESS it? Compression can be very CPU intensive and may be a factor here.

    Aside from that you should probably start looking at some lock snapshots while the BACKUP is running, to see if some other DB operation might be holding a lock that interferes with your BACKUP performance.

    Thanks
    Kelly Rodger