Subject: Re: Seeking advice regarding a design problem
From: Wei Weng <wweng@kencast.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20020802112052.Y41371-100000@megazone23.bigpanda.com>
References: <20020802112052.Y41371-100000@megazone23.bigpanda.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 02 Aug 2002 14:41:36 -0400
Message-Id: <1028313697.9763.45.camel@Monet>
Mime-Version: 1.0

On Fri, 2002-08-02 at 14:24, Stephan Szabo wrote:
> On 2 Aug 2002, Wei Weng wrote:
> 
> > I am running postgresql as database backend, and I have some scripts
> > dealing with constant incoming data and then insert these data into the
> > database, in a quite complex way, involving a couple of procedures.
> >
> > But the performance of the database is worse than I had thought. After
> > about 100 times of the script being run, the speed of the insertion
> > slowed down dramatically. But it went back to the regular fast speed
> > after I did a vacuum analyze.
> >
> > how can I redesign the system to avoid the bottleneck? And why is it
> 
> Upgrade to 7.2 so that you can vacuum while other things are going on
> and vacuum analyze after modifying a large portion of the database (note
> that if the database is particularly large you'll probably need to change
> the free space map configuration as well).
I found this in my postgresql.conf

#shared_buffers = 64        # 2*max_connections, min 16
#max_fsm_relations = 100    # min 10, fsm is free space map
#max_fsm_pages = 10000      # min 1000, fsm is free space map
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8            # min 4

Which ones are critical to the insertion performance? I looked for them
in the interactive dev doc, but the descriptions were not clear enough.

> 
> It's hard to tell what particularly you're running into, is it just a
> case that you're accessing the dead tuples and that's slowing it down,
What do you mean by "dead tuples"?


> is it that you're changing the data in a fashion that changes how the
> optimizer should be choosing to do queries, etc...
> 

Thanks!
 
-- 
Wei Weng
Network Software Engineer
KenCast Inc.