Openedge 10 on Windows 2003 poor performance

M

mkools79

New Member
Hi all,

We are using a third party application here which uses Progress on Windows 2003 (32-bit) as backend database.

We used to run it on Progress Openedge v9.2 but we now upgraded to version 10 with the latest service pack.

We hoped version 10 would resolve our performance problems but it didn't. We sometimes have extremly poor perfomance of the application, where queries sometimes take a minute or longer to complete. Most of the time perfomance is poor or very poor :confused:

When we upgraded to version 10 we upgraded te hardware as well:

- Windows 2003 Enterprise SP2 32-bit.
- 4 GB RAM.
- Dual quadcore cpu's.
- gigabit ethernet.

The database is stored on a RAID5 disk enclosure with all 15K rpm scsi discs, the log files are stored on a RAID1 volume with also 15K rpm scsi discs.

When users complain about the poor perfomance we can't see anyhting on the server. CPU usage is around 0 - 5% with spikes to about 14%. Memory usage is about 1.5 gb and I can't see no disk or network bottle necks with perfmon.

The performance of all our other applications is fine, so that would indicate this is a database issue. The only thing that might be different is that we run our progress client on Citrix Presentation Server 4.5, which are all brand new machines too.

I've been searching the net for quite some time now and all I found were tools like protop, but they are all for unix or linux. I hope somebody can give me some tips on how to monitor the cause of this poor performance, are there any tools, or any advice what we can do to get better perfomance?

Thanks in advance!

Regards,

Martijn.
 
Btw, here is some promon output of our live database

♀Activity - Sampled at 08/21/08 17:48 for 292:15:56.

Event Total Per Sec Event Total Per Sec
Commits 1514510 1.4 Undos 518 0.0

Record Updates 1969799 1.9 Record Reads 12219965549 11614.2

Record Creates 549090 0.5 Record Deletes 82364 0.1

DB Writes 1802981 1.7 DB Reads 6860012363 6520.0

BI Writes 746091 0.7 BI Reads 1088844 1.0

AI Writes 0 0.0
Record Locks 344313001 327.2 Record Waits 144 0.0

Checkpoints 142 0.0 Buffs Flushed 38636 0.0


Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 96 % Writes by BIW 78 % Writes by AIW 0 %
Buffer Hits 76 %
DB Size 11 GB BI Size 749 MB AI Size 0 K
FR chain 0 blocks RM chain 6 blocks
Shared Memory 426328K Segments 4

10 Servers, 81 Users (0 Local, 81 Remote, 0 Batch),4 Apws


Locking and Waiting:
Type Usr Name Record Trans Schema
Lock 999 TOTAL... 344314656 0 12927
Wait 999 TOTAL... 144 0 0
Lock 0 SYSTEM 0 0 0
Wait 0 SYSTEM 0 0 0
Lock 1 SYSTEM 0 0 0
Wait 1 SYSTEM 0 0 0
Lock 2 SYSTEM 0 0 0
Wait 2 SYSTEM 0 0 0
Lock 3 SYSTEM 0 0 0
Wait 3 SYSTEM 0 0 0
Lock 4 SYSTEM 0 0 0
Wait 4 SYSTEM 0 0 0
Lock 5 SYSTEM 0 0 0
Wait 5 SYSTEM 0 0 0
Lock 6 SYSTEM 0 0 0
Wait 6 SYSTEM 0 0 0
Lock 7 SYSTEM 0 0 0
Wait 7 SYSTEM 0 0 0
Lock 8 SYSTEM 0 0 0

Database Status:
Database version number: 4246
Database state: Open (1)
Database damaged flags: None (0)
Integrity flags: None (0)
Database block size (bytes): 4096
Total number of database blocks: 2947433
Database blocks high water mark: 2944698
Free blocks below highwater mark: 0
Record blocks with free space: 6
Before image block size (bytes): 4096
Before image cluster size (kb): 16384
After image block size (bytes): 8192
Last transaction number: 222937721
Highest file number defined: 261
Database created (multi-volume): 04/14/06 17:39
Most recent database open: 08/09/08 13:32
Previous database open: 08/09/08 13:32
Local schema cache time stamp: 07/29/08 14:33
Most recent .bi file open: 08/09/08 13:32

Shared Resources:
Number of database buffers (-B): 100000
Number of before image buffers (-bibufs): 25
Number of after image buffers (-aibufs): 5
Before-image truncate interval (-G): 0
No crash protection (-i): Not enabled
Maximum private buffers per user (-Bpmax): 64
Current size of locking table (-L): 200480
Locking table entries in use: 15
Locking table high water mark: 62308
Maximum number of clients per server (-Ma): 30
Delay of before-image flush (-Mf): 3
Maximum number of servers (-Mn): 11
Maximum number of users (-n): 301
Before-image file I/O (-r -R): Raw
Shared memory version number: 10042
Number of semaphores used: 316
 
Actually ProTop runs just fine on Windows.

It does, however, have a character interface but it's still lots sexier than PROMON ;)
 
Merely upgrading doesn't usually improve performance. To gain significant performance you have to do things that take advantage of new features. In most cases that means dumping and loading your db.

To do that properly takes some analysis of your data. Best practice is to assign tables and indexes to storage areas with properly matched rows per block and cluster sizes. This usually requires what may seem to be a large number of storage areas to be defined.

Create a distinct storage area for:

• Very large tables (tables larger than 1GB)
• Very active tables (tables with IO rates consistently greater than 1000/sec)
• Each group of tables needing a particular rows per block setting
• Tables smaller than 256 blocks
• Create discrete index storage areas that correspond to the data areas

Progress OpenEdge 10 supports Type 2 storage areas. These storage areas allow the DBA to specify a cluster size so that certain operations within the database manager can be completed more efficiently. Larger cluster sizes perform noticeably better, especially for large tables. The maximum cluster size is 512 blocks.

There are, however, several caveats:

• There is no advantage to having a cluster size more than twice the size of the table being stored.

• Except that you need a cluster size of at least 8 blocks in order to have have “asocial” blocks (blocks whose data is from just one table). Some tiny, but frequently accessed, tables do benefit from this.

• Individual indexes do not typically require as much space as the corresponding data. The amount of excess allocation (“wasted space”) associated with indexes can be quite large if index areas are universally configured with cluster sizes of 512.

All OE10 data should be in type-2 storage areas.

Looking at the PROMON data strongly suggests that you need to do this. You're reading a lot of records and, even with -B 100000 your hit ratio stinks.

Furthermore you are not running after imaging. No after-imaging is a very bad mistake. Even with a smallish database. It isn't a performance issue but it is a job secutiry issue.

There is probably more that could use work but it's hard to tell from this view.
 
Ideally you would post data from a more limited time-frame. A representative sample of activity while the performance problem is happening is best.

If you are going to use PROMON try the "s" command.

Also, the startup parameters screen would be interesting.
 
Hi Tom,

Thank you very much for your insight on this. This is more helpful than all those months of support we got from our application supplier.

That is where the problem lies in the first place, we are taking progress licenses from our application supplier so we are not a direct customer of progress and therefore we don't get any direct support from them.

I'll let our DBA see your suggestions and see what he thinks of all this. From what I can see it looks pretty logical to me that this has to improve performance. Every small gain in performance we can get would be very helpfull already.

As for the suggestions in your last post, when I'm back in the office tomorrow I will post the other promon outputs you are suggesting.

The performance problem is all day long, sometimes less than other times but it's always there so a limited time-frame shouldn't be any problem ;)

Many thanks! :)

Regards,

Martijn.
 
Tom,

One more thing. You are saying in your post we should enable after imaging.
We do have consistency problems with our database, records that suddenly change, or records that change which are read-only in the first place, in other words, weird unexplainable things are going on.

You think that after imaging being disabled has something to do with that?

Thanks again!

Martijn.
 
No, those sorts of problems are probably coding problems. Likely related to record, lock and transaction scoping.

After-imaging is all about recoverability and protecting your butt against the day when something goes horribly wrong. That day will come. And when it does properly implemented after-imaging will save your bacon.
 
mkools79 said:
Hi Tom,

Thank you very much for your insight on this. This is more helpful than all those months of support we got from our application supplier.
Click to expand...

Awww, shucks... :blush: Click on the "scales" and show that you mean it :awink:

That is where the problem lies in the first place, we are taking progress licenses from our application supplier so we are not a direct customer of progress and therefore we don't get any direct support from them.
Click to expand...

You can solve that problem by forming a relationship with a good consultant :cool:
 
You must log in or register to reply here.
Back
Top