Thread: Application bottlenecks

Application bottlenecks

From
Carlos Henrique Reimer
Date:
Hello,
 
We are using Cobol Microfocus and pgsqlODBC to access Postgresql.
 
Originally these applications only accessed Oracle databases without ODBC, only Procobol and now we´re migrating to Postgresql  and ODBC. The application logic doesn´t changed, but some applications have higher response times than the Oracle version. We worked on database tunning but even so, the response times are very different.
 
I turned on logging sql statemants in the server (log_duration) and discovered that the sum of all SQL duration times gives only 30% of the total response time felt at the client. This gives me a suspect that the bottleneck is outside the server.
 
Am I in the right way?
 
How can I discover the SQL duration times at the client? Is there some tool I could use?
 
Thanks in advance!
 
Reimer 


Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!

Re: Application bottlenecks

From
"Maximiliano Di Rienzo"
Date:
Hi Carlos, we had the same problem with an application, with some queries the response taked much more time to get to the cliente than it taked to execute on the server.
Because we develop in Delphi we switch to postgresDAC, a commercial product, and the problem was solved, that's why i suspect the problem is in the ODBC driver instead of outside the server. Maybe you can send a sample table/query to the ODBC driver development group to analyze it...
Regards.


De: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] En nombre de Carlos Henrique Reimer
Enviado el: miércoles, 07 de septiembre de 2005 8:24
Para: pgsql-odbc@postgresql.org
Asunto: [ODBC] Application bottlenecks

Hello,
 
We are using Cobol Microfocus and pgsqlODBC to access Postgresql.
 
Originally these applications only accessed Oracle databases without ODBC, only Procobol and now we´re migrating to Postgresql  and ODBC. The application logic doesn´t changed, but some applications have higher response times than the Oracle version. We worked on database tunning but even so, the response times are very different.
 
I turned on logging sql statemants in the server (log_duration) and discovered that the sum of all SQL duration times gives only 30% of the total response time felt at the client. This gives me a suspect that the bottleneck is outside the server.
 
Am I in the right way?
 
How can I discover the SQL duration times at the client? Is there some tool I could use?
 
Thanks in advance!
 
Reimer 


Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!

Re: Application bottlenecks

From
"Merlin Moncure"
Date:
Hi Carlos, I am working on project right now you might be interested in, namely a extfh interface to postgresql and a
isamwrapper for libpq.  I already have an isam bridge working direct to postgresql from AcuCobol without using odbc or
acommercial driver.  I just need to convert it to extfh and do a few other things to support microfocus. 

Merlin

________________________________________
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Carlos Henrique Reimer
Sent: Wednesday, September 07, 2005 7:24 AM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Application bottlenecks

Hello,
 
We are using Cobol Microfocus and pgsqlODBC to access Postgresql.
 
Originally these applications only accessed Oracle databases without ODBC, only Procobol and now we´re migrating to
Postgresql and ODBC. The application logic doesn´t changed, but some applications have higher response times than the
Oracleversion. We worked on database tunning but even so, the response times are very different.  
 
I turned on logging sql statemants in the server (log_duration) and discovered that the sum of all SQL duration times
givesonly 30% of the total response time felt at the client. This gives me a suspect that the bottleneck is outside the
server. 
 
Am I in the right way?
 
How can I discover the SQL duration times at the client? Is there some tool I could use?
 
Thanks in advance!
 
Reimer 
________________________________________
Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!

Re: Application bottlenecks

From
Cleber Nardelli
Date:
Hi Merlin...

We have an application that he uses one extfh that was modified by us
so that it has access Oracle bases natively through the use of
ProCobol.  I am interested in its work, would like to get greaters
information.

We use Microfocus NetExpress 3.0.

Cleber.

2005/9/8, Merlin Moncure <merlin.moncure@rcsonline.com>:
> Hi Carlos, I am working on project right now you might be interested in, namely a extfh interface to postgresql and a
isamwrapper for libpq.  I already have an isam bridge working direct to postgresql from AcuCobol without using odbc or
acommercial driver.  I just need to convert it to extfh and do a few other things to support microfocus. 
>
> Merlin
>
> ________________________________________
> From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Carlos Henrique Reimer
> Sent: Wednesday, September 07, 2005 7:24 AM
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Application bottlenecks
>
> Hello,
>
> We are using Cobol Microfocus and pgsqlODBC to access Postgresql.
>
> Originally these applications only accessed Oracle databases without ODBC, only Procobol and now we´re migrating to
Postgresqland ODBC. The application logic doesn´t changed, but some applications have higher response times than the
Oracleversion. We worked on database tunning but even so, the response times are very different. 
>
> I turned on logging sql statemants in the server (log_duration) and discovered that the sum of all SQL duration times
givesonly 30% of the total response time felt at the client. This gives me a suspect that the bottleneck is outside the
server.
>
> Am I in the right way?
>
> How can I discover the SQL duration times at the client? Is there some tool I could use?
>
> Thanks in advance!
>
> Reimer
> ________________________________________
> Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>


--
Cleber Nardelli
IPM Automação e Consultoria
9998-8670

Re: Application bottlenecks

From
"Merlin Moncure"
Date:
> Hi Merlin...
>
> We have an application that he uses one extfh that was modified by us
> so that it has access Oracle bases natively through the use of
> ProCobol.  I am interested in its work, would like to get greaters
> information.
>
> We use Microfocus NetExpress 3.0.
>
> Cleber.
>
> 2005/9/8, Merlin Moncure <merlin.moncure@rcsonline.com>:
> > Hi Carlos, I am working on project right now you might be interested
in,
> namely a extfh interface to postgresql and a isam wrapper for libpq.
I
> already have an isam bridge working direct to postgresql from AcuCobol
> without using odbc or a commercial driver.  I just need to convert it
to
> extfh and do a few other things to support microfocus.
> >
> > Merlin

I have a project open on gborg called 'postisam'.  Right now there is no
code and no information on it...but right now I am developing a COBOL
ERP which works with postgresql and AcuCobol.  The reason why there is
no public code is that the AcuCobol file interface requires re-linking
the runtime and AcuCorp does not grant redistribution rights on the key
header files required to do that.

With version 7.0 AcuCorp now supports extfh file interfaces so I will
convert my current project to support extfh.  In theory, this will allow
postisam to support any cobol compiler that supports extfh.  Over the
next couple of weeks I am going to be updating the PostISAM site with
new information and sending in the code that was been reworked with all
the AcuCorp stuff removed and made more portable.

Merlin



Re: Application bottlenecks

From
Hasns-Juergen Schoenig
Date:
Reimer,

Did you use Windows XP or Windows 2000?

    Best regards,

        Hans


Am 07.09.2005 um 13:24 schrieb Carlos Henrique Reimer:

Hello,
 
We are using Cobol Microfocus and pgsqlODBC to access Postgresql.
 
Originally these applications only accessed Oracle databases without ODBC, only Procobol and now we´re migrating to Postgresql  and ODBC. The application logic doesn´t changed, but some applications have higher response times than the Oracle version. We worked on database tunning but even so, the response times are very different.
 
I turned on logging sql statemants in the server (log_duration) and discovered that the sum of all SQL duration times gives only 30% of the total response time felt at the client. This gives me a suspect that the bottleneck is outside the server.
 
Am I in the right way?
 
How can I discover the SQL duration times at the client? Is there some tool I could use?
 
Thanks in advance!
 
Reimer 


Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!

Re: Application bottlenecks

From
"Merlin Moncure"
Date:

Ideally the odbc driver would log driver time and sql time (optionally) for each statement execution.  On windows this would mean rigging up the hardware counter to get accurate timing info.  This facility does not exist right now so your best bet is to write a small C test app that interfaces directly with the libpq library and compare latencies with the odbc driver.

 

You also need to factor in latency from the odbc ISAM wrapper provided by micofocus.

 

Merlin

 


From: Carlos Henrique Reimer [mailto:carlosreimer@yahoo.com.br]
Sent: Thursday, September 08, 2005 12:50 PM
To: Merlin Moncure
Subject: Re: [ODBC] Application bottlenecks

 

Merlin,

 

This will be very usefull. But we have a deadline and I´m not sure how much time we can wait. We must propose something to solve the worst cases at least.

 

But before that, I would like to be sure that our problem is the ODBC driver. Do you know how can I measure the ODBC overhead in our application?

 

Did you have similar problems?

 

Thanks,

 

Reimer

Merlin Moncure <merlin.moncure@rcsonline.com> escreveu:

> Hi Merlin...
>
> We have an application that he uses one extfh that was modified by us
> so that it has access Oracle bases natively through the use of
> ProCobol. I am interested in its work, would like to get greaters
> information.
>
> We use Microfocus NetExpress 3.0.
>
> Cleber.
>
> 2005/9/8, Merlin Moncure :
> > Hi Carlos, I am working on project right now you might be interested
in,
> namely a extfh interface to postgresql and a isam wrapper for libpq.
I
> already have an isam bridge working direct to postgresql from AcuCobol
> without using odbc or a commercial driver. I just need to convert it
to
> extfh and do a few other things to support microfocus.
> >
> > Merlin

I have a project open on gborg called 'postisam'. Right now there is no
code and no information on it...but right now I am developing a COBOL
ERP which works with postgresql and AcuCobol. The reason why there is
no public code is that the AcuCobol file interface requires re-linking
the runtime and AcuCorp does not grant redistribution rights on the key
header files required to do that.

With version 7.0 AcuCorp now supports extfh file interfaces so I will
convert my current project to support extfh. In theory, this will allow
postisam to support any cobol compiler that supports extfh. Over the
next couple of weeks I am going to be updating the PostISAM site with
new information and sending in the code that was been reworked with all
the AcuCorp stuff removed and made more portable.

Merlin



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly


Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!