DB2 restore takes quite long time

[former_member459694]

Dear Experts,

We met a quite strange problem, DB2 restore for a 300GB database will take 40 hours to be finished...

We are running on DB2 9.7 FP10, database backup time is about 40 minutes (for offline backup) and 2.5 hours (for online backup with compress option), now we are restoring it to other servers, but the restore speed is quite unbearable, estimated to be finished in 2 days

We have tried both offline backup set and online backup set, and was on different servers, all will take 2 days...even slower than load based method.

Do you have any idea what's the root cause of it? I searched on the SCN but not satisfied answers...

Please help me out. Thank you very much.

Fresh

[former_member264034]

Hi,

When you perform a restore operation, DB2 will automatically choose an
optimal value for the number of buffers, the buffer size and the
parallelism settings. The values will be based on the amount of utility
heap memory available, the number of processors available and the
database configuration. T
Therefore, depending on the amount of storage
available on your system, you should consider allocating more memory by
increasing the UTIL_HEAP_SZ configuration parameter.

Additionally, you can also choose to do any of the following to reduce
the amount of time required to complete a restore operation:

1, Increase the restore buffer size.
The restore buffer size must be a positive integer multiple of the
backup buffer size specified during the backup operation. If an
incorrect buffer size is specified, the buffers allocated will be the
smallest acceptable size.

2, Increase the number of buffers.
The value you specify must be a multiple of the number of pages that you
specified for the backup buffer. The minimum number of pages is 8.

3, Increase the value of the PARALLELISM parameter.
This will increase the number of buffer manipulators (BM) that will be
used to write to the database during the restore operation.

you can find the best recommendations for backup / restore in the
DBA guide for DB2 LUW.

https://service.sap.com/~sapidb/011000358700001449002009E/

db6_admin_guide

Regards,
Aidan

[former_member459694]

Hello Aidan,

Thank you so much for your reply.

I have stopped the restore process and issue new command with the increased number of those parameters:

RESTORE DATABASE PRD FROM '/sapsetup/PRD_BACKUP' TAKEN AT 20150304120606 ON '/db2/QAS/sapdata1' ,  '/db2/QAS/sapdata2' ,  '/db2/QAS/sapdata3' ,  '/db2/QAS/sapdata4' INTO QAS NEWLOGPATH '/db2/QAS/log_dir/NODE0000/' WITH 32 BUFFERS BUFFER 16384 REPLACE HISTORY FILE REDIRECT PARALLELISM 32

I think those values are big enough however the restore speed doesn't change much.

The CPU utilization as showed in command "top" still around 1%-2%, do you have any idea what's the reason? I am afraid in critical situations like system crash, we wouldn't accept so long time system restore. It's really quite strange...

I remember two years ago I have restored DB2 database (windows platform) from tape to disk, 300GB data size cost around 1.5hours, I don't know why on Linux platform and the hardware are not so bad takes so long...

Best regards,

Fresh

[manumohandas82]

Hi Fresh ,

Check your DBM parameter INSTANCE_MEMORY  (  assign your full memory if found leaa than actual server memory )

What is the priority ( check the value UTIL_IMPACT_LIM ) increase it to 90

May not be helpful at this stage, in case you have an option

Check the High water mark levels of the tablespaces in the source system  , reduce  and take a new backup and use for restore

Thanks ,

Manu

[former_member264034]

Hi,

To improve further, please note that a good restore time does not begin with the restore command, but with database layout, and the planning of the backup. Influencing factors are

  - number of sessions used for the backup (e.g. TSM, or storage subsystem), or number of paths to write the backup to (backup to disk)

  - size of largest table spaces

    One very large table space making up most of the database will define the time required for backup, and restore

  - parallelism of the backup

  - hardware (I/O subsystem, CPU, memory, ...)

Have you already tried the following DB Registry variable?

DB2_USE_FAST_PREALLOCATION=ON

Operating system: AIX, Linux and Solaris

Allows the fast preallocation file system feature to reserve table space, and speed up the
process of creating or altering large table spaces and database restore operations. This
speed improvement is implemented at a small delta cost of performing actual space
allocation during runtime when rows are inserted.

Regards,
Aidan

[Former Member]

Hi-

Assuming that database software binaries are consistent, CPU and Memory for the QA server are all good, we can look at storage also here-

The file system from which you are restoring from, is it a local drive? Or you are pulling from a remotely shared drive?  If the CPU and Memory are not capped at , check if the disk utilization during restore process is reaching 100%.

Bad IO between CPU and Disks/NAS can also lead to performance bottlenecks. Get in touch with the storage guys in your firm and have them look at the hits the QA is getting when you are restoring.

-Amit

[former_member459694]

Hi Manu,

INSTANCE_MEMORY is already 20GB assigned in parameter, total memory consumption till now is less than 20GB, so I think INSTANCE_MEMORY is not the problem

set UTIL_IMPACT_LIM to 90 during restore process, seems the speed doesn't increase....

Best regards,

Fresh

[former_member459694]

Hi Aidan,

for DB2_USE_FAST_PREALLOCATION, I think the space for this database was preallocated, because right after I issued the restore database command, there are 330GB diskspace occupied under /db2/QAS

Does the backup media affect the speed of the restore?

I use the following command to have a offline backup:

db2 backup database PRD to /db2/PRD_BACKUP with 2 buffers buffer 1024 parallelism 2

by the way, during the restore process, I found the CPU utilization still under 2% while the load average always on a high level:

load average: 79.79, 79.24, 79.27

Best regards,

Fresh

[manumohandas82]

After you set the parameters you need to restart the database and hence the restore .

Thanks ,

Manu