Server Performance

[NoProgress]

Hi - I've gotten involved with an old Progress system that is currently in use at > 200 facilities; it's Progress v9.1E04 (I know, I know), and that's the version that we're going to be stuck with until admin decides what software to upgrade us to in the next year or two....In the mean time we have to keep users happy. The old db admin left the company about five years ago, so there has been virtually no major db maintenance besides splitting the db as it gets larger and truncating the BI. Relatively speaking, our db's are fairly small (usually between 2-5 GB). Of course, the complaint from the field is "slowness". Attached is proutil dump from a tabanalys; it's fairly representative.

The typical server runs MS Server 2003 R2, Standard Edition, dual or quad processor, 4GB memory. Admin will not upgrade to Enterprise license so we are stuck at 4GB memory max.

My knowledge of Progress is slowly growing based on what I can find online - my first line of attack is going to be a dump and bulkload. Many of the larger tables show a scatter factor of over 4... I am constrained by the hardware out in the field already; one mirrored hard disk, so can't change storage location. (I've seen talk in the forums about type 1 & 2 storage areas, but never found a definition...)

It's probably obvious from the table names that this is the Vista Keane application - typical model is one local server, with 1-5 users on their pc's. I've found recently that when user's complain of sluggishness that I can often run the process in 1/2 the time when I run it directly on the server, and not from their pc. Users have 100MB to 1GB connections to the server. Do we need to tweak client settings? I really don't think any parameters have been changed for years, so maybe there are some things we should be considering.

Here is the dump from the ra.lg log; are there any obvious parameters that we should consider changing? Due to limited resources, no major system or db changes will be possible - I'll be doing get just to get resources assigned just to do a D&L process on some of the worst servers

Wed Mar 07 05:00:08 2012
05:00:08 BROKER 0: Multi-user session begin. (333)
05:00:08 BROKER 0: Begin Physical Redo Phase at 8656 . (5326)
05:00:09 BROKER 0: Physical Redo Phase Completed at blk 6528 off 1726 upd 0. (7161)
05:00:09 BROKER 0: Started for ra001facility using TCP, pid 5184. (5644)
05:00:09 BROKER 0: PROGRESS Version 9.1E on WINNT. (4234)
05:00:09 BROKER 0: Server started by SYSTEM on CON:. (4281)
05:00:09 BROKER 0: Started using pid: 5184. (6574)
05:00:09 BROKER 0: Physical Database Name (-db): d:\lsp\ra\db\fac001\ra. (4235)
05:00:09 BROKER 0: Database Type (-dt): PROGRESS. (4236)
05:00:09 BROKER 0: Force Access (-F): Not Enabled. (4237)
05:00:09 BROKER 0: Direct I/O (-directio): Not Enabled. (4238)
05:00:09 BROKER 0: Number of Database Buffers (-B): 168. (4239)
05:00:09 BROKER 0: Maximum private buffers per user (-Bpmax): 42. (9422)
05:00:09 BROKER 0: Excess Shared Memory Size (-Mxs): 16390. (4240)
05:00:09 BROKER 0: The shared memory segment is not locked in memory. (10014)
05:00:09 BROKER 0: Current Size of Lock Table (-L): 100000. (4241)
05:00:09 BROKER 0: Hash Table Entries (-hash): 43. (4242)
05:00:09 BROKER 0: Current Spin Lock Tries (-spin): 0. (4243)
05:00:09 BROKER 0: Number of Semaphore Sets (-semsets): 1. (6526)
05:00:09 BROKER 0: Crash Recovery (-i): Enabled. (4244)
05:00:09 BROKER 0: Database Blocksize (-blocksize): 4096. (6573)
05:00:09 BROKER 0: Delay of Before-Image Flush (-Mf): 3. (4245)
05:00:09 BROKER 0: Before-Image File I/O (-r -R): Reliable. (4247)
05:00:09 BROKER 0: Before-Image Truncate Interval (-G): 0. (4249)
05:00:09 BROKER 0: Before-Image Cluster Size: 131072. (4250)
05:00:09 BROKER 0: Before-Image Block Size: 8192. (4251)
05:00:09 BROKER 0: Number of Before-Image Buffers (-bibufs): 5. (4252)
05:00:09 BROKER 0: BI File Threshold size (-bithold): 0.0 Bytes. (9238)
05:00:09 BROKER 0: BI File Threshold Stall (-bistall): Disabled. (6552)
05:00:09 BROKER 0: After-Image Stall (-aistall): Not Enabled. (4254)
05:00:09 BROKER 0: Number of After-Image Buffers (-aibufs): 5. (4256)
05:00:09 BROKER 0: Storage object cache size (-omsize): 1024 (8527)
05:00:09 BROKER 0: Maximum Number of Clients Per Server (-Ma): 5. (4257)
05:00:09 BROKER 0: Maximum Number of Servers (-Mn): 5. (4258)
05:00:09 BROKER 0: Minimum Clients Per Server (-Mi): 1. (4259)
05:00:09 BROKER 0: Maximum Number of Users (-n): 21. (4260)
05:00:09 BROKER 0: Host Name (-H): FAC22119S. (4261)
05:00:09 BROKER 0: Service Name (-S): ra001facility. (4262)
05:00:09 BROKER 0: Network Type (-N): TCP. (4263)
05:00:09 BROKER 0: Character Set (-cpinternal): ISO8859-1. (4264)
05:00:09 BROKER 0: Parameter File: Not Enabled. (4282)
05:00:09 BROKER 0: Minimum Port for Auto Servers (-minport): 3000. (5648)
05:00:09 BROKER 0: Maximum Port for Auto Servers (-maxport): 5000. (5649)
05:00:09 BROKER 0: This broker supports both 4GL and SQL server groups. (8865)
05:00:09 BROKER 0: Created shared memory with segment_id: 8781824 (9336)
05:15:00 SRV 1: Started on port 3000 using TCP, pid 4160. (5646)

Thanks in advance for the assistance!

 

[TomBascom]

Hi - I've gotten involved with an old Progress system that is currently in use at > 200 facilities; it's Progress v9.1E04 (I know, I know), and that's the version that we're going to be stuck with until admin decides what software to upgrade us to in the next year or two....In the mean time we have to keep users happy.

Progress applications only go away when the company gets bought by someone who already uses something else. And then the odds aren't all that bad that the Progress app will live.

The old db admin left the company about five years ago, so there has been virtually no major db maintenance besides splitting the db as it gets larger and truncating the BI.

From the looks of thing's he wasn't doing anything prior either...

Relatively speaking, our db's are fairly small (usually between 2-5 GB). Of course, the complaint from the field is "slowness". Attached is proutil dump from a tabanalys; it's fairly representative.

That's interesting but not really terribly relevant to performance.

The typical server runs MS Server 2003 R2, Standard Edition, dual or quad processor, 4GB memory. Admin will not upgrade to Enterprise license...

When you say "Enterprise" are you referring to your Progress license being "Workgroup" and your "admin" (management?) being unwilling to upgrade?

... so we are stuck at 4GB memory max.

Do you mean that you are stuck because Windows won't go higher? Or do you somehow think that 32 bit Progress on Windows has a 4GB limit?

My knowledge of Progress is slowly growing based on what I can find online - my first line of attack is going to be a dump and bulkload. Many of the larger tables show a scatter factor of over 4...

Dumping and reloading would probably be beneficial. But "bulkload" is not especially fast. You probably ought to look into a binary load. OTOH if the databases are small it probably doesn't matter. On the third hand if there are 200 of them you will want to automate this process.

In any event -- if you reload back in to the same structure you probably won't get nearly as much benefit out of it as if you reloaded into a more optimized structure.

If you do nothing else at least reload back into a two storage areas -- one for data and another for the indexes. Get your data out of the schema area.

I am constrained by the hardware out in the field already; one mirrored hard disk, so can't change storage location. (I've seen talk in the forums about type 1 & 2 storage areas, but never found a definition...)

You only have the one mirrored disk but that doesn't mean that you cannot make things better.

