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