Re: measuring the impact of increasing WAL segment size

Hi,

On 08/15/2017 03:37 AM, Andres Freund wrote:
> Hi,
>
> Thanks for running this!
>
> On 2017-08-15 03:27:00 +0200, Tomas Vondra wrote:
>> Granted - this chart does not show latency, so it's not a complete
>> picture.
>
> That'd be quite useful to see here, too.
>

Unfortunately I don't have the detailed transaction log, with latencies
for individual transactions - that would be an order of magnitude more
of data, so I decided not to collect that for the initial tests. I'm
willing to collect that info for a smaller subset of the tests, though,
so if you can pick a few combinations that you find interesting, I'll
get that running.

What I have collected is the aggregated transaction log, with 1-second
resolution, i.e. for each second we have TPS, min/max latency, sum(lat)
and sum(lat*lat), etc. The best way to summarize this is CDF chart, I
think, and I have already generated those into the git repository.

See for example this, with charts for the SATA RAID case:

https://github.com/tvondra/wal-segment-size-tests/tree/master/xeon-e5-2620v4-sata-raid/simple-update/300/flush

The latency-max-cdf.png shows max(latency) for the 14400 samples (each
second of the 4-hour run), latency-stddev-cdf.png shows STDDEV(latency).
It's not perfect, but better than nothing.

Reading CDF charts is not difficult - essentially lines 'above' other
lines are better, as it shows most values are lower (which is what we
want both for latency and stddev).

>
>> Also, if you care about raw OLTP performance you're probably
>> already running on flash, where this does not seem to be an issue.
>> It's also not an issue if you have RAID controller with write
>> cache, which can absorb those writes. And of course, those
>> machines have reasonable dirty_background_bytes values (like 64MB
>> or less).
>
> The problem is that dirty_background_bytes = 64MB is *not* actually
> a generally reasonable config, because it makes temp table, disk
> sort, etc operations flush way too aggressively.
>

That is a good point. I with we could set amount of dirty data per
filesystem, that would make configuring this so much simpler ...

>
>> b) The "flushing enabled" case seems to be much more sensitive to
>> WAL segment size increases. It seems the throughput drops a bit (by
>> 10-20%), for some segment sizes, and then recovers. The behavior
>> seems to be smooth (not just a sudden drop for one segment size)
>> but the value varies depending on the scale, test type (tpc-b
>> /simple-update).
>
> That's interesting. I presume you've not tested with separate data /
> xlog disks?
>

No, mostly because of lack of time / additional complexity. But I'm
willing to repeat some of the tests in a more complex setup. Perhaps you
can recommend which test to rerun?

regards

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