Dump, Load and Index Rebuild on Type II Database. - Performa

When will the customer be satisfied? How many hours wil be okay for him? What is the maximum number of hours they want/can be down. For these kind of actions i allways use binary dump & load. Can also be done in parallel. For fast dumps of large tables the primary index may not be the right one. Look for an index with a date if records are added daily and use this index instead of the primary. You may need to do some dump tests. And why do you want to all at once? Why not db after db. No real solution, just some thoughts. Tom Bascom (Whitestar) could give you the best advice on this.

Hi Ahmed,  I would also recommend a binary dump and load.  When dumping out the data I would suggest setting your -B as large as possible.  

Another option is to use the -i param when loading your data (worse case you would have to reload if errors occur).  This parameter is a huge time-saver.  

Paul Koufalis gave a great presentation on this at the last EMEA Conference.  The presentations are available for download, i'm sure Pieter has them already.

Excellent baseline questions from Simon -- it really helps to know what the target is.  If the customer keeps moving the goal posts it is very hard to every make them happy.

When the customer really and truly wants the process to be as short as possible you have to spend time testing various options and doing dry runs.  Every system is different and your results *will* vary from what someone's Powerpoint or forum posting has to say.

None the less... Paul's talk from the EMEA PUG is quite good and covers a lot.

I second the recommendation for binary d&l.  It is almost always going to be faster.

If the data is already in type 2 storage areas and speed is your main objective then a binary dump with "-index 0" will probably be fastest.  You should also probably use -RO rather than starting a server.

You will need to test it but, in many cases, starting one dump per (real) CPU in parallel will be fastest.  Getting the scripts written to balance the threads and get them all to finish more or less together can be challenging.

For the load I would use -r rather than -i, you get pretty much all the same benefits with better recoverability (neither option is suitable for *production* but they are useful when building a db from scratch).

In a virtual environment there are a million or so things that can fool you.  Be especially careful not to mistake vCPUs for real CPUs - a hyper threaded vCPU is not worth much trying to do things like allocate one thread per CPU.  You are probably better off not counting hyper threaded CPUs at all.  Your IO subsystem is going to make a huge difference too.

I concur with all of Tom's points above.

Some thoughts:

• I assume you have a reasonable Type II structure, with each large table in its own area, and each large table's indexes in their own area.  Each area should contain objects of only one type: tables or indexes or LOBs.  No application objects should be in the schema area or other Type I areas.
• 32 GB of RAM isn't much for a database of this size.  Increasing that, even if only temporarily for the duration of the idxbuild, could help performance; it would give you more sort/merge buffers and could reduce disk I/O.  Same goes for vCPUs, assuming they aren't thin-provisioned.  More CPUs could allow you to improve performance with more threads.
• re: idxbuild all -TB 64 -TM 32 -l 2000 -B 1000 -SG 64 -TF 80 -thread 1 -threadnum 8 -mergethreads 8 -datascanthreads 8 -pfactor 90 -rusage:
  
  • You don't need -thread 1; it is the default on Enterprise.
  • -l is a client startup parameter (local buffers); it isn't useful here, as far as I am aware.
  • With 8 CPUs you likely have way too many threads.  The idxbuild maximum thread count is threadnum * ( mergethreads + 1) + 1.  With these parameter values you will have up to 73 concurrent threads, depending on how many merge groups are in use for a given area.  Aim for threads = 1.5 * CPUs, e.g. -threadnum 3 -mergethreads 4 (with 8 CPUs). 
    
    It is also worth noting that every thread opens every file, so if you have a lot of extents and you aren't running as root it is possible to hit the process handle limit (ulimit -n).  If your idxbuild runs out of file handles and crashes, you have to restore from backup.
  • -pfactor 90 is on the high side.  Packing keys that tightly into index blocks could cause a lot of expensive block splits as you do creates after the rebuild .  Use -pfactor 80.
  • Look in the log (and idxbuild stdout) for the 11480 and 11483 messages per area.  They will tell you how much I/O you did to the temporary files on disk.  The ideal is to have no I/O at all, i.e.e all of the sort/merge activity is in the -TF buffers.  E.g.:
    
    DBUTIL   : (11480) Temporary sort file at /home/robf/db/idxbuild used up 0K of disk space.
    DBUTIL   : (11483) A total of 0K of temporary sort disk space was used for area 8.
    
    If you have a number larger than 0K then you are doing disk reads and writes during the sort/merge phase for that area.  This could possibly be eliminated by adding RAM or by restructuring the areas such that you are processing less key data (fewer objects per area). 
    
    This disk I/O is done to the temporary files.  Their location is specified by the -T or -SS parameters.  If neither parameter is specified then those files will be in the current directory, taking away some of your I/O bandwidth if it is in the db volume.  If you do have to do disk I/O, it is preferable to do it on non-database disks, or use -SS to spread the I/O across multiple volumes, if possible.

• You have specified -datascanthreads 8; try 12.  Note that this parameter has restrictions:
  
  • data must be in Type II areas;
  • you must answer 'yes' when asked if you have enough disk space for sorting;
  • the data area being scanned must not also contain indexes being rebuilt;
  • the area being scanned must not contain tables that have word indexes.
    If these restrictions are not met for a given area then its data scan is single-threaded.
    
    
  • If you have tables with word indexes, then:
    • if the table is small, put it in a "word indexes" area;
    • if the table is large, it should be in its own area anyway.
• With a database this large, it can be helpful to load data and build indexes in a pre-grown set of extents (if you are writing to variable extents).  For example, do a dry run where you load the data and build the indexes.  Then truncate all the areas, leaving them logically empty.  Then in your next load/build, you can write the data without the cost of any extend operations.
• Another small optimization you can try is to truncate index areas prior to the rebuild.  This eliminates the first phase per area of the index rebuild, the index scan.  Before attempting this, be sure to check that your index areas only contain index objects, not tables or LOBs.  If you have the testing time, try this and see how much time it saves.
• Not a performance optimization, but still important: be sure to back up your DB between load and idxbuild. 
• Re: dump: compacting or rebuilding the primary indexes in your source DB prior to the dump might help a little with dump performance.

Thanks guys for your input. I am going to test the dump and load Binary on a temp server, Index Rebuild with the options above to get the best time. Will advise.

Hi all, and thanks for your valuable input.

In the instance we are preparing for, we are doing a fair amount of re-arranging of the DBs in the process.

Historically the "DB" was actually 3 separate DBs, and we are 'merging' these into one dictionary. So we can quite easily run multiple parallel dump streams.  I'm not sure if we can run parallel BulkLoads into the single taget DB, but we'll look at that. Any advice around that?

For the Index Build I find no reference to being able to run multiple parallel 'proutile -idxbuild' processes - except for the internal multi-threading which has been discussed.  The thought that went through my mind was "surely if idxbuild can be run per area, then one could run the separate areas in parallel ...".  But I have been known to be a wishful thinker. :)

Re Areas: We have put the 2 largest transaction tables into their own areas, and index areas are separate too. Not sure about LOBS - Ahmed?

Our client is - surpisingly ;) - very demanding, but also very tightfisted with server provisioning. They gave us the objective of completing the process (detail below) within 24 hours, but we've "talked them down" to 39 hours now. But it's still very tight.  This is scheduled to start in the evening of 26 Jan.

The process includes moving onto new hardware, though the new spec is not much improved from the old (did I mention they are tight-fisted?) Servers config is VMWare VM, 8vCPUs, 32GB RAM, disks sized to be at 85% capacity once the DBs are built, and the backaup area is only large enough for one backup. We have saved significant time by negotiating with them to provide additional partitions temporarily during this process - which allows us to move data from server to server by moving the partitions using VMDK (1/2 hour) instead of doing network copies of the DBs (nearly 5 hours).

Here are the highlights of our current schedule, based on the dry-runs we have already performed:

--- 5:30 hours: Dump all data ~ 1.2 TB from 2 servers of 750 Gb and 450GB

--- 0:30 hour: Transfer data from old servers to new servers (partition unmount & remount from storage layer)

--- 7:00 hours: Load all data onto the 2 new servers

--- 10:00 hours: Index Build on both servers

--- 1:00 hour: backup DBs post-build.

--- 6:00 hours: time required for application upgrade, rebuild and "sanity testing" on the new platforms.

--- 1:00 hour: Network reconfig for new servers to replace the old in-place (Customer's IT team).

--- 4:00 hours: enable replication, backup, transfer backup to TEST server, restore, start test server for UAT.

--- 3:00 hours: transfer same backup to DR server, restore, start replication.

--- Hand-over to customer for full testing and sign-off. Original target 24 hours, extended to 39 hours.

The total time is 38 hours - we have a 39-hour window. Which means no slack to speak of. if we add the backup pre-idxbuild as Rob suggested, then slack is literally 0.

So - this is why we are trying to save time on the big chunks (IdxBuild, Dump, Load).

Life is fun in IT! ;)

> --- 5:30 hours: Dump all data ~ 1.2 TB from 2 servers of 750 Gb and 450GB

Was it a text dump (Data Dictionary)?

Type 1 or type 2 storage areas?

The Data Base is type II

Used Dictionary dump in parallel with -RO Parameter.

Text dump with TABLE-SCAN option or binary dump with the -index 0 will be faster.

Or check the quality of the indexes and use the one with the minimal logical scatter factor (or the one with smallest size).

You can use my SeekRatio.p program:

ftp.progress-tech.ru/.../SeekRatio.p

By default the program reads only first 10,000 records using each index.

I am busy doing a Binary dump of the DB's with Multiple sessions. I would like to know if I can do Multi Session Binary load per Data Area. I have separated my data and Index areas and have created areas for each of my large files. Can I start the DB in multi User with the -i (No Integrity) option and load multiple areas at the same time.?

Yes and yes

Some things can be done well in advance and should not be part of the outage -- for instance, the empty database should be built, have the .df loaded and the extents pre-sized a day or two in advance.

It also sounds like you might be doing a lot of things sequentially -- i.e. dump the data, transfer the data, load the data.

If that is true there are a couple of things you can do that will save you a lot of outage time.

Instead of a "transfer the data" step use a shared filesystem.  NFS mount some disks and dump to those disks.  As  soon as the dump of a table is complete you can start loading it.  How do you know it is safe to start loading?  Have the dump go to a "dump" directory.  Use the Linux "mv" command on the dump side to rename the .bd file from the dump directory to the "stage" directory.  IOW:

proutil dbname -C dump tableName /share/dump ; mv /share/stage/tableName.bd /share/stage

then on the load side do something like:

mv /share/stage/tableName.bd /share/load ; proutil dbname -C load /share/load/tableName.bd  -r ; mv /share/load/tableName.bd /share/arc

In this way only *active* dump files will be in the dump dir and only active load files will be in the load directory.  Any file that is in the stage directory is ready to be loaded.  The dump and load processes can overlap.

Naturally small tables will be ready to load first.  In a lot of cases the time to do the whole process becomes the time needed to dump the largest table plus the time needed to load that table plus the index rebuild.  (All the smaller stuff will have been completed by the time the biggest table is ready to load.)

You can experiment to find the best balance of dump threads to get the fastest time.  I often find that:

1) dump all of the tables that are expected to be very small in one thread -- this is usually something like half or two thids of all the tables.

2) dump the largest table in it's own thread

3) spread the rest among N - 1 threads round robin where N is the number of full cores available (NOT hyper threaded cores)

95% of the time that's going to be more than good enough.  If your disks and network are really fast you can try more threads but you are probably going to bottleneck on the disks on the dump side.

On the load side -- *most* of the time a single loader thread with idxbuild all at the end of the process is going to be fastest.  That is at least partly because most of the time one big table is dictating the schedule.  Everything else is done by the time the biggest table is ready to start loading.

I am in similar situation and have few questions

1) I have few large archive tables (100GB)+ which does not change anymore but needs to be in the database for some compliance reasons.  I am planning to dump/load/reindex them in advance (Outside of outage window) .

WIll  proutil idxbuild all rebuild indexes on these large tables again?  Does pre-building index on some of these archive tables save  any time when we are running  proutil idxbuild all  after loading all data?

2) In some of the large transaction tables past data does not change (historical orders), can partial dump/reload/reindex  with  PROUTIL DUMPSPECIFIED for older data reduce maintenance window?

"all" means all so if you tell it to rebuild all indexes it will indeed rebuild all of your indexes.

Alternatively you can pick and choose and specify a list of indexes to rebuild.  If you do not specify "all" you get a menu:

Index Rebuild Utility

=====================

Select one of the following:

All           (a/A) - Rebuild all the indexes      

Some          (s/S) - Rebuild only some of the indexes      

By Area       (r/R) - Rebuild indexes in selected areas

By Schema     (c/C) - Rebuild indexes  by schema owners

By Table      (t/T) - Rebuild indexes in selected tables      

By Activation (v/V) - Rebuild selected active or inactive indexes

(The answers can  be provided by redirecting stdin from a file...)

I have never found DUMPSPECIFIED to be helpful.  In testing it has always been significantly slower than any other approach.

Thanks to everyone for the suggestions. I brought the process down from 27 Hours 30 Minutes to 12 hours. This is what the client wanted. Our entire process to run less than 24 hours. So with our Upgrade and Dump and Load the entire process should be done in +- 20 hours.

Of course everyone's results will vary but I'm curious -- which suggestions gave *you* the most "bang for the buck" (or maybe that should be "ring for the rand"?)

Binary Dump using multiple large dumps and rest of tables. Binary load in multi user using the 4 Data Areas. Load for each Data Area. Index Rebuild with the following options

proutil DBNAME -C idxbuild -TB 64 -TM 32 -TMB 64 -B 1000000 -SG 64 -TF 80 -T /SORTAREA/ -threadnum 4 -mergethreads 4 -datascanthreads 4 -pfactor  80 -rusage