WAL Rate Limiting

On 01/15/2014 10:20 AM, Simon Riggs wrote:

(discuss: do we need another parameter to specify "cost"? Currently
patch uses "sleep every 64kB of WAL")

It might be nicer to express this as "up to n MB of WAL per second", but
otherwise seems reasonable, and it's easy enough to convert WAL MB/s
into a delay by hand.

Initial tests of this show that it does limit index creation rates as
expected, while continuing to allow WAL generation at normal rates for
other concurrent work.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 1/14/14, 8:20 PM, Simon Riggs wrote:

We've discussed previously the negative impact of large bulk
operations, especially wrt WAL writes. Patch here allows maintenance
operations to have their WAL generation slowed down as a replication
lag prevention feature.

I believe there was originally intended to be some work on I/O rate
limiting, but that hasn't happened and is in some ways orthogonal to
this patch and we will likely eventually want both.

Single new parameter works very similarly to vacuum_cost_delay

wal_rate_limit_delay = Xms

From a replication standpoint, I think it would be a lot nicer if this also (or instead) considered the replication delay we were currently experiencing.

Related to that... it would be extremely useful to us if we could similarly rate limit certain backend operations, for the purpose of not overloading the database or replication. That's fairly unrelated except that sometimes you might want to WAL rate limit a backend operation, besides just COPY.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jan 15, 2014 at 7:50 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

We've discussed previously the negative impact of large bulk
operations, especially wrt WAL writes. Patch here allows maintenance
operations to have their WAL generation slowed down as a replication
lag prevention feature.

I believe there was originally intended to be some work on I/O rate
limiting, but that hasn't happened and is in some ways orthogonal to
this patch and we will likely eventually want both.

Single new parameter works very similarly to vacuum_cost_delay

wal_rate_limit_delay = Xms

slows down CLUSTER, VACUUM FULL, ALTER TABLE (rewrite & set
tablespace), CREATE INDEX
so basically same things we optimise WAL for and the same places where
we honour maintenance_work_mem
(discuss: should we add COPY, CTAS etc also?)
(discuss: do we need another parameter to specify "cost"? Currently
patch uses "sleep every 64kB of WAL")

VACUUM is not included, since we already have controls for that -
honouring two controls would be complex and weird.

I wonder if should replace vacuum_cost/delay with say
maintenance_cost/delay and use it in all maintenance activities including
what you just listed out above. While the exact semantics of vacuum and
other maintenance activities may differ, ISTM the final goal is just the
same i.e. reduce load on the master.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

On Wed, Jan 15, 2014 at 3:20 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

We've discussed previously the negative impact of large bulk
operations, especially wrt WAL writes. Patch here allows maintenance
operations to have their WAL generation slowed down as a replication
lag prevention feature.

I believe there was originally intended to be some work on I/O rate
limiting, but that hasn't happened and is in some ways orthogonal to
this patch and we will likely eventually want both.

Single new parameter works very similarly to vacuum_cost_delay

wal_rate_limit_delay = Xms

Seems like a really bad name if we are only slowing down some commands -
that seems to indicate we're slowing down all of them. I think it should be
something that indicates that it only affects the maintenance commands.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Wed, Jan 15, 2014 at 7:54 AM, Magnus Hagander <magnus@hagander.net> wrote:

On Wed, Jan 15, 2014 at 3:20 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

We've discussed previously the negative impact of large bulk
operations, especially wrt WAL writes. Patch here allows maintenance
operations to have their WAL generation slowed down as a replication
lag prevention feature.

I believe there was originally intended to be some work on I/O rate
limiting, but that hasn't happened and is in some ways orthogonal to
this patch and we will likely eventually want both.

Single new parameter works very similarly to vacuum_cost_delay

wal_rate_limit_delay = Xms

Seems like a really bad name if we are only slowing down some commands -
that seems to indicate we're slowing down all of them. I think it should be
something that indicates that it only affects the maintenance commands.

And why should it only affect the maintenance commands anyway, and who
decides what's a maintenance command?

I thought Heroku suggested something like this previously, and their
use case was something along the lines of "we need to slow the system
down enough to do a backup so we can delete some stuff before the disk
fills". For that, it seems likely to me that you would just want to
slow everything down.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-01-16 09:06:30 -0500, Robert Haas wrote:

Seems like a really bad name if we are only slowing down some commands -
that seems to indicate we're slowing down all of them. I think it should be
something that indicates that it only affects the maintenance commands.

And why should it only affect the maintenance commands anyway, and who
decides what's a maintenance command?

I think implementing it for everything might have some use, but it's a
much, much more complex task. You can't simply do rate limiting in
XLogInsert() or somesuch - we're holding page locks, buffer pins, other
locks... I don't see why that needs to be done in the same feature.

I don't really see much difficulty in determining what's a utility
command and what not for the purpose of this? All utility commands which
create WAL in O(table_size) or worse.

I thought Heroku suggested something like this previously, and their
use case was something along the lines of "we need to slow the system
down enough to do a backup so we can delete some stuff before the disk
fills". For that, it seems likely to me that you would just want to
slow everything down.

