Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Aug 2010
    Posts
    18

    Angry Unanswered: Backup performance 9.5

    Version running is DB2 9.5 Workgroup edition

    I have a database in production and a database in development. The database in development is actually about 6 months old and was a copy of production. The database in development is running on a vm, running x64 windows 2003 with 6gb of memory. The production database is windows 2003 x64 has 20 gb of memory, is a physical box. Each box is connected to an emc nas, each box has four volumes/ four seperate file systems in which the database is configured to backup to. The issue I have is the 200GB database will backup in about 2 hours in development and takes almost 7 hours in production. I've gone over disk config and everything with a fine tooth comb and cant of the life of me understand why backups are taking so long in production. The last config i used for he backup command based on the config from the gui backup setup was:

    backup database pms3 to "f:\","g:\","h:\","i:\" with 10 buffers buffer 1024 parallelism 4

    The backup starts off like gangbusters in production almost 10% complete after first 10 minutes, and seems to eventually hit a wall and starts to crawl. Not sure else is going on disk wise across entire nas, and just cant figure out if it could be something with the structure of the database itself? any thoughts you might have would be great. like i said, the weird thing is the performance on the vm thats running a copy of the db from 5 months ago seems to have decent backup performance. processor on production database is doing nothing, lots of memory available. i understand some suggest not applying any settings while issuing the command to kickoff backup, but have tried several different ways with similar results, including not issue any extra commands.

    I've also copied out the config from the db to diplay:
    "Applications","AVG_APPLS","AU - Anonymous - W4g7tATC - Pastebin.com

    one last thing to share, is i did a dump of the config parms on each box and then compraed the two
    left side is production(slow backup) right side is dev(fast backup)
    http://imagebin.ca/img/3xN2Rp.png

    I'm not really a db2 admin, just trying to fill the role since our vendor cant
    Last edited by duhaas; 10-05-10 at 10:35. Reason: added image

  2. #2
    Join Date
    Jan 2010
    Posts
    335
    The Parameters in the Image show that the Parameters are set to automatic, so during backup you might have other values.

    Do the Backup-Images have the same size for DEV and Prod? If the DB uses DMS-Tablespaces, the 200 GB might be preallocated, but nearly empty on dev.

    First i'ld remove the 'with 10 buffers buffer 1024 parallelism 4'. DB2 will now compute values for the buffers and parallelism. If you are IO-bound, then you might want to use compression for backups:
    backup database pms3 to "f:\","g:\","h:\","i:\" compress

    Are any other IO-intensive Jobs on the Storage during the backup?

  3. #3
    Join Date
    Aug 2010
    Posts
    18
    thanks for following up, looking into possibly IO contention during the same time. Will also try your suggestion of running the backup with no parameters. Not sure i follow your comment about the automatic settings in the diff file image i show? thanks again for your help

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You may also want to compare the following between prod and dev:
    util_heap_sz
    num_ioservers
    bufferpool sizes
    HWM (high-water mark) for DMS tablespaces. db2 will backup pages up to the HWM


    Check if there is any lock contention while the backup is executing. You can also test how long it takes to backup to /dev/null

  5. #5
    Join Date
    Aug 2010
    Posts
    18
    What's quickest way to find those settings? Sorry, like I said a bit new tondb2

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    db2 get db cfg for <db name>
    db2pd -d <db name> -buf
    db2pd -d <db name> -tab

  7. #7
    Join Date
    Aug 2010
    Posts
    18
    so I have the following

    for db2pd -d <db name> -buf
    http://imagebin.ca/img/BEuaz2iR.png
    for db2pd -d <db name> -tab
    http://imagebin.ca/img/QZUxK3eX.png

    UTIL heap size is 524288
    num_ioservers is automatic

    any help or insight you can provide on those settingst would be great. like i said, db util is super low when backup runs and the amount of memory used is never about 3-4GB with 20gb avail

  8. #8
    Join Date
    Jan 2010
    Posts
    335
    Hi,

    backup is an IO Task. The DB reads a set of Pages and flushes them into the backup file.
    You don't need much Cache for these Actions.

    Is DB2_PARALLEL_IO set?
    >db2set -all | grep DB2_PARALLEL_IO
    [i] DB2_PARALLEL_IO=*

    Is there one large table consuming nearly all the disk-space?

  9. #9
    Join Date
    Aug 2010
    Posts
    18
    that parm is not set, and not sure best way to asses which tables consume x amount of space??

  10. #10
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Take few snapshot of the memory / cpu usage of your WINDOWS box during the time the backup is running. RESET your monitors, and take few database SNAPSHOTS as well during the time the backup is running.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  11. #11
    Join Date
    Jan 2010
    Posts
    335
    You should set DB2_PARALLEL_IO. Also check the Documentation for this Parameter, you maybe could fine-tune it. Setting the Parameter requires a restart of the Instance.

    Quote Originally Posted by duhaas View Post
    that parm is not set, and not sure best way to asses which tables consume x amount of space??
    Is there a single huge file in e:\db2\node0000\sql00001\sqlt0002.0\?
    If your statistics are up to date, you could also check if there is a table with a significant higher card than the other tables.

  12. #12
    Join Date
    Aug 2010
    Posts
    18

    her. chipsty

    there are several large files, largest being 42GB/24GB/16GB/15GB/15GB/9GB/8GB and so on, thoughts??

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by duhaas View Post
    so I have the following

    for db2pd -d <db name> -buf
    http://imagebin.ca/img/BEuaz2iR.png
    for db2pd -d <db name> -tab
    http://imagebin.ca/img/QZUxK3eX.png

    UTIL heap size is 524288
    num_ioservers is automatic

    any help or insight you can provide on those settingst would be great. like i said, db util is super low when backup runs and the amount of memory used is never about 3-4GB with 20gb avail

    Is this info from slow or fast server? Your default bufferpool is ~2GB, userspace1 is ~175GB, You said you have 20GB of RAM in prod. Why don't you increase this bufferpool (the last column "Automatic" is missing in the db2pd -buf output). If it's set to automatic, try hard-coding it to some higher value (let's say 10GB) and test the backup.

    You should also test how long it takes to backup to NUL (for Windows).

  14. #14
    Join Date
    Aug 2010
    Posts
    18
    sorry, this was coming from the slow backup server, the physical box with lots of memory/cpu. i'm not getting a column automatic in the db2pd -d pms3 -buf and thanks for spending the time to help
    Last edited by duhaas; 10-06-10 at 14:08.

  15. #15
    Join Date
    Aug 2010
    Posts
    18
    also, the idea of backuping up to null is just to take the disk out of the picture is that accurate?

Posting Permissions

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