Question Best Performance Settings for a Dump and Load (& DB Startup)

[BigSlick]

OpenEdge 11.7.19
DB Size ~2TB
BlockSize 4096
Windows 2019

Hello there gang. Firstly let me start with all my excuses
The business likes to waste money and drag its feet - usually into another meeting. So this is why we are midway into a OE12.8.9 migration and why were upgrading from Windows 2012/2016 to 2019, then 2022 all in the next 12 months. Currently 18 months into this 12 month project.

So we are where we are.

Meanwhile im looking into D&L strategies and I know there's a plethora of information out there but its scatter factor is greater than my databases. Hoping the attached proves that other than some random impulsive purchases and the odd wiki wormhole, i think ive done a little research to merit posting on a topic thats been asked 1000's of times before no doubt. But hoping to have all the best advice and settings collated into thread.

This is where you ask why, and this is why...

Existing Structure File

d "DataArea":7,64;1 G:\DB\Test_7.d1 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d2 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d3 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d4 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d5 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d6 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d7 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d8

New areas to be added

d "Data:DBD":106,16;512 G:\DB\Test_106.d1 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d2 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d3 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d4 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d5 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d6 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d7
#
d "Index:DBD":206,1;64 G:\DB\Test_206.d1 f 16777216
d "Index:DBD":206,1;64 G:\DB\Test_206.d2

Prostrct add/addonline
prostrct ADD docdb docdbadd_DocBatchData2.st -validate
prostrct ADD docdb docdbadd_DocBatchData2.st -validate
prostrct ADD docdb docdbadd_DocBatchData2.st -validate
prostrct ADD docdb docdbadd_DocBatchData2.st -validate

This wasnt created by me, before you all go mental for the state of this.
Bonus point if youve worked it out already...oh and i dont mean the glaringly obvious....the other ticking timebomb.

I am working on a non-prod database to test and tune, before replicating the entire process in Prod. I only want to dump and load one table - you know the drill; red tape, bureaucracy, upper management, etc...

We can afford downtime for this....

Database Startup Parameters:
Firstly, i guess would be; does it really need anything at all?
Secondly, i've changed about 400 files this week, dumped and loaded more tables than minutes ive slept, so be kind on the absolute garbage parameters ive just copied from the latest .pf file that i used. Lets just agree that anything that is nonsense; then that was advised from Mr. Paul K (Sorry Paul)

-db G:\DB\Test
-n 30           
-B 26214400
-L 5000000     
-T D:\Temp
-spin 10000
-Mm 16384
-omitLgMsgs "14780,10032"
-prefetchDelay
-prefetchPriority 100
-prefetchNumRecs 9999
-prefetchFactor 100

Truncate BI

Binary Dump (Multi-User Mode)

Binary Load (Multi-User Mode)

IdxBuild



Extra credit; Dumpspecified.....

We have a table in another database which is 2TB in size containing 7.8 Billion records.

On a test table my first dumpspecified missed out 13 of 495m records. Not Sure why....

My plan is to dump out this table over a 2-6 month period using DS to get all records before date 01/01/2030 for example, then when were ready to switch bring the remaining records across. So all dates between 01/01/2030 when i get sign off for the hardware and business sign off, and 01/01/2045 when we get full approval. Date jokes aside, in a window like this, i'd be best redoing a DS for dates from 01/01/2030 until "Date Of Go Live" - 1, to minimise downtime, does that sound feasible?

The Dump:

I managed to create some decent DS scripts to thread the process, heres a sample of the 21;

[CODE]start "" /REALTIME "C:\Progress\OpenEdge11.6\bin\_proutil.exe" G:\DB\Test -C dumpspecified "DBData.ReqId" LT "36578232" "F:\Dump\1\" -preferidx ReqIdx
start "" /REALTIME "C:\Progress\OpenEdge11.6\bin\_proutil.exe" G:\DB\Test -C dumpspecified "DBData.ReqId" GE "36578232" AND LT "108003599" "F:\Dump\2\" -preferidx ReqIdx

[/CODE]

The Load:

Am I right in it being a single thread sequential load...well for my one table?/area?