I think the usecase for this more along the lines of not slowing normal
operations or cause replication delays to standbys unduly, while
performing maintenance operations on relations.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> writes:

I don't really see much difficulty in determining what's a utility
command and what not for the purpose of this? All utility commands which
create WAL in O(table_size) or worse.

By that definition, INSERT, UPDATE, and DELETE can all be "utility
commands". So would a full-table-scan SELECT, if it's unfortunate
enough to run into a lot of hint-setting or HOT-pruning work.

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.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-01-16 10:35:20 -0500, Tom Lane wrote:

Andres Freund <andres@2ndquadrant.com> writes:

I don't really see much difficulty in determining what's a utility
command and what not for the purpose of this? All utility commands which
create WAL in O(table_size) or worse.

By that definition, INSERT, UPDATE, and DELETE can all be "utility
commands". So would a full-table-scan SELECT, if it's unfortunate
enough to run into a lot of hint-setting or HOT-pruning work.

Well, I said *utility* command. So everything processed by
ProcessUtility() generating WAL like that.

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.
We shouldn't let the the need for generic resource control stop us from
providing some for of resource control for a consistent subset of
commands.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Heikki Linnakangas <hlinnakangas@vmware.com> writes:

On 01/16/2014 05:39 PM, Andres Freund wrote:

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.

We probably don't really need to implement it for "all" commands; I think
everyone can agree that, say, ALTER TABLE RENAME COLUMN isn't going to
emit enough WAL to really matter. My point was that we should try to hit
everything that potentially *could* generate lots of WAL, rather than
arbitrarily deciding that some are utility commands and some are not.

For INSERT/UPDATE/DELETE, likely you could do this with a single
CHECK_FOR_WAL_BUDGET() added at a strategic spot in nodeModifyTable.c.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> writes:

On 2014-01-16 17:47:51 +0200, Heikki Linnakangas wrote:

In suitable safe places, check if you've written too much WAL, and sleep if
so.

That'd be most places doing XLogInsert() if you want to be
consistent.

See my other response. There's no need for "consistency", only to be sure
that code paths that might generate a *lot* of WAL include such checks.
We've gotten along fine without any formal methodology for where to put
CHECK_FOR_INTERRUPTS() or vacuum_delay_point() calls, and this seems like
just more of the same.

I think the usecases that would want this for DML probably also wan this
to work for unlogged, temp tables.

Huh? Unlogged tables generate *zero* WAL, by definition.

If your point is that WAL creation isn't a particularly good resource
consumption metric, that's an argument worth considering, but it seems
quite orthogonal to whether we can implement such throttling reasonably.
And in any case, you didn't provide such an argument.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 16 January 2014 16:56, Andres Freund <andres@2ndquadrant.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-01-16 11:19:28 -0500, Tom Lane wrote:

I think the usecases that would want this for DML probably also wan this
to work for unlogged, temp tables.

Huh? Unlogged tables generate *zero* WAL, by definition.

Yes. That's my point. If we provide it as a generic resource control -
which what's being discussed here sounds to me - it should be generic.

If we provide as a measure to prevent standbys from getting out of date
due to maintenance commands, then it only needs to cover those.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 16 January 2014 17:22, Andres Freund <andres@2ndquadrant.com> wrote:

On 2014-01-16 11:19:28 -0500, Tom Lane wrote:

I think the usecases that would want this for DML probably also wan this
to work for unlogged, temp tables.

Huh? Unlogged tables generate *zero* WAL, by definition.

Yes. That's my point. If we provide it as a generic resource control -
which what's being discussed here sounds to me - it should be generic.

If we provide as a measure to prevent standbys from getting out of date
due to maintenance commands, then it only needs to cover those.

Agreed, but it won't happen in this release. I/O resource control to
follow in later releases.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 16 January 2014 17:29, Andres Freund <andres@2ndquadrant.com> wrote:

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.

Good point, but rather happily I can say "thought of that" and refer
you to the other patch which limits SELECT's ability to dirty pages,
and thus, with checksums enabled will limit the generation of WAL.
(And no, they aren't the same thing).

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Simon Riggs <simon@2ndQuadrant.com> writes:

Agreed, but it won't happen in this release. I/O resource control to
follow in later releases.

"This release"? TBH, I think this patch has arrived too late to be
considered for 9.4. It's a fairly major feature and clearly not
without controversy, so I don't think that posting it for the first
time the day before CF4 starts meets the guidelines we all agreed
to back in Ottawa.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 16 January 2014 17:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

Agreed, but it won't happen in this release. I/O resource control to
follow in later releases.

"This release"? TBH, I think this patch has arrived too late to be
considered for 9.4. It's a fairly major feature and clearly not
without controversy, so I don't think that posting it for the first
time the day before CF4 starts meets the guidelines we all agreed
to back in Ottawa.

I think its majorly useful, but there really isn't much to the
implementation and I expect its beneath the level of being defined as
a major feature. (I think it may have taken less time to write than
this current conversation has lasted).

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers