just lately we found interesting case about shared_buffers settings.
the database in question is rather simple:
- 3 tables
- a bit over 60 gigabytes
- around 150 million rows
the server we run it on is not really fancy:
- 2, single core, 3ghz xeons
- 16 gb of ram
- 6x 72gb, scsi, 15krpm discs in low-end hardware raid 10.
initially postgresql was configured to use 2gb for shared_buffers. it's definitely less than “generally suggested" 20-25%, but so it was.
the problem we had was that during checkpoints (every 5 minutes) load on discs was so high that the whole system basically freezed. effect: load around 20, unhappy clients.
bgwriter tuning didn't really gave us much of anything, so we decided to make more “interesting" changes. for starters – increase checkpoint_timeout to 30 minutes. load dropped, but when 30 minutes passed load spiked over 30.
then we thought about the least “sensible" change – checkpoint saves data from shared_buffers. so let's limit the amount of data in shared buffers. shared_buffers were brought down to 128megs. effect – load in peak hours of day hits 3. not 30. 3! daily average is around 1.5!
now, lowering shared_buffers is definitely not for everyone. but, it just happened that it did help (a lot!) in our case.