I try to restore a 2.8 Tb Online backup made on a Datawarehouse Production AIX server onto a different AIX server that deserve QA env. with much less ressources ( CPU & RAM ) than the production env.
Prod: AIX DB2 ESE v9.5 fp 5 SinglePartition, 12 CPU, 90 Gb RAM, FULL Online backup using 4 Backup session taken 3.0 hrs to backup
QA env: AIX DB2 ESE v9.5 fp 5 SinglePartition, 2 CPU, 9 Gb RAM restore take more than 32 Hours !! to complete.
The Prod DB have over 45 Gb of Bufferpool and plenty of DB & Instance memory, whereas for the QA Restore I can't, so I use a UTIL_HEAP_SZ of 524288 (ie 2Gb ) and STMM on, DBHEAP & INSTANCE_MEMORY set to AUTOMATIC.
I specify to overwrite the BP allocation via these system variable, but neither since to kicks in properly as shown on db2pd output:
vdedwp1@sainda1(/product/db2/vdedwp1)> db2pd -db dwh01p -bufferpools
Database Partition 0 -- Database DWH01P -- Quiesced -- Up 0 days 17:01:04
First Active Pool ID 4096
Max Bufferpool ID 0
Max Bufferpool ID on Disk 0
Num Bufferpools 4
Address Id Name PageSz PA-NumPgs BA-NumPgs BlkSize NumTbsp PgsToRemov CurrentSz PostAlter SuspndTSCt Automatic
0x070000006030E940 4096 IBMSYSTEMBP4K 4096 16 0 0 266 0 16 16 0 False
0x070000006030ED80 4097 IBMSYSTEMBP8K 8192 16 0 0 1 0 16 16 0 False
0x070000006030F1C0 4098 IBMSYSTEMBP16K 16384 16 0 0 59 0 16 16 0 False
0x070000006030F640 4099 IBMSYSTEMBP32K 32768 16 0 0 80 0 16 16 0 False
Anything you can think of to reduce this 32 hours RESTORE ??
This topic has been locked.
7 replies Latest Post - 2012-06-12T15:51:40Z by SystemAdmin
Pinned topic How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-06-12T15:51:40Z at 2012-06-12T15:51:40Z by SystemAdmin
mor 110000Q8NJ476 PostsACCEPTED ANSWER
Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different2012-06-08T15:18:09Z in response to royerdNow why would you you an insane thing like that?
With one tenth of the RAM, one sixth of the CPU resources, and you don't mention the I/O bandwidth difference between Prod and QA.
What possible benefit could be derived from having a QA environment so radically undersized?
Almost all performance comparisons would be useless.
Just say no.
Or scale the hardware and/or QA-data-volume more appropriately to fit your hardware constraints.
Jotac 270004UBMH82 PostsACCEPTED ANSWER
Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different2012-06-08T15:32:24Z in response to royerd>Anything you can think of to reduce this 32 hours RESTORE ??
I will start to increase the general I/O troughput. IMO Restore isn't CPU & MEMORY bound but I still do not know DB2 so deeptly.
Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different2012-06-08T16:14:38Z in response to royerdThanks Mor, to categorize this as > insane < ....this really don't help.
It's not at all a real QA env. Developper called it that way, but in fact it's DEV env. with a 100 copy of the prod data.
Sizing of the resource have not been made by DBA neither...the point is that I need to speed up the Restore process anyhow...
So if you have INTERESTING comment, please do so otherwise you know what to do..
SystemAdmin 110000D4XK17917 PostsACCEPTED ANSWER
Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different2012-06-11T15:09:29Z in response to royerdWhat is the RESTORE command that you're issuing? And does a DB of that name already exist on the target (dev) system when you issue the restore, or is the restore utility creating the DB for you?
Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different2012-06-11T15:19:54Z in response to SystemAdminThe DB was not existing before the restore ( actually yes, but a drop DB have been done just before the restore ).
The RESTORE cmd used was this one & the requested Backup Image was on disk :
db2 "restore database DWH01P
rebuild with all tablespaces in database
from '/vldb2/backup_FC/dwh01p/backup/online', '/vldb2/backup/dwh01p/backup/online'
taken at 20120604200014
DBPATH ON /vldb2db/dbpath
REPLACE HISTORY FILE
without prompting " ;
Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different2012-06-11T15:30:29Z in response to SystemAdminHow could we know what the restore jobs was actually doing at a specific point in time ?? Have been told by an IBMer that some kind of traces could be direct to the DIAGPATH for Backup/Restore jobs by using a special system variable called DB2_BAR_STATS .... but it seem to be an undocumented feature since I haven't find any reference/documentation for this variable and when I try the DB2SET, I always receive erreor message DBI1303W !! What is the value that I should use to set this variable ??
SystemAdmin 110000D4XK17917 PostsACCEPTED ANSWER
Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different2012-06-12T15:51:40Z in response to royerdThat's not yet a documented, supported feature which is why you can't find any documentation on it, and it doesn't exist at all in 9.5 anyway so even DB2 support staff wouldn't be able to see that data until 9.7.
You're restoring onto significantly less powerful hardware from the sounds of things so you'll have do some digging into what the bottleneck might be. You did say that you backed up to 4 sessions though and I see that you're restoring from only 2, which means that the other 2 image sequences will be serialized after the first two. You may see some benefit from specifying all 4 paths up front, even if that means specifying some paths more than once.