ABL on Aplication Server and RDBMS on separate Database Server - OE 10

B

belgarion

New Member
Hi All,

I've recently inherited a Progress application that users love but its slow. The app has just been upgraded from Progress 9 to OE 10 and shifted to a new server. All the code is in ABL. CPU is 100% for long periods when crunching through reports which slows down other activities. Memory doesn't appear under any presure and disk IO queues are low. OS is Linux, RHEL.

Some of the developers and systems guys - all non-progress experts - suggested spliting the app into two tiers with the application code (all in ABL) being moved onto another server, leaving the database being served on the existing server. This makes sense to me as we do this frequently with other databases and applications for security, licensing, managability, and performance reasons.

Can this be done with an application that is completely ABL? ... Can I just put the .r (executable code?) on the app server?

I know within ABL there is a CONNECT statement that allows ABL to connect to a remote database but after searching through the code I can't find one anywhere. How does a ABL OE10 application connect to its database when it first fires up?

Sorry if this is dumb newbie question. I'm new in the App manager role and have a very limited budget but I really want to help my users out as quick as I can. Having a very limited budegt means I can't call in a consultant only to be told "no you can't do it" so I thought there'd be someone here who had done this before and can tell me whats involved. If it indeed doable, when/if I do get a consultant in at least I'll be able to talk senibly to them.

Any help/guidance greatfully appreciated.
 
You can leave your application on this server and just move DB to another. You dont have to change anything exept DB connection statement.
Create dbname.pf file in root directory.
instead
mpro -db dbpath
use
mpro -pf dbname.pf

or if you have connect statment inside your program
instead
connect value("dbpath") no-error.
use
connect -pf dbname.pf

in dbname.pf specify:
-db dbname
-H DBhost_ip_or_Name
-S DB_PORT

On other server (DB_HOST_IP_OR_NAME) install db server and start it with
proserve -S DB_PORT

You dont have to setup appserver to split your application and DB server.

You can always change dbname.pf to
-db dbpath
And application will work as it was working before (db and code on the same server)
 
There is a big advantage keeping the application on the same machine: It can connect to the database as self-service client directly via shared memory which is much, much faster than connecting via the network layer as a remote client. The advantage you gain splitting the CPU load might very well introduce a new performance bottleneck with the network. Unless it's really a big application which many, many users, I wouldn't advise that. The network layer is the slowest component in your system ...

Having said that, I would advise you to pin down your performance problems first before you think in investing in new hardware. Out of my experience, in most cases, bad performance comes from badly coded queries not utilizing indexes and therefore causing a lot of table scans which directly causes much disk i/o and therefore hitting the CPU. And then, there might still be lots of potential to tune the database itself.

But performance tuning a system is not about just hitting a magic button, instead it's a process to tune the system step by step. Not to just "make it faster", but to get rid of identified performance issues.


HTH, RealHeavyDude.
 
Maxim, Thanks a bunch. I'll do some reading of the code and manuals and follow what you've said. For now, it makes some sense (but I'm sure I'll get lost again).

RealHeavyDude, The servers would be connected via a 10 Gbit switch so there might be a few nanoseconds of latency but bandwidth shouldn't be a problem so each DB request will be only slightly slower and as the DB server won't be competing for memory with the app server, I hope it'll balance out. The app (i'm told) is a multiuser report server type of app. It import files, crunches them and then persists the results to the DB for users to run reports against. The progress guy that did the upgrade to OE10 was well know by the non-progress experts and they rated him very highly as he has done non-progress work as well. He left in his wrap-up report that he'd optimised everything obvious in the DB (and no table scans that he could see) and identified a couple of reports that could be smarter but that raw grunt was the problem. He also suggested splitting the app across two servers as being more economical than doubling the size of the current server (and acknoledged that there would be two points of failure). But you raise a great point ...

Does connecting to the database as self-service client directly via shared memory justify doubling the size of the server (which comes at much more than double the cost!)? ... Or is it better using two lower spec'ed servers and connecting via the network layer as a remote client? ... What's the best bang for ya buck?

In the Windows servers world (and some of the Linux/Unix worlds) I've dealt with, its generally the later. Is Progress different in this regard? Is one big server better when all costs are considered?
 
I Agree with RHD. Your bottleneck is a lot of virtual reads from DB tables. Most reads are cached in memory, so it is CPU load, not disk load. If you move DB to another server there will be the same virtual reads. But instead of reading it from shared memory segment you will read it from DB server through network. Thus all you get is slow down your system.

If I setup appserver, I always prefer to use this same machine with db server and setup
-db dbpath in appserver parameters
instead
-H -S

Appserver and db server on different machines is good choise only if your program uses a lot SQL-like quieries, that process a lot of records on DB server and return small number of records on appserver.
for example
for each table1 where,
each table2 of table1 where,
....
each tableN:
do something with record set.
end.
If your application uses many for each and many find statements inside for each cycles, DB server will get a lot small queries and total performance will be low.
for each table1:
for each table2 of table1
end.
find table N of table1
end.

non-progress experts uses SQL-92. They are expirienced in right sql queries.
But progress old programming method allows users to write non-sql scenarios to get info. It is more convinient, but system will be slow.
 
Basically one can hardly be positive unless a test has proven ...

But, not only is the network bandwidth relevant, but also the way how the network is used. Comparing a direct access via shared memory with the remote access to a database is like standing on the shore of a lake having unlimited supply of water against standing in front of a pipe where the water comes out drop by drop ( IP packets ).

As Maxim explained, the way a database is used via SQL vs. 4GL is completely different. A SQL query is sent to the server, processed on the server and then the result gets back to the client. In the 4GL world most developers use FOR EACH statements for doing reports. That means that there is a roundtrip between the client and the server for almost each IP packet. That's why getting rid of the network makes 4GL applications so much faster.

Regards, RealHeavyDude.
 
Thanks guys. I'm getting your point.

I guess I was expecting the internal architecture of ABL between 9 and 10 to have changed so that ABL at 10 behaved more like SQL. Guess this isn't the case?

The reason why I was expecting this is that every other software vendors app server/DB server offerings are moving towards 'set based' (read SQL based) processing between the application tier where the code is and the database tier where the data is. I guess ABL isn't going to get there - ever?
 
The ABL is there since, I think, 1996 or 1997 - that was when the AppSever was introduced late in Progress V8 into the product.

Mostly the developers are not there.

Plus, it's not just a configuration setting to run an application in an n-tier architecture instead of client/server. Most client/server applications do have UI and business logic mixed and matched in the same code. In general that means that the whole application needs to go through a transformation process, basically re-designing it to utilize a modern, agile and robust architecture.

Regards, RealHeavyDude.
 
belgarion said:
I guess ABL isn't going to get there - ever?
Click to expand...

It is not question of ABL, it is question of programing style used by developer.
New systems can easy use sql-style in queries and those systems can be well tuned even on appserver + db server enviroment.

But there are tonns of old code that used old method with many small for each and find statements. It was written for CHUI and direct DB access. It works fast only on direct access. If you try use "modern" method of splitting application on layers where db/appserver/interface layers are divided you understand one simple thing - you have to redesign whole code. You have to rewrite all queries to sql-like style.
It is not possible on projects with hundreds MB of source code.
It is why old systems stucks on old techlology. It is not question can be solved fast.
 
Network overhead will not be nanoseconds. Even with gigabit gear it will be in miliseconds. And what really kills the performance is the multiple context switches on both machines as the data goes up and down the IP stacks and back and forth in multiple packets.

Shared memory access is usually a few microseconds and there is no packaging of the data or handshaking between multliple processes needed. It is much faster.

You would be well advised to get an experienced performance consultant to take a look at your system.
 
I guess I am just echoing RHD but in my experience, people who advocate separating the DB and AppServers onto separate machines are clueless. There is no way transferring every record read over the network and between machines, even if they are 2" apart and joined by fiber, will be as fast as shared memory.

On the other hand, it does buy you some horizontal scalability. If your app spends 90% of its time crunching numbers and 10% reading records, it might be a smart thing to do. But I believe this is true of a tiny subset of business apps. Anyways, it should be easy enough to run some benchmarks and try it out.

We do this in production (for one customer only, and out of necessity) and we basically put as many users as we can on the DB box and farm the users we don't care about to the remote AppServers. Sucks to be them.

By the way, if you DO do this, you may find all kinds of little issues that you hadn't thought of. For example, any flat file processing that you do could be complicated by the fact that instead of all being on one box, files may come and go (depending on your architecture) from any of N boxes. I suppose you could also have the same type of issue with any external dependencies such as message queues.
 
ABL is perfectly capable of set logic when needed, but has the advantage of being able to be record oriented when wanted ... which is most of the time!
 
I'd have to disagree here RHD ... yes, for smaller systems, the advantage of shared memory clients are a big plus, but as one makes the system bigger and bigger the AppServer and the DB are competing so for larger systems putting them on separate systems is the way to go ... possibly even mupltiple AppServer systems.

You are right, of course, that one needs to know the source of the performance issues before flinging money at a solution ... fling that money at the right consultant first! :)
 
In principle there might be situations where app servers and db access are competing and there aren't enough resources for both. In my experience those situations are very, very rare. I'm pretty sure that I have never solved a performance problem by putting app servers on another server.

In any event that doesn't seem to be the problem here:

CPU is 100% for long periods when crunching through reports
Click to expand...

This is classic un-tuned database or poorly designed application behavior.

The original post also says that there is no pressure on memory and that IO rates are low.
 
@tamhas: some day it might be entertaining to open up the whole 'set based' vs. 'record based' discussion. I am afraid though we would end up agreeing, and have the remaining 99.999% of the non-Progress programming world wondering what the hell we are talking about, if we could even distract them for long enough to realize that non-SQL, non-noSQL databases even exist.
 
You must log in or register to reply here.
Back
Top