pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Martin Foster <martin@ethereal-realms.org>
To: pgsql-performance@postgresql.org
Subject: Performance Bottleneck
Date: Tue, 03 Aug 2004 18:05:04 GMT
Message-ID: <ktQPc.23297$yT2.16983@clgrps13> (raw)

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@ethereal-realms.org




view thread (38+ messages)  latest in thread

Message-ID: <ktQPc.23297$yT2.16983@clgrps13>
Permalink:  ../ktQPc.23297$yT2.16983@clgrps13/
Also on:    postgresql.org/message-id/ktQPc.23297$yT2.16983@clgrps13

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: martin@ethereal-realms.org
  Subject: Re: Performance Bottleneck
  In-Reply-To: <ktQPc.23297$yT2.16983@clgrps13>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox