Re: Performance Bottleneck

On Tue, Aug 03, 2004 at 06:05:23PM +0000, 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!
>
> Martin Foster
> martin(at)ethereal-realms(dot)org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

Martin,

Maybe a connection pool middleware like pgpool could help. This would
allow a lower number of backends to serve the same frontends more
effectively and reduce the connection startup costs.

--Ken