Type 2 areas are available starting in OpenEdge 10. Type 2 areas differ from Type 1 areas in that a block can only contain records from one table. In a Type 1 area blocks contain records from many tables all mixed together. This sometimes helps to optimize "space" but is not usually very good for performance.

But even a Type 1 area is better than just leaving everything in the schema area. If you break up your data and assign it to areas based on average record size and activity levels you can make some major improvements.

You should read this: http://dbappraise.com/ppt/sos.pptx

It's probably obvious from the table names that this is the Vista Keane application - typical model is one local server, with 1-5 users on their pc's. I've found recently that when user's complain of sluggishness that I can often run the process in 1/2 the time when I run it directly on the server,

This is typical. Actually that's a somewhat modest improvement. If you eliminate -S from the client startup parameters the session will use shared memory rather than TCP/IP and it will be much, much faster. But you, obviously, have to be on the same server for that to work.

... and not from their pc. Users have 100MB to 1GB connections to the server. Do we need to tweak client settings?

Probably. But you haven't provided the client parameters so it is difficult to say.

I really don't think any parameters have been changed for years, so maybe there are some things we should be considering.

Actually this looks like an "out pf the box" install. I suspect that nothing has been changed...

Here is the dump from the ra.lg log; are there any obvious parameters that we should consider changing?

Yes.

Due to limited resources, no major system or db changes will be possible - I'll be doing get just to get resources assigned just to do a D&L process on some of the worst servers

So you want help but you aren't going to take any suggestions that actually require work? At least you're being honest about it...

Wed Mar 07 05:00:08 2012
05:00:08 BROKER 0: Multi-user session begin. (333)
05:00:08 BROKER 0: Begin Physical Redo Phase at 8656 . (5326)
05:00:09 BROKER 0: Physical Redo Phase Completed at blk 6528 off 1726 upd 0. (7161)
05:00:09 BROKER 0: Started for ra001facility using TCP, pid 5184. (5644)
05:00:09 BROKER 0: PROGRESS Version 9.1E on WINNT. (4234)
05:00:09 BROKER 0: Server started by SYSTEM on CON:. (4281)
05:00:09 BROKER 0: Started using pid: 5184. (6574)
05:00:09 BROKER 0: Physical Database Name (-db): d:\lsp\ra\db\fac001\ra. (4235)
05:00:09 BROKER 0: Database Type (-dt): PROGRESS. (4236)
05:00:09 BROKER 0: Force Access (-F): Not Enabled. (4237)
05:00:09 BROKER 0: Direct I/O (-directio): Not Enabled. (4238)
05:00:09 BROKER 0: Number of Database Buffers (-B): 168. (4239)

This is your first big win. That is the default value. 168 x 4k = a mere 672KB to cache db requests. Your phone had that much memory in it 20 years ago.

Try running MS SQL Server with less than 1MB of memory...

Change it to 100000. That will be 400MB. Which is still awfully small given that you have 4GB available on your servers. You can probably get it up over 300000 before you start getting errors about exceeding the allowable shared memory (the actual limit varies somewhat from machine to machine based on lots of factors, a certain amount of trial and error is typical).

-B is your biggest knob to turn. Adding memory to -B reduces IO operations as the square root of the size of the increase. To cut IO in half multiply -B x 4. To get 1/4th the IO multiply by 16... 100,000 is approx 600x 168 so you can expect about 1/24th the IO that you currently generate.

05:00:09 BROKER 0: Maximum private buffers per user (-Bpmax): 42. (9422)
05:00:09 BROKER 0: Excess Shared Memory Size (-Mxs): 16390. (4240)
05:00:09 BROKER 0: The shared memory segment is not locked in memory. (10014)
05:00:09 BROKER 0: Current Size of Lock Table (-L): 100000. (4241)
05:00:09 BROKER 0: Hash Table Entries (-hash): 43. (4242)
05:00:09 BROKER 0: Current Spin Lock Tries (-spin): 0. (4243)

This is either a "Workgroup" license or a very badly managed Enterprise license. I'll assume workgroup.

Among other things that means that you should not be running it on a multi-core system if you can at all avoid that.

If you cannot avoid running multi-core try to lock Progress onto a single CPU.

05:00:09 BROKER 0: Number of Semaphore Sets (-semsets): 1. (6526)
05:00:09 BROKER 0: Crash Recovery (-i): Enabled. (4244)
05:00:09 BROKER 0: Database Blocksize (-blocksize): 4096. (6573)
05:00:09 BROKER 0: Delay of Before-Image Flush (-Mf): 3. (4245)
05:00:09 BROKER 0: Before-Image File I/O (-r -R): Reliable. (4247)
05:00:09 BROKER 0: Before-Image Truncate Interval (-G): 0. (4249)
05:00:09 BROKER 0: Before-Image Cluster Size: 131072. (4250)

Because this is a Workgroup license you might want to set the bi cluster size smaller if users are complaining about periodic simultaneous "lockups".

05:00:09 BROKER 0: Before-Image Block Size: 8192. (4251)
05:00:09 BROKER 0: Number of Before-Image Buffers (-bibufs): 5. (4252)
05:00:09 BROKER 0: BI File Threshold size (-bithold): 0.0 Bytes. (9238)
05:00:09 BROKER 0: BI File Threshold Stall (-bistall): Disabled. (6552)
05:00:09 BROKER 0: After-Image Stall (-aistall): Not Enabled. (4254)
05:00:09 BROKER 0: Number of After-Image Buffers (-aibufs): 5. (4256)
05:00:09 BROKER 0: Storage object cache size (-omsize): 1024 (8527)
05:00:09 BROKER 0: Maximum Number of Clients Per Server (-Ma): 5. (4257)
05:00:09 BROKER 0: Maximum Number of Servers (-Mn): 5. (4258)
05:00:09 BROKER 0: Minimum Clients Per Server (-Mi): 1. (4259)
05:00:09 BROKER 0: Maximum Number of Users (-n): 21. (4260)
05:00:09 BROKER 0: Host Name (-H): FAC22119S. (4261)
05:00:09 BROKER 0: Service Name (-S): ra001facility. (4262)
05:00:09 BROKER 0: Network Type (-N): TCP. (4263)
05:00:09 BROKER 0: Character Set (-cpinternal): ISO8859-1. (4264)
05:00:09 BROKER 0: Parameter File: Not Enabled. (4282)
05:00:09 BROKER 0: Minimum Port for Auto Servers (-minport): 3000. (5648)
05:00:09 BROKER 0: Maximum Port for Auto Servers (-maxport): 5000. (5649)
05:00:09 BROKER 0: This broker supports both 4GL and SQL server groups. (8865)

Are you supporting ODBC connections? If you are then you should setup a dedicated SQL-92 broker instead of sharing it with 4GL users.

05:00:09 BROKER 0: Created shared memory with segment_id: 8781824 (9336)
05:15:00 SRV 1: Started on port 3000 using TCP, pid 4160. (5646)

Thanks in advance for the assistance!

 

[NoProgress]

Thanks for the reply, Tom. I've read some of the your articles online and they have been enlightening.

When I mentioned Enterprise I guess I wasn't clear - the servers are on Server 2003 Standard Edition, which has a software limit of 4GB. Windows Server 2003 Enterprise Edition of course has almost unlimited memory support, but admin will not be upgrading licensing in the foreseable future, since the next software package will probably be centralized and web-based.

Yes, it would be nice to automate this D&L process, but $50K for Pro D&L for isn't forthcoming anytime soon....

"So you want help but you aren't going to take any suggestions that actually require work? At least you're being honest about it..." Lol, I'm actually in the financial support team but do have a bit of liberty to do some testing and research. I try to put my computer science background to work every once in a while. The db operations group is "too busy" to do any research.

No, we don't use ODBC; users run a shortcut pointing to the network app location.

I'll definitely experiment with the -B parameter; I had experimented with it before but never with such a high value.

Thanks for the link - I'd love to move the data out of the schema area if possible.

 

[NoProgress]

Followup question - how to I force Progress to lock onto only one processor? Could I do that with the spin-lock parameter?

 

[cj_brandt]

The spin setting is a parameter only available on Enterprise version of the database. If you are using workgroup then spin is 0 on version 9 and below. There should be a showconfig icon in your progress folder off the windows start menu. That should display if your database is workgroup or enterprise. If you actually have an enterprise license, you'll get a big performance boost by changing spin from 0 to 1.

Do you use windows perfmon to collect stats ? Do you use the promon utility to look at disk reads ? I think you'll see a large drop in disk IO after you increase the -B parameter to at least 100,000 and a good performance boost. I would use 350k or 400k as the upper limit on the -B setting.

I don't think BravePoint's ProD&L utility is what you want to dump and load these databases. It is more focused on very large databases and requires user interaction. You just want a couple windows batch files that would dump out the data using the binary dump and then load the data with the binary load with the build indexes option.

You mentioned $50,000 isn't going to happen - I understand that. Would a couple thousand be an option though ? I think in 1 or 2 days someone could either remote in to a server or come on site and get a system properly tuned. Then you could use those notes and scripts to tune the rest. I think you'll be wondering around for a few months if you try and do this on your own - I've been in your shoes before....

 

[NoProgress]

Thanks for the reply - Yup, Workgroup 9.1E.

I've boosted the -B to 300000 on one facility today, and we'll see what kind of results we get. The database is pretty much the only app that runs on the server, so it's about time it gets its fair share of memory.

I've done a few test d&l's and am double-checking with our vendor to make sure I'm not missing any steps before running the process on a production machine. It only takes a couple hours, so that's not too bad, and I'm sure we could script it at some point...I just hate to script & forget such a critical operation.

Yes, an expert evaluation is something that would probably be good...

While we're on the topic of D&L - here's the steps that I've put together, and seems to work ok. This is based on a decade-old document I found online...any suggestions or red flags here?

Steps to run dump and load on a Keane database:

1. Re-index the database (all)
2. Dump all visible & hidden tables, create the Data Definition and bulk loader files, as well as the Sequences Current Values.
3. Create a new DB
4. If necessary, create multi-volume db from the ra.st file for large databases
5. Load the Data Definition file
6. Run the bulk load process
7. Re-index all as in step 1.
8. Initialize sequences by loading Sequences Current Values file (is this necessary?)

Thanks everyone for your valuable input!

 

[cj_brandt]

I would take a db backup as step 1.

If your data is stored in locations other than area 6 Schema Area, I would dump the data, truncate the area and then load the data back into the area. The data goes back into the same database so no worries about users, sequences. To me this is much easier.

Maybe post a table or database analysis.

 

[NoProgress]

Thanks - yeah, there is a nightly backup that is performed, I should have mentioned that, lol.

The table analysis is actually attached in the first post I made above...our data is, unfortunately, all in the Schema area. Since I'm not the software vendor, is there a reasonably easy way to move it to another area in one fell swoop? I know this could result in performance improvements as well.

 

[cj_brandt]

I would still run a backup before doing this work. You don't appear to be running after imaging so all data entered since the last backup would be lost if you had to restore from backup.

If I was doing this I would pick some off hours when system can be down.
1. Create an new area called data_area, you could even create an area for indexes as well.
2. Use tablemove to move all tables (smaller than 100mb) and indexes to the data_area and if desired index_area.
3. Use binary dump to dump the last remaining table data from schema area.
4. Delete all data from those tables - assuming the binary dump worked without error.
5. Use tablemove to move the empty tables from schema area to the new area.
6. Use binary load to load the data back into the new area. Remember to use the build indexes option with binary load, or separate idxbuild command.

I would do all this on a copy of the database to see about how long the binary dump and deleting all the data from the few large tables would take.

For extra credit...
Look at proutil -C mvsch command to shrink the schema area back down so it takes less space. Make sure you have a good backup before running, this utility has failed on me a few times.

 

[NoProgress]

Thanks for the info - I'll see what improvements I can implement.

 

[TomBascom]

