Will BI cluster size change on Live invalidate existing AI ?

[methyl]

Progress 8.3B and 8.3D. HPUX 11.00 and 11.11 . Multiple databases, multiple computers. Largest database 26 Gb with 8k block size; -B=80000 blocks; 3*APW; 1*BIW; 1*AIW.

Checkpoint interval is down to < 10 seconds at peak times on largest database.
I believe I need to increase BI cluster size from 512 kb to say 4096 kb.
Have a Disaster Recovery database which we are feeding AI files.

1) Expected syntax
proutil mydb -C truncate bi -bi 4096
2) If I change the BI cluster size on the Live database, do I need to stop AI on Live and re-create the Disaster Recovery database?
3) Is the change to the BI cluster size permanent, or does it have to be in the command line every time we truncate BI?

 

[Krokodile]

Syntax on Point 1 is correct.
In addition to this You can also think about increasing number of clusters by using proutil db[size=+0]-[/size]name [size=+0]-[/size]C bigrow n
By default cluster# are 4 but you can see how much you want.

#2 I believe to change cluster size your DB must be SHUT; AI doesn't have to be. I'm not sure.
#3 Change in Cluster size or BI Block size is permanent if you don't restore your old Backup DB on to new one.

Do think about increasing Cluster numbers because when these cluster fills Checkpoint occurs, also if you have enough RAM then think about bumping -B to 125000 & then use -HASH parameter in the same line. Value of -HASH shud be 30-35% of what you used for -B. Default is 25%.

Do let me know what you did here...

 

[methyl]

BI Cluster size change successful

Hi Krokodile

We finally changed the 27 Gb Progress 8 database on Sunday to coincide with a schema change. The order or events was:

1) Shut down LIVEDB database
2) Stop AI on the LIVEDB
rfutil LIVEDB -C aimage end
Also stop auto-copying AI files to the DR computer.
3) Change the BI cluster size on the LIVEDB
proutil LIVEDB -C truncatebi -bi 4096
4) Full Progess backup of the LIVEDB (40 mins to write 17 Gb backup)
We used a scripted "probkup" which creates 800 Mb chunks and auto-answers the filename questions.
5) Delete (prodel) then re-create Disaster Recovery (DR) database
prostrct create DRDB -blocksize 8192 (60 mins to create 27 Gb)
6) Copy the LIVEDB backup to the DR computer (25 mins at 100 mbits/s)
7) Full Progress restore into the DRDB (40 mins to read 17 Gb backup)
We used a scripted "prorest" which auto-answers the filename questions.
6) Start AI on the LIVEDB
rfutil LIVEDB -C aimage begin
This truncated the already-truncated BI file.
If we wanted to "bigrow" the BI file, this would have been the moment.
7) Start up LIVEDB
8) Start auto-copying AI files to the DR computer again.

Many thanks to all on this forum who have posted their experiences and who know the actual commands to tune mature Progress 8 databases against the broad recommendations in Gus Bjorklund's performance white papers.

The BI checkpoint interval (promon/R&D/3/4) has changed from 3 secs during prime shift to about 5 mins at peak with an occasional peak of quick flushes! We've had less than 100 checkpoints today instead of 2000-3000. One side effect of the BI file growing to 200 Mb during one horrendous program run which generated 50 million record locks in 20 mins.

We'll look at "proutil bigrow" soon, bearing in mind the rule that "bigrow" unit is in clusters so you must know your cluster size. To find out the actual size of the BI in kilobytes we implemented VSTs and used a Progess program to display the value of "_Logging-BiLogSize".

We debated the actual value of BI cluster size and scoured the internet to find out what other DBAs do. There is a vague warning in the Progress 8 Systems Administration Guide p14-17 about "Increased disc space usage for the BI file". All we DBAs really want to know is whether the BI file will grow to more than the safe maximum of 800 Mb. There wasn't enough good voodoo to risk a cluster size of 16 Mb so we split the difference on Gus's 1997 recommended range of 1-8 Mb and settled on 4 Mb.

Now there are some gaps in the database BI and AI activity we can tune some of the usual suspects such as "-spin" and be able to see the difference.

Thanks DBAs.

Methyl