Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Columbus, OH

    Unanswered: Exploring why a db2 backup runs so long

    Good morning DB2 friends! I'm looking for some insight into what all controls the time it takes to perform a database backup. I have a backup that's running longer than expected when I compare it to other backups. Both the daily online and the weekly offline run significantly longer than other slightly similar databases. I'm not just looking for an answer to speed it up, but more of a better understanding into what's in play that controls the backup time.

    I've attached a comparison of databases (the long running one versus 2 others that run in a reasonable time) The comparision details the database size, OS, version of DB, memory, backup times, info from dbm and db cfg and the backup statments (for online only)

    The attachment shows a 32 GB db taking nearly 3 hours to perform an online backup while in comparision a 80 GB db takes 30 minutes and a 24 GB db takes 16 mintues. Other than the db2 version difference (slow one is 9.5 while the other 2 are 9.7) most things appear to be similar. I'm sure I'm missing something though...that's why I'm posting

    My first attempt to speed up the backup was to bump up the UTIL_HEAP_SZ from 5000 to 7500. This had no affect at all, so now I'm taking a deeper look.

    Does anyone have thoughts on increasing the BACKBUFSZ or specifiying additional buffers in the backup command? If so, why wouldn't the other databases need the same to complete in a reasonable time?

    Any thoughts or sugestions to help me learn more about DB2 backup speeds? I can provide any other info necessary!

    Attached Thumbnails Attached Thumbnails compare_database.JPG  

  2. #2
    Join Date
    Apr 2012
    Provided Answers: 27
    You are backing up to /db2backups
    but you did not post *what* or *where* that file-system lives
    and what kind of physical resources it comprises.

    That can significantly impact performance of backup.

    Your databases are tiny so 3 hours seems way too long
    if you look at it from the gigabytes-per-second figure...

    If your environment (and I/O subsystems) are heavily virtualized
    then you are at the mercy of external factors
    that can influence backup performance. Your post did not
    indicate whether your hosts are physical or virtual.

    If /db2backups physical-resources (disks) are shared with the tablespace containers or transaction-logs then backup performance will suffer.

    If /db2backups physical-resources are on a SAN , and the underlying disks are shared with other (possibly non-db2 applications) then performance can suffer if those resources are heavily used. Your
    SAN admin person will be able to spot if there is contention.

    You can improve db2 backup performance by doing things like these:

    * backing up to multiple directory-paths (preferably on different disks)
    although in this case you might need to adjust
    other scripts, such as restore scripts etc.

    * ensuring that the backup directory does not share any disks
    either with the tablespace-containers or disks for transaction logs files.

    * smaller gains are possible by experimenting with the
    values of WITH x BUFFERS BUFFER y PARALLELISM z options
    (if util_heap_sz is large enough, and Y is an even multiple of
    the extent size for you biggest tablespaces).

    * In virtualized environments, assign sufficient i/o bandwidth to
    your backup channels to get a reasonable gigabytes-per-second figure for your db2-backups.

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    From my experience these factors contribute the most to the backup performance (in no particular order):
    - for compressed backups -- CPU speed (actual, not what shows in the DBM config).
    - backup media I/O throughput.
    - for remote destinations -- network throughput.

    DB2 9.7 generally does a better job of self-tuning backup parameters (buffer size and parallelism).
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Aug 2011
    Columbus, OH
    Thanks db2mor and n_i, you both got me looking in the right direction. The backup location is SAN. This sent me on a visit to my storage admin. She indicated there may be an issue with our VIO servers. This is new to me, so maybe you know, but it's an I/O sublayer between my DB2 servers and the SAN. At any rate, I'm going to have a discussion with my OS admin when he returns from training. Thanks again for pointing me in the right direction and giving me some insight!

  5. #5
    Join Date
    Jan 2009
    Zoetermeer, Holland
    Same old DBA song. You're surrounded by black boxes:
    - black box #1 virtualization
    - black box #2 NAS/SAN storage layers
    - black box #3 Network
    - black box #4 what did I forget?

    Them black boxes have a lot of influence on the performance of databases for which we are responsable. I think, as a DBA, you should not only keep track of the KPI's coming from the databases but also start to monitor those black-boxes too. So you can play a pro-active role and pay a visit to the storage-admin and tell hem/her there might be a problem with the VIO-server-black-box-thingy.

    .... yeah, I know: sounds like covering you *ss...
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  6. #6
    Join Date
    Aug 2011
    Database backup performance also depends on how evenly the data is distribuited over different table spaces.

    PARALLELISM parameter of the DB2 backup command influences the number of table spaces which can be read in parallel by the backup. On process or thread level, the
    PARALLELISM parameter defines the number of DB2 buffer manipulators (db2bm) that are used for the backup. Each db2bm EDU is assigned to a specific table space.

    The best way to ensure optimal parallelism of a database backup is to distribute the data as even as possible over the table spaces, for example, by using dedicated table spaces for the largest tables. Instead of 80 percent data in a database residing in one big table space, the data should be evenly distributed over different table spaces with similar sizes. This type of setup supports optimal backup parallelism.

    Moving large tables to their dedicated table spaces is a common best practice for large databases, this can be done online using online table move where a table can be moved online to a new table space without significant impact on the production database.

Posting Permissions

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