Re: Performance Tuning / RAM Usage

On Tue, 27 Aug 2002, Garo Hussenjian wrote:

> I am running postgresql 7.1.2 on redhat 7.2 and having problems with
> performance... During peak activity, the postgres processes lock up and
> report "UPDATE waiting" (under ps listing) with no light at the end of the
> tunnel.
>
> There is a script which has to insert up to a couple hundred new records
> into two large (800,000 record each) tables, and the inserts take well over
> a minute. I don't mind it taking time, but I'm concerned when the processes
> start failing all together!

Check for the size of both your tables and indexes, you could have a
problem with uncontrolled growth in one or the other.  you can use reindex
to recreate and index and reclaim lost space.

also, do all your inserts as a single transaction if you aren't already.

> I have 512M of RAM on a 1Ghz PIII server (Dell), and all database
> connections are made through php. They are all persistent connections.
>
> I am considering adding 1536M of additional RAM (for a grand total of 2G)
> and possibly updating to redhat 7.3 (with postgres 7.2) if necessary.

If you aren't running out of RAM and your CPUs are sitting at 100% during
this process, the extra memory probably won't help much.

> I am really looking to find the best way to configure the database to
> utilize the existing RAM (and any new RAM I may add). Also, to what degree
> will adding memory help the database performance if the select and insert
> operations are mostly disk-intensive?

turn off fsync.  It may not be the safest setting but it sure makes
postgresql go faster, and if you're on a stable machine with UPS that's
backed up daily, the chances of losing data because of it are pretty
small.