Re: [HACKERS] Proposal for CSN based snapshots

2017-10-05 Thread Robert Haas
On Mon, Sep 25, 2017 at 10:17 AM, Alexander Kuzmenkov wrote: > Here is some news about the CSN patch. > > * I merged it with master (58bd60995f), which now has the clog group update. > With this optimization, CSN is now faster than the master by about 15% on > 100 to

Re: [HACKERS] Proposal for CSN based snapshots

2017-08-02 Thread Alexander Kuzmenkov
What problem exactly you are seeing in the clog, is it the contention around CLOGControlLock or generally accessing CLOG is slower. If former, then we already have a patch [1] to address it. It's the contention around CLogControlLock. Thank you for the pointer, next time I'll try it with the

Re: [HACKERS] Proposal for CSN based snapshots

2017-08-01 Thread Amit Kapila
On Tue, Aug 1, 2017 at 7:41 PM, Alexander Kuzmenkov wrote: > Hi all, > > So I did some more experiments on this patch. > > * I fixed the bug with duplicate tuples I mentioned in the previous letter. > Indeed, the oldestActiveXid could be advanced past the transaction's

Re: [HACKERS] Proposal for CSN based snapshots

2017-07-19 Thread Alexander Kuzmenkov
That's not actually a problem as I am reusing an older v4 from Heikki now for the future, but I wanted to let you know about that first. Thank you, I'll look into that. -- Alexander Kuzmenkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -- Sent via

Re: [HACKERS] Proposal for CSN based snapshots

2017-07-18 Thread Michael Paquier
On Wed, Jun 21, 2017 at 1:48 PM, Alexander Kuzmenkov wrote: > Glad to see you working on this! I've been studying this topic too. Attached > you can find a recently rebased version of Heikki's v4 patch. > I also fixed a bug that appeared on report-receipts isolation

Re: [HACKERS] Proposal for CSN based snapshots

2017-06-21 Thread Alexander Kuzmenkov
On 21.06.2017 04:48, Michael Paquier wrote: There has not been much activity on this thread for some time, and I mentioned my intentions to some developers at the last PGCon. But I am planning to study more the work that has been done here, with as envisaged goal to present a patch for the first

Re: [HACKERS] Proposal for CSN based snapshots

2017-06-20 Thread Michael Paquier
On Wed, Aug 24, 2016 at 5:54 PM, Heikki Linnakangas wrote: > And here are the results on the 72 core machine (thanks again, Alexander!). > The test setup was the same as on the 32-core machine, except that I ran it > with more clients since the system has more CPU cores. In

Re: [HACKERS] Proposal for CSN based snapshots

2016-12-22 Thread Amit Kapila
On Wed, Aug 24, 2016 at 2:24 PM, Heikki Linnakangas wrote: > > And here are the results on the 72 core machine (thanks again, Alexander!). > The test setup was the same as on the 32-core machine, except that I ran it > with more clients since the system has more CPU cores. In

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-24 Thread Alexander Korotkov
On Wed, Aug 24, 2016 at 11:54 AM, Heikki Linnakangas wrote: > On 08/23/2016 06:18 PM, Heikki Linnakangas wrote: > >> On 08/22/2016 08:38 PM, Andres Freund wrote: >> >>> On 2016-08-22 20:32:42 +0300, Heikki Linnakangas wrote: >>> I remember seeing ProcArrayLock

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-24 Thread Heikki Linnakangas
On 08/23/2016 06:18 PM, Heikki Linnakangas wrote: On 08/22/2016 08:38 PM, Andres Freund wrote: On 2016-08-22 20:32:42 +0300, Heikki Linnakangas wrote: I remember seeing ProcArrayLock contention very visible earlier, but I can't hit that now. I suspect you'd still see contention on bigger

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-23 Thread Andres Freund
On 2016-08-23 18:18:57 +0300, Heikki Linnakangas wrote: > I installed Greg Smith's pgbench-tools kit on that server, and ran some > tests. I'm seeing some benefit on "pgbench -N" workload, but only after > modifying the test script to use "-M prepared", and using Unix domain > sockets instead of

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-23 Thread Heikki Linnakangas
On 08/22/2016 08:38 PM, Andres Freund wrote: On 2016-08-22 20:32:42 +0300, Heikki Linnakangas wrote: I remember seeing ProcArrayLock contention very visible earlier, but I can't hit that now. I suspect you'd still see contention on bigger hardware, though, my laptop has oly 4 cores. I'll have

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-23 Thread Amit Kapila
On Mon, Aug 22, 2016 at 11:11 PM, Robert Haas wrote: > On Mon, Aug 22, 2016 at 1:32 PM, Heikki Linnakangas wrote: >>> But what about the best case? If we create a scenario where there are >>> no open read-write transactions at all and (somehow) lots and

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-22 Thread Andres Freund
On 2016-08-22 13:41:57 -0400, Robert Haas wrote: > On Mon, Aug 22, 2016 at 1:32 PM, Heikki Linnakangas wrote: > >> But what about the best case? If we create a scenario where there are > >> no open read-write transactions at all and (somehow) lots and lots of > >> ProcArrayLock

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-22 Thread Robert Haas
On Mon, Aug 22, 2016 at 1:32 PM, Heikki Linnakangas wrote: >> But what about the best case? If we create a scenario where there are >> no open read-write transactions at all and (somehow) lots and lots of >> ProcArrayLock contention, how much does this help? > > I ran some quick

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-22 Thread Andres Freund
Hi, On 2016-08-22 20:32:42 +0300, Heikki Linnakangas wrote: > I ran some quick pgbench tests on my laptop, but didn't see any meaningful > benefit. I think the best I could see is about 5% speedup, when running > "pgbench -S", with 900 idle connections sitting in the background. On the > positive

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-22 Thread Heikki Linnakangas
On 08/22/2016 07:49 PM, Robert Haas wrote: Nice to see you working on this again. On Mon, Aug 22, 2016 at 12:35 PM, Heikki Linnakangas wrote: A sequential scan of a table like that with 10 million rows took about 700 ms on my laptop, when the hint bits are set, without this

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-22 Thread Robert Haas
Nice to see you working on this again. On Mon, Aug 22, 2016 at 12:35 PM, Heikki Linnakangas wrote: > A sequential scan of a table like that with 10 million rows took about 700 > ms on my laptop, when the hint bits are set, without this patch. With this > patch, if there's a

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-22 Thread Heikki Linnakangas
On 08/22/2016 07:35 PM, Heikki Linnakangas wrote: And here's a new patch version... And here's the attachment I forgot, *sigh*.. - Heikki csn-4.patch.gz Description: application/gzip -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-22 Thread Heikki Linnakangas
And here's a new patch version. Still lots of work to do, especially in performance testing, and minimizing the worst-case performance hit. On 08/09/2016 03:16 PM, Heikki Linnakangas wrote: Next steps: * Hot standby feedback is broken, now that CSN != LSN again. Will have to switch this back

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Alexander Korotkov
On Wed, Aug 10, 2016 at 8:26 PM, Greg Stark wrote: > On Wed, Aug 10, 2016 at 5:54 PM, Alexander Korotkov > wrote: > > Oh, I found that I underestimated complexity of async commit... :) > > Indeed. I think Tom's attitude was right even if the specific >

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Kevin Grittner
On Wed, Aug 10, 2016 at 12:26 PM, Greg Stark wrote: > Complexity like this makes it hard to implement other features such as > CSNs. IIRC this already bit hot standby as well. I think it would be a > big improvement if we had a clear, well defined commit order that was > easy to

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Ants Aasma
On Wed, Aug 10, 2016 at 7:54 PM, Alexander Korotkov wrote: > Oh, I found that I underestimated complexity of async commit... :) > > Do I understand right that now async commit right as follows? > 1) Async transaction confirms commit before flushing WAL. > 2) Other

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Greg Stark
On Wed, Aug 10, 2016 at 5:54 PM, Alexander Korotkov wrote: > Oh, I found that I underestimated complexity of async commit... :) Indeed. I think Tom's attitude was right even if the specific conclusion was wrong. While I don't think removing async commit is viable I

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Heikki Linnakangas
On 08/10/2016 07:54 PM, Alexander Korotkov wrote: Do I understand right that now async commit right as follows? 1) Async transaction confirms commit before flushing WAL. Yes. 2) Other transactions sees effect of async transaction only when its WAL flushed. No. Other transactions also see

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Alexander Korotkov
On Wed, Aug 10, 2016 at 6:09 PM, Heikki Linnakangas wrote: > On 08/10/2016 05:51 PM, Tom Lane wrote: > >> Heikki Linnakangas writes: >> >>> On 08/10/2016 05:09 PM, Tom Lane wrote: >>> Uh, what? That's not the semantics we have today, and I don't see why

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Ants Aasma
On Wed, Aug 10, 2016 at 6:09 PM, Heikki Linnakangas wrote: > Hmm. There's one more possible way this could all work. Let's have CSN == > LSN, also for asynchronous commits. A snapshot is the current insert > position, but also make note of the current flush position, when you

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Joshua D. Drake
On 08/10/2016 09:04 AM, Stephen Frost wrote: * Joshua D. Drake (j...@commandprompt.com) wrote: +1 for Robert here, removing async commit is a non-starter. It is PostgreSQL performance 101 that you disable synchronous commit unless you have a specific data/business requirement that needs it.

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Stephen Frost
* Joshua D. Drake (j...@commandprompt.com) wrote: > +1 for Robert here, removing async commit is a non-starter. It is > PostgreSQL performance 101 that you disable synchronous commit > unless you have a specific data/business requirement that needs it. > Specifically because of how much faster Pg

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Joshua D. Drake
On 08/10/2016 08:28 AM, Robert Haas wrote: On Wed, Aug 10, 2016 at 11:09 AM, Heikki Linnakangas wrote: Still, having to invent CSNs seems like a huge loss for this design. Personally I'd give up async commit first. If we had only sync commit, the rule could be "xact LSN less

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Robert Haas
On Wed, Aug 10, 2016 at 11:09 AM, Heikki Linnakangas wrote: >> Still, having to invent CSNs seems like a huge loss for this design. >> Personally I'd give up async commit first. If we had only sync commit, >> the rule could be "xact LSN less than snapshot threshold and less than

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Heikki Linnakangas
On 08/10/2016 05:51 PM, Tom Lane wrote: Heikki Linnakangas writes: On 08/10/2016 05:09 PM, Tom Lane wrote: Uh, what? That's not the semantics we have today, and I don't see why it's necessary or a good idea. Once the commit is in the WAL stream, any action taken on the

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Tom Lane
Heikki Linnakangas writes: > On 08/10/2016 05:09 PM, Tom Lane wrote: >> Uh, what? That's not the semantics we have today, and I don't see why >> it's necessary or a good idea. Once the commit is in the WAL stream, >> any action taken on the basis of seeing the commit must be

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Heikki Linnakangas
On 08/10/2016 05:09 PM, Tom Lane wrote: Heikki Linnakangas writes: Imagine that you have a stream of normal, synchronous, commits. They get assigned LSNs: 1, 2, 3, 4. They become visible to other transactions in that order. The way I described this scheme in the first

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Tom Lane
Heikki Linnakangas writes: > Imagine that you have a stream of normal, synchronous, commits. They get > assigned LSNs: 1, 2, 3, 4. They become visible to other transactions in > that order. > The way I described this scheme in the first emails on this thread, was > to use the

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Heikki Linnakangas
On 08/10/2016 04:34 PM, Alexander Korotkov wrote: On Tue, Aug 9, 2016 at 3:16 PM, Heikki Linnakangas wrote: I switched to using a separate counter for CSNs. CSN is no longer the same as the commit WAL record's LSN. While I liked the conceptual simplicity of CSN == LSN a lot,

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Alexander Korotkov
On Tue, Aug 9, 2016 at 3:16 PM, Heikki Linnakangas wrote: > (Reviving an old thread) > > I spent some time dusting off this old patch, to implement CSN snapshots. > Attached is a new patch, rebased over current master, and with tons of > comments etc. cleaned up. There's more

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Alexander Korotkov
On Wed, Aug 10, 2016 at 2:10 PM, Heikki Linnakangas wrote: > Yeah, if the csnlog access turns out to be too expensive, we could do > something like this. In theory, you can always convert a CSN snapshot into > an old-style list of XIDs, by scanning the csnlog between the xmin

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Heikki Linnakangas
On 08/10/2016 01:03 PM, Ants Aasma wrote: On Tue, Aug 9, 2016 at 3:16 PM, Heikki Linnakangas wrote: (Reviving an old thread) I spent some time dusting off this old patch, to implement CSN snapshots. Attached is a new patch, rebased over current master, and with tons of

Re: [HACKERS] Proposal for CSN based snapshots

2016-08-10 Thread Ants Aasma
On Tue, Aug 9, 2016 at 3:16 PM, Heikki Linnakangas wrote: > (Reviving an old thread) > > I spent some time dusting off this old patch, to implement CSN snapshots. > Attached is a new patch, rebased over current master, and with tons of > comments etc. cleaned up. There's more

Re: [HACKERS] Proposal for CSN based snapshots

2015-07-27 Thread Alexander Korotkov
On Sat, Jul 25, 2015 at 11:39 AM, Simon Riggs si...@2ndquadrant.com wrote: On 24 July 2015 at 19:21, Robert Haas robertmh...@gmail.com wrote: On Fri, Jul 24, 2015 at 1:00 PM, Simon Riggs si...@2ndquadrant.com wrote: It depends on the exact design we use to get that. Certainly we do not

Re: [HACKERS] Proposal for CSN based snapshots

2015-07-25 Thread Simon Riggs
On 24 July 2015 at 19:21, Robert Haas robertmh...@gmail.com wrote: On Fri, Jul 24, 2015 at 1:00 PM, Simon Riggs si...@2ndquadrant.com wrote: It depends on the exact design we use to get that. Certainly we do not want them if they cause a significant performance regression. Yeah. I think

