 |
|
|
|
|
|
Comments: 42 |
The postgresql.conf file is pretty big and if you ever wondered what you should change in it but were too lazy to read documentation here are my modifications based on my experiences:
- work_mem - I use a value of 16MB instead of the default 1MB. This will enable postgresql to do more in-memory sorts wich is a lot faster than writing it out to disk.
- maintenance_work_mem - set it to 64MB (or even higher if you have a lot of RAM) to speed up autovacuums
- max_stack_depth - I always set it to the maximum: 7680kb - the documentation warns you that a too hi setting may cause server crash but it never happened to me
- vacuum_cost_delay - I leave it the default 0 but there may be some use because this option inserts pauses into the vacuum process that allows the HDD to catch up but as a sideeffect vacuums can be much slower
- bgwriter_delay - I usually increase this to 10000ms to reduce the number of writes
- checkpoint_segments - increase it if you do a lot of inserts for example to 20 or more so flushing will occur later
- seq_page_cost - this is measured in an arbitrary scale (meaning that only it's ratio is important to the other settings), I usually set it to 10 instead of the default 1 and leave the next four parameters untuched. This will make postgresql use the indexes more often instead of sequential scans or sorting; indexes are faster, maybe not for 10 rows but when you have 10 rows it doesn't really matter what you do
- log_min_messages - I alwas set this to fatal, because the default notice logs a lot of crap I never read anyway
- log_min_error_statement - I also always set this to fatal because I do not want to look in the server error log for failing sql statements, I log them in the applications I write
- stats_start_collector - set it to on; this is the default nowadays, I just mention it because it is needed for autovacuum
- stats_rowlevel - set it to on because it is needed for autovacuum
- autovacuum - set it to on - autovacuum is not mandatory especially if you do manual vacuums for example from a cronjob but vacuuming in general is a good thing because it helps the engine to use less HDD space and to make better statement execution plans; the rest of the autovacuum parameters you can leave untuched because my experiences say that the default values are the optimal ones
- shared_buffers - the developers of postgres recommend you to set this to 25% of your RAM but if you want to change this parameter you will probably need to increase the SHMMAX value of your kernel otherwise postgres will not start. On linux the default is 24MB but setting it to a higher value causes a decent performance boost. Just a warning: when you start postgres, if your SHMMAX is too low, postgres will write an entry to your logfile telling how much it wanted to allocate before exitting. When you set your SHMMAX it seems you have to use a bigger value, I'm not sure why but it seems an extra 50 mega is enough (maybe even less)
- fsync - setting this to off is the biggest performance boost ever but if you don't have a UPS or other means to ensure there will be no power losses leave this to on to avoid database inconsistency
- synchronous_commit - for people with postgres 8.3 or higher setting this to off is a safer alternative to the fsync parameter because you don't risc database inconsistency but the performance gain is also slightly lower; using this you just risc loosing a few transactions
- wal_writer_delay - if you turn synchronous_commit off this is the timeout your transactions will be buffered (and these may be lost;. you may increase this up to 10 seconds but on a very busy database a very high value is makes no difference to a low one so better safe then sorry
- tcp_keepalives_idle
- tcp_keepalives_interval
- tcp_keepalives_count - use these three parameters if you have long lived connections to your server but those may be idle for long times, for example if you develop applications and you use pgAdmin3. The values I set them to are 60, 10 and 3 but higher values are probably also good and generate less traffic.
That's all. If you also have some experiences please share them with the rest of us...
Last edited by soger at 2011-04-07 09:55 GMT |
 |
|
