From tr75@gmx.net Wed Jul  12 08:33:56 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id A57F59FB1DC
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Jul 2006 05:33:56 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 71578-10 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Jul 2006 05:33:46 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from mail.gmx.net (mail.gmx.net [213.165.64.21])
	by postgresql.org (Postfix) with SMTP id A75849FA634
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Jul 2006 05:33:46 -0300 (ADT)
Received: (qmail 23573 invoked by uid 0); 12 Jul 2006 08:33:44 -0000
Received: from 82.103.0.42 by www032.gmx.net with HTTP;
	Wed, 12 Jul 2006 10:33:44 +0200 (CEST)
Content-Type: multipart/alternative;
	boundary="========GMXBoundary4421152693224656719"
Date: Wed, 12 Jul 2006 10:33:44 +0200
From: "Thomas Radnetter" <tr75@gmx.net>
Message-ID: <20060712083344.4420@gmx.net>
MIME-Version: 1.0
Subject: Performance Problem between Ora 10g and Psql
To: pgsql-performance@postgresql.org
X-Authenticated: #6387982
X-Flags: 0001
X-GMX-UID: mqCBdUZxIyd16iscFWdrU99aa2FkZlWR
X-Mailer: WWW-Mail 6100 (Global Message Exchange)
X-Priority: 3
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/102
X-Sequence-Number: 19962

--========GMXBoundary4421152693224656719
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 8bit

Hello!

We're facing a performance problem here in a Oracle 10g -> PostgreSQL 
environment. The Oracle DB accesses the PostgreSQL DB via UnixODBC + 
psqlODBC.

There are some 100 records on the Oracle DB to be updated with data 
obtained from a view of the PostgreSQL DB. The fetched data accumulates to 
a few kB's and the update takes some 5 - 7 hours. During this time the 
PostgreSQL machine is running at approx. 100% CPU usage.
If a select for the same data is issued from the Oracle DB the statement is 
executed in half a second.

Is this the correct place to issue this problem?

How can I trace down the cause for this performance problem?

Thanx in advance!

Regards, 
Thomas Radnetter

p.s. Mr. Ludek Finstrle, can you help again?         

-- 

 "Feel free" &ndash; 10 GB Mailbox, 100 FreeSMS/Monat ...
 Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail     
-- 


"Feel free" – 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

--========GMXBoundary4421152693224656719
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: 7bit

<html>
    <head>
        <title></title>
        <style media="screen" type="text/css">
body {font-family:verdana, arial, helvetica, sans-serif;font-size:10px;padding:5px;margin:0;background-color:#FFF;}
p, ul, li {margin-top:0;margin-bottom:0;}
blockquote {margin-left:5px;}
div.signature {color:#666;font-size:0.8em;}

body {font-family:verdana, arial, helvetica, sans-serif;font-size:10px;padding:5px;margin:0;background-color:#FFF;}
p, ul, li {margin-top:0;margin-bottom:0;}
blockquote {margin-left:5px;}
div.signature {color:#666;font-size:0.8em;}</style>
    </head>
    <body style="margin: 0pt; padding: 0pt;">
        <font size="3">         Hello!<br /><br />We're facing a performance problem here in a Oracle 10g -&gt; PostgreSQL environment. The Oracle DB accesses the PostgreSQL DB via UnixODBC + psqlODBC.<br /><br />There are some 100 records on the Oracle DB to be updated with data obtained from a view of the PostgreSQL DB. The fetched data accumulates to a few kB's and the update takes some 5 - 7 hours. During this time the PostgreSQL machine is running at approx. 100% CPU usage.<br />If a select for the same data is issued from the Oracle DB the statement is executed in half a second.<br /><br />Is this the correct place to issue this problem?<br /><br />How can I trace down the cause for this performance problem?<br /><br />Thanx in advance!<br /><br /><br />Regards, <br />Thomas Radnetter<br /><br />p.s. Mr. Ludek Finstrle, can you help again?</font>
        <div class="signature"> <br /><br /><br />-- <br /><br /><br /> &quot;Feel free&quot; &ndash; 10 GB Mailbox, 100 FreeSMS/Monat ...<br /> Jetzt GMX TopMail testen: <a href="http://www.gmx.net/de/go/topmail">http://www.gmx.net/de/go/topmail</a> </div>
    <div class="signature"><br /><br /><br />-- <br /><br /><br />
"Feel free" &#x2013; 10 GB Mailbox, 100 FreeSMS/Monat ...<br/>
Jetzt GMX TopMail testen: <a href="http://www.gmx.net/de/go/topmail">http://www.gmx.net/de/go/topmail</a>
</div></body>
</html>

--========GMXBoundary4421152693224656719--



From guillaume.smet@gmail.com Wed Jul  12 09:47:17 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 801299FB200
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Jul 2006 06:47:17 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 97599-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Jul 2006 06:47:08 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from py-out-1112.google.com (py-out-1112.google.com
 [64.233.166.180])
	by postgresql.org (Postfix) with ESMTP id 471119FB1DC
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Jul 2006 06:47:07 -0300 (ADT)
Received: by py-out-1112.google.com with SMTP id s49so118136pyc
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Jul 2006 02:47:06 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=n+sEIPd6TIiN0Ba+30gC4j6DRJInE30X05pigT3iJR1m3kUK6KqugHGW8fgzC5/V4AYEokUGtt8MvL8cJirNSYKnuEYouCl840GoRCZ3rSIr8omKgrvHEEtfItz8b9Cgx4zxMBn2kGqkW6pLHTlEmkcy6ON6Alqwflc6EJs0L9Y=
Received: by 10.35.20.14 with SMTP id x14mr647076pyi;
	Wed, 12 Jul 2006 02:47:06 -0700 (PDT)
Received: by 10.35.53.5 with HTTP; Wed, 12 Jul 2006 02:47:06 -0700 (PDT)
Message-ID: <1d4e0c10607120247m4e7e4339p6dd97df9d8c641d7@mail.gmail.com>
Date: Wed, 12 Jul 2006 11:47:06 +0200
From: "Guillaume Smet" <guillaume.smet@gmail.com>
To: "Thomas Radnetter" <tr75@gmx.net>
Subject: Re: Performance Problem between Ora 10g and Psql
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20060712083344.4420@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <20060712083344.4420@gmx.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/103
X-Sequence-Number: 19963

Thomas,

On 7/12/06, Thomas Radnetter <tr75@gmx.net> wrote:
> Is this the correct place to issue this problem?

It is if your issue is due to a PostgreSQL performance problem.

> How can I trace down the cause for this performance problem?

The first thing to do is to determine if it is a problem due to the
Oracle -> ODBC -> PostgreSQL thing or if it is a problem with the
query. My advice is to set log_min_duration_statement to 0 in your
postgresql.conf (and set the logger so that you can see the log output
somewhere). Then you'll see if your query is slow.

If your query is slow, post the output of an explain analyze on the
list with all the relevant information (structure of the concerned
tables, indexes, size...).

If not, it's probably more an ODBC problem.

Regards,

--
Guillaume Smet
Open Wide



From jd@commandprompt.com Wed Jul  12 14:34:11 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 933389FB377
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Jul 2006 11:34:11 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 41936-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Jul 2006 11:34:00 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from projects.commandprompt.com (host-130.commandprompt.net
	[207.173.203.130])
	by postgresql.org (Postfix) with ESMTP id 503F69FB372
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Jul 2006 11:34:01 -0300 (ADT)
Received: from [192.168.1.50] (or-67-76-146-141.sta.embarqhsd.net
	[67.76.146.141]) (authenticated bits=0)
	by projects.commandprompt.com (8.13.6/8.13.6) with ESMTP id
	k6CEXqoq015859; Wed, 12 Jul 2006 07:33:54 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
To: pgsql-performance@postgresql.org
Subject: Re: Performance Problem between Ora 10g and Psql
Date: Wed, 12 Jul 2006 07:33:52 -0700
User-Agent: KMail/1.9.1
Cc: "Thomas Radnetter" <tr75@gmx.net>
References: <20060712083344.4420@gmx.net>
In-Reply-To: <20060712083344.4420@gmx.net>
XIDTRACK: jd@commandprompt.com
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200607120733.52847.jd@commandprompt.com>
X-Virus-Scanned: ClamAV version 0.88.2,
	clamav-milter version 0.88.2 on projects.commandprompt.com
X-Virus-Status: Clean
X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by
	milter-greylist-1.6 (projects.commandprompt.com
	[192.168.2.159]); Wed, 12 Jul 2006 07:33:54 -0700 (PDT)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/104
X-Sequence-Number: 19964

On Wednesday 12 July 2006 01:33, Thomas Radnetter wrote:
> Hello!
>
> We're facing a performance problem here in a Oracle 10g -> PostgreSQL
> environment. The Oracle DB accesses the PostgreSQL DB via UnixODBC +
> psqlODBC.
>
> There are some 100 records on the Oracle DB to be updated with data
> obtained from a view of the PostgreSQL DB. The fetched data accumulates to
> a few kB's and the update takes some 5 - 7 hours. During this time the
> PostgreSQL machine is running at approx. 100% CPU usage.
> If a select for the same data is issued from the Oracle DB the statement is
> executed in half a second.
>
> Is this the correct place to issue this problem?

Sure but you haven't provided a TON of information that would be needed to 
help?

If you execute the same query that is being executed via ODBC, via psql is the 
performance problem still there?

If so, you probably have a postgresql issue, otherwise look at Oracle or ODBC.

If it is a PostgreSQL issue:
 Do you have indexes applied?
 What is the explain plan?
 When was the last time you analyzed?
 What about vacuum?

etc. etc.

Sincerely,

Joshua D. Drake


>
> How can I trace down the cause for this performance problem?
>
> Thanx in advance!
>
> Regards,
> Thomas Radnetter
>
> p.s. Mr. Ludek Finstrle, can you help again?
>
> --
>
>  "Feel free" &ndash; 10 GB Mailbox, 100 FreeSMS/Monat ...
>  Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail

-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/