RE: reloption to prevent VACUUM from truncating empty pages at the end of relation

On February 1, 2019, Tsunakawa, Takayuki wrote: >> As most people seem to agree adding the reloption, here's the patch. >> It passes make check, and works like this: >Sorry, I forgot to include the modified header file. Revised patch attached. Thank you for this. I applied the patch. It

RE: reloption to prevent VACUUM from truncating empty pages at the end of relation

From: Tsunakawa, Takayuki [mailto:tsunakawa.ta...@jp.fujitsu.com] > As most people seem to agree adding the reloption, here's the patch. It > passes make check, and works like this: Sorry, I forgot to include the modified header file. Revised patch attached. Regards Takayuki Tsunakawa

RE: reloption to prevent VACUUM from truncating empty pages at the end of relation

From: Jamison, Kirk [mailto:k.jami...@jp.fujitsu.com] > >On Thu, Nov 15, 2018 at 2:30 PM Alvaro Herrera > wrote: > >> > >> On 2018-Nov-15, Laurenz Albe wrote: > >> > > > > This new option would not only mitigate the long shared_buffers > > > > scan, it would also get rid of the replication

RE: reloption to prevent VACUUM from truncating empty pages at the end of relation

>On Thu, Nov 15, 2018 at 2:30 PM Alvaro Herrera >wrote: >> >> On 2018-Nov-15, Laurenz Albe wrote: >> > > > This new option would not only mitigate the long shared_buffers > > > scan, it would also get rid of the replication conflict caused by > > > the AccessExclusiveLock taken during

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Thu, Nov 15, 2018 at 03:41:03PM +0530, Amit Kapila wrote: > On Thu, Nov 15, 2018 at 2:30 PM Alvaro Herrera > wrote: >> FWIW I was just reminded yesterday that the AEL-for-truncation has been >> diagnosed to be a severe problem in production, and with no other >> solution in sight, I propose

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Thu, Nov 15, 2018 at 2:30 PM Alvaro Herrera wrote: > > On 2018-Nov-15, Laurenz Albe wrote: > > > This new option would not only mitigate the long shared_buffers scan, > > it would also get rid of the replication conflict caused by the > > AccessExclusiveLock taken during truncation, which is

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On 2018-Nov-15, Laurenz Albe wrote: > This new option would not only mitigate the long shared_buffers scan, > it would also get rid of the replication conflict caused by the > AccessExclusiveLock taken during truncation, which is discussed in >

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Tue, 2018-04-17 at 15:09 -0400, Tom Lane wrote: > Alvaro Herrera writes: > > Andres was working on a radix tree structure to fix this problem, but > > that seems to be abandoned now, and it seems a major undertaking. While > > I agree that the proposed solution is a wart, it seems much better

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

Andres Freund wrote: > On 2018-04-19 16:56:59 -0300, Alvaro Herrera wrote: > > Michael Paquier wrote: > > > > > Then, let's consider the beginning of the first commit fest of v12 as > > > judgement. Implementing radix tree for shared buffers is a long-term > > > project, which has no guarantee

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On 2018-04-19 16:56:59 -0300, Alvaro Herrera wrote: > Michael Paquier wrote: > > > Then, let's consider the beginning of the first commit fest of v12 as > > judgement. Implementing radix tree for shared buffers is a long-term > > project, which has no guarantee to get merged, while a

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

2018-04-19 21:56 GMT+02:00 Alvaro Herrera : > Michael Paquier wrote: > > > Then, let's consider the beginning of the first commit fest of v12 as > > judgement. Implementing radix tree for shared buffers is a long-term > > project, which has no guarantee to get merged,

RE: reloption to prevent VACUUM from truncating empty pages at the end of relation

From: Fujii Masao [mailto:masao.fu...@gmail.com] > a very long time before accessing to the relation. Which would cause the > response-time spikes, for example, I observed such spikes several times > on > the server with shared_buffers = 300GB while running the benchmark. FYI, a long transaction

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Wed, Apr 18, 2018 at 07:41:44PM +0530, Amit Kapila wrote: > I think it makes sense to pursue that approach, but it might be worth > considering some alternative till we have it. I remember last time > (in 2015) we have discussed some another solution [1] to this problem > (or similar) and we

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

I wrote: > Pavan Deolasee writes: >> What if we remember the buffers as seen by count_nondeletable_pages() and >> then just discard those specific buffers instead of scanning the entire >> shared_buffers again? > That's an idea. BTW, before pushing too hard on any of

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

