Re: WAL Rate Limiting

On 2014-01-16 17:20:19 +0100, Simon Riggs wrote:
> 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).

If we're going to do this for DML - which I am far from convinced of -
we also should do it for SELECT, since that can generate significant
amounts of WAL with checksums turned on.
Otherwise stuff like INSERT ... SELECT, UPDATE FROM et al. will behave
very confusingly since suddenly thez will not only block the WAL
generated by the INSERT but also the SELECT.

Greetings,

Andres Freund

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