From: | Hannu Krosing <hannu(at)krosing(dot)net> |
---|---|
To: | Zeugswetter Andreas SB SD <ZeugswetterA(at)spardat(dot)at> |
Cc: | Jean-Paul ARGUDO <jean-paul(dot)argudo(at)idealx(dot)com>, pgsql-hackers(at)postgresql(dot)org, David BARTH <dbarth(at)idealx(dot)com>, Nat MAKAREVITCH <nat(at)idealx(dot)com>, Nicolas NICLAUSSE <nicolas(dot)niclausse(at)idealx(dot)com>, Sébastien DINOT <sebastien(dot)dinot(at)idealx(dot)com> |
Subject: | Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration |
Date: | 2002-03-14 04:32:19 |
Message-ID: | 1016080340.2249.43.camel@rh72.home.ee |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ashley Cambrell | 2002-03-14 05:16:19 | Pre-preparing / parsing SQL statements |
Previous Message | Hannu Krosing | 2002-03-14 04:08:55 | Re: Survey results on Oracle/M$NT4 to PG72/RH72 migration |