On Wed, 4 Nov 1998, Terry Mackintosh wrote:
> > I have a rather large database as well (> 2 Meg of tuples). I thought
> > my system was souped up enough: PII/400 MHz (100 MHz bus) 256 Meg SDRAM,
> > 18 Gig SCSI harddrive, Red Hat Linux 5.1. However, my swap space (512
> > Meg) is on the same harddrive as the database (albeit on a separate
> > partition). It sounds like you are saying that this is a no-no.
>
> Just that under heavy loads it may degrade performance as you yourself
> mention.
>
> > The database runs quite fast except with processes involving repetitive
> > inserts or updates. With each successive update in a continuous process,
> > the speed drops (almost like an exponentially decreasing process). Plus,
> > when this happens, I can't really use the computer that runs the
> > database because it is soooooo slow. When I run top, the computer is
> > using all 256 Meg of memory and going about 30-40 meg into swap space.
> > >From what you've suggested, this 30-40 meg of swap is also competing
> > with the database trying to write to the harddrive (since they are using
> > the same head).
>
> This is the type of performance degradation I was referring to.
>
> > If I put in a second drive exclusively for the swap space, could this
> > increase my speed? Or, would it be better to invest in more RAM so that
> > the job wouldn't need to use any swap space at all?
>
> Why not both? :-)
Are you running with fsync() disabled?
What version of PostgreSQL are you running? v6.4 has several
memory leak fixes in it, which may or may not help...on long term
connections, memory leak *may* be attributing to your problem. If you run
top while doing the 'update/inserts', does the process size just continue
to rise?
Something else to try...close and reconnect your insert/update
process(es). Not a long term solution, just curious if that shows an
overall speed improvement. Similar to the 'memory leak' problem, at least
this will let go of the process, clean out the memory, and start over
again....
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
