Re: increasing the default WAL segment size

On Tue, Sep 20, 2016 at 4:25 PM, Andres Freund <andres@anarazel.de> wrote:
>> Even with a 1GB segment size, some of them will fill multiple files
>> per minute.  At the current limit of 64MB, a few of them would still
>> fill more than one file per second.  That is not sane.
>
> I doubt generating much larger files actually helps a lot there. I bet
> you a patch review that 1GB files are going to regress in pretty much
> every situation; especially when taking latency into account.

Well, you have a point: let's find out.  Suppose we create a cluster
that generates WAL very quickly, and then try different WAL segment
sizes and see what works out best.  Maybe something like: create N
relatively small tables, with 100 or so tuples in each one.  Have N
backends, each assigned one of those tables, and it just updates all
the rows over and over in a tight loop.  Or feel free to suggest
something else.

> I think what's actually needed for that is:
> - make it easier to implement archiving via streaming WAL; i.e. make
>   pg_receivexlog actually usable
> - make archiving parallel
> - decouple WAL write & fsyncing granularity from segment size
>
> Requiring a non-default compile time or even just cluster creation time
> option for tuning isn't something worth expanding energy on imo.

I don't agree.  The latency requirements on an archive_command when
you're churning out 16MB files multiple times per second are insanely
tight, and saying that we shouldn't increase the size because it's
better to go redesign a bunch of other things that will eventually
*maybe* remove the need for archive_command does not seem like a
reasonable response.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company