Step 1 -- "reindex" won't help. It will just make the whole process take longer.
...
Step 8 -- Yes, reloading sequence values is necessary.

"mvschema" will beat the living crap out of the bi file. Don't do it unless the db is of trivial size. The last time I thought it might be a good idea it took 10x the db size for the bi. And it took all night. On a fairly small db.

You're looking for "processor affinity" re: locking a process to a CPU. It is usually only a big deal if the system is heavily loaded.

It really wouldn't be hard to create a "better" set of storage areas (perhaps not "perfect" but plenty good enough) and get a dump & load nicely scripted. Scripting the users and so forth isn't hard either. Certainly less than $50k... I know someone who does this on a professional basis -- see the signature line

 

[Rob Fitzpatrick]

Here is one option for reorganizing your structure. Note that there is no one way to design a database structure that is ideal. What is ideal for you is determined in part by information I can't access, and by how the database will be monitored and maintained, among other things. So take from it what you will.

I took your table analysis, parsed it into a .csv, and loaded it into Excel; see the attached workbook.

<tangent>
Rich, if you're reading this, *please* give us a CSV output option for dbanalys in 11.1. I don't ask for much!
</tangent>

Notes:

• In addition to the data in the tabanalys output, I added the following columns:
  • extra frags (fragments - records);
  • frag % (extra frags / records); I find the outliers jump out more readily than by looking at Progress' frag factor;
  • Ideal RPB: the recommended records-per-block setting for a storage area, based on Tom's formula: take the table's mean record size, add 20 bytes for record overhead, divide the DB block size by the prior sum, then choose the integral power of two that is higher than the resulting quotient;
  • data storage area: a suggested storage area for the table in question (you would create these in your structure file when creating the new DB). I put three tables in their own areas: charge and gltrx, because they are by far the largest tables, making up 70% of the record data (at least in this installation), and bill-data, because it is heavily fragmented, owing to its large records. If bill-data is in its own area you can increase its area toss limit (I think you can do this in v9, but you should check) to reduce fragmentation without affecting other tables. All the rest of the tables are small in size, so I put them into separate areas based on their ideal RPB, and obviously you would set the appropriate RPB in the new structure file.
  • index area: a suggested storage area for the indexes of each table. I kept it simple, putting all indexes in one area, with the exception of the largest tables, charge and gltrx.
  • tablemove command: a formula creates the command you could put in a batch file to move the table and its indexes, based on the information above.
• 36 of your 175 tables are empty, so an optional approach you could take is to put these tables in their own "empty_table" area, and their indexes in a corresponding "empty_index" area. Be cautious with this, as it is something you will have to monitor if you do it. A table could be empty because you will never use it, or it could be empty because it was added to the schema last week and hasn't been populated yet. Decide for yourself whether this approach has value for you.
• In my experience, with very small databases like this a binary dump and load is more hassle than it's worth. Depending on your test run timing, you may want to binary D&L the charge and gltrx tables, as they constitute 70% of your data. I would definitely stay with dictionary D&L for the rest, unless your testing shows the time difference between dictionary and binary justifies the extra manual steps. This decision is also affected by the window of time you get from the business, based on allowed downtime.
• If you are going to try to implement storage areas in the manner I have suggested, do it on an empty database. In other words, don't load the data and then do table moves. That is slow, locks the tables, and uses a ton of BI file space; a table move is one big transaction. Create the target database with the appropriate new areas and load the application schema, then do the table and index moves on the empty objects (which can be done ahead of time, outside your downtime window), and then load the data from your dump files.
• The workbook contains a table, so you will need a relatively recent version of Excel (hopefully newer than your Progress version... ) to view it.
• One other thing to keep in mind: your DBs are in need of a D&L, but that isn't necessarily going to be a silver bullet for the reported application slowness. We don't know anything about the client side; what startup parameters the clients use, where they pull code from, whether these servers are shared with other apps, etc. There may be opportunity for performance improvement on that side as well.
• One final note: please don't consider my late-night rambling to be an acceptable substitute for the advice of a professional consultant. My advice just may be worth what you paid for it.

I hope this helps. Good luck.