On Thu, Oct 30, 2008 at 4:41 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > On Thu, Oct 30, 2008 at 3:41 PM, Oliver Johnson > <oliverjjohnson@xxxxxxxxx> wrote: > >> Another thing to note, we have VACUUM ANALYZE running on an hourly >> interval and the switch from CPU to IO wait appears to always coincide >> with a vacuum. > > Why are you not using autovacuum with appropriate wait parameters to > keep it out of your way? Autovacuum tends to make pretty good > decisions and you can adjust the aggressiveness with which it kicks in > if you need to. Thanks for the quick feedback. I struggled with autovacuum in a past life and developed a favor for explicit table level vacuums. Also, vacuum'ing did not jump out to me as a culprit originally as there is no significant impact (or indicators of duress) during the early day vacuums. You and Alan have brought up some good points, though. I turned autovacuum on and increased the checkpoint_segments. I will let it run over night and see how things look. Thanks again. > >> What might cause this shift? >> >> I have tried adjusting buffer_cache from 512 MB to 1024 MB, but this >> did not appear to have an impact. > > Do you mean shared_buffers? It may well be that larger shared_buffers > aren't going to help if you're dealing with a largely random > transactional load. that said, 1G shared_buffers is not that big > nowadays. I'm assuming by your testing methods you're on a real db > server with several dozen gigs of ram... > >> I also tried upping the work_mem from 1MB to 10MB, and this did not >> appear to have an impact either. > > Look into upping your checkpoint_segments (64 or so is reasonable for > a large production server) and possibly increasing your > checkpoint_completion_target to something closer to 1.0 (0.7 to 0.8) > and see if that helps. > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
