
> So this finaly makes the batch work taking 300% the time Oracle needs.
> We clearly see our ECPG programs waits for PostgreSQL in the functions
> were CURSORs are opened. Then, we know the problem is not in ECPG but in
> PG backend.

> This is unaceptable for our customer. Many batches are launched during
> the night and have to be completed in 5h (between 0h and 5h). With a
> ratio of 3, this is not worth think about migration anymore :-(

So why exactly can you not simply do the whole batch in one transaction ?

Unless you need to run concurrent vacuums, or are low on disk space, or need 
to concurrently update the affected rows (thus fear deadlocks or locking out
interactive clients that update), there is no need to do frequent commits in 
PostgreSQL for batch work.

Andreas

PS: I know that coming from other DB's one fears "snapshot too old", filling
rollback segments, or other "deficiencies" like long transaction aborted :-)

Wed, 13 Mar 2002 22:30:59 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration
On Thu, 2002-03-14 at 02:30, Zeugswetter Andreas SB SD wrote:
> 
> > So this finaly makes the batch work taking 300% the time Oracle needs.
> > We clearly see our ECPG programs waits for PostgreSQL in the functions
> > were CURSORs are opened. Then, we know the problem is not in ECPG but in
> > PG backend.
> 
> > This is unaceptable for our customer. Many batches are launched during
> > the night and have to be completed in 5h (between 0h and 5h). With a
> > ratio of 3, this is not worth think about migration anymore :-(
> 
> So why exactly can you not simply do the whole batch in one transaction ?
> 
> Unless you need to run concurrent vacuums,

I ran some tests based on their earlier description and concurrent
vacuums (the new, non-locking ones) are a must, best run every few
seconds, as without them the ratio of dead/live tuples will be huge and
that will bog down the whole process.

> or are low on disk space, or need 
> to concurrently update the affected rows (thus fear deadlocks or locking out
> interactive clients that update), there is no need to do frequent commits in 
> PostgreSQL for batch work.

I also suspect (from reading their description) that the main problem of
parsing/optimising each and every similar query will remain even if they
do run in one transaction.

In my tests of simple updates I got 3/2 speed increase (from 1050 to
1500 updates/sec) by using prepared statements inside a stored procedure
--------------------
Hannu


14 Mar 2002 09:32:19 +0500
Hannu Krosing <hannu@krosing.net>
Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration
Le Thursday Mar 14, 2002 at 09:32:19AM +0500, Hannu Krosing a ï¿½crit :
> On Thu, 2002-03-14 at 02:30, Zeugswetter Andreas SB SD wrote:
> > 
> > > So this finaly makes the batch work taking 300% the time Oracle needs.
> > > We clearly see our ECPG programs waits for PostgreSQL in the functions
> > > were CURSORs are opened. Then, we know the problem is not in ECPG but in
> > > PG backend.
> > 
> > > This is unaceptable for our customer. Many batches are launched during
> > > the night and have to be completed in 5h (between 0h and 5h). With a
> > > ratio of 3, this is not worth think about migration anymore :-(
> > 
> > So why exactly can you not simply do the whole batch in one transaction ?

Ah! Sorry forgot to say this work has double use. In fact, it can be
used in batch and it is also used in a kind of "daemon".

This daemon wakes up every 5 seconds. It scans (SELECT...) for new
insert in a table (lika trigger). When new tuples are found, it launches
the work. The work consist in computing total sales of a big store...

Each receipt as many items. The batch computes total sales for each
section/sector of the store.

The "daemon" mode permit having a total sales in "real time"...

The batch mode is here to compute final total sales in the end of the
day. It can be also use to compute back previous days (up to 5).

So, putting "the whole batch in one transaction" is not possible, due to
daemon mode. A commit by receipt also permit to not loose previous
database work in the case the daemon goes down, for example.

> > Unless you need to run concurrent vacuums,

Forgot to say too that de x3 ratio is based only on batch mode. Daemon
mode is as faster as Oracle (wow!).

Forgot to say too that in batch mode we launch concurrent vacuum analyze
on the 2 tables constantly accessed (update/inserts only : updating
total sale by sector/ sub-sector/ sub-sub-sector, etc.. the total sales
has a tree structure then).

The vacuum analyze on those 2 tables has a sleep of 10 s, in a 
while [ 1 ] loop in a .sh
 
> I ran some tests based on their earlier description and concurrent
> vacuums (the new, non-locking ones) are a must, best run every few
> seconds, as without them the ratio of dead/live tuples will be huge and
> that will bog down the whole process.

Yes, concurrent vaccums is really *GREAT* without it, the batch work is
going slower and slower with time. Concurrent vaccum allows constant
performances.
 
> I also suspect (from reading their description) that the main problem of
> parsing/optimising each and every similar query will remain even if they
> do run in one transaction.

Exactly.
 
To answer a question in this thread: the batch has really basic SQL
statments! CURSORS are really simple too, based on 1 to 2 "bind
variables" that unfortunately are not processed the same way has Oracle.
:-(

Thanks for your support, much appreciated :-))

-- 
Jean-Paul ARGUDO

Thu, 14 Mar 2002 10:20:53 +0100
Jean-Paul ARGUDO <jean-paul.argudo@idealx.com>
Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration
On Thu, 2002-03-14 at 11:20, Jean-Paul ARGUDO wrote:
> > > Unless you need to run concurrent vacuums,
> 
> Forgot to say too that de x3 ratio is based only on batch mode. Daemon
> mode is as faster as Oracle (wow!).
> 
> Forgot to say too that in batch mode we launch concurrent vacuum analyze
> on the 2 tables constantly accessed (update/inserts only : updating
> total sale by sector/ sub-sector/ sub-sub-sector, etc.. the total sales
> has a tree structure then).
> 
> The vacuum analyze on those 2 tables has a sleep of 10 s, in a 
> while [ 1 ] loop in a .sh

If the general distribution of values does not drastically change in
these tables then you can save some time by running just VACUUM, not
VACUUM ANALYZE.

VACUUM does all the old tuple removing work

VACUUM ANALYZE does that + also gathers statistics which make it slower.
  
> > I ran some tests based on their earlier description and concurrent
> > vacuums (the new, non-locking ones) are a must, best run every few
> > seconds, as without them the ratio of dead/live tuples will be huge and
> > that will bog down the whole process.
> 
> Yes, concurrent vaccums is really *GREAT* without it, the batch work is
> going slower and slower with time. Concurrent vaccum allows constant
> performances.
>  
> > I also suspect (from reading their description) that the main problem of
> > parsing/optimising each and every similar query will remain even if they
> > do run in one transaction.
> 
> Exactly.
>  
> To answer a question in this thread: the batch has really basic SQL
> statments! CURSORS are really simple too, based on 1 to 2 "bind
> variables" that unfortunately are not processed the same way has Oracle.
> :-(

can you give me a small made-up example and then tell me what
performance you get on Oracle/NT and what on PostgreSQL/Linux ?

I'd like to try to move cursor -> backend proc and see 

1) if it is big enough gain to warrant further work

2) if it can be done automatically, either by preprocessing ECPG or just
   changing it

--------------
Hannu



14 Mar 2002 15:53:57 +0200
Hannu Krosing <hannu@tm.ee>
Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration
On Thu, 14 Mar 2002, Jean-Paul ARGUDO wrote:

> This daemon wakes up every 5 seconds. It scans (SELECT...) for new
> insert in a table (lika trigger). When new tuples are found, it
> launches the work. The work consist in computing total sales of a big
> store...

You might find it worthwhile to investigate "listen" and
"notify" -- combined with a rule or trigger, you can get
this effect in near-real-time

You'll probably still want a sleep(5) at the end of the
loop so you can batch a reasonable number of updates if
there's a lot going on.

Matthew.


Thu, 14 Mar 2002 16:15:25 +0000 (GMT)
Matthew Kirkwood <matthew@hairy.beasts.org>
Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration