Topic
7 replies Latest Post - ‏2012-06-12T15:51:40Z by SystemAdmin
royerd
royerd
4 Posts
ACCEPTED ANSWER

Pinned topic How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different

‏2012-06-08T14:57:44Z |
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:
[i] DB2_HIDDENBP=1500
[i] DB2_OVERRIDE_BPF=4000

vdedwp1@sainda1(/product/db2/vdedwp1)> db2pd -db dwh01p -bufferpools

Database Partition 0 -- Database DWH01P -- Quiesced -- Up 0 days 17:01:04

Bufferpools:
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 ??
Updated on 2012-06-12T15:51:40Z at 2012-06-12T15:51:40Z by SystemAdmin
  • mor
    mor
    476 Posts
    ACCEPTED ANSWER

    Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different

    ‏2012-06-08T15:18:09Z  in response to royerd
    Now 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
    Jotac
    82 Posts
    ACCEPTED ANSWER

    Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different

    ‏2012-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.
    ciao
    GIovanni
  • royerd
    royerd
    4 Posts
    ACCEPTED ANSWER

    Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different

    ‏2012-06-08T16:14:38Z  in response to royerd
    Thanks 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
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different

    ‏2012-06-11T15:09:29Z  in response to royerd
    What 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?
    • royerd
      royerd
      4 Posts
      ACCEPTED ANSWER

      Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different

      ‏2012-06-11T15:19:54Z  in response to SystemAdmin
      The 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
      except tablespace(SCL_DT16K01
      ,SCL_XS16K01
      ,SRMO_DT04K01
      ,SRMO_DT16K01
      ,SRMO_XS16K01
      ,SRMOA03_2010_DP16K
      ,SRMOA03_2011_DP16K
      ,SRMOA03_2012_DP16K
      ,SRMOA03_9999_DP16K
      ,SRMOA03_XP16K )
      from '/vldb2/backup_FC/dwh01p/backup/online', '/vldb2/backup/dwh01p/backup/online'
      taken at 20120604200014
      DBPATH ON /vldb2db/dbpath
      into DWH01P
      LOGTARGET /vldba/db2/dbaops/db2restor/logtarget/DWH01P
      NEWLOGPATH /vldb2db/db2log_01/VDEDWP1/DWH01P
      REPLACE HISTORY FILE
      REPLACE EXISTING
      without prompting " ;
    • royerd
      royerd
      4 Posts
      ACCEPTED ANSWER

      Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different

      ‏2012-06-11T15:30:29Z  in response to SystemAdmin
      How 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
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: How to speed up DB2 v9.5 fp5 RESTORE of 2.8TB Online Backup on a different

    ‏2012-06-12T15:51:40Z  in response to royerd
    That'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.