lazy_truncate_heap()

While watching WAL records float by I noticed some AccessExclusiveLocks
occurring unnecessarily during VACUUMs.

This is caused by lines 186-189 in lazy_vacuum_rel(), vacuumlazy.c

possibly_freeable = vacrelstats->rel_pages -
vacrelstats->nonempty_pages;
if (possibly_freeable >= REL_TRUNCATE_MINIMUM ||
possibly_freeable >= vacrelstats->rel_pages /
REL_TRUNCATE_FRACTION)
lazy_truncate_heap(onerel, vacrelstats);

If you look closely you'll see that if rel_pages is small then we will
attempt to truncate the heap even if possibly_freeable == 0.

While looking at this some more, I notice there is another bug. When
VACUUM has nothing at all to do, then it appears that
vacrelstats->nonempty_pages is zero, so that possibly_freeable is always
set to vacrelstats->rel_pages. vacrelstats->nonempty_pages doesn't
appear to be used anywhere else, so nobody notices it is wrongly set.

Both of these bugs are minor, but the effect of either/both of them is
to cause more AccessExclusiveLocks than we might expect.

For Hot Standby this means that many VACUUMs take AccessExclusiveLocks
on relations, which would potentially lead to having queries cancelled
for no reason at all.

Does anybody think any of the above is intentional? Can I fix?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On 31 Dec 2008, at 13:21, Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>
> Both of these bugs are minor, but the effect of either/both of them is
> to cause more AccessExclusiveLocks than we might expect.
>
> For Hot Standby this means that many VACUUMs take AccessExclusiveLocks
> on relations, which would potentially lead to having queries cancelled
> for no reason at all.

Well by default it would just cause wal to pause briefly until the
queries with those locks finish, no?

Simon Riggs wrote:
> While watching WAL records float by I noticed some AccessExclusiveLocks
> occurring unnecessarily during VACUUMs.
>
> This is caused by lines 186-189 in lazy_vacuum_rel(), vacuumlazy.c
>
> possibly_freeable = vacrelstats->rel_pages -
> vacrelstats->nonempty_pages;
> if (possibly_freeable >= REL_TRUNCATE_MINIMUM ||
> possibly_freeable >= vacrelstats->rel_pages /
> REL_TRUNCATE_FRACTION)
> lazy_truncate_heap(onerel, vacrelstats);
>
> If you look closely you'll see that if rel_pages is small then we will
> attempt to truncate the heap even if possibly_freeable == 0.

Good catch! And it goes all the way back to 7.4.

> While looking at this some more, I notice there is another bug. When
> VACUUM has nothing at all to do, then it appears that
> vacrelstats->nonempty_pages is zero, so that possibly_freeable is always
> set to vacrelstats->rel_pages. vacrelstats->nonempty_pages doesn't
> appear to be used anywhere else, so nobody notices it is wrongly set.

Hmm, this is a new issue with the visibility map. For pages at the end
that are skipped, we don't know if they're empty or not. Currently we
assume that they are, but perhaps it would be better to assume they're
not? On the other hand, that makes it much less likely that we even try
to truncate a relation, and when we do, we wouldn't truncate it as far
as we could.

> Does anybody think any of the above is intentional? Can I fix?

No. Yes please.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Greg Stark wrote:
>
> On 31 Dec 2008, at 13:21, Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>>
>> Both of these bugs are minor, but the effect of either/both of them is
>> to cause more AccessExclusiveLocks than we might expect.
>>
>> For Hot Standby this means that many VACUUMs take AccessExclusiveLocks
>> on relations, which would potentially lead to having queries cancelled
>> for no reason at all.
>
> Well by default it would just cause wal to pause briefly until the
> queries with those locks finish, no?

Wait a minute. Why does an AccessExclusiveLock lead to cancelled queries
or pausing WAL application? I thought it'd just block other queries
trying to acquire a conflicting lock in the standby, just like holding
an AccessExclusiveLock on the primary does. It's unrelated to the xmin
horizon issue.

There is a noteworthy point though. In the primary, vacuum trying to
truncate takes AccessExclusiveLock conditionally, so that it doesn't
disturb queries accessing the table, and only truncates the table if it
got the lock. But in standby, we have to truncate the table, and
therefore have to acquire the lock, waiting until we get it. I guess we
have to stop applying WAL while waiting.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, 2008-12-31 at 14:45 -0500, Greg Stark wrote:
> On 31 Dec 2008, at 13:21, Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
> >
> > Both of these bugs are minor, but the effect of either/both of them is
> > to cause more AccessExclusiveLocks than we might expect.
> >
> > For Hot Standby this means that many VACUUMs take AccessExclusiveLocks
> > on relations, which would potentially lead to having queries cancelled
> > for no reason at all.
>
> Well by default it would just cause wal to pause briefly until the
> queries with those locks finish, no?

Yes, but why allow pointless actions in the first place?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Wed, 2008-12-31 at 21:45 +0200, Heikki Linnakangas wrote:
> > Can I fix?
>
> Yes please.

Fix attached.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Thu, 2009-01-01 at 12:00 +0200, Heikki Linnakangas wrote:
> Greg Stark wrote:
> >
> > On 31 Dec 2008, at 13:21, Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
> >>
> >> Both of these bugs are minor, but the effect of either/both of them is
> >> to cause more AccessExclusiveLocks than we might expect.
> >>
> >> For Hot Standby this means that many VACUUMs take AccessExclusiveLocks
> >> on relations, which would potentially lead to having queries cancelled
> >> for no reason at all.
> >
> > Well by default it would just cause wal to pause briefly until the
> > queries with those locks finish, no?
>
> Wait a minute. Why does an AccessExclusiveLock lead to cancelled queries
> or pausing WAL application? I thought it'd just block other queries
> trying to acquire a conflicting lock in the standby, just like holding
> an AccessExclusiveLock on the primary does. It's unrelated to the xmin
> horizon issue.

Yes, it is unrelated to the xmin horizon issue. There are two reasons
for delaying WAL apply:
* locks
* xmin horizon

When a lock is acquired on the primary it almost always precedes an
action which cannot occur concurrently. For example, if VACUUM did
truncate a table then queries could get errors because parts of their
table disappear from under them. Others are drop table etc..

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> On Thu, 2009-01-01 at 12:00 +0200, Heikki Linnakangas wrote:
>> Greg Stark wrote:
>>> On 31 Dec 2008, at 13:21, Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>>>> Both of these bugs are minor, but the effect of either/both of them is
>>>> to cause more AccessExclusiveLocks than we might expect.
>>>>
>>>> For Hot Standby this means that many VACUUMs take AccessExclusiveLocks
>>>> on relations, which would potentially lead to having queries cancelled
>>>> for no reason at all.
>>> Well by default it would just cause wal to pause briefly until the
>>> queries with those locks finish, no?
>> Wait a minute. Why does an AccessExclusiveLock lead to cancelled queries
>> or pausing WAL application? I thought it'd just block other queries
>> trying to acquire a conflicting lock in the standby, just like holding
>> an AccessExclusiveLock on the primary does. It's unrelated to the xmin
>> horizon issue.
>
> Yes, it is unrelated to the xmin horizon issue. There are two reasons
> for delaying WAL apply:
> * locks
> * xmin horizon
>
> When a lock is acquired on the primary it almost always precedes an
> action which cannot occur concurrently. For example, if VACUUM did
> truncate a table then queries could get errors because parts of their
> table disappear from under them. Others are drop table etc..

Have you implemented the query cancellation mechanism for that scenario
too? (I'm cool either way, just curious..)

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Sun, 2009-01-04 at 13:01 +0200, Heikki Linnakangas wrote:
> Why does an AccessExclusiveLock lead to cancelled queries
> >> or pausing WAL application? I thought it'd just block other queries
> >> trying to acquire a conflicting lock in the standby, just like holding
> >> an AccessExclusiveLock on the primary does. It's unrelated to the xmin
> >> horizon issue.
> >
> > Yes, it is unrelated to the xmin horizon issue. There are two reasons
> > for delaying WAL apply:
> > * locks
> > * xmin horizon
> >
> > When a lock is acquired on the primary it almost always precedes an
> > action which cannot occur concurrently. For example, if VACUUM did
> > truncate a table then queries could get errors because parts of their
> > table disappear from under them. Others are drop table etc..
>
> Have you implemented the query cancellation mechanism for that scenario
> too? (I'm cool either way, just curious..)

Yes, they both lead to a conflict between WAL and standby queries, so
are treated the same, currently: if conflict occurs, wait until
max_standby_delay expires, then cancel.

Logically, "xmin horizon" conflicts could be flexible/soft. That is, if
we implemented the idea to store a lastCleanedLSN for each buffer then
"xmin horizon" conflicts would be able to continue executing until they
see a buffer with buffer.lastCleanedLSN > conflictLSN. Whereas the lock
would be a hard limit beyond which a query could not progress.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

> Logically, "xmin horizon" conflicts could be flexible/soft.
> That is, if we implemented the idea to store a lastCleanedLSN for each buffer then
> "xmin horizon" conflicts would be able to continue executing until they
> see a buffer with buffer.lastCleanedLSN > conflictLSN.

I think the trouble is, that HOT can put extremely recent lastCleanedLSN's on pages.
It would need some knobs to avoid this, that most likely reduce efficiency of HOT.

What about using the page LSN after max_standby_delay ?
Using the page LSN cancels queries earlier than the lastCleanedLSN,
but probably in many cases later than an immediate cancel after max_standby_delay.
Of course that only helps when reading static parts of tables :-(

Instead of a cancel message, the replay would need to send (set in shmem) the first
LSN applied after max_standby_delay to the relevant backend for it's LSN checks
(if buffer.LSN >= received_max_delay_lsn cancel).

Andreas

On Mon, 2009-01-05 at 18:24 +0100, Zeugswetter Andreas OSB sIT wrote:
> > Logically, "xmin horizon" conflicts could be flexible/soft.
> > That is, if we implemented the idea to store a lastCleanedLSN for each buffer then
> > "xmin horizon" conflicts would be able to continue executing until they
> > see a buffer with buffer.lastCleanedLSN > conflictLSN.
>
> I think the trouble is, that HOT can put extremely recent lastCleanedLSN's on pages.
> It would need some knobs to avoid this, that most likely reduce efficiency of HOT.
>
> What about using the page LSN after max_standby_delay ?
> Using the page LSN cancels queries earlier than the lastCleanedLSN,
> but probably in many cases later than an immediate cancel after max_standby_delay.
> Of course that only helps when reading static parts of tables :-(
>
> Instead of a cancel message, the replay would need to send (set in shmem) the first
> LSN applied after max_standby_delay to the relevant backend for it's LSN checks
> (if buffer.LSN >= received_max_delay_lsn cancel).

I like your train of thought there: If HOT is the problem then
lastCleanedLSN approx= LSN on HOT blocks, so having lastCleanedLSN
doesn't help much.

OK, so I'll skip that idea and go with what you suggest.

Design:

When conflict occurs we set a RecoveryConflictLSN on the Proc of the
backend to be cancelled. Every time we read a block in recovery we check
MyProc.RecoveryConflictLSN against the LSN on the block and backend will
commit suicide (ERROR) if block LSN is equal or greater.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> On Wed, 2008-12-31 at 21:45 +0200, Heikki Linnakangas wrote:
>>> Can I fix?
>> Yes please.
>
> Fix attached.

> --- 183,192 ----
> * number of pages. Otherwise, the time taken isn't worth it.
> */
> possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages;
> ! if (vacrelstats->tuples_deleted > 0 &&
> ! (possibly_freeable >= REL_TRUNCATE_MINIMUM ||
> ! (possibly_freeable >= vacrelstats->rel_pages / REL_TRUNCATE_FRACTION &&
> ! possibly_freeable > 0)))
> lazy_truncate_heap(onerel, vacrelstats);
>

Where did that "tuples_deleted > 0" condition come from? It seems
counter-productive; if a previous vacuum failed to acquire the lock,
subsequent vacuums wouldn't even try if they don't remove any tuples.
How about simply:

***************
*** 183,190 ****
* number of pages. Otherwise, the time taken isn't worth it.
*/
possibly_freeable = vacrelstats->rel_pages -
vacrelstats->nonempty_pages;
! if (possibly_freeable >= REL_TRUNCATE_MINIMUM ||
! possibly_freeable >= vacrelstats->rel_pages / REL_TRUNCATE_FRACTION)
lazy_truncate_heap(onerel, vacrelstats);

/* Vacuum the Free Space Map */
--- 183,191 ----
* number of pages. Otherwise, the time taken isn't worth it.
*/
possibly_freeable = vacrelstats->rel_pages -
vacrelstats->nonempty_pages;
! if (possibly_freeable > 0 &&
! (possibly_freeable >= REL_TRUNCATE_MINIMUM ||
! possibly_freeable >= vacrelstats->rel_pages / REL_TRUNCATE_FRACTION))
lazy_truncate_heap(onerel, vacrelstats);

/* Vacuum the Free Space Map */

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, 2009-01-06 at 15:48 +0200, Heikki Linnakangas wrote:
> How about simply:

OK

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> On Tue, 2009-01-06 at 15:48 +0200, Heikki Linnakangas wrote:
>> How about simply:
>
> OK

Committed and backpatched all the way back to 7.4 stable.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com