Re: WAL Rate Limiting
WAL Rate Limiting
От:
Simon Riggs <simon@2ndQuadrant.com>
Дата:
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. Uses GetCurrentTransactionWALVolume() patch, which is included within the patch to make it easier to review as a whole. Technically, we can't simply wait before/after WAL inserts because these typically occur while holding buffer locks. So we need to put the waits at a higher level, notably in safe places that currently do CHECK_FOR_INTERRUPTS(). Doing that during query execution might make locking of blocks for nested loops joins much worse. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: WAL Rate Limiting
От:
Simon Riggs <simon@2ndQuadrant.com>
Дата:
On 16 January 2014 17:20, Simon Riggs wrote: > 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. Parameter renamed to wal_rate_limit. Not yet modified to produce this in MB/s > I would like to call it CHECK_FOR_WAL_RATE_LIMIT() Used CHECK_WAL_BUDGET() as proposed > 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 Added, no problems or technical difficulties encountered. > 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 ? v2 attached -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: WAL Rate Limiting
От:
Pavan Deolasee <pavan.deolasee@gmail.com>
Дата:
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,
Re: WAL Rate Limiting
От:
Magnus Hagander <magnus@hagander.net>
Дата:
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/
Re: WAL Rate Limiting
От:
Jeff Janes <jeff.janes@gmail.com>
Дата:
On Thu, Jan 16, 2014 at 8:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think the usecases that would want this for DML probably also wan thisHuh? Unlogged tables generate *zero* WAL, by definition.
> to work for unlogged, temp tables.
Transactions that only change unlogged tables still generate commit records to WAL.
I don't think that amount of WAL is particularly relevant to this discussion, but I was recently surprised by it, so wanted to publicize it. (It was causing a lot of churn in my WAL due to interaction with archive_timeout)
Cheers,
Jeff
