From: Greg Smith <greg@2ndquadrant.com>
To: Benjamin Krajmalnik <kraj@servoyant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Configuration for a new server.
Date: Tue, 01 Feb 2011 20:16:26 -0500
Message-ID: <4D48B06A.6020100@2ndquadrant.com> (raw)
In-Reply-To: <F4E6A2751A2823418A21D4A160B689887B0E39@fletch.stackdump.local>
References: <F4E6A2751A2823418A21D4A160B689887B0E2A@fletch.stackdump.local>
<4D47F44D.2000305@2ndquadrant.com>
<F4E6A2751A2823418A21D4A160B689887B0E39@fletch.stackdump.local>
Benjamin Krajmalnik wrote:
>
> There are approximately 50 tables which get updated with almost 100%
> records updated every 5 minutes -- what is a good number of autovacuum
> processes to have on these? The current server I am replacing only
> has 3 of them but I think I may gain a benefit from having more.
>
>
> Watch pg_stat_user_tables and you can figure this out for your
> workload. There are no generic answers in this area.
>
> What in particular should I be looking at to help me decide?
>
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.
> You'd probably be better off decreasing the delay rather than pushing
> up the other two parameters. It's easy to tell if you did it right or
> not; just look at pg_stat_bgwriter. If buffers_backend is high
> relative to the others, that means the multiplier or delay is wrong.
> Or if maxwritten_clean is increasing fast, that means
> bgwriter_lru_maxpages is too low.
>
> checkpoints_timed = 261
>
> checkpoints_req = 0
>
> buffers_checkpoint = 49,058,438
>
> buffers_clean = 3,562,421
>
> maxwritten_clean = 243
>
> buffers_backend = 11,774,254
>
> buffers_alloc = 42,816,578
>
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@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, kraj@servoyant.com
Subject: Re: Configuration for a new server.
In-Reply-To: <4D48B06A.6020100@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox