what do y'all think about through put

I guess my point is that NetApp all flash storage is the bomb!

I did a D&L in 6.5 hours -Database size internally 325GB

DB Size dbhw: 42,644,980.00   Database block size: 8192

Bits  349,347,676,160

Bytes 3,331,639,063

Kilo  341,159,840

MB    333,163,906

GB    325.36

Storage Areas: 35         Total Extents: 282

6.5 hours seems like a lot. Can you break it down to dump, load and idxbuild? Did you overlap dump and load processes? Wait - you did inline idxbuilds? That's usually slower. Were there one or two HUGE tables that dominated the elapsed time? How did you dump? With or w/out an _mprosrv? Same question for load.

There used to be an HPUX bug in the idxbuild where the process could not use more than one shared memory segment, so even if you had 128 GB RAM on the box, and you set -TF 80, you still only used 1-2 GB of RAM for sorting. Did you notice if you used disk temp file? Did you use -z -rusage? These give you more verbose logging.

Really!  I guess it is all relative.  

I know i need some tuning, however, going from 36 hours, to 11 hours to 6.5 hours is a BIG improvement.

I did the whole shebang in one swoop.  

I did a parallel dump and load with the build indexes inline.

No HPUX BUG.

Of course binary D&L

here are my settings:

my $Util_Dump_Param1  = '-RO -B 1000';

my $Util_Dump_Param2  = '-RO -B 10000';

my $Util_Dump_Param3  = '-RO -B 10000';

my $Util_Load_Param  = 'build indexes -i -TB 64 -TM 32 -rusage -SS /rd1/dump/db.

srt -G 0';

sample dump script (snipet)

echo "Dumping arett ; \c" >> /rd1/dump/scripts/DumpArea51.log 2>&1

/opt/dlc/bin/proutil /db_dy1/data/carolina -C dump arett /rd1/dump/dump/51/ -RO

-B 1000 > /rd1/dump/dump/51//arett.log 2>&1

sample load script (snipet)

        echo "`date` \c">> /rd1/dump/scripts/LoadArea222.log

        echo "Loading ${T[$count]} ; \c"  >> /rd1/dump/scripts/LoadArea222.log

        /opt/dlc/bin/proutil /db1/data/carolina -C load /rd1/dump/dump/222/${T[

$count]}.bd build indexes -i -TB 64 -TM 32 -rusage -SS /rd1/dump/db.srt -G 0 >>

Sorry no disrespect meant. I agree that it's a huge improvement. We've seen similar going from spinning rust to ssd.

Try it w/out build index inline. This is why you did not hit the HPUX issue (a Progress issue, not an HPUX bug): the inline idxbuild does not use the -TF and I believe uses disk temp files the way the old idxbuild used to (your -SS param, which I no longer use). You also get -datascanthreads, -mergethreads, -SG, -z, -rusage and a bunch of other params that may or may not be usable with inline build indexes.

Let me know if you see any differences using the various -B params on the dump. I never use them because in -RO mode, you're mostly depending on the FS cache. I would be curious to hear if they provide any additional boost.

With the improvements in idxbuild it may be faster to run the idxbuild separately rather than have the binary load build the indexes.

If you run it separately and use the -TF parameter, look at the output to check if the -TF parameter was used properly.

Given what you have posted about your previous environments I'm sure it is much better.

I did not take it that way Paul,

With the -B params and -RO mode the dump took 37 minutes to finished - db size 325GB

OE: 11.4

See - that dump time sounds good to me. That means you spent 6h on load+idxbuild.

Please try single-threaded load with -i, then idxbuild will all the fancy params. Watch the -z output to see if it did any temp file writes (it should not).

Ahhh...I wish I could spend days and weeks benchmarking this stuff. I'm surprised that [mention:94ec1a223dfc4a1f959f430a2ceea152:e9ed411860ed4f2ba0265705b8793d05] found that inline build indexes is faster. Conceptually I would agree with smaller DBs where the difference isn't huge one-way-or-another, but for large DBs with a few dominant, large tables?

I will have to test test test. Wouldn't be the first time I'm wrong.

I did use the -i for the load, however, not the single-threaded load.  In my mind that does not make any sense to use single-threaded.   Logic tells me parallel load would be faster.  

It took me 10 years to convince people otherwise. Try it.

[mention:94ec1a223dfc4a1f959f430a2ceea152:e9ed411860ed4f2ba0265705b8793d05] : do you have all the details for the load and idxbuild? What startup params etc? I'll have another opportunity to test in May. I'm VERY surprised by some of your results and would love to apply them to a customer DB.

Dumping and loading methodologies are the ultimate proof of the "it depends" maxim.

I could not agree more Tom.  For large volumes, physical order of data for scanning as well as logical order of data for the index building portion can produce dramatic differences in performance results.   This is likely why a separate index rebuild is often faster than building indexes inline and visa versa.  If sorting is not needed, it is probably faster to do inline.  If sorting is needed, it Is likely faster to index rebuild separately.

That's a great point.

I have occasionally found the inline idxbuild to be faster.  Looking back on what I remember of those occasions I can also say that those were databases that probably didn't need a lot of sorting.  I hadn't thought of that connection but it makes a lot of sense.