Re: WAL Rate Limiting

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Magnus Hagander <magnus(at)hagander(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: WAL Rate Limiting
Date: 2014-01-16 16:20:19
Message-ID: CA+U5nMKXDzJvAS5furAJFZ_JV7dzrCGc2oBRBRNKu1+JusyWZA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 16 January 2014 16:56, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-01-16 17:47:51 +0200, Heikki Linnakangas wrote:
>> On 01/16/2014 05:39 PM, Andres Freund wrote:
>> >On 2014-01-16 10:35:20 -0500, Tom Lane wrote:
>> >>I think possibly a more productive approach to this would be to treat
>> >>it as a session-level GUC setting, rather than hard-wiring it to affect
>> >>certain commands and not others.
>> >
>> >Do you see a reasonable way to implement this generically for all
>> >commands? I don't.
>>
>> In suitable safe places, check if you've written too much WAL, and sleep if
>> so. Call it CHECK_FOR_WAL_BUDGET(), along the lines of
>> CHECK_FOR_INTERRUPTS(), but called less frequently. Or maybe
>> vacuum_delay_point() is a better analogy. Hopefully you don't need to
>> sprinkle them in too many places to be useful.
>
> That'd be most places doing XLogInsert() if you want to be
> consistent. Each needing to be analyzed whether we're blocking important
> resources, determining where in the callstack we can do the
> CHECK_FOR_WAL_BUDGET().
>
> I don't think the the add power by allowing bulk DML to be metered is
> worth the increased implementation cost.
>
> I think the usecases that would want this for DML probably also wan this
> to work for unlogged, temp tables. That'd require a much more generic
> resource control framework.

Thank you everyone for responding so positively to this proposal. It
is something many users have complained about.

In time, I think we might want both WAL Rate Limiting and I/O Rate
Limiting. IMHO I/O rate limiting is harder and so this proposal is
restricted solely to WAL rate limiting.

I'm comfortable with a session level parameter. I was mulling over a
wal_rate_limit_scope parameter but I think that is too much.
I will follow Craig's proposal to define this in terms of MB/s, adding
that I would calc from beginning of statement to now, so it is
averaged rate. Setting of zero means unlimited. The rate would be
per-session (in this release) rather than a globally calculated
setting.

I would like to call it CHECK_FOR_WAL_RATE_LIMIT()

That seems like a cheap enough call to allow it to be added in more
places, so my expanded list of commands touched are
CLUSTER/VACUUM FULL
ALTER TABLE (only in phase 3 table rewrite)
ALTER TABLE SET TABLESPACE
CREATE INDEX
which are already there, plus new ones suggested/implied
COPY
CREATE TABLE AS SELECT
INSERT/UPDATE/DELETE

Please let me know if I missed something (rather than debating what is
or is not a "maintenance" command).

Any other thoughts before I cut v2 ?

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2014-01-16 16:22:48 Re: WAL Rate Limiting
Previous Message Andres Freund 2014-01-16 16:20:05 Re: Why conf.d should be default, and auto.conf and recovery.conf should be in it