Re: [HACKERS] Proposal for CSN based snapshots

2015-07-24 Thread Robert Haas
On Fri, Jul 24, 2015 at 1:00 PM, Simon Riggs si...@2ndquadrant.com wrote: It depends on the exact design we use to get that. Certainly we do not want them if they cause a significant performance regression. Yeah. I think the performance worries expressed so far are: - Currently, if you see an

Re: [HACKERS] Proposal for CSN based snapshots

2015-07-24 Thread Simon Riggs
On 24 July 2015 at 14:43, Alexander Korotkov a.korot...@postgrespro.ru wrote: What is current state of this patch? Does community want CSN based snapshots? CSN snapshots could give us 1. More scalable GetSnapshotData() calls 2. Faster GetSnapshotData() calls 3. Smaller snapshots which could

Re: [HACKERS] Proposal for CSN based snapshots

2015-07-24 Thread Andres Freund
On 2015-07-24 14:21:39 -0400, Robert Haas wrote: - Mixing synchronous_commit=on and synchronous_commit=off won't work as well, because if the LSN ordering of commit records matches the order in which transactions become visible, then an async-commit transaction can't become visible before a

Re: [HACKERS] Proposal for CSN based snapshots

2015-07-24 Thread Alexander Korotkov
On Wed, Aug 27, 2014 at 10:42 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 08/27/2014 09:40 AM, Heikki Linnakangas wrote: On 08/27/2014 08:23 AM, Jeff Davis wrote: On Tue, 2014-08-26 at 13:45 +0300, Heikki Linnakangas wrote: Yeah. This patch in the current state is likely much

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-27 Thread Heikki Linnakangas
On 08/27/2014 08:23 AM, Jeff Davis wrote: On Tue, 2014-08-26 at 13:45 +0300, Heikki Linnakangas wrote: Yeah. This patch in the current state is likely much much slower than unpatched master, except in extreme cases where you have thousands of connections and short transactions so that without

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-27 Thread Heikki Linnakangas
On 08/27/2014 09:40 AM, Heikki Linnakangas wrote: On 08/27/2014 08:23 AM, Jeff Davis wrote: On Tue, 2014-08-26 at 13:45 +0300, Heikki Linnakangas wrote: Yeah. This patch in the current state is likely much much slower than unpatched master, except in extreme cases where you have thousands of

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-26 Thread Jeff Davis
On Fri, 2014-06-13 at 13:24 +0300, Heikki Linnakangas wrote: Attached is a new patch. It now keeps the current pg_clog unchanged, but adds a new pg_csnlog besides it. pg_csnlog is more similar to pg_subtrans than pg_clog: it's not WAL-logged, is reset at startup, and segments older than

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-26 Thread Heikki Linnakangas
On 08/26/2014 12:03 PM, Jeff Davis wrote: On Fri, 2014-06-13 at 13:24 +0300, Heikki Linnakangas wrote: Attached is a new patch. It now keeps the current pg_clog unchanged, but adds a new pg_csnlog besides it. pg_csnlog is more similar to pg_subtrans than pg_clog: it's not WAL-logged, is reset

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-26 Thread Jeff Davis
On Tue, 2014-08-26 at 13:45 +0300, Heikki Linnakangas wrote: My current thinking is that access to the csnlog will need to be made faster. Currently, it's just another SLRU, but I'm sure we can do better than that. Or add a backend-private cache on top of it; that might already alleviate it

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-26 Thread Greg Stark
On Tue, Aug 26, 2014 at 11:45 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: It appears that this patch weakens the idea of hint bits. Even if HEAP_XMIN_COMMITTED is set, it still needs to find out if it's in the snapshot. That's fast if the xid is less than snap-xmin, but otherwise it

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-26 Thread Jeff Davis
On Tue, 2014-08-26 at 19:25 +0100, Greg Stark wrote: I don't immediately see how to make that practical. One thought would be to have a list of xids in the page header with their corresponding csn -- which starts to sound a lot like Oralce's Interested Transaction List. But I don't see how to

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-26 Thread Greg Stark
On Tue, Aug 26, 2014 at 8:32 PM, Jeff Davis pg...@j-davis.com wrote: We are mixing two kinds of data: user data and visibility information. Each is changed under different circumstances and has different characteristics, and I'm beginning to think they shouldn't be mixed at all. What if we

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-26 Thread Jeff Davis
On Tue, 2014-08-26 at 21:00 +0100, Greg Stark wrote: Well fundamentally the reason the visibility information is with the user data is so that we don't need to do two i/os to access the data. The whole point of hint bits is to guarantee that after some amount of time you can read data directly

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-26 Thread Amit Kapila
On Tue, Aug 26, 2014 at 11:55 PM, Greg Stark st...@mit.edu wrote: Interesting analysis. I suppose the equivalent of hint bits would be to actually write the CSN of the transaction into the record when the hint bit is set. I don't immediately see how to make that practical. One thought would

Re: [HACKERS] Proposal for CSN based snapshots

2014-08-26 Thread Jeff Davis
On Tue, 2014-08-26 at 13:45 +0300, Heikki Linnakangas wrote: Yeah. This patch in the current state is likely much much slower than unpatched master, except in extreme cases where you have thousands of connections and short transactions so that without the patch, you spend most of the time

Re: [HACKERS] Proposal for CSN based snapshots

2014-07-10 Thread Alvaro Herrera
Heikki Linnakangas wrote: Attached is a new patch. It now keeps the current pg_clog unchanged, but adds a new pg_csnlog besides it. pg_csnlog is more similar to pg_subtrans than pg_clog: it's not WAL-logged, is reset at startup, and segments older than GlobalXmin can be truncated. This

Re: [HACKERS] Proposal for CSN based snapshots

2014-06-30 Thread Simon Riggs
On 12 May 2014 17:14, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 05/12/2014 06:26 PM, Andres Freund wrote: With the new commit-in-progress status in clog, we won't need the sub-committed clog status anymore. The commit-in-progress status will achieve the same thing. Wouldn't that

Re: [HACKERS] Proposal for CSN based snapshots

2014-06-19 Thread Amit Langote
Hi, On Fri, Jun 13, 2014 at 7:24 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Yeah, that seems like a better design, after all. Attached is a new patch. It now keeps the current pg_clog unchanged, but adds a new pg_csnlog besides it. pg_csnlog is more similar to pg_subtrans than

Re: [HACKERS] Proposal for CSN based snapshots

2014-06-18 Thread Robert Haas
On Tue, Jun 17, 2014 at 9:00 PM, Craig Ringer cr...@2ndquadrant.com wrote: On 06/18/2014 12:41 AM, Robert Haas wrote: On Mon, Jun 16, 2014 at 12:58 AM, Craig Ringer cr...@2ndquadrant.com wrote: On 05/30/2014 11:14 PM, Heikki Linnakangas wrote: Yeah. To recap, the failure mode is that if the

Re: [HACKERS] Proposal for CSN based snapshots

2014-06-18 Thread Craig Ringer
On 06/18/2014 09:12 PM, Robert Haas wrote: No. Otherwise, one of those bits could get changed after a backend takes a snapshot and before it finishes using it - so that the transaction snapshot is in effect changing underneath it. You could avoid that by memorizing the contents of CLOG when

Re: [HACKERS] Proposal for CSN based snapshots

2014-06-17 Thread Robert Haas
On Mon, Jun 16, 2014 at 12:58 AM, Craig Ringer cr...@2ndquadrant.com wrote: On 05/30/2014 11:14 PM, Heikki Linnakangas wrote: Yeah. To recap, the failure mode is that if the master crashes and restarts, the transaction becomes visible in the master even though it was never replicated.

Re: [HACKERS] Proposal for CSN based snapshots

2014-06-17 Thread Craig Ringer
On 06/18/2014 12:41 AM, Robert Haas wrote: On Mon, Jun 16, 2014 at 12:58 AM, Craig Ringer cr...@2ndquadrant.com wrote: On 05/30/2014 11:14 PM, Heikki Linnakangas wrote: Yeah. To recap, the failure mode is that if the master crashes and restarts, the transaction becomes visible in the master

Re: [HACKERS] Proposal for CSN based snapshots

2014-06-15 Thread Craig Ringer
On 05/30/2014 11:14 PM, Heikki Linnakangas wrote: Yeah. To recap, the failure mode is that if the master crashes and restarts, the transaction becomes visible in the master even though it was never replicated. Wouldn't another pg_clog bit for the transaction be able to sort that out? --

Re: [HACKERS] Proposal for CSN based snapshots

2014-06-03 Thread Robert Haas
On Fri, May 30, 2014 at 2:38 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: (forgot to answer this question) On 05/30/2014 06:27 PM, Andres Freund wrote: On 2014-05-30 17:59:23 +0300, Heikki Linnakangas wrote: * I expanded pg_clog to 64-bits per XID, but people suggested keeping

Re: [HACKERS] Proposal for CSN based snapshots

2014-06-03 Thread Greg Stark
On Tue, Jun 3, 2014 at 2:55 PM, Robert Haas robertmh...@gmail.com wrote: That doesn't address Bruce's concern about CLOG disk consumption. Well we would only need the xid-lsn mapping for transactions since globalxmin. Anything older we would just need the committed bit. So we could maintain two

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-30 Thread Greg Stark
On Fri, May 30, 2014 at 3:59 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: If transaction A commits synchronously with commit LSN 1, and transaction B commits asynchronously with commit LSN 2, B cannot become visible before A. And we cannot acknowledge B as committed to the client until

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-30 Thread Heikki Linnakangas
On 05/30/2014 06:09 PM, Greg Stark wrote: On Fri, May 30, 2014 at 3:59 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: If transaction A commits synchronously with commit LSN 1, and transaction B commits asynchronously with commit LSN 2, B cannot become visible before A. And we cannot

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-30 Thread Andres Freund
Hi, On 2014-05-30 17:59:23 +0300, Heikki Linnakangas wrote: So, here's a first version of the patch. Still very much WIP. Cool. One thorny issue came up in discussions with other hackers on this in PGCon: When a transaction is committed asynchronously, it becomes visible to other backends

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-30 Thread Heikki Linnakangas
On 05/30/2014 06:27 PM, Andres Freund wrote: On 2014-05-30 17:59:23 +0300, Heikki Linnakangas wrote: One thorny issue came up in discussions with other hackers on this in PGCon: When a transaction is committed asynchronously, it becomes visible to other backends before the commit WAL record is

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-30 Thread Heikki Linnakangas
(forgot to answer this question) On 05/30/2014 06:27 PM, Andres Freund wrote: On 2014-05-30 17:59:23 +0300, Heikki Linnakangas wrote: * I expanded pg_clog to 64-bits per XID, but people suggested keeping pg_clog as is, with two bits per commit, and adding a new SLRU for the commit LSNs beside

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-15 Thread Bruce Momjian
On Mon, May 12, 2014 at 06:01:59PM +0300, Heikki Linnakangas wrote: Some of the stuff in here will be influence whether your freezing replacement patch gets in. Do you plan to further pursue that one? Not sure. I got to the point where it seemed to work, but I got a bit of a cold feet

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-15 Thread Robert Haas
On Thu, May 15, 2014 at 2:34 PM, Bruce Momjian br...@momjian.us wrote: On Mon, May 12, 2014 at 06:01:59PM +0300, Heikki Linnakangas wrote: Some of the stuff in here will be influence whether your freezing replacement patch gets in. Do you plan to further pursue that one? Not sure. I got to

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-15 Thread Andres Freund
On 2014-05-15 15:40:06 -0400, Robert Haas wrote: On Thu, May 15, 2014 at 2:34 PM, Bruce Momjian br...@momjian.us wrote: On Mon, May 12, 2014 at 06:01:59PM +0300, Heikki Linnakangas wrote: Some of the stuff in here will be influence whether your freezing replacement patch gets in. Do you

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-15 Thread Bruce Momjian
On Thu, May 15, 2014 at 10:06:32PM +0200, Andres Freund wrote: If the larger clog size is a show-stopper (and I'm not sure I have an intelligent opinion on that just yet), one way to get around the problem would be to summarize CLOG entries after-the-fact. Once an XID precedes the xmin of

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-15 Thread Andres Freund
On 2014-05-15 16:13:49 -0400, Bruce Momjian wrote: On Thu, May 15, 2014 at 10:06:32PM +0200, Andres Freund wrote: If the larger clog size is a show-stopper (and I'm not sure I have an intelligent opinion on that just yet), one way to get around the problem would be to summarize CLOG

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-15 Thread Alvaro Herrera
Andres Freund wrote: On 2014-05-15 15:40:06 -0400, Robert Haas wrote: On Thu, May 15, 2014 at 2:34 PM, Bruce Momjian br...@momjian.us wrote: If the larger clog size is a show-stopper (and I'm not sure I have an intelligent opinion on that just yet), one way to get around the problem

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-15 Thread Andres Freund
On 2014-05-15 17:37:14 -0400, Alvaro Herrera wrote: Andres Freund wrote: On 2014-05-15 15:40:06 -0400, Robert Haas wrote: On Thu, May 15, 2014 at 2:34 PM, Bruce Momjian br...@momjian.us wrote: If the larger clog size is a show-stopper (and I'm not sure I have an intelligent opinion

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-15 Thread Alvaro Herrera
Andres Freund wrote: On 2014-05-15 17:37:14 -0400, Alvaro Herrera wrote: Andres Freund wrote: On 2014-05-15 15:40:06 -0400, Robert Haas wrote: On Thu, May 15, 2014 at 2:34 PM, Bruce Momjian br...@momjian.us wrote: If the larger clog size is a show-stopper (and I'm not sure I have

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-13 Thread Amit Kapila
On Mon, May 12, 2014 at 7:26 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: In theory, we could use a snapshot LSN as the cutoff-point for HeapTupleSatisfiesVisibility(). Maybe it's just because this is new, but that makes me feel uneasy. To accomplish this won't XID-CSN map table be

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-13 Thread Heikki Linnakangas
On 05/13/2014 09:44 AM, Amit Kapila wrote: On Mon, May 12, 2014 at 7:26 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: In theory, we could use a snapshot LSN as the cutoff-point for HeapTupleSatisfiesVisibility(). Maybe it's just because this is new, but that makes me feel uneasy. To

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-13 Thread Heikki Linnakangas
On 05/13/2014 08:08 AM, Rajeev rastogi wrote: The core of the design is to store the LSN of the commit record in pg_clog. Currently, we only store 2 bits per transaction there, indicating if the transaction committed or not, but the patch will expand it to 64 bits, to store the LSN. To check the

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-13 Thread Amit Kapila
On Tue, May 13, 2014 at 1:59 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 05/13/2014 09:44 AM, Amit Kapila wrote: To accomplish this won't XID-CSN map table be required and how will it be maintained (means when to clear and add a entry to that map table)? Not sure I understand.

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-13 Thread Rajeev rastogi
On 13 May 2014 14:06, Heikki Linnakangas The core of the design is to store the LSN of the commit record in pg_clog. Currently, we only store 2 bits per transaction there, indicating if the transaction committed or not, but the patch will expand it to 64 bits, to store the LSN. To check

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Heikki Linnakangas
On 01/24/2014 02:10 PM, Rajeev rastogi wrote: We are also planning to implement CSN based snapshot. So I am curious to know whether any further development is happening on this. I started looking into this, and plan to work on this for 9.5. It's a big project, so any help is welcome. The

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Andres Freund
On 2014-05-12 16:56:51 +0300, Heikki Linnakangas wrote: On 01/24/2014 02:10 PM, Rajeev rastogi wrote: We are also planning to implement CSN based snapshot. So I am curious to know whether any further development is happening on this. I started looking into this, and plan to work on this for

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Heikki Linnakangas
On 05/12/2014 05:41 PM, Andres Freund wrote: I haven't fully thought it through but I think it should make some of the decoding code simpler. And it should greatly simplify the hot standby code. Cool. I was worried it might conflict with the logical decoding stuff in some fundamental way, as

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Robert Haas
On Mon, May 12, 2014 at 10:41 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-05-12 16:56:51 +0300, Heikki Linnakangas wrote: On 01/24/2014 02:10 PM, Rajeev rastogi wrote: We are also planning to implement CSN based snapshot. So I am curious to know whether any further development is

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Ants Aasma
On Mon, May 12, 2014 at 4:56 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 01/24/2014 02:10 PM, Rajeev rastogi wrote: We are also planning to implement CSN based snapshot. So I am curious to know whether any further development is happening on this. I started looking into this,

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Ants Aasma
On Mon, May 12, 2014 at 6:09 PM, Robert Haas robertmh...@gmail.com wrote: However, I wonder what happens if you write the commit record and then the attempt to update pg_clog fails. I think you'll have to PANIC, which kind of sucks. CLOG IO error while commiting is already a PANIC,

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Greg Stark
On Mon, May 12, 2014 at 2:56 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Currently, before consulting the clog for an XID's status, it is necessary to first check if the transaction is still in progress by scanning the proc array. To get rid of that requirement, just before writing

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Heikki Linnakangas
On 05/12/2014 06:26 PM, Andres Freund wrote: With the new commit-in-progress status in clog, we won't need the sub-committed clog status anymore. The commit-in-progress status will achieve the same thing. Wouldn't that cause many spurious waits? Because commit-in-progress needs to be waited on,

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Andres Freund
On 2014-05-12 19:14:55 +0300, Heikki Linnakangas wrote: On 05/12/2014 06:26 PM, Andres Freund wrote: With the new commit-in-progress status in clog, we won't need the sub-committed clog status anymore. The commit-in-progress status will achieve the same thing. Wouldn't that cause many

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Ants Aasma
On Mon, May 12, 2014 at 7:10 PM, Greg Stark st...@mit.edu wrote: Would it be useful to store the current WAL insertion point along with the about to commit flag so it's effectively a promise that this transaction will commit no earlier than XXX. That should allow most transactions to decide if

Re: [HACKERS] Proposal for CSN based snapshots

2014-05-12 Thread Rajeev rastogi
On 12 May 2014 19:27, Heikki Linnakangas Wrote: On 01/24/2014 02:10 PM, Rajeev rastogi wrote: We are also planning to implement CSN based snapshot. So I am curious to know whether any further development is happening on this. I started looking into this, and plan to work on this for

Re: [HACKERS] Proposal for CSN based snapshots

2014-01-24 Thread Rajeev rastogi
On 11th June 2013, Markus Wanner wrote: Agreed. Postgres-R uses a CommitOrderId, which is very similar in concept, for example. Do you think having this snapshot scheme would be helpful for Postgres-R? Yeah, it could help to reduce patch size, after a rewrite to use such a CSN.

Re: [HACKERS] Proposal for CSN based snapshots

2013-06-11 Thread Markus Wanner
Ants, the more I think about this, the more I start to like it. On 06/07/2013 02:50 PM, Ants Aasma wrote: On Fri, Jun 7, 2013 at 2:59 PM, Markus Wanner mar...@bluegap.ch wrote: Agreed. Postgres-R uses a CommitOrderId, which is very similar in concept, for example. Do you think having this

Re: [HACKERS] Proposal for CSN based snapshots

2013-06-07 Thread Markus Wanner
Ants, On 06/07/2013 12:42 AM, Ants Aasma wrote: Given the recent ideas being thrown about changing how freezing and clog is handled and MVCC catalog access I thought I would write out the ideas that I have had about speeding up snapshots in case there is an interesting tie in with the current

Re: [HACKERS] Proposal for CSN based snapshots

2013-06-07 Thread Greg Stark
On Thu, Jun 6, 2013 at 11:42 PM, Ants Aasma a...@cybertec.at wrote: To refresh your memory the basic idea is to change visibility determination to be based on a commit sequence number (CSN for short) - a 8 byte number incremented on every commit representing the total ordering of commits I

  1   2   >