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