purge and resize db

[George Potemkin]

Also use -rusage. After a test run, look for (17262) and (11480) and (11483) messages in the output. They will tell you about your CPU and temp disk utilization. Ideally, the (11483) message will tell you that you used 0KB of sort disk space (i.e. all of the data fit in the -TF sort buffers in memory).

-z (since V11.1?) adds a few extra messages:

Thread memory usage #threads: %d Required: %J Limit: %J In use: %J    %B %s: numEntries: %i
Threads requested: Data scan: %d, sort: %d, merge: %d, sort groups %d
*** Used %l in-memory sort buffers out of %l (%j MB out of %j MB)

 

[Rob Fitzpatrick]

-z (since V11.1?)

It was also back-ported to late 10.2B (SP06 - 08?). I have confirmed that it is in SP08 HF 48.

 

[George Potemkin]

It was also back-ported to late 10.2B (SP06 - 08?). I have confirmed that it is in SP08 HF 48.

OpenEdge Release 10.2B06 as of Mon Mar 19 19:14:30 EDT 2012
OpenEdge Release 10.2B07 as of Fri Sep 7 02:16:34 EDT 2012
OpenEdge Release 10.2B08 as of Tue Nov 12 19:06:41 EST 2013
OpenEdge Release 10.2B0848 as of Tue Nov 10 05:16:39 EST 2015
OpenEdge Release 11.0 as of Fri Dec 2 19:00:19 EST 2011
OpenEdge Release 11.0.0.002 as of Thu Feb 2 09:07:01 EST 2012
OpenEdge Release 11.0.0.017 as of Tue Jul 30 02:16:04 EDT 2013
OpenEdge Release 11.1 as of Fri Jun 15 19:01:20 EDT 2012

Based on the release dates (June, 2012) it's either 10.2B06 or 10.2B07.

 

[parttime Admin]

Thanks Rob for the detailed pointers, much appreciated.

Increasing -bibufs made a clear difference — empty buffer waits dropped to near zero, so that bottleneck is gone.
On idxbuild, I’m now using -rusage and see significant sort spill (10–20 GB), even with -TF 80/90, so still some disk I/O there. Threads (up to 8/8) helped reduce runtime a bit, but not a gamechanger.
Overall runtime is now stable in the ~2h40–2h50 range for 240 GB load-DB, so mainly chasing the last few minutes.
Will also have a closer look at -thread/-threadnum interaction and pfactor for the final DB.

Thanks again!

 

[George Potemkin]

Overall runtime is now stable in the ~2h40–2h50 range for 240 GB load-DB

Can you post the timing per phases (dump, idxbuild, load)?

 

[Rob Fitzpatrick]

I once had an experience of trying to rebuild indexes of a large table on a resource-constrained machine. I didn't have enough RAM to prevent disk I/O when building all eight indexes at the same time. In the end it was faster to run idxbuild multiple times, with different sets of indexes, to keep the sort/merge phase memory-resident.

The lesson is that there is no one way to run idxbuild that is best. You have to experiment with what works best with your hardware, data, and structure.

Also, if this is a virtualized environment, see if you can temporarily bump up the specs of the machine, or relocate it to a better host, to give idxbuild more resources to work with.

Threads (up to 8/8) helped reduce runtime a bit, but not a gamechanger.

Which threading parameters and values specifically? There are several of them. What is your full idxbuild command line? And how many CPU cores do you have?