Thread: Index-only scans and non-MVCC snapshots
Hi, As part of a research project, I'm trying to change Read Committed isolation to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every command [1]. Things appear to have gone reasonably well so far, except certain queries fail with "ERROR: non-MVCC snapshots are not supported in index-only scans." I'm using v9.3.2, and the docs claim that index-only scans work without MVCC, but require some extra locking to avoid races [2]. Is this not actually implemented? If that is the case, shouldn't the query optimizer avoid selecting index-only scans for non-MVCC snapshots? I realize I'm playing with fire here, but any pointers to sections of code I might look at to either work around or fix this issue would be greatly appreciated. I've been looking around in index_fetch_heap (indexam.c) as well as other locations that use scan->xs_continue_hot; there seems to be code in place to detect when a non-MVCC snapshot is in use, as if that were nothing out of the ordinary, but nothing prevents the error from arising if a hot chain is actually encountered. Thanks, Ryan [1] Right now, Read Committed is significantly *slower* than Repeatable Read---for transactions involving multiple short commands---because the former acquires multiple snapshots per transaction and causes a lwlock bottleneck on my 12-core machine. [2] http://www.postgresql.org/docs/9.3/static/index-locking.html: > with a non-MVCC-compliant snapshot (such as SnapshotNow), it would be > possible to accept and return a row that does not in fact match the > scan keys ... [so] we use a pin on an index page as a proxy to > indicate that the reader might still be "in flight" from the index > entry to the matching heap entry. Making ambulkdelete block on such a > pin ensures that VACUUM cannot delete the heap entry before the reader > is done with it. ... This solution requires that index scans be > "synchronous": we have to fetch each heap tuple immediately after > scanning the corresponding index entry. This is expensive for a number > of reasons. An "asynchronous" scan in which we collect many TIDs from > the index, and only visit the heap tuples sometime later, requires > much less index locking overhead and can allow a more efficient heap > access pattern. Per the above analysis, we must use the synchronous > approach for non-MVCC-compliant snapshots.
Ryan Johnson wrote: > As part of a research project, I'm trying to change Read Committed > isolation to use HeapTupleSatisfiesNow rather than acquiring a new > snapshot at every command [1]. Are you aware of this? commit 813fb0315587d32e3b77af1051a0ef517d187763 Author: Robert Haas <rhaas@postgresql.org> Date: Thu Aug 1 10:46:19 2013 -0400 Remove SnapshotNow and HeapTupleSatisfiesNow. We now use MVCC catalog scans, and, per discussion, have eliminated all other remaining uses of SnapshotNow, so that we can now get rid of it. This will break third-party codewhich is still using it, which is intentional, as we want such code to be updated to do things the new way. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 26/06/2014 11:04 PM, Alvaro Herrera wrote: > Ryan Johnson wrote: >> As part of a research project, I'm trying to change Read Committed >> isolation to use HeapTupleSatisfiesNow rather than acquiring a new >> snapshot at every command [1]. > Are you aware of this? > > commit 813fb0315587d32e3b77af1051a0ef517d187763 > Author: Robert Haas <rhaas@postgresql.org> > Date: Thu Aug 1 10:46:19 2013 -0400 > > Remove SnapshotNow and HeapTupleSatisfiesNow. That would be wonderful news... if snapshots weren't so darned expensive to create. I guess there's no avoiding that bottleneck now, though. Ryan
On 2014-06-26 22:47:47 -0600, Ryan Johnson wrote: > Hi, > > As part of a research project, I'm trying to change Read Committed isolation > to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every > command [1]. Things appear to have gone reasonably well so far, except > certain queries fail with "ERROR: non-MVCC snapshots are not supported in > index-only scans." You're aware that unless you employ additional locking you can simply miss individual rows or see them several times because of concurrent updates? The reason it has worked (< 9.4) for system catalogs is that updates of rows were only performed while objects were locked access exclusively - that's the reason why some places in the code use inplace updates btw... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Ryan Johnson wrote: > On 26/06/2014 11:04 PM, Alvaro Herrera wrote: > >Ryan Johnson wrote: > >>As part of a research project, I'm trying to change Read Committed > >>isolation to use HeapTupleSatisfiesNow rather than acquiring a new > >>snapshot at every command [1]. > >Are you aware of this? > > > >commit 813fb0315587d32e3b77af1051a0ef517d187763 > >Author: Robert Haas <rhaas@postgresql.org> > >Date: Thu Aug 1 10:46:19 2013 -0400 > > > > Remove SnapshotNow and HeapTupleSatisfiesNow. > > That would be wonderful news... if snapshots weren't so darned > expensive to create. I take it you aren't aware of this other effort, either: http://archives.postgresql.org/message-id/539AD153.9000004@vmware.com -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 27/06/2014 3:14 AM, Andres Freund wrote: > On 2014-06-26 22:47:47 -0600, Ryan Johnson wrote: >> Hi, >> >> As part of a research project, I'm trying to change Read Committed isolation >> to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every >> command [1]. Things appear to have gone reasonably well so far, except >> certain queries fail with "ERROR: non-MVCC snapshots are not supported in >> index-only scans." > You're aware that unless you employ additional locking you can simply > miss individual rows or see them several times because of concurrent > updates? > The reason it has worked (< 9.4) for system catalogs is that updates of > rows were only performed while objects were locked access exclusively - > that's the reason why some places in the code use inplace updates btw... Yes, I was aware of the need for locking. The documentation just made it sound that locking was already in place for non-MVCC index scans. I was hoping I'd missed some easy way to activate it. Regards, Ryan
On 2014-06-27 08:39:13 -0600, Ryan Johnson wrote: > On 27/06/2014 3:14 AM, Andres Freund wrote: > >On 2014-06-26 22:47:47 -0600, Ryan Johnson wrote: > >>Hi, > >> > >>As part of a research project, I'm trying to change Read Committed isolation > >>to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every > >>command [1]. Things appear to have gone reasonably well so far, except > >>certain queries fail with "ERROR: non-MVCC snapshots are not supported in > >>index-only scans." > >You're aware that unless you employ additional locking you can simply > >miss individual rows or see them several times because of concurrent > >updates? > >The reason it has worked (< 9.4) for system catalogs is that updates of > >rows were only performed while objects were locked access exclusively - > >that's the reason why some places in the code use inplace updates btw... > Yes, I was aware of the need for locking. The documentation just made it > sound that locking was already in place for non-MVCC index scans. I was > hoping I'd missed some easy way to activate it. Well, it is/was for the places (i.e. DDL) that actually use non-MVCC scans. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 27/06/2014 8:20 AM, Alvaro Herrera wrote: > Ryan Johnson wrote: >> On 26/06/2014 11:04 PM, Alvaro Herrera wrote: >>> Ryan Johnson wrote: >>>> As part of a research project, I'm trying to change Read Committed >>>> isolation to use HeapTupleSatisfiesNow rather than acquiring a new >>>> snapshot at every command [1]. >>> Are you aware of this? >>> >>> commit 813fb0315587d32e3b77af1051a0ef517d187763 >>> Author: Robert Haas <rhaas@postgresql.org> >>> Date: Thu Aug 1 10:46:19 2013 -0400 >>> >>> Remove SnapshotNow and HeapTupleSatisfiesNow. >> That would be wonderful news... if snapshots weren't so darned >> expensive to create. > I take it you aren't aware of this other effort, either: > http://archives.postgresql.org/message-id/539AD153.9000004@vmware.com That is good news, though from reading the thread it sounds like proc array accesses are being exchanged for accesses to an SLRU, so a lot of lwlock calls will remain. It will definitely help, though. SLRU will get ex-locked a lot less often, so the main source of contention will be for the actual lwlock acquire/release operations. Regards, Ryan