Pavan Deolasee writes: > What if we remember the buffers as seen by count_nondeletable_pages() and > then just discard those specific buffers instead of scanning the entire > shared_buffers again? That's an idea. > Surely we revisit all to-be-truncated blocks before >

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Wed, Apr 18, 2018 at 10:50 PM, Fujii Masao wrote: > > > I'm not sure if it's safe to cancel forcibly VACUUM's truncation during > scaning shared_buffers. That scan happens after WAL-logging and before > the actual truncation. > > Ah ok. I misread your proposal. This is

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Wed, Apr 18, 2018 at 11:29 PM, Pavan Deolasee wrote: > > > On Tue, Apr 17, 2018 at 11:05 PM, Fujii Masao wrote: >> >> Hi, >> >> I'd like to propose to add $SUBJECT for performance improvement. >> >> When VACUUM tries to truncate the trailing

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On 17 April 2018 at 20:09, Tom Lane wrote: > Alvaro Herrera writes: >> Andres was working on a radix tree structure to fix this problem, but >> that seems to be abandoned now, and it seems a major undertaking. While >> I agree that the proposed

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Tue, Apr 17, 2018 at 11:05 PM, Fujii Masao wrote: > Hi, > > I'd like to propose to add $SUBJECT for performance improvement. > > When VACUUM tries to truncate the trailing empty pages, it scans > shared_buffers > to invalidate the pages-to-truncate during holding an

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Wed, Apr 18, 2018 at 7:46 AM, Andres Freund wrote: > On 2018-04-18 10:46:51 +0900, Michael Paquier wrote: >> On Tue, Apr 17, 2018 at 06:13:31PM -0700, Andres Freund wrote: >> > Not sure what you mean? >> >> Do you need help on it? I suggest that I could undertake the

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On 2018-04-18 10:46:51 +0900, Michael Paquier wrote: > On Tue, Apr 17, 2018 at 06:13:31PM -0700, Andres Freund wrote: > > Not sure what you mean? > > Do you need help on it? I suggest that I could undertake the proposed > patch and submit it earlier in the development cycle of v12. I think it's

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Tue, Apr 17, 2018 at 06:13:31PM -0700, Andres Freund wrote: > Not sure what you mean? Do you need help on it? I suggest that I could undertake the proposed patch and submit it earlier in the development cycle of v12. -- Michael signature.asc Description: PGP signature

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On April 17, 2018 6:00:59 PM PDT, Michael Paquier wrote: >On Tue, Apr 17, 2018 at 12:12:26PM -0700, Andres Freund wrote: >> On 2018-04-17 15:09:18 -0400, Tom Lane wrote: >>> Alvaro Herrera writes: Andres was working on a radix tree structure

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Tue, Apr 17, 2018 at 12:12:26PM -0700, Andres Freund wrote: > On 2018-04-17 15:09:18 -0400, Tom Lane wrote: >> Alvaro Herrera writes: >>> Andres was working on a radix tree structure to fix this problem, but >>> that seems to be abandoned now, and it seems a major

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On 2018-04-17 15:09:18 -0400, Tom Lane wrote: > Alvaro Herrera writes: > > Andres was working on a radix tree structure to fix this problem, but > > that seems to be abandoned now, and it seems a major undertaking. I hope to re-ignite work on that later in the v12 cycle.

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

Alvaro Herrera writes: > Andres was working on a radix tree structure to fix this problem, but > that seems to be abandoned now, and it seems a major undertaking. While > I agree that the proposed solution is a wart, it seems much better than > no solution at all. Can

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

Tom Lane wrote: > Fujii Masao writes: > > When VACUUM tries to truncate the trailing empty pages, it scans > > shared_buffers > > to invalidate the pages-to-truncate during holding an AccessExclusive lock > > on > > the relation. So if shared_buffers is huge, other

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Tue, Apr 17, 2018 at 11:09 AM, Tom Lane wrote: > So rather than a klugy solution that only fixes > VACUUM (and not very well, requiring user intervention and an unpleasant > tradeoff), we ought to look at ways to avoid needing a whole-pool scan to > find the pages belonging

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

Fujii Masao writes: > When VACUUM tries to truncate the trailing empty pages, it scans > shared_buffers > to invalidate the pages-to-truncate during holding an AccessExclusive lock on > the relation. So if shared_buffers is huge, other transactions need to wait > for > a

<    1   2