Progress DB Engine Is Stupid About Some Things

[joey.jeremiah]

first off, thank you for having a real conversation.

who knows maybe we can even share knowledge. i can certainly learn alot from you.

seriously, believe it or not, i don't have these performance problems, and imo a few changes can make a huge diff.


for example, i've recently dumped ~15gig, ~40 million records database

went thru the entire database plus saved the data on file in maybe a quarter of an hour.

as a rule of thumb i and non of my customers would except a query that takes more then a couple of minutes.

i would really be interested if you'll let me try having a go at an example that's been giving you trouble.


i agree with you about the spatial indices, if i understood you correctly, and a few others which i've known about for years.

but i don't know if it's such a huge handicap or more of a high end feature. index merges are a very low cost operation.

 

[BCM]

joey -
I wish I could share the code with you. The application vendor does not provide any code, and they are mostly unable to resolve the problems we find. It's a pity we pay them a hefty maintenance fee.

Tell me, do you ever run dbTool on a single table in the database? If so, how long does it take? When we run dbTool it takes 2-3 hours to make a pass through a single table.

Do you run your Progress database on a Windows server? Our Progress database sits on a multiple processor, Windows server with 4 gigs of ram. I have observed poor processing integration between a Progress 4GL client and its Windows host. Especially with regard to time slicing.

 

[joey.jeremiah]

i don't mean contracting me (at no cost) or sending me the data. just a
test case, i'll write and send to you.

i would really be interested sounds like a great challenge. i've got some
time off from school and work on passover next week.

j Tell me, do you ever run dbTool on a single table in the database? If so, how long does it take? When we run dbTool it takes 2-3 hours to make a pass through a single table.

i don't use progress sql a whole lot.

i remember it had a rocky start in v9. i played around with a few things
back then that were really unusable.

Do you run your Progress database on a Windows server? Our Progress database sits on a multiple processor, Windows server with 4 gigs of ram. I have observed poor processing integration between a Progress 4GL client and its Windows host. Especially with regard to time slicing.

mostly unix/linux, but there have been some windows. i didn't feel a huge diff.

i'd like to know more about the setup. webspeed ? maybe you're using an
appserver ?

especially if the server or clients running the queries are on the same
machine as the database ?

 

[BCM]

The nightly jobs that each take a long time to run are executing on a separate machine on our network and not on the same machine as the database. The machine is part of our server farm in the same server room, and it has a single processor with 2 Gb of ram. Both machines run the same version of Windows Server software.

I am going to post my email address for any of you who may be interested in communicating directly. I promise to stop criticizing Progress. I realize that those working in a dedicated Progress shop are probably quite happy. My primary problem with Progress is that it does not integrate well with Windows-based products. In the U.S., virtually 99% of all white collar workers have been provided with a Windows laptop or desktop computer equipped with Microsoft Word and Excel.

Additionally, you may be interested to know that in 1997 I began working with Progress 7.x as a contractor doing custom work for the UniFi loan applications (4GL). At that time it was apparent that one faced serious difficulties interfacing with the Progress database from a non-4GL language. Through version 9.x Progress has not made significant improvement to this dilemna. I cannot comment on version 10.x. My firm is committed to another course that does not include Progress licensing issues.

bcm000@earthlink.net

 

[joey.jeremiah]

The nightly jobs that each take a long time to run are executing on a separate machine on our network and not on the same machine as the database. The machine is part of our server farm in the same server room, and it has a single processor with 2 Gb of ram. Both machines run the same version of Windows Server software.

I am going to post my email address for any of you who may be interested in communicating directly. I promise to stop criticizing Progress. I realize that those working in a dedicated Progress shop are probably quite happy. My primary problem with Progress is that it does not integrate well with Windows-based products. In the U.S., virtually 99% of all white collar workers have been provided with a Windows laptop or desktop computer equipped with Microsoft Word and Excel.

Additionally, you may be interested to know that in 1997 I began working with Progress 7.x as a contractor doing custom work for the UniFi loan applications (4GL). At that time it was apparent that one faced serious difficulties interfacing with the Progress database from a non-4GL language. Through version 9.x Progress has not made significant improvement to this dilemna. I cannot comment on version 10.x. My firm is committed to another course that does not include Progress licensing issues.

bcm000@earthlink.net

that's nothing, i personally threatened to break the legs of a top progress vp

i did a few other crazy things growing up, like, sh itting on the door step of
my school teacher.


progress has a huge problem with remote connections where the client/agent
and database are on diff machines

that have alot to do with p4gl designed with navigation in mind first.

it's not out of the ordinary for queries to take almost 100x more in remote clients


give me a shot, what's the worst that can happen

 

[Casper]

Glad we can talk now,

Some numbers:
Dbtool on AIX P series single processor about 3-4 minutes for 4GB DB.
Unfortunatley I haven't much experience with Progress on windows machine although there is significant performance between Unix systems and Windows systems.
You say you have 4 cpu's --> do you start dbtool with 6 threads and still it takes 2-3 hours to finish? How big is that table?
edited:
Oops just saw 4 GB of RAM.... (not 4 cpu ) well you can start multiple threads with dbtool: rule of thumb threads = ROUND(cpu * 1,5)..
But 2-3 hours then there must be some other issue on the windows machine....

The nightly report if you look at promon do you see abnormally many records read, compared to expected number of records with regard to the data? High cpu usage?
Disk contention?

Regards,
Casper

 

[Lee Curzon]

i did a few other crazy things growing up, like, sh itting on the door step of
my school teacher.

Who hasn't done that?

I promise to stop criticizing Progress.

Big-Time, there is no need for you to do that. You are one of the more entertaining posters on the forum (and I don't mean that in a provocative way). The threads you participate in can be pretty amusing as far as Tech threads go.

I think you would find that the relationship some of us here have with Progress is bitter/sweet, but as many of us are programmers rather than DBAs by nature, so Oracle and SQL Server have looked less attractive than Progress in the past. That is likely to change, at least for me, the more impressive Microsoft related technologies become, and the more obscurely OpenEdge pursues the market.

Some positive steps (focusing on back-end services and integrating with superior 3rd party frontends), as well as the traditional giant leaps backward (making access through the Web unaffordable) are being made in that area.

There are very few of us here who would defend Progress to the death, but when faced with people who seemed determined to turn it into that sort of battle, it's kind of hard not to respond.

I hope Joey/anyone can help you resolve the problems you've been having - it does sound like the service you've been receiving from your Progress suppliers has been 'differently good'.

 

[BCM]

Joey, Lee, and Casper, thank you for replying. Let's see if I understand your recommendations...
- For best performance, execute Progress 4GL routines on same box as Progress database.
- Take advantage of 'threads' option when running dbTool.
- Big performance differences between Windows and Unix probably exist. If possible, move database, application server, and application routines to Unix/Linux OS.

 

[TomBascom]

Yes, try as hard as you can to run "large" 4gl programs on the same server -- Progress client/server connections will kill performance. We can debate the wisdom of that forever but it is what it is and if you want it to go fast you'll need to get the processes on the same box.

Yes, take advantage of threads.

No, there should not be major performance differences between Linux and Windows when reasonably configured and compared on identical hardware.
I do think that Linux is a lot easier to work with but that's probably just because I'm a UNIX guy. If you're seeing a big difference you are probably not comparing apples to apples.

Your configuration doesn't sound like it is designed to perform well with Progress. It sounds like it was put together with SQL Server in mind rather than Progress.

 

[BCM]

Tom,

Your configuration doesn't sound like it is designed to perform well with Progress. It sounds like it was put together with SQL Server in mind rather than Progress.

The configuration was selected prior to my joining the firm. How should the configuration be changed to provide better Progress performance?

Thanks.

 

[TomBascom]

I don't have enough detail to say beyond what has already been said.

Just guessing but I wouldn't be terribly surprised to find that you have an inadequate disk subsystem (perhaps even RAID5) and that few, if any, tuning parameters have been changed from their defaults.

The obvious first cut server candidates are:

1) -spin 20,000

2) -B 100,000

3) bi cluster size 32,000


on the client side:

1) -q

2) -mmax 16384

3) -TB 31 -TM 32

 

[joey.jeremiah]

haven't checked and there are no docs near by.

but if the -H, -S, -N client startup parameters used wouldn't it still
use a remote connection ?

could he just remove any -H, -S, -N startup parameters from the client
just to be sure, it couldn't hurt ?


i'd start with a "pure" test case to elimnate any other issues, maybe
badly written progs you've received.

you can just open the editor and write a few for each queries, joins,
complex where clauses, go thru large volumes of records etc.

 

[TomBascom]

Yes.

If -H and -S are used, even on the same box as the db, then all communications pass up and down through the TCP/IP stack rather than through shared memory. If any significant amount of data is involved it is considerably slower that way.

So you're right -- step 1 is to get everything on one box and remove the use of -H and -S.

 

[BCM]

Joey - Are you recommending that I compare some FOR EACH queries executing local to the database to the same FOR EACH queries running on a remote client?

Tom - I'll have to check the startup config file for the process that runs our nightly jobs. We have been provided no code by the vendor. With regard to the disk subsystem are you saying that RAID5 is not a good choice? Is this purely from the perspective of Progress or is this a general statement? What disk subsytem do you recommend?

I thank you both for your suggestions.

 

[Lee Curzon]

Raid 5:

KB P66330
Title: "Does the Progress RDBMS work with RAID?"

http://tinyurl.com/kze7y


KB 21152
Title: "Frequently Asked Questions About RAID 5"

http://tinyurl.com/efrdn

 

[Lee Curzon]

(From a previous post, in case you lost the reference)

Also (general tuning stuff):

KB P19806
Title: "The Top 10 Performance Tuning Tips For The Progress Database"

http://tinyurl.com/qanpw

 

[Lee Curzon]

FYI Tom and other DB types have posted extensively on the subject on dba@peg.com.

 

[BCM]

Thanks, Lee. I'm printing all the documentation to which you provided links.

 

[Lee Curzon]

Can't remember if this has been posted before...

KB P8479
Title: "What factors affect the performance of my Progress database"

http://tinyurl.com/ly58p


Sorry about the constant external linking btw - but hopefully they will give you more info to think about.

 

[BCM]

Thanks again, Lee. No problem with the external links. I came to this firm after all these decisions had been made. I am not granted privileges on the server itself, only in the database. Also, I prefer the application dba role to the system dba role. So, I have not developed a good working knowledge of the hardware and network components that impact performance.