Progress DBA Best Practice

[mattk]

Hi there,

Forgive me if I have posted this in the wrong place but I was wondering if there were any documents available about best practice when it comes to adminstrating Progress databases? Things such as:

• How often to do dump and load to maintain peak database performance.
• How often to do an index check/rebuild.
• How often to check the integrity of your backups by applying them to a test server.
• How often to run statistics and dbanal tools to monitor performance

I realise that the majority of these tasks would be dependent on what version of Progress we are running, database size and hardware limitations, however any help or pointers that anyone could provide would be much appreciated.

Thanks and regards.

 

[TomBascom]

Hi there,

Forgive me if I have posted this in the wrong place but I was wondering if there were any documents available about best practice when it comes to adminstrating Progress databases?

There are many kbase articles and a great deal of folklore. Much of it should be ignored.

How often to do dump and load to maintain peak database performance.

Never. If you're dumping and loading for performance reasons you're doing something wrong.

Now that I've said that... it may very well be a short term fix to certain problems while the long term solution is being queued up. But never mistake it for "the right thing".

The main good reason to dump and load is to take advantage of new features of the storage engine. For example you need to d&l in order to move from type 1 storage areas to type 2 areas.

How often to do an index check/rebuild.

See above :awink:

Sometimes an online index compact might be helpful. Basically you want to keep an eye on the "index utilization" column in your regular dbanalysis reports.

How often to check the integrity of your backups by applying them to a test server.

Always.

The real best practice here is to be continuously rolling forward your after-image files against the most recent backup. That way you know that the backup is good and that roll-forward recovery is working.

A "worst practice" would be to run your database with after imaging disabled.

How often to run statistics and dbanal tools to monitor performance.

Continuously.

For a really good solution to this problem I suggest ProTop (which is free) and DBAppraise/ (which is not).

 

[Casper]

Never. If you're dumping and loading for performance reasons you're doing something wrong.

Interesting. Do you think that also counts if you're still on V9. We are moving all our customers to 10.1B (next year that will be 10.1C). In version 9 we did a dump/load every say 1 or 2 years in order to get a much more organised database (So, hate to admit, but purely for performance reasons, felt like the right thing...).
Are there other ways in version 9 to avoid scattering?
(Hmm, somehow I guess the answer is seperate storage area's for large tables....)

And how to handle utility % of indexes without doing an index rebuild?

I'm now working on a more thorough dump/load procedure to make full advantage of type II storage area's. And more area's to get better block filling by taking more advatage of rpb. I was more of a 256 rpb kind of person (;-)) which by the way worked out fine in version 9...

tx,

Casper

 

[TomBascom]

You're right, for v9 lots of areas is the cure.

Each table can only be stored in a single physical order -- making records be in order for one sort will almost certainly put them out of order for another. So unless you have a single very dominant record ordering there is little, if any, value to trying to maintain a particular order. If you do have a dominant order then you want to make sure that your primary index reflects that. And if your data is in a dedicated storage area you're done -- dump & load isn't going to buy you anything (once you get to that happy state).

If you have multiple sort orders then you need appropriate indexes. Which are going to be best supported by a dedicated data area or, in OE10, a type 2 area.

Personally I don't find much advantage to index rebuilds. Yes, I like to keep the utilization% under control which is why I kept the door open a bit there but, in reality, I haven't seen that it makes much difference.

 

[Casper]

Thanks for clarifying that. We have some 100 customers. Hope to get them in this happy state as soon as possible.

 

[mattk]

Thanks for your thoughts guys this has proved very useful