Re: Seeking advice regarding a design problem

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.