System Performance

[IMS]

One of our customer’s sites is running slowly on a periodic basis - we can't seem to figure out where the bottleneck might be. The system will crawl at various points during the day - then after a few minutes (anything from 2mins to an hour) it will spring back in to life.

There is no real evidence to where the problem lies, apart from the fact they have a large number of users on a work group database, which in turn allows them to have no bi writers, apw’s, etc.

About three weeks ago I did an index rebuild on the database and over the past few days I have been changing the bi block size to see what affect it has on the number, length and the size of the flushes when the database checkpoints.

Server Details:
Users: 49
Connections: 65
Machine : Alpha
OS : Tru64
Progress: 9.1C Workgroup Database - (Do I need Enterprise version with apw's??)
Memory : 500mb (+ another 500mb to go in at the end of the week)
CPU's : 1 x 466 Mhz

Startup script:
proserve <db> -n 65 -Mn 13 -Ma 5 -B 80000 -L 50000 -N tcp –S <service>

512 buffer size (98% buffer hits)
Ckpt ------ Database Writes ------
No. Time Len Dirty CPT Q Scan APW Q Flushes

9 14:07:43 0 349 0 0 0 0
8 13:39:04 1639 614 0 0 0 41
7 12:34:04 3926 507 0 0 0 102
6 11:55:56 2288 336 0 0 0 64
5 11:21:16 2080 491 0 0 0 103
4 10:37:47 2609 556 0 0 0 103
3 09:57:19 2428 593 0 0 0 113
2 09:19 2244 517 0 0 0 127

128 buffer size (95% Buffer hits)
Ckpt ------ Database Writes ------
No. Time Len Dirty CPT Q Scan APW Q Flushes

11 09:44:27 0 244 0 0 0 0
10 09:39:22 305 211 0 0 0 80
9 09:32:38 404137 0 0 0 62
8 09:24:23 495 153 0 0 0 41
7 09:18:07 376 124 0 0 0 44
6 08:44:48 1999 61 0 0 0 12
5 08:11:50 1978 334 0 0 0 15
4 07:57:20 870 611 0 0 0 87

64 buffer size (98% Buffer hits)
Ckpt ------ Database Writes ------
No. Time Len Dirty CPT Q Scan APW Q Flushes

10 10:45:20 0 150 0 0 0
9 10:40:06 280 163 0 0 0 70
8 10:38:06 154 155 0 0 0 50
7 10:35:48 138 104 0 0 0 60
6 10:32:08 220 179 0 0 0 44
5 10:26:44 324 157 0 0 00 43
4 10:22:44 267 215 0 0 0 45
3 10:17:16 301 148 0 0 0 37


I do realise we are really at the physical limit for the database and if any more users are added they will need to go to enterprise. Can anyone recommend anything?

Thanks

Joel Serjeant
Integer Micro Systems Ltd.

 

[dlund]

We also have experienced a similar situation. We are running an old 8.3B with 44 users on NT, dual processor. All of the server's performance monitors indicate that the server is under no load, yet database activity comes almost to a complete halt for a few minutes to an hour or so.

Are you seeing the same lack of load on your server when the slow down occures?

 

[drunkahol]

We run a 4-way Alpha Tru64 machine with 8Gb RAM.

This is tuned to use the maximum -B possible to provide a 2Gb buffer to the main database.

We do use several APW, AIW & BIW's to eliminate disk bottlenecks as much as possible. You need Enterprise license to run these. You also need Enterprise license to run the number of users you claim by the way - expect a call from Progress if they bother to read these threads!

What system parameters are you checking?

If this slowdown is a regular problem, create a program that connects to the database and writes out the important VST entries to a log file every minute. We run such a program constantly here to monitor the database activity. The VST entries that I monitor (probably not the optimum list) are:

_ActSummary._Summary-Commits
_ActSummary._Summary-DbReads
_ActSummary._Summary-DbWrites
_ActSummary._Summary-Flushed
_ActSummary._Summary-RecReads
_ActSummary._Summary-RecWait
_ActBuffer._Buffer-Deferred
_ActBuffer._Buffer-LogicRds
_ActBuffer._Buffer-LogicWrts
_ActBuffer._Buffer-OSRds
_ActBuffer._Buffer-OSWrts
_ActBiLog._BiLog-BbuffWaits
_ActBiLog._BiLog-BIWWrites
_ActBiLog._BiLog-EbuffWaits
_ActBiLog._BiLog-PartialWrts
_ActBiLog._BiLog-TotalWrts
_ActAiLog._AiLog-BbuffWaits
_ActAiLog._AiLog-AIWWrites
_ActAiLog._AiLog-NoBufAvail
_ActAiLog._AiLog-PartialWrt
_ActAiLog._AiLog-TotWrites
_Repl-Server._ReplSrv-BlocksSent

I then import this data into Excel so I can calculate the delta values. This then lets me graph the important figures to get a very clear indication of when the actual database finds itself under stress.

On the operating system side, what kinds of things are you monitoring?

Check that you don't page swap too much on the system and that there isn't a disk bottleneck anywhere.

 

[methyl]

If you have 8 Kb blocksize, "-B 80000" is about 625 Mb. i.e. more memory than you have fitted.

With TRU64 unix the kernel parameter "ubc-maxpercent" defaults to 100% and "ubc-minpercent" defaults to 10% . Worth checking out if only to make sure that you have left at least 10% of memory for disc buffers. In general you will need to share out free memory after loading unix and programs between unix disc buffer space and database buffer space without forcing memory pages into system swap. Too large Progress "-B" can make a system crawl no matter what promon says.