From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: Re: bgwriter autotuning might be unnecessarily penalizing bursty workloads
Date: Thu, 18 Jul 2013 10:40:20 -0700
Message-ID: <51E82884.2090505@agliodbs.com> (raw)
In-Reply-To: <WM!535d62d3c4c6efb89be44b0660a8fb779d27d758aeb319fee71c04d1d2acae6000d966c343c4cc093077ec23d1d91955!@asav-3.01.com>
References: <51E71EBF.1030702@agliodbs.com>
<51E741CA.7020409@2ndQuadrant.com>
<WM!535d62d3c4c6efb89be44b0660a8fb779d27d758aeb319fee71c04d1d2acae6000d966c343c4cc093077ec23d1d91955!@asav-3.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: josh@agliodbs.com
Subject: Re: Re: bgwriter autotuning might be unnecessarily penalizing bursty workloads
In-Reply-To: <51E82884.2090505@agliodbs.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