what do y'all think about through put - Forum - OpenEdge RDBMS - Progress Community

what do y'all think about through put

 Forum

what do y'all think about through put

This question is not answered

HP-UX 11.32 ai64

OE: 11.4

STORAGE: NetApp All flash

37700000 records processed. (15165)

Sorting and index building group 0.
Sorting index group 0 complete. Elapsed time: 60.030 (16761)
Resource usage: CPU user 40.410000, system 11.670000
Resource usage: DISK reads: 6223498 KB at 101 MB/sec, writes: 6149935 KB at 100 MB/sec
Building index 728 (k-fill) of group 0 (16757)
Building of indexes in group 0 completed. Elapsed time: 528.869 (16762)
Resource usage: CPU user 110.010000, system 6.630000
Resource usage: DISK reads: 1920977 KB at 3632 KB/sec, writes: 421848 KB at 797 KB/sec

Sorting and index building group 1.
Sorting index group 1 complete. Elapsed time: 55.355 (16761)
Resource usage: CPU user 39.470000, system 10.410000
Resource usage: DISK reads: 6330944 KB at 112 MB/sec, writes: 6257381 KB at 110 MB/sec
Building index 729 (k-prod) of group 1 (16757)
Building of indexes in group 1 completed. Elapsed time: 458.269 (16762)
Resource usage: CPU user 105.150000, system 9.250000
Resource usage: DISK reads: 2107855 KB at 4599 KB/sec, writes: 722992 KB at 1577 KB/sec

Sorting and index building group 2.
Sorting index group 2 complete. Elapsed time: 65.129 (16761)
Resource usage: CPU user 47.780000, system 10.620000
Resource usage: DISK reads: 6202418 KB at 93 MB/sec, writes: 6128855 KB at 92 MB/sec
Building index 730 (k-specns) of group 2 (16757)
Building of indexes in group 2 completed. Elapsed time: 400.764 (16762)
Resource usage: CPU user 102.430000, system 4.950000
Resource usage: DISK reads: 1712466 KB at 4273 KB/sec, writes: 202392 KB at 505 KB/sec

Sorting and index building group 3.
Sorting index group 3 complete. Elapsed time: 28.728 (16761)
Resource usage: CPU user 22.910000, system 3.370000
Resource usage: DISK reads: 1753701 KB at 60 MB/sec, writes: 1680231 KB at 57 MB/sec
Building index 731 (k-zcntrpntqtno) of group 3 (16757)
Building of indexes in group 3 completed. Elapsed time: 333.421 (16762)
Resource usage: CPU user 99.090000, system 1.900000
Resource usage: DISK reads: 566460 KB at 1698 KB/sec, writes: 13688 KB at 41 KB/sec

Sorting and index building group 4.
Sorting index group 4 complete. Elapsed time: 28.313 (16761)
Resource usage: CPU user 22.450000, system 3.380000
Resource usage: DISK reads: 1641357 KB at 57 MB/sec, writes: 1567763 KB at 54 MB/sec
Building index 732 (z-transdt) of group 4 (16757)
Building of indexes in group 4 completed. Elapsed time: 413.124 (16762)
Resource usage: CPU user 101.300000, system 2.290000
Resource usage: DISK reads: 564000 KB at 1365 KB/sec, writes: 52520 KB at 127 KB/sec

Sorting and index building group 6.
Sorting index group 6 complete. Elapsed time: 26.632 (16761)
Resource usage: CPU user 19.350000, system 4.620000
Resource usage: DISK reads: 2475009 KB at 91 MB/sec, writes: 2401415 KB at 88 MB/sec
Building index 726 (k-oeel) of group 6 (16757)
Building of indexes in group 6 completed. Elapsed time: 337.007 (16762)
Resource usage: CPU user 100.820000, system 4.350000
Resource usage: DISK reads: 1108576 KB at 3289 KB/sec, writes: 339592 KB at 1007 KB/sec

Sorting and index building group 7.
Sorting index group 7 complete. Elapsed time: 42.272 (16761)
Resource usage: CPU user 32.080000, system 5.270000
Resource usage: DISK reads: 2424324 KB at 56 MB/sec, writes: 2350699 KB at 54 MB/sec
Building index 727 (k-commcalc) of group 7 (16757)
Building of indexes in group 7 completed. Elapsed time: 303.750 (16762)
Resource usage: CPU user 97.810000, system 3.020000
Resource usage: DISK reads: 821230 KB at 2703 KB/sec, writes: 49016 KB at 161 KB/sec
Index k-commcalc was activated. (16154)
Index k-fill was activated. (16154)
Index k-oeel was activated. (16154)
Index k-prod was activated. (16154)
Index k-specns was activated. (16154)
Index k-zcntrpntqtno was activated. (16154)
Index z-transdt was activated. (16154)
A total of 0K of temporary sort disk space was used. (5284)
Loaded 37717937 records. (15167)
Binary Load complete. (6256)

All Replies
  • 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.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • 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.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • 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.

    --
    Tom Bascom
    tom@wss.com

  • 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).

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • See the bunker series on Dump and Load here: http://pugchallenge.org/downloads2016/231%20-%20Bunker%20v6.pdf

    Our results showed it’s fastest to parallel dump, and to load with building indexes on the way in.  YMMV for sure.

    The DBAs on my team are managing > 2,000 databases, so we dump and load basically every weekend.  For the most part we do not need to do any acrobatics (parallel processes, etc) as the machines are fast enough to do the work in the allotted downtime.  But on occasion we need to squeeze the square peg into the round hole.  It’s good to know there are options.  If you are really strapped for downtime, then Pro2 can help, see this presentation: http://pugchallenge.org/downloads2017/Platform%20and%20Data%20Migration%20With%20Little%20Downtime%20v2.pptx

    We are finding more and more people are interested the little down-time dump and loads.  We talk to someone about once a month or so for the large customer that can’t tolerate more than 1 hour of downtime.

    Mike
    -
    Mike Furgal
    Director – Database and Pro2 Services
    PROGRESS Bravepoint
    617-803-2870 


  • Ahhh...I wish I could spend days and weeks benchmarking this stuff. I'm surprised that 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.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • 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.  

  • Paul, we were equally surprised that load build indexes won the speed test, as previous experiences proved otherwise.  But we had the machine, the database, the time, and the need to come up with something for the bunker test series, so the methodology was sound and repeatable.

    Mike
    -- 
    Mike Furgal
    Director – Database and Pro2 Services
    PROGRESS Bravepoint
    617-803-2870 


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

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • : 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.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • Dumping and loading methodologies are the ultimate proof of the "it depends" maxim.

    --
    Tom Bascom
    tom@wss.com