On 04/08/2004 13:45 Paul Serby wrote:
Can anyone give a good reference site/book for getting the most out of your postgres server.
All I can find is contradicting theories on how to work out your settings.
This is what I followed to setup our db server that serves our web applications.
http://www.phpbuilder.com/columns/smith20010821.php3?page=2
We have a Dell Poweredge with the following spec.
CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) Physical Memory: 2077264 kB Swap Memory: 2048244 kB
Apache on the Web server can take up to 300 connections and PHP is using pg_pconnect
Postgres is set with the following.
max_connections = 300 shared_buffers = 38400
Might be higher that neccessary. Some people reckon that there's no measurable performance going above ~10,000 buffers
sort_mem = 12000
Do you really need 12MB of sort memory? Remember that this is per connection so you could end up with 300x that being allocated in a worst case scenario.
But Apache is still maxing out the non-super user connection limit.
The machine is under no load and I would like to up the max_connections but I would like to know more about what you need to consider before doing so.
I can't think why you should be maxing out when under no load. Maybe you need to investigate this further.
The only other source I've found is this:
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
But following its method my postgres server locks up straight away as it recommends setting max_connections to 16 for Web sites?
I think you've mis-interpreted that. She's talking about using persistent connections - i.e., connection pooling.
Is there a scientific method for optimizing postgres or is it all 'finger in the air' and trial and error.
Posting more details of the queries which are giving the performance problems will enable people to help you. You're vacuum/analyzing regularly of course ;) People will want to know:
- PostgreSQL version - hardware configuration (SCSI or IDE? RAID level?) - table schemas - queries together with EXPLAIN ANALYZE output
also output from utils like vmstat, top etc may be of use.
HTH
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for Business |
| Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
