Message-ID: <51E82884.2090505@agliodbs.com>
Date: Thu, 18 Jul 2013 10:40:20 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130623 Thunderbird/17.0.7
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Re: bgwriter autotuning might be unnecessarily penalizing
 bursty workloads
References: <51E71EBF.1030702@agliodbs.com> <51E741CA.7020409@2ndQuadrant.com>
 <WM!535d62d3c4c6efb89be44b0660a8fb779d27d758aeb319fee71c04d1d2acae6000d966c343c4cc093077ec23d1d91955!@asav-3.01.com>
In-Reply-To: 
 <WM!535d62d3c4c6efb89be44b0660a8fb779d27d758aeb319fee71c04d1d2acae6000d966c343c4cc093077ec23d1d91955!@asav-3.01.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Greg,

> There look to be a good number of buffers on this server that are only
> being written at checkpoint time.  The background writer will only deal
> with buffers when their usage count is low.  Fast servers can cycle over
> shared_buffers such that as soon as their usage counts get low, they're
> immediately reallocated by a hungry backend.  You might try to quantify
> how many buffers the BGW can possibly do something with using
> pg_buffercache.

Yeah, that's going to be the next step.

> 2007's defaults can be a bummer in 2013.  I don't hesitate to bump that
> up to 500 on a server with decent hardware.

Right, that's what I just tested.  The results are interesting.  I
changed the defaults as follows:

bgwriter_delay = 100ms
bgwriter_lru_maxpages = 512
bgwriter_lru_multiplier = 3.0

... and the number of buffers being written by the bgwriter went *down*,
almost to zero.  Mind you, I wanna gather a full week of data, but there
seems to be something counterintuitive going on here.

One potential factor is that they have their shared_buffers set
unusually high (5GB out of 16GB).

Here's the stats:

postgres=# select * from pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed     | 330
checkpoints_req       | 47
checkpoint_write_time | 55504727
checkpoint_sync_time  | 286743
buffers_checkpoint    | 2809031
buffers_clean         | 789
maxwritten_clean      | 0
buffers_backend       | 457456
buffers_backend_fsync | 0
buffers_alloc         | 943734
stats_reset           | 2013-07-17 17:09:18.945194-07

So we're not hitting maxpages anymore, at all.  So why isn't the
bgwriter doing any work?

-[ RECORD 1 ]-----------+--------
pct_checkpoints_req     | 12.0
avg_frequency_min       | 2.78
avg_write_time_s        | 146.91
avg_sync_time_s         | 0.76
mb_written              | 25617.8
mb_written_per_min      | 24.42
mb_per_checkpoint       | 58.27
pct_checkpoint_buffers  | 86.0
pct_bgwriter_buffers    | 0.0
pct_backend_buffers     | 14.0
bgwriter_halt_freq      | 0.00
bgwriter_halt_potential | 0.00
buffer_allocation_ratio | 0.288


And your query, with some rounding added:

-[ RECORD 1 ]---+------
alloc_mbps      | 0.116
checkpoint_mbps | 0.340
clean_mbps      | 0.000
backend_mbps    | 0.056
write_mbps      | 0.396


-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance