Re: [HACKERS] WAL Rate Limiting

2014-02-21 Thread Jim Nasby
On 1/16/14, 9:19 PM, Craig Ringer wrote: On 01/16/2014 11:52 PM, Tom Lane wrote: Heikki Linnakangas 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

Re: [HACKERS] WAL Rate Limiting

2014-02-21 Thread Alvaro Herrera
It's clear now that if what Greg wants is an uncontroversial patch to commit, this is not it. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make cha

Re: [HACKERS] WAL Rate Limiting

2014-02-21 Thread Robert Haas
On Thu, Feb 20, 2014 at 12:07 PM, Alvaro Herrera wrote: > I think "bulk" (maintenance) operations should legitimately configured > separately from regular UPDATE etc operations. For the latter I think > it's pretty reasonable to assume that users are going to want to tweak > the GUC for each indi

Re: [HACKERS] WAL Rate Limiting

2014-02-20 Thread Alvaro Herrera
Robert Haas escribió: > On Thu, Feb 20, 2014 at 9:43 AM, Simon Riggs wrote: > > The design choice of making the limit only apply to bulk ops is > > because that is where the main problem lies. Rate limiting will cause > > a loss of performance in the main line for non-bulk operations, so > > addin

Re: [HACKERS] WAL Rate Limiting

2014-02-20 Thread Hannu Krosing
On 02/20/2014 04:16 PM, Robert Haas wrote: > On Thu, Feb 20, 2014 at 9:43 AM, Simon Riggs wrote: >> The design choice of making the limit only apply to bulk ops is >> because that is where the main problem lies. Rate limiting will cause >> a loss of performance in the main line for non-bulk operat

Re: [HACKERS] WAL Rate Limiting

2014-02-20 Thread Robert Haas
On Thu, Feb 20, 2014 at 10:10 AM, Greg Stark wrote: > On Thu, Feb 20, 2014 at 2:43 PM, Simon Riggs wrote: >> The design choice of making the limit only apply to bulk ops is >> because that is where the main problem lies. Rate limiting will cause >> a loss of performance in the main line for non-b

Re: [HACKERS] WAL Rate Limiting

2014-02-20 Thread Robert Haas
On Thu, Feb 20, 2014 at 9:43 AM, Simon Riggs wrote: > The design choice of making the limit only apply to bulk ops is > because that is where the main problem lies. Rate limiting will cause > a loss of performance in the main line for non-bulk operations, so > adding tests there will not be valuab

Re: [HACKERS] WAL Rate Limiting

2014-02-20 Thread Greg Stark
On Thu, Feb 20, 2014 at 2:43 PM, Simon Riggs wrote: > The design choice of making the limit only apply to bulk ops is > because that is where the main problem lies. Rate limiting will cause > a loss of performance in the main line for non-bulk operations, so > adding tests there will not be valuab

Re: [HACKERS] WAL Rate Limiting

2014-02-20 Thread Simon Riggs
On 19 February 2014 13:28, Greg Stark wrote: > On Mon, Jan 20, 2014 at 5:37 PM, Simon Riggs wrote: > >> Agreed; that was the original plan, but implementation delays >> prevented the whole vision/discussion/implementation. Requirements >> from various areas include WAL rate limiting for replicati

Re: [HACKERS] WAL Rate Limiting

2014-02-20 Thread Greg Stark
On Thu, Feb 20, 2014 at 1:45 PM, Simon Riggs wrote: > On 19 February 2014 16:04, Robert Haas wrote: > >> Well, *I* don't think this is ready to go. A WAL rate limit that only >> limits WAL sometimes still doesn't impress me. > > Could you be specific in your criticism? "Sometimes" wouldn't impre

Re: [HACKERS] WAL Rate Limiting

2014-02-20 Thread Simon Riggs
On 19 February 2014 16:04, Robert Haas wrote: > Well, *I* don't think this is ready to go. A WAL rate limit that only > limits WAL sometimes still doesn't impress me. Could you be specific in your criticism? "Sometimes" wouldn't impress anybody, but we need to understand whether you are referri

Re: [HACKERS] WAL Rate Limiting

2014-02-19 Thread Robert Haas
On Wed, Feb 19, 2014 at 8:28 AM, Greg Stark wrote: > On Mon, Jan 20, 2014 at 5:37 PM, Simon Riggs wrote: > >> Agreed; that was the original plan, but implementation delays >> prevented the whole vision/discussion/implementation. Requirements >> from various areas include WAL rate limiting for rep

Re: [HACKERS] WAL Rate Limiting

2014-02-19 Thread Greg Stark
On Mon, Jan 20, 2014 at 5:37 PM, Simon Riggs wrote: > Agreed; that was the original plan, but implementation delays > prevented the whole vision/discussion/implementation. Requirements > from various areas include WAL rate limiting for replication, I/O rate > limiting, hard CPU and I/O limits for

Re: [HACKERS] WAL Rate Limiting

2014-01-20 Thread Simon Riggs
On 17 January 2014 16:30, Heikki Linnakangas wrote: > It occurs to me that this is very similar to the method I proposed in June > to enforce a hard limit on WAL usage, to avoid PANIC caused by running out > of disk space when writing WAL: > > http://www.postgresql.org/message-id/51b095fe.6050...@

Re: [HACKERS] WAL Rate Limiting

2014-01-20 Thread Simon Riggs
On 17 January 2014 16:10, Tom Lane wrote: > Andres Freund writes: >> On 2014-01-17 09:04:54 -0500, Robert Haas wrote: >>> That having been said, I bet it could be done at the tail of >>> XLogInsert(). > >> I don't think there are many locations where this would be ok. Sleeping >> while holding ex

Re: [HACKERS] WAL Rate Limiting

2014-01-20 Thread Simon Riggs
On 17 January 2014 16:34, Heikki Linnakangas wrote: > On 01/17/2014 05:20 PM, Simon Riggs wrote: >> >> + if (RelationNeedsWAL(indexrel)) >> + CHECK_FOR_WAL_BUDGET(); > > > I don't think we need the RelationNeedsWAL tests. If the relation is not > WAL-logged, you won't write muc

Re: [HACKERS] WAL Rate Limiting

2014-01-17 Thread Heikki Linnakangas
It occurs to me that this is very similar to the method I proposed in June to enforce a hard limit on WAL usage, to avoid PANIC caused by running out of disk space when writing WAL: http://www.postgresql.org/message-id/51b095fe.6050...@vmware.com Enforcing a global limit needs more book-keepin

Re: [HACKERS] WAL Rate Limiting

2014-01-17 Thread Heikki Linnakangas
On 01/17/2014 05:20 PM, Simon Riggs wrote: + if (RelationNeedsWAL(indexrel)) + CHECK_FOR_WAL_BUDGET(); I don't think we need the RelationNeedsWAL tests. If the relation is not WAL-logged, you won't write much WAL, so you should easily stay under the limit. And CHECK_FOR_WA

Re: [HACKERS] WAL Rate Limiting

2014-01-17 Thread Simon Riggs
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 prop

Re: [HACKERS] WAL Rate Limiting

2014-01-17 Thread Tom Lane
Andres Freund writes: > On 2014-01-17 09:04:54 -0500, Robert Haas wrote: >> That having been said, I bet it could be done at the tail of >> XLogInsert(). > I don't think there are many locations where this would be ok. Sleeping > while holding exclusive buffer locks? Quite possibly inside a criti

Re: [HACKERS] WAL Rate Limiting

2014-01-17 Thread Andres Freund
On 2014-01-17 09:04:54 -0500, Robert Haas wrote: > That having been said, I bet it could be done at the tail of > XLogInsert(). if there are cases where that's not desirable, then add > macros HOLDOFF_WAL_THROTTLING() and RESUME_WAL_THROTTLING() that bump > a counter up and down. When the counter

Re: [HACKERS] WAL Rate Limiting

2014-01-17 Thread Robert Haas
On Thu, Jan 16, 2014 at 10:56 AM, Andres Freund wrote: > 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(). And doing that pro

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Craig Ringer
On 01/16/2014 11:52 PM, Tom Lane wrote: > Heikki Linnakangas 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

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Peter Eisentraut
The documentation doesn't build: openjade:config.sgml:1416:11:E: end tag for "VARIABLELIST" omitted, but OMITTAG NO was specified openjade:config.sgml:1390:5: start tag was here -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Jeff Janes
On Thu, Jan 16, 2014 at 8:19 AM, 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. > Transactions that only change unlogged tables still generate comm

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Heikki Linnakangas
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

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Simon Riggs
On 16 January 2014 17:49, Tom Lane wrote: > Simon Riggs 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 clear

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Tom Lane
Simon Riggs 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 po

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Simon Riggs
On 16 January 2014 17:29, Andres Freund 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 sig

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Andres Freund
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 state

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Simon Riggs
On 16 January 2014 17:22, Andres Freund 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 po

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Tom Lane
Andres Freund 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 "consistenc

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Andres Freund
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 - wh

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Simon Riggs
On 16 January 2014 16:56, Andres Freund 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

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Andres Freund
On 2014-01-16 10:35:20 -0500, Tom Lane wrote: > Andres Freund 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, UPDAT

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Andres Freund
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 affe

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Tom Lane
Heikki Linnakangas 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

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Tom Lane
Andres Freund 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 f

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Andres Freund
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 shou

Re: [HACKERS] WAL Rate Limiting

2014-01-16 Thread Robert Haas
On Wed, Jan 15, 2014 at 7:54 AM, Magnus Hagander wrote: > On Wed, Jan 15, 2014 at 3:20 AM, 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 d

Re: [HACKERS] WAL Rate Limiting

2014-01-15 Thread Magnus Hagander
On Wed, Jan 15, 2014 at 3:20 AM, 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

Re: [HACKERS] WAL Rate Limiting

2014-01-14 Thread Pavan Deolasee
On Wed, Jan 15, 2014 at 7:50 AM, 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

Re: [HACKERS] WAL Rate Limiting

2014-01-14 Thread Jim Nasby
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 in

Re: [HACKERS] WAL Rate Limiting

2014-01-14 Thread Craig Ringer
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 int