Benjamin Krajmalnik wrote:
The information reported that's related to vacuuming. If you don't have enough workers, you can watch the dead row counts pop upwards without enough "last autovacuum time" updates on enough tables to suggest it's keeping up. If you see >20% dead rows on lots of tables and they're not being processed by AV and having their timestamps, that's your sign that you don't have enough workers.
See how buffers_backend is much larger than buffers_clean, even though maxwritten_clean is low? That means the background writer isn't running often enough to keep up with cleaning things, even though it does a lot of work when it does kick in. In your situation I'd normally do a first pass by cutting bgwriter_lru_maxpages to 1/4 of what it is now, cut bgwriter_delay to 1/4 as well (to 50ms), and then see how the proportions change. You can probably cut the multiplier, too, yet still see more pages written by the cleaner. I recommend saving a snapsot of this data with a timestamp, i.e.: select now(),* from pg_stat_bgwriter; Anytime you make a change to one of the background writer or checkpoint timing parameters. That way you have a new baseline to compare against. These numbers aren't very useful with a single value, but once you get two of them with timestamps you can compute all sorts of fun statistics from the pair. -- Greg Smith 2ndQuadrant US greg@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books |
