Database performance

I

IMS

New Member
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. This occurs mostly around month end where the load on the system is very high.

There is no real evidence to where the problem lies, apart from the fact they have increased the number of users on a work group database as the company continues to grow. 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: 38
Connections: 40
OS : SCO Openserver (release 5)
Progress: 9.1D Workgroup Database
Memory : 2gig
CPU's : Proliant ML350 Twin 3.2GHz Xeon server

Startup script:
proserve <db> -n 38 -Mn 8 -Ma 5 -B 25000 -L 50000 -N tcp –S <service>

512 buffer size (95%+ buffer hits)
Ckpt ------ Database Writes ------
No. Time Len Dirty CPT Q Scan APW Q Flushes
74 15:28:07 0 453 0 0 0 0
73 15:19:09 538 337 0 0 0 42
72 15:15:04 245 1469 0 0 0 191
71 15:14:56 8 1452 0 0 0 686
70 15:14:47 9 1277 0 0 0 804
69 15:14:40 7 1019 0 0 0 473
68 15:14:30 10 1130 0 0 0 546
67 15:14:22 8 1378 0 0 0 584

256 buffer size (97% Buffer hits)
Ckpt ------ Database Writes ------
No. Time Len Dirty CPT Q Scan APW Q Flushes
22 12:29:29 0 734 0 0 0 0
21 12:16:26 783 685 0 0 0 348
20 12:05:55 631 828 0 0 0 220
19 11:57:36 499 808 0 0 0 390
18 11:45:53 703 853 0 0 0 418
17 11:30:07 946 799 0 0 0 435
16 11:14:39 928 567 0 0 0 364
15 10:58:40 959 986 0 0 0 181

128 buffer size (98% Buffer hits)
Ckpt ------ Database Writes ------
No. Time Len Dirty CPT Q Scan APW Q Flushes
115 12:27:02 0 416 0 0 0 0
114 12:22:08 294 546 0 0 0 218
113 12:15:13 415 539 0 0 0 328
112 12:12:51 142 484 0 0 0 211
111 12:08:23 268 479 0 0 0 273
110 12:05:24 179 494 0 0 0 206
109 12:03:26 118 546 0 0 0 288
108 11:59:52 214 515 0 0 0 258

The most recent change being the -bi 128. 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 more?

Thanks
Joel Serjeant
Integer Micro Systems Ltd.
 
The decision about whether or not you should be running Enterprise should be based on performance expectations -- not number of users. You should be running Enterprise.

Having said that...

You are confusing a few things in your post. You are not changing the bi block size with proutil dbname -C truncate bi -bi, you are changing the bi cluster size. The good news is that, in spite of saying that you are changing the wrong thing, you are actually changing the right thing ;) Making it smaller may help by smoothing out the IO -- but you'll need to monitor it and track results during a busy time. As it is it looks like you're flushing quite a few buffers at checkpoints. That's not good -- you might want to keep going lower.

The posted "buffer hits" have no direct relevance to bi cluster size. But they do indicate that you are not very well tuned. Buffer hits of 98% really aren't very good and 95% is basically pathetic. You've got 2GB of RAM and only 38 users. -B could easily be 250000 or even 500000. If you can change the database block size (you'll need an empty database that was created by an Enterprise db license somewhere else) and re-allocate storage areas you might be able to get some very nice improvements too.

Since you're apparently running client/server you might also want to look at the possibility that there are network issues or that the clients are badly tuned. You could, for instance, try the Progress -Mm parameter (and look at things from the OS/network POV too).

Of course code is always a distinct possibility in these cases too. You might have some specific problem coding that needs to be addressed.

And think about dumping that SCO server, or at least upgrading it to Linux -- it probably has pretty horrid IO performance.

Speaking of upgrades... is that plain vanilla 9.1d? Or has it had service packs applied to it? There were some very significant performance enhancements made starting around 9.1d07. (And, of course, OpenEdge 10.1 is the current release with lots of even better performance enhancements...)

For a good, free, monitoring tool try ProTop
 
Thank you TomBascom for the prompt reply.

Sorry for the mis-wording, your are correct, I am changing the bi cluster size.

There has been no patching done, I will do that today. We cannot up the -B as we have a couple of other DBs running on this server (which could also be a hinderance).

Excellent suggestions, will follow these through.
 
You must log in or register to reply here.
Back
Top