From: Mark Stosberg <mark@summersault.com>
To: pgsql-performance@postgresql.org
Subject: Most effective tuning choices for busy website?
Date: Wed, 1 Jun 2005 19:19:03 +0000 (UTC)
Message-ID: <slrnd9s2n5.ca0.mark@simba.summersault.com> (raw)
Hello,
I'm the fellow who was interviewed in the fall about using PostgreSQL on
1-800-Save-A-Pet.com:
http://techdocs.postgresql.org/techdocs/interview-stosberg.php
The site traffic continues to grow, and we are now seeing parts of the
day where the CPU load (according to MRTG graphs) on the database server
is stuck at 100%. I would like to improve this, and I'm not sure where
to look first. The machine is a dedicated PostgreSQL server which two
web server boxes talk to.
I've used PQA to analyze my queries and happy overall with how they are
running. About 55% of the query time is going to variations of the pet
searching query, which seems like where it should be going. The query is
frequent and complex. It has already been combed over for appropriate
indexing.
I'm more interested at this point in tuning the software and hardware
infrastructure, but would like to get a sense about which choices will
bring the greatest reward.
Let me explain some avenues I'm considering.
- We are currently running 7.4. If I upgrade to 8.0 and DBD::Pg 1.42,
then the "server side prepare" feature will be available for use.
We do run the same queries a number of times.
- PhpPgAds seems to sucking up about 7.5% of our query time and is
unrelated to the core application. We could move this work to another
machine. The queries it generates seem like they have some room to
optimized, or simply don't need to be run in some cases. However, I
would like to stay out of modifying third-party code and PHP if
possible.
- I saw the hardware tip to "Separate the Transaction Log from the
Database". We have about 60% SELECT statements and 14% UPDATE
statements. Focusing more on SELECT performance seems more important
for us.
- We have tried to tune 'shared_buffers' some, but haven't seen a
noticeable performance improvement.
Our hardware: Dual 3 Ghz processors 3 GB RAM, running on FreeBSD.
I'm not quite sure how to check our average connection usage, but
maybe this is helpful: When I do:
select count(*) from pg_stat_activity ;
I get values around 170.
We have these values:
max_connections = 400
shared_buffers = 4096
Most other values in postgresql.conf are still at the their defaults.
Any suggestions are which avenues might offer the most bang for the buck
are appreciated!
( I have already found: http://www.powerpostgresql.com/PerfList/ and it has
been a very helpful source of suggestions. )
Mark
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: mark@summersault.com
Subject: Re: Most effective tuning choices for busy website?
In-Reply-To: <slrnd9s2n5.ca0.mark@simba.summersault.com>
* 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