Re: Replication/backup defaults

On 01/05/2017 02:23 PM, Magnus Hagander wrote:
>
>
> On Thu, Jan 5, 2017 at 12:44 AM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com <mailto:tomas(dot)vondra(at)2ndquadrant(dot)com>> wrote:
>
> On 01/03/2017 11:56 PM, Tomas Vondra wrote:
>
> Hi,
>
> ...
>
> I'll push results for larger ones once those tests complete
> (possibly
> tomorrow).
>
>
> I just pushed additional results (from the additional scales) to the
> git repositories. On the larger (16/32-cores) machine with 2x
> e5-2620, the results look like this
>
> scale minimal replica logical
> -----------------------------------------------------
> 100 23968 24393 24393
> 1000 23412 23656 23794
> 10000 5283 5320 5197
>
> and on the smaller one (i5-2500k with 4 cores) I got this:
>
> scale minimal replica logical
> -----------------------------------------------------
> 50 5884 5896 5873
> 400 5324 5342 5478
> 1000 5341 5439 5425
>
> The scales were chosen so that the smallest one fits into shared
> buffers, the medium exceeds shared buffers but still fits into RAM,
> and the largest scale exceeds RAM.
>
> The results seem to confirm that for this workload (regular
> pgbench), there's very little difference between the different WAL
> levels. Actually, the 'replica' seems a tad faster than 'minimal',
> but the difference may be easily due to noise.
>
> I've also looked at the amount of WAL actually produced, by doing
> pgbench runs throttled to the same throughput, and counting the
> number of archived WAL segments & running pg_xlogdump. Interestingly
> enough, those two metrics differ quite a bit - for example for scale
> 1000 (on the 32-core machine), the 2h runs produced these number of
> WAL segments:
>
> minimal: 5515 (88.2GB)
> replica: 5587 (89.4GB)
> logical: 6058 (96.9GB)
>
> so 'replica' adds ~1.3% and 'logical' ~9.8%. But per pg_xlogdump,
> the WAL amounts are only 73.3GB, 73.9GB and 74.4GB - a difference of
> only ~1.5% between minimal and logical. The values are also much
> lower than raw WAL size, so I assume it's because pg_xlogdump
> ignores some extra overhead, present in the segments. Moreover, the
> sequential nature of WAL writes means even the +10% is not a big
> deal (unless it results in saturating the bandwidth, but running on
> >90% is a bad idea anyway).
>
>
> If you are using log archiving, it also means your log archive grows by
> 10% (well, 8% assuming it was 9.8% on top of 0, not on top of replica).
>

... and that the standby has to chew through the additional 10% of WAL.
We already have standbys that occasionally struggle to keep up with the
master, and adding more load won't make them happy (even if just 10%).

>
>
> My conclusion from these results is that using 'wal_level=replica'
> by default seems fine. Perhaps even wal_level=logical would be OK,
> but that's probably a too big step for 10.0.
>
>
>
> I think it sounds like 'replica' is the safe default.
>
> If we can make it possible to go replica<->logical without a restart,
> that makes it easy enough to increase it if necessary, and the default
> still applies to most people (most people take backups, most people
> probably don't do logical replication).
>

My thoughts, exactly.

>
>
> Any ideas how to construct a plausible workload where the
> differences are significantly larger? Running the tests on non-SSD
> storage might also be useful.
>
>
> It's easy enough to construct a benchmark specifically to show the
> difference, but of any actual "normal workload" for it. Typically the
> optimization applies to things like bulk loading, which typically never
> done alone and does not lend itself to that type of benchmarking very
> easily.
>

Not sure if I understand correctly what you're saying. You're saying
that although it'd be easy to construct a benchmark showing significant
performance impact, it won't represent a common workload. Correct?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services