Advice on AI implementation

[MarkW]

Hi,
First time posting on here so "hello" and I hope this is the right forum area for this question. Just so you know, I'm no expert regarding Progress but have picked up a little bit of knowledge over the last couple of years. My main role is server admin not DBA.
We are deep into a project to migrate our Progress v11.6 databases off HP UX onto RHEL 8 and upgrading to Progress v12.2 at the same time.
Currently we have AI enabled on 6 of our databases and use 12 variable length AI extents for each db, managed with scripts and a complicated cron schedule.
Switching happens at various intervals ranging from every 10 minutes to every 2 hours. I think a DBA set this up based on system usage many moons ago.
I would like to move to using fixed length AI extents (as advised by a Progress consultant) and manage them with the Progress AI archiver.
What I'm struggling with is working out how big to make my fixed length extents. Using the formula in the Progress docs I get a values between 16MB and 40MB depending on when I check the checkpoint values.
As a reference point our variable AI extents get up to almost 600MB during the day when we switch every 2 hours.
I guess my question is, what is the impact of either under or over sizing the fixed length extents? Is there a performance hit switching a 600MB extent compared to a 50MB one? Or is there a worse one switching small extents very frequently?
I should probably add that our storage is all SAN-based (I know, I know) and connected using fibre-channel.

Any help on this would be greatly appreciated as this is a good opportunity to get things like this right.
Regards,
Mark.

 

[TomBascom]

Under size them and you will be switching and archiving frequently and run the risk of filling them all up and stalling or crashing.

Frankly, I strongly disagree with the suggestion that people should use fixed extents. Even on crappy SAN storage. Yes, if you benchmark it you can detect a performance difference. No, the difference is not worth the pain of dealing with fixed extents except in very, very rare circumstances (which I am not hearing any hint of in this discussion).

Variable extents are much, much easier to manage. Start there. Monitor how long the automatic archive process takes. If, during peak load periods, you are managing to switch and archive within a minute or two then you’re fine. Don’t worry about it. If it takes longer than that, or if you see after-imaging related latch waits, then bring your evidence and we can discuss that problem (hint: get rid of the SAN).

 

[jdpjamesp]

As a DBA who has had many years of struggle and heartache as a result of fixed length extents, I wholeheartedly agree with Tom. Unless there is a very valid reason to use fixed extents, don't.

 

[Rob Fitzpatrick]

Hi Mark, welcome to ProgressTalk. Some good things here and some points of concern.

We are deep into a project to migrate our Progress v11.6 databases off HP UX onto RHEL 8 and upgrading to Progress v12.2 at the same time.

Good. I assume you are upgrading to 12.2 because it is an LTS release. From a timing perspective, you should note that 12.8 will be released in the second half of this year and it will be the next LTS release. According to the Life Cycle Policy Guide, when 12.8 is released, 12.2 will have three more years of support in Sunset Phase before it moves to retired. So fairly soon after your project completes, you may want to think about starting the next upgrade project.

Currently we have AI enabled on 6 of our databases

Are these different parts of a single application? Different applications? All production?

Given the value of the data and the rate at which it changes, which we don't know, is it appropriate for the other six databases to not have AI enabled?

Switching happens at various intervals ranging from every 10 minutes to every 2 hours.

I think a DBA set this up based on system usage many moons ago.

The switching interval was set up a long time ago for reasons that you don't know. I'd say that is justification to revisit this decision.

The switching interval should be a function of your transaction activity and your business' RPO (recovery point objective). In other words, in a disaster, how much data loss can the business tolerate? It is expressed in units of time (seconds/minutes/hours). It is quite possible that the business needs or expectations have changed over the years and the decision made by that DBA is no longer appropriate.

Start by having this conversation with the business stakeholders and have them provide the RPO (in writing!). Then you will know what your recovery plan needs to achieve, and whether a log-switching strategy is even appropriate.

 

[MarkW]

Thanks for all your replies and apologies for not getting back sooner.
I'll go back to variable length AI extents after reading your advice as anything that avoids hassle/problems is a bonus for us.
The 6 DBs with AI enabled are part of a single application which is the heart of our business. The other DBs are peripheral and not critical.

In terms of RPO this is something that has never been formalized but I believe has been under discussion for some time as part of a DR strategy.

 

[Rob Fitzpatrick]

I'll go back to variable length AI extents after reading your advice as anything that avoids hassle/problems is a bonus for us.

It sounds like you have been doing manual switches via shell scripts. You should think about using the AI File Management Daemon as well. And either way, it is important to monitor your AI files to ensure switching is happening as expected. If it is not, and it is not detected, this can have severe impacts for the business.

In terms of RPO this is something that has never been formalized but I believe has been under discussion for some time as part of a DR strategy.

I find business people often just assume that all data is magically protected, until there is an outage and data is lost. Surprise! It is important for them to understand that they, not you, are responsible for choosing RPO. And based on that, you decide what approach (e.g. log-shipping versus OpenEdge Replication Plus) is best tactically.