I'm the fellow who was interviewed in the fall about using PostgreSQL on

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

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

 - 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. )


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to