Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration - Mailing list pgsql-hackers

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