Re: WAL Rate Limiting

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