Re: Insertion puzzles

On Sat, 2004-11-13 at 18:00, dentfirst13(at)earthlink(dot)net wrote:
> I ran into the exact same problem you did. I tried many, many changes to
> the conf file, I tried O.S. tuning but performance stunk. I had a fairly
> simple job that had a lot of updates and inserts that was taking 4 1/2
> hours. I re-wrote it to be more "Postgres friendly" - meaning less
> database updates and got it down under 2 1/2 hours (still horrible).
> Understand, the legacy non-postgres ISAM db took about 15 minutes to
> perform the same task. I assumed it was a system problem that would go
> away when we upgraded servers but it did not. I converted to MySQL and the
> exact same java process takes 5 minutes! Postgres is a great DB for some,
> for our application it was not - you may want to consider other products
> that are a bit faster and do not require the vacuuming of stale data.

I have to wonder if the difference is in how your job is being chopped
up by the different connection mechanisms. The only time I've had
performance problems like this, it was the result of pathological and
unwelcome behaviors in the way things were being handled in the
connector or database design.

We have a 15GB OLTP/OLAP database on five spindles with a large
insert/update load and >100M rows, and I don't think it takes 2.5 hours
to do *anything*. This includes inserts/updates of hundreds of
thousands of rows at a shot, which takes very little time.

I've gotten really bad performance before under postgres, but once I
isolated the reason I've always gotten performance that was comparable
to any other commercial RDBMS on the same hardware.

J. Andrew Rogers