The Proof of Research | Potential Small Signs of some Neurodevelopmental Condition
I don't like to forget what i didnt quite get completed, so i'll come back to it...

 

[Rob Fitzpatrick]

I am not going to try to tackle all of this at once.

Existing Structure File

d "DataArea":7,64;1 G:\DB\Test_7.d1 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d2 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d3 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d4 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d5 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d6 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d7 f 16777216
d "DataArea":7,64;1 G:\DB\Test_7.d8

This is not the existing structure file; it is an excerpt from the existing structure file. This area has a size of 112 GB, plus whatever may be in the variable extent, if anything. You say the database size is approximately 2 TB. Is there a reason why we can't see the rest of your current and new structures?

So this is why we are midway into a OE12.8.9 migration and why were upgrading from Windows 2012/2016 to 2019, then 2022 all in the next 12 months.

Windows Server 2022 Lifecycle​

Windows Server 2022 follows Microsoft’s Fixed Lifecycle Policy, which includes two phases: Mainstream Support and Extended Support.​

​

Key Dates​

• Release date: August 18, 2021
• End of Mainstream Support: October 13, 2026
• End of Extended Support: October 14, 2031

These dates apply to all editions:​

• Standard
• Datacenter
• Datacenter: Azure Edition
• Essentials

​

What each phase means​

Mainstream Support (until Oct 13, 2026)​

Includes:​

• Security updates
• Feature updates
• Non‑security fixes
• Design changes
• Complimentary support (depending on licensing)

Extended Support (Oct 14, 2026 → Oct 14, 2031)​

Includes:​

• Security updates only
• Paid support options
• No new features or non‑security fixes

After October 14, 2031, Windows Server 2022 receives no updates at all, including security patches.​

​

Mainstream support for your target OS, Server 2022, ends later this year. Why not change that to Server 2025 at this point? OpenEdge 12.8 is certified on it. Its dates are November 2029 for end of mainstream support and November 2034 for extended support.

Prostrct add/addonline
prostrct ADD docdb docdbadd_DocBatchData2.st -validate
prostrct ADD docdb docdbadd_DocBatchData2.st -validate
prostrct ADD docdb docdbadd_DocBatchData2.st -validate
prostrct ADD docdb docdbadd_DocBatchData2.st -validate

I don't know what to make of this.

Bonus point if youve worked it out already...oh and i dont mean the glaringly obvious....the other ticking timebomb.

Getting the right advice on solving a problem starts with providing a clear and complete problem description. I suggest you dial down the snark and dial up the clarity and detail. Remember that you're getting free help and people's patience tends to deplete more quickly when you make the act of helping you more difficult and time-consuming.

I assume the "time bomb" you are referring to is area 7, where the current physical size of the area is at least 112 GB and the maximum size possible is 128 GB.

New areas to be added

d "Data:DBD":106,16;512 G:\DB\Test_106.d1 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d2 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d3 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d4 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d5 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d6 f 33554432
d "Data:DBD":106,16;512 G:\DB\Test_106.d7

I assume this is the target area for one large, fast-growing table. I don't know whether your RPB choice and extent configuration are appropriate without knowing anything about that table.

d "Index:DBD":206,1;64 G:\DB\Test_206.d1 f 16777216
d "Index:DBD":206,1;64 G:\DB\Test_206.d2

I advise against using RPB 1 for index areas. That is obsolete guidance from the era of Type 1 areas, an effort to conserve dbkeys. That is unnecessary in Type 2 areas.

Remember that an "index area" is a DBA aspiration, not a database-enforced setting. If someone drops a fast-growing table or LOB into such an area, the area will grow extremely quickly, causing an acute maintenance problem and hurting your caching efficiency. I suggest RPB 128 instead, assuming you stay with the 4 KB block size. Otherwise, use 256.

Speaking of which, I recommend you take this opportunity to move to an 8 KB block size. With 8 KB blocks, you have greater caching efficiency, less block overhead, and greater index compression. If you do change to 8 KB blocks, be sure to re-evaluate your RPB choices accordingly, along with -B/-B2 values.