Performance issues

[Blakey43]

Hi,

This is my first post here and of course I'm looking for some free advice!

Our Progress 9.1E application is slow and frequently so slow as to be almost unusable.
About 40 users, coming from a mix of local installs and rdp. Also about 40 telnet users using Georgia telnet server on the same machine as the db. ODBC connections are used for a web site, and to connect to other apps on site.
The Dell 2950 server has W2K3 R2 SP2 standard, 4GB RAM, twin quad-core Xeon processors. Six 15k rpm sas disks in 3 RAID 1 arrays.
Its been in for about a year and we've been regularly trimming things off the server "to take the strain off".
I'm still new to progress but when it goes slow, its me they call. I'm coming to the conclusion that the server's not up to the job, but also am aware that upgrading hardware can just buy you time until the real problem grows to outstrip the new machine. The application providers are experienced progress developers.
Occasionally we get db crashes. Analysis of the server has shown its good for db reads, slow for writes.
Page faults are in the hundreds and in busy periods, average over a thousand per second.
Telnet requests are in the order of 500 per second, tcp retry transmissions are common, possibly because of the queued requests to the db. The machine is up to date with all the latest bios, firmware, drivers.
Would anyone like to say where to start? Is anything obviously bad/wrong in the hardware?
All replies welcome.
Thanks
S

 

[TomBascom]

Not to get religious or anything but if you're running telnet sessions why on Earth are you using a Windows server? Linux would be much more natural for that sort of thing...

Offhand the server sounds more than adequate for 40 users.

My guess is that even though you're dealing with "experienced developers" you are not dealing with experienced Progress performance experts. There is a world of difference between a developer and a DBA.

From a Progress perspective there is a lot to look at. You should start by monitoring the db and providing some metrics regarding the configuration, workload and behavior of the system. My favorite monitoring tool is ProTop.

 

[Blakey43]

Hi and thanks for your help.

Specifically:
I personally like Linux but am no expert. Our entire systems are Windows based. I'm also no telnet expert! Could I use a Linux telnet server but still connect to the Windows db server? (i.e. move the telnet from the db server)

We do need to get trained as Progress DBAs also. By the way have you any knowledge/experience of the computer-based training offerings from Progress?

I have the following info, which I don't pretend to understand, but was gathered by the application providers at my request after reading a Progress performance tuning article:

Database blocksize is 8096
Buffer Pool Size is 50000
Data extents are striped over a pair of physical disks but I don't understand how because the disks are mirrored.
BI logs are on separate disks
4 page writers
BI writer is started
-spin set to 30,000
Number of BI buffers 60

I checked the ProTop website and I would not be confident in installing this - I could ask the application people.

Best Regards,
S

 

[TomBascom]

Hi and thanks for your help.

Specifically:
I personally like Linux but am no expert. Our entire systems are Windows based. I'm also no telnet expert! Could I use a Linux telnet server but still connect to the Windows db server? (i.e. move the telnet from the db server)

You could do that but you will get the most performance benefit from having the telnet sessions on the same server so that they can use a shared memory connection.

One thing that you ought to do is to make sure that the current config is using self-service connections and NOT client/server. IOW the -S parameter should not be in the telnet session startup parameters.

Setting up Linux to support this kind of operation is pretty trivial. You really don't need any special expertise.

We do need to get trained as Progress DBAs also. By the way have you any knowledge/experience of the computer-based training offerings from Progress?

I know that they exist. I wouldn't hold out much hope for gaining much from them. But I am sort of biased...

I have the following info, which I don't pretend to understand, but was gathered by the application providers at my request after reading a Progress performance tuning article:

Database blocksize is 8096
Buffer Pool Size is 50000

400MB of buffer pool. That's fairly small.

Data extents are striped over a pair of physical disks but I don't understand how because the disks are mirrored.

You're right. That makes no sense at all. (Which sort of backs up my developers <> performance analysts remark...) You might want to double check and make sure that nobody has done anything silly like put all of this on a RAID5.

BI logs are on separate disks

That's nice but unless you have a major write bottleneck it isn't likely to matter.

4 page writers

Probably excessive. But not likely a problem.

BI writer is started

Good. How about an AIW? You are running after-imaging. Right?

-spin set to 30,000

Probably high but probably not a big issue either.

Number of BI buffers 60

Also probably high but probably not a problem.

What is the bi cluster size? Are there any storage areas defined? If so how are they configured?

I checked the ProTop website and I would not be confident in installing this - I could ask the application people.

Ouch! That hurt! Trust me, it's harmless. It's a read-only monitor and has no appreciable impact on your system's performance. All the source code is there so it isn't like anything nasty can be hidden in it.

If you don't go with ProTop you will have to settle for PROMON to obtain any data about your system performance.

Anyhow, along with the questions sprinkled above it would be good to know the following (X per second during a busy period -- not the average over 24 hours...):

1) What is your "logical read" rate?
2) What is the "os read" rate in the same period?
3) Record reads/sec?
4) How many trx/sec?
5) Are you experiencing latch timeouts?
6) Are there blocked sessions?
7) What are the most active tables and how active are they (ProTop can tell you this, PROMON cannot.)
8) Time between checkpoints?
9) Where are temp-files going and how much IO is there to them? (You need to use -T to specify a temp-file directory; ideally on a stand-alone filesystem so that you can easily measure the IO rate. The (lower case) -t option should also be used so that the temp-files are visible which makes analyzing their activity easier...)
10) What startup parameters are being used -- there should be 2 sets of parameters, one for the db server and another for the telnet sessions.
11) ODBC options might be relevant too. Are they using read-uncommitted?

 

[Blakey43]

Hi Tom,
Thank you again for your input - I'm going to come back to you later, probably Monday.
S