Re: Performance Bottleneck - Mailing list pgsql-novice

Scott Marlowe wrote:

> On Tue, 2004-08-03 at 12:05, Martin Foster wrote:
>
>>I run a Perl/CGI driven website that makes extensive use of PostgreSQL
>>(7.4.3) for everything from user information to formatting and display
>>of specific sections of the site.   The server itself, is a dual
>>processor AMD Opteron 1.4Ghz w/ 2GB Ram and 2 x 120GB hard drives
>>mirrored for redundancy running under FreeBSD 5.2.1 (AMD64).
>>
>>Recently loads on the site have increased during peak hours to the point
>>of showing considerable loss in performance.    This can be observed
>>when connections move from the 120 concurrent connections to PostgreSQL
>>to roughly 175 or more.     Essentially, the machine seems to struggle
>>to keep up with continual requests and slows down respectively as
>>resources are tied down.
>>
>>Code changes have been made to the scripts to essentially back off in
>>high load working environments which have worked to an extent.
>>However, as loads continue to increase the database itself is not taking
>>well to the increased traffic taking place.
>>
>>Having taken a look at 'Tuning PostgreSQL for Performance'
>>(http://www.varlena.com/GeneralBits/Tidbits/perf.html) using it as best
>>I could in order to set my settings.    However, even with statistics
>>disabled and ever setting tweaked things still consider to deteriorate.
>>
>>Is there anything anyone can recommend in order to give the system a
>>necessary speed boost?   It would seem to me that a modest dataset of
>>roughly a Gig combined with that type of hardware should be able to
>>handle substantially more load then what it is.  Can anyone provide me
>>with clues as where to pursue?    Would disabling 'fsync' provide more
>>performance if I choose that information may be lost in case of a crash?
>>
>>If anyone needs access to logs, settings et cetera.   Please ask, I
>>simply wish to test the waters first on what is needed. Thanks!
>
>
> Are you properly vacuuming the database and have you set your fsm
> settings high enough?  You may need to do a vacuum full first to reclaim
> lost space.
>
> Post your postgresql.conf settings if you can.
>

Actually, I have it do a VACUUM FULL ANALYZE nightly now.   On a weekly
basis, it would take about five minutes to run the maintenance process,
now it takes less then a minute.

I was also using the pg_autovacuum daemon with little to no performance
improvements overall when loads were light, but heavy penalties during
high load averages.   This has since been disabled in favour of the
existing script maintenance that I put into place already.

    Martin Foster
    Creator/Designer Ethereal Realms
    martin@ethereal-realms.org