Horton
Menu

Thread

  1. Optimize truncation logic to reduce AccessExclusive lock impact

    Stepan Neretin <slpmcf@gmail.com> — 2025-03-18T06:03:38Z

    Hi hackers,

We've encountered an issue where autovacuum holds an AccessExclusive lock
for an extended period when attempting to truncate heap tables. The root
cause appears to be the way autovacuum handles block truncation,
specifically when scanning shared buffers during smgrtruncate. This issue
is particularly painful for large shared buffer configurations (e.g., 1.8
TiB with NUMA and multiple workers), and pg_statistic is among the most
affected tables.

When autovacuum detects freeable blocks at the tail of a relation, it
attempts to truncate them. It acquires an AccessExclusive lock and scans
all shared buffers to remove the corresponding blocks. With a large shared
buffer configuration (e.g., 1.8 TiB, 200 million blocks, and multiple NUMA
nodes), this process is slow and significantly impacts performance.

We propose modifying the truncation condition in should_attempt_truncation
to avoid unnecessary full buffer scans. The new formula ensures we only
attempt truncation when we can free at least 3% of the relation size + 2
pages. This change prevents excessive truncation attempts on small tables
while reducing the impact on larger relations. Previously, small tables
could theoretically be truncated to a single page, but with this change,
they may remain around 3 pages instead. We don't see this as a significant
issue.

The idea for this patch was proposed by Yuriy Sokolov.

There is also an open discussion about optimizing the shared buffer
traversal to minimize the impact of AccessExclusive locks. This patch is a
step toward improving the situation.

The patch is attached to this message.

Best regards, Stepan Neretin.

  2. Re: Optimize truncation logic to reduce AccessExclusive lock impact

    David Rowley <dgrowleyml@gmail.com> — 2025-03-19T02:09:07Z

    On Tue, 18 Mar 2025 at 19:04, Stepan Neretin <slpmcf@gmail.com> wrote:
> We propose modifying the truncation condition in should_attempt_truncation to avoid unnecessary full buffer scans. The new formula ensures we only attempt truncation when we can free at least 3% of the relation size + 2 pages. This change prevents excessive truncation attempts on small tables while reducing the impact on larger relations. Previously, small tables could theoretically be truncated to a single page, but with this change, they may remain around 3 pages instead. We don't see this as a significant issue.

I'm just following this code through. Looking at
DropRelationBuffers(), d6ad34f34 added an optimisation so that we can
skip scanning all of shared buffers while in recovery (where
smgrnblocks_cached() can return a non-InvalidBlockNumber value), which
the following code might decide to lookup the buffers one-by-one
rather than scanning the entire buffer pool:

if (BlockNumberIsValid(nBlocksToInvalidate) &&
    nBlocksToInvalidate < BUF_DROP_FULL_SCAN_THRESHOLD)
    {
        for (j = 0; j < nforks; j++)
            FindAndDropRelationBuffers(rlocator.locator, forkNum[j],
                nForkBlock[j], firstDelBlock[j]);
        return;
}

While I don't know this code too well, from a quick look, it seem to
me that since DropRelationBuffers() is being called from
smgrtruncate() in this case, smgrtruncate() has access to old_nblocks
so it knows the old size of the relation. Couldn't we do something
like have another version of DropRelationBuffers() which accepts a
parameter for the old number of blocks and uses that instead of
calling smgrnblocks_cached()?  That would allow the
FindAndDropRelationBuffers() optimisation to be used in cases where
the buffers to truncate is small or shared buffers is large.

As for the patch being proposed, it looks like you're maybe just
showing the answer to the question left in the comment below is "Yes".

/*
 * Space/time tradeoff parameters: do these need to be user-tunable?
 *
 * To consider truncating the relation, we want there to be at least
 * REL_TRUNCATE_MINIMUM or (relsize / REL_TRUNCATE_FRACTION) (whichever
 * is less) potentially-freeable pages.
 */
#define REL_TRUNCATE_MINIMUM 1000
#define REL_TRUNCATE_FRACTION 16

David

  3. Re: Optimize truncation logic to reduce AccessExclusive lock impact

    Yura Sokolov <y.sokolov@postgrespro.ru> — 2025-03-19T16:05:13Z

    19.03.2025 05:09, David Rowley wrote:
> On Tue, 18 Mar 2025 at 19:04, Stepan Neretin <slpmcf@gmail.com> wrote:
>> We propose modifying the truncation condition in should_attempt_truncation to avoid unnecessary full buffer scans. The new formula ensures we only attempt truncation when we can free at least 3% of the relation size + 2 pages. This change prevents excessive truncation attempts on small tables while reducing the impact on larger relations. Previously, small tables could theoretically be truncated to a single page, but with this change, they may remain around 3 pages instead. We don't see this as a significant issue.
> 
> I'm just following this code through. Looking at
> DropRelationBuffers(), d6ad34f34 added an optimisation so that we can
> skip scanning all of shared buffers while in recovery (where
> smgrnblocks_cached() can return a non-InvalidBlockNumber value), which
> the following code might decide to lookup the buffers one-by-one
> rather than scanning the entire buffer pool:
> 
> if (BlockNumberIsValid(nBlocksToInvalidate) &&
>     nBlocksToInvalidate < BUF_DROP_FULL_SCAN_THRESHOLD)
>     {
>         for (j = 0; j < nforks; j++)
>             FindAndDropRelationBuffers(rlocator.locator, forkNum[j],
>                 nForkBlock[j], firstDelBlock[j]);
>         return;
> }
> 
> While I don't know this code too well, from a quick look, it seem to
> me that since DropRelationBuffers() is being called from
> smgrtruncate() in this case, smgrtruncate() has access to old_nblocks
> so it knows the old size of the relation. Couldn't we do something
> like have another version of DropRelationBuffers() which accepts a
> parameter for the old number of blocks and uses that instead of
> calling smgrnblocks_cached()?  That would allow the
> FindAndDropRelationBuffers() optimisation to be used in cases where
> the buffers to truncate is small or shared buffers is large.

Yes, it will work for smaller relations.
But it doesn't fix issue with large relations:
- imho, there is no big need to truncate 2TB table to release every 8MB of
free space, but it happens now.

So, probably we should do both:
- improve truncate of smaller relations
- reduce frequency of truncation larger relations.

I believe, suggested patch is acceptable in its form and will not harm
after optimization for small relations will be implemented.
(Disclaimer: I'm the author of the patch.)

> As for the patch being proposed, it looks like you're maybe just
> showing the answer to the question left in the comment below is "Yes".
>
> /*
>  * Space/time tradeoff parameters: do these need to be user-tunable?

Well, the patch changes formula as well. It is harder to implement
user-tunable formula.

-------
regards
Yura Sokolov

  4. Re: Optimize truncation logic to reduce AccessExclusive lock impact

    Yura Sokolov <y.sokolov@postgrespro.ru> — 2025-03-21T11:50:22Z

    19.03.2025 19:05, Yura Sokolov wrote:
> 19.03.2025 05:09, David Rowley wrote:
>> On Tue, 18 Mar 2025 at 19:04, Stepan Neretin <slpmcf@gmail.com> wrote:
>>> We propose modifying the truncation condition in should_attempt_truncation to avoid unnecessary full buffer scans. 
>> I'm just following this code through. Looking at
>> DropRelationBuffers(), d6ad34f34 added an optimisation so that we can
>> skip scanning all of shared buffers while in recovery (where
>> smgrnblocks_cached() can return a non-InvalidBlockNumber value), which
>> the following code might decide to lookup the buffers one-by-one
>> rather than scanning the entire buffer pool:
>>
>> if (BlockNumberIsValid(nBlocksToInvalidate) &&
>>     nBlocksToInvalidate < BUF_DROP_FULL_SCAN_THRESHOLD)
>>     {
>>         for (j = 0; j < nforks; j++)
>>             FindAndDropRelationBuffers(rlocator.locator, forkNum[j],
>>                 nForkBlock[j], firstDelBlock[j]);
>>         return;
>> }
>>
>> While I don't know this code too well, from a quick look, it seem to
>> me that since DropRelationBuffers() is being called from
>> smgrtruncate() in this case, smgrtruncate() has access to old_nblocks
>> so it knows the old size of the relation. Couldn't we do something
>> like have another version of DropRelationBuffers() which accepts a
>> parameter for the old number of blocks and uses that instead of
>> calling smgrnblocks_cached()?  That would allow the
>> FindAndDropRelationBuffers() optimisation to be used in cases where
>> the buffers to truncate is small or shared buffers is large.
> 
> Yes, it will work for smaller relations.
> But it doesn't fix issue with large relations:
> - imho, there is no big need to truncate 2TB table to release every 8MB of
> free space, but it happens now.

After thinking a bit, I realized I was not quite right: if large table is
truncated on small number of pages, and we know old size and new one, we
can precisely remove them from shared buffers.

Exactly as you've described, excuse me for not understanding you clearly
for the first time.

But the question still remains: should we use this possibility with huge
tables, or we should just truncate them rarely?

-- 
regards
Yura Sokolov aka funny-falcon