OpenEdge 10.1A database settings

[baddos]

We have a progress database about 600Mb that is running on a 3ghz quad core machine with 2gb of ram on Windows 2003.

What would be some good settings to adjust to give progress more memory, because it appears the process is only using about 25mb of ram and performance is quite terrible as it just constantly hammers the disk with reads.

 

[baddos]

The database was setup using the Progress Explorer Tool using the default settings.

 

[TomBascom]

10.1A really ought to be upgraded. It is getting long in the tooth. 10.1C is the current stable release and has significant improvements over 10.1A, 10.2A is the "bleeding edge".

Tuning is a big topic but the -B parameter controls the size of the buffer cache. The default value is quite small. You have 2GB of RAM; if this is the only database on the server and if it is using 4K database blocks (which is the default for Windows) then -B 125000 would use 600MB of RAM. There is no point in making larger than the db

You might want to consider some consulting help to get your entire tuning problem addressed as well as to obtain some training and mentoring.

 

[baddos]

Awesome thanks for the help. I added that argument to the defaultConfig and it's using more memory.

This database is used by an application we have made by another company, so I don't much about it's layout and such.

 

[baddos]

I can't upgrade 10.1a to a new version. The application that uses this database was made with progress and is compiled to that version. Until I get a new version of the software I have to run this version of progress, at least that is what my vendor told me.

 

[TomBascom]

I can't upgrade 10.1a to a new version. The application that uses this database was made with progress and is compiled to that version. Until I get a new version of the software I have to run this version of progress, at least that is what my vendor told me.

They're confused.

 

[baddos]

I've added the -B command and the process is using more memory, however it doesn't seem to be doing anything usefull. I ran a SELECT Count(*) query on a table and it continued to take 20 minutes before and after the change, even after running it multiple times.

 

[TomBascom]

Tuning is a complex topic.

Is your database license a "Workgroup" license or an "Enterprise" license?

If it is Enterprise have you set the -spin parameter?

 

[tamhas]

The more information, the better. Despite your thinking "all default", it would be more useful to actually list all the parameter settings. You should also tell us something about disks, layout, extents, storage areas, etc.

As for the vendor lock-in, if you don't have source, then your options are limited, but you can still campaign with the vendor to get you more current. There is no good reason for them to keep you in the dark ages.

 

[baddos]

The server has a single raid5 disk using 4 spindles I beleive with a hot spare.

The progress license is workgroup.

The settings are default based upon what the progress explorer tool sets. They are as follows:
# Blocks in DB Buffer: 0
# Hash table entries: 0
# Lock table entries: 8192
Maximum users: 20
Maximum servers: 4
Other server arguments: -B 125000
Employ direct I/O: Unchecked (I have tested with this checked, has no noticable affect)

 

[baddos]

Also, several months back our vendor tried to increase performance of the database by creating extents. dbname.d1 through dbname.d75 all at exactly 102,400 kb. After doing this performance seemed to remain about the same. It seems crazy to me since they are all on the same disk. Before splitting the database up into those files, it was about 600mb iirc. Is there a way to check to see how much space the database is actually using inside all of those files?

 

[TomBascom]

Making lots of extents was a technique that had a limited amount of usefulness 20 years ago. It worked because some operating systems had some very serious limitations that no longer exist. Even on Windows.

What your vendor should have done was to create properly configured storage areas. That will result in more extents than a default configuration but it is very unlikely that they would all be the same size.

For more detailed ideas regarding storage areas read this article. The article was written for Progress v9. The ideas still work with OE10 but type 2 storage areas provide still more benefits.

You can get the information that you seek regarding utilization of your storage areas and extents by running a dbanalysis report and/or writing some custom schema/vst reports. Or you could obtain a copy of ProTop. The GUI version is much nicer but you will need a 10.2A client to run it (the db can be any point release of v9 or OE10). ProTop has answers to those sorts of questions already built in.

Your other big problem is that you have a "workgroup" license. This license does not support most of the really useful tuning options. Worse yet prior to 10.1B there are very serious performance issues when running a WG db on a multi-core server. The WG db uses a resource locking algorithm which assumes a single CPU -- it performs spectacularly poorly on multi-core systems (nobody anticipated the ubiquity of multi-core systems when v9 and 10.1A were still current products). There is a partial fix for the problem in 10.1B. No amount of tuning will cure this problem.

 

[tamhas]

Wasn't there a fix by locking Progress on one CPU? Or something?

What, no harsh words about the RAID 5? Or are there just too many other things wrong to justify getting that far?

Sure sounds like another case of a VAR providing shockingly bad guidance on equipment and setup. Why companies that don't have the skills don't hire people like you to provide direction, I don't know. Heck, if they worked alongside you a couple of times, they could probably do most of it themselves after that with just an occasional question. One would think that VARs cared more about their customer's success!

 

[baddos]

Is there a way to move these extents back into 1 file or a couple of large files? The database in question only has 1 table, but that table currently has a little over 41 million rows.

 

[baddos]

Wasn't there a fix by locking Progress on one CPU? Or something?

What, no harsh words about the RAID 5? Or are there just too many other things wrong to justify getting that far?

Sure sounds like another case of a VAR providing shockingly bad guidance on equipment and setup. Why companies that don't have the skills don't hire people like you to provide direction, I don't know. Heck, if they worked alongside you a couple of times, they could probably do most of it themselves after that with just an occasional question. One would think that VARs cared more about their customer's success!

The vendor is far from helpfull getting any knowledge let alone what should have been setup several years ago when the system was implemented. The whole system is a joke beyond belief, if I didn't work here I'd be laughing for days.

 

[tamhas]

Yes, you can do it with a backup and restore using a void database created from a new structure file.

I hope you have more than one database in the application ... an application with only one table seems like it would be somewhat boring.

With 41M records it might also be interesting to do some analysis.

 

[tamhas]

I'd be tempted to bring in a good consultant like White Star and bill the VAR.

But, the consultant can't fix the multi-core workgroup problem.

 

[TomBascom]

Wasn't there a fix by locking Progress on one CPU? Or something?

Sort of. If you can disable all but one CPU or lock the process onto a CPU then that can help a lot. I know how to do it with Linux, but not Windows. And it sort of cripples the rest of the system...

What, no harsh words about the RAID 5? Or are there just too many other things wrong to justify getting that far?

Sure sounds like another case of a VAR providing shockingly bad guidance on equipment and setup. Why companies that don't have the skills don't hire people like you to provide direction, I don't know. Heck, if they worked alongside you a couple of times, they could probably do most of it themselves after that with just an occasional question. One would think that VARs cared more about their customer's success!

My guess is simple ignorance. They don't even know that they don't know what they are doing.

 

[baddos]

How would I go about performing a progress backup and restore? Got any links?

Right now how the database is backed up, is essentially the progress service is stopped and the files are backuped up.

Also, something to note about the records in this table. The software only stores 999 days of records, so when records reach 1000 days I'm assuming the software then deletes them. I'm wondering if these database files are heavily fragmented as a result of this daily inserting and deleting.

 

[TomBascom]

You want to read up about "probkup" and "prorest".

The documentation should be installed along with Progress. Go to the start menu, find "OpenEdge", choose "help", select the "index" tab, type "probkup" and you should be golden. It's pretty easy to make a backup. To restore it into a different structure you need to understand how to create a structure file describing the extents that you want to have. There should be a .st file in the database directory. Take a look at it -- combining the extents should be moderately obvious.

As Tamhas mentioned, a few days of consulting/mentoring could turn your system around