Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Sep 28, 2015 at 2:05 AM, Haribabu Kommi wrote: > I got the following way to get the whether data file is present in the > DISK or SSD. > > 1. Get the device file system that table data file is mapped using the > following or similar. > > df -P "filename" | awk 'NR==2{print $1}' > > 2. if t

Re: [HACKERS] optimizing vacuum truncation scans

On Tue, Aug 4, 2015 at 2:18 AM, Jeff Janes wrote: > On Mon, Jul 27, 2015 at 1:40 PM, Simon Riggs wrote: >> >> On 22 July 2015 at 17:11, Jeff Janes wrote: >>> >>> On Wed, Jul 22, 2015 at 6:59 AM, Robert Haas >>> wrote: On Mon, Jun 29, 2015 at 1:54 AM, Jeff Janes wrote: > Att

Re: [HACKERS] optimizing vacuum truncation scans

On 3 August 2015 at 17:18, Jeff Janes wrote: > That does still leave the prefetch technique, so all is not lost. >> >> Can we see a patch with just prefetch, probably with a simple choice of >> stride? Thanks. >> > > I probably won't get back to it this commit fest, so it can be set to > returne

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jul 27, 2015 at 1:40 PM, Simon Riggs wrote: > On 22 July 2015 at 17:11, Jeff Janes wrote: > >> On Wed, Jul 22, 2015 at 6:59 AM, Robert Haas >> wrote: >> >>> On Mon, Jun 29, 2015 at 1:54 AM, Jeff Janes >>> wrote: >>> > Attached is a patch that implements the vm scan for truncation. It

Re: [HACKERS] optimizing vacuum truncation scans

On 7/27/15 10:39 AM, Robert Haas wrote: But that's not enough: we also need to know that any tuple that survived the prune operation (that is, it wasn't redirected or marked unused) has a new-enough xmin, which isn't tracked anywhere. Wouldn't that be old-enough xmin? heap_prune_chain() is alr

Re: [HACKERS] optimizing vacuum truncation scans

On 22 July 2015 at 17:11, Jeff Janes wrote: > On Wed, Jul 22, 2015 at 6:59 AM, Robert Haas > wrote: > >> On Mon, Jun 29, 2015 at 1:54 AM, Jeff Janes wrote: >> > Attached is a patch that implements the vm scan for truncation. It >> > introduces a variable to hold the last blkno which was skippe

Re: [HACKERS] optimizing vacuum truncation scans

On Sat, Jul 25, 2015 at 4:46 AM, Simon Riggs wrote: > On 22 July 2015 at 14:59, Robert Haas wrote: >> Urgh. So if we do this, that forever precludes having HOT pruning set >> the all-visible bit. > > What is the reason why we don't do that already? Surely its a one liner? I wish! It actually d

Re: [HACKERS] optimizing vacuum truncation scans

On 22 July 2015 at 14:59, Robert Haas wrote: > Urgh. So if we do this, that forever precludes having HOT pruning set > the all-visible bit. What is the reason why we don't do that already? Surely its a one liner? -- Simon Riggshttp://www.2ndQuadrant.com/

Re: [HACKERS] optimizing vacuum truncation scans

On 7/22/15 11:11 AM, Jeff Janes wrote: I guess add an "Assert(InRecovery || running_a_vacuum);" to the visibilitymap_set with a comment there, except that I don't know how to implement running_a_vacuum so that it covers manual vacs as well as autovac. static bool RunningVacuum; ? It only needs

Re: [HACKERS] optimizing vacuum truncation scans

On 2015-07-23 11:06:25 -0400, Robert Haas wrote: > I don't know what to do about the fact that it loses to the VM-bit > based approach, but I think it's a bad bet that we will never care > about setting visibility map bits anyplace other than VACUUM. +1 A background process that tries to hint all

Re: [HACKERS] optimizing vacuum truncation scans

On Wed, Jul 22, 2015 at 12:11 PM, Jeff Janes wrote: >> Urgh. So if we do this, that forever precludes having HOT pruning set >> the all-visible bit. > > I wouldn't say forever, as it would be easy to revert the change if > something more important came along that conflicted with it. Well, the pe

Re: [HACKERS] optimizing vacuum truncation scans

On Wed, Jul 22, 2015 at 6:59 AM, Robert Haas wrote: > On Mon, Jun 29, 2015 at 1:54 AM, Jeff Janes wrote: > > Attached is a patch that implements the vm scan for truncation. It > > introduces a variable to hold the last blkno which was skipped during the > > forward portion. Any blocks after bo

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jun 29, 2015 at 1:54 AM, Jeff Janes wrote: > Attached is a patch that implements the vm scan for truncation. It > introduces a variable to hold the last blkno which was skipped during the > forward portion. Any blocks after both this blkno and after the last > inspected nonempty page (wh

Re: [HACKERS] optimizing vacuum truncation scans

On 16 July 2015 at 06:51, Haribabu Kommi wrote: > I marked the patch as ready for committer. The most recent results seem to indicate that the prefetch isn't worth pursuing, but the vm test is. Can someone repeat the perf tests on something larger so we can see, when/if there is a benefit? Je

Re: [HACKERS] optimizing vacuum truncation scans

On Thu, Jul 16, 2015 at 10:17 PM, Amit Kapila wrote: > On Thu, Jul 16, 2015 at 11:21 AM, Haribabu Kommi > wrote: > >> >> Here I attached updated patches, >> 1) without prefetch logic. >> 2) with combined vm and prefetch logic. >> > > I think it is better to just get the first patch in as that in

Re: [HACKERS] optimizing vacuum truncation scans

On Thu, Jul 16, 2015 at 11:21 AM, Haribabu Kommi wrote: > > On Wed, Jul 15, 2015 at 11:19 PM, Amit Kapila wrote: > > > > One case where this patch can behave differently then current > > code is, when before taking Exclusive lock on relation for truncation, > > if some backend clears the vm bit a

Re: [HACKERS] optimizing vacuum truncation scans

On Wed, Jul 15, 2015 at 11:19 PM, Amit Kapila wrote: > On Mon, Jun 29, 2015 at 11:24 AM, Jeff Janes wrote: >> >> >> Attached is a patch that implements the vm scan for truncation. It >> introduces a variable to hold the last blkno which was skipped during the >> forward portion. Any blocks afte

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jun 29, 2015 at 11:24 AM, Jeff Janes wrote: > > > Attached is a patch that implements the vm scan for truncation. It introduces a variable to hold the last blkno which was skipped during the forward portion. Any blocks after both this blkno and after the last inspected nonempty page (whi

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jul 13, 2015 at 5:16 PM, Haribabu Kommi wrote: > On Mon, Jul 13, 2015 at 12:06 PM, Haribabu Kommi > wrote: >> On Thu, Jul 9, 2015 at 5:36 PM, Haribabu Kommi >> wrote: >>> >>> I will do some performance tests and send you the results. >> >> Here are the performance results tested on my m

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jul 13, 2015 at 12:06 PM, Haribabu Kommi wrote: > On Thu, Jul 9, 2015 at 5:36 PM, Haribabu Kommi > wrote: >> >> I will do some performance tests and send you the results. > > Here are the performance results tested on my machine. > > > Head vm patch

Re: [HACKERS] optimizing vacuum truncation scans

On Thu, Jul 9, 2015 at 5:36 PM, Haribabu Kommi wrote: > > I will do some performance tests and send you the results. Here are the performance results tested on my machine. Head vm patchvm+prefetch patch First vacuum120sec<1sec

Re: [HACKERS] optimizing vacuum truncation scans

On Thu, Jul 9, 2015 at 4:37 PM, Jeff Janes wrote: > On Wed, Jul 8, 2015 at 9:46 PM, Haribabu Kommi > wrote: >> >> On Mon, Jun 29, 2015 at 3:54 PM, Jeff Janes wrote: >> > >> > There is still the case of pages which had their visibility bit set by a >> > prior vacuum and then were not inspected by

Re: [HACKERS] optimizing vacuum truncation scans

On Wed, Jul 8, 2015 at 9:46 PM, Haribabu Kommi wrote: > On Mon, Jun 29, 2015 at 3:54 PM, Jeff Janes wrote: > > > > Attached is a patch that implements the vm scan for truncation. It > > introduces a variable to hold the last blkno which was skipped during the > > forward portion. Any blocks af

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jun 29, 2015 at 3:54 PM, Jeff Janes wrote: > > Attached is a patch that implements the vm scan for truncation. It > introduces a variable to hold the last blkno which was skipped during the > forward portion. Any blocks after both this blkno and after the last > inspected nonempty page (

Re: [HACKERS] optimizing vacuum truncation scans

On Tue, May 26, 2015 at 12:37 PM, Jeff Janes wrote: > On Mon, Apr 20, 2015 at 10:18 AM, Jim Nasby > wrote: > >> On 4/20/15 1:50 AM, Jeff Janes wrote: >> >>> >>> For that matter, why do we scan backwards anyway? The comments don't >>> explain it, and we have nonempty_pages as a starting

Re: [HACKERS] optimizing vacuum truncation scans

Jeff Janes writes: > As it is currently, it is not clear what purpose nonempty_pages serves. It's to allow an early exit at vacuumlazy.c:271 in case there is no chance of deleting a useful number of pages. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql

Re: [HACKERS] optimizing vacuum truncation scans

On Tue, May 26, 2015 at 12:37 PM, Jeff Janes wrote: > On Mon, Apr 20, 2015 at 10:18 AM, Jim Nasby > wrote: > >> On 4/20/15 1:50 AM, Jeff Janes wrote: >> >>> >>> For that matter, why do we scan backwards anyway? The comments don't >>> explain it, and we have nonempty_pages as a starting

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Apr 20, 2015 at 10:18 AM, Jim Nasby wrote: > On 4/20/15 1:50 AM, Jeff Janes wrote: > >> >> For that matter, why do we scan backwards anyway? The comments don't >> explain it, and we have nonempty_pages as a starting point, so why >> don't we just scan forward? I suspect that

Re: [HACKERS] optimizing vacuum truncation scans

On Sun, Apr 19, 2015 at 7:09 PM, Jeff Janes wrote: > I did literally the simplest thing I could think of as a proof of concept > patch, to see if it would actually fix things. I just jumped back a certain > number of blocks occasionally and prefetched them forward, then resumed the > regular b

Re: [HACKERS] optimizing vacuum truncation scans

On 4/20/15 1:50 AM, Jeff Janes wrote: Shouldn't completely empty pages be set as all-visible in the VM? If so, can't we just find the largest not-all-visible page and move forward from there, instead of moving backwards like we currently do? If the entire table is all-visible, we wo

Re: [HACKERS] optimizing vacuum truncation scans

On Sun, Apr 19, 2015 at 10:38 PM, Jim Nasby wrote: > On 4/19/15 9:09 PM, Jeff Janes wrote: > >> I did literally the simplest thing I could think of as a proof of >> concept patch, to see if it would actually fix things. I just jumped >> back a certain number of blocks occasionally and prefetched

Re: [HACKERS] optimizing vacuum truncation scans

On 4/19/15 9:09 PM, Jeff Janes wrote: I did literally the simplest thing I could think of as a proof of concept patch, to see if it would actually fix things. I just jumped back a certain number of blocks occasionally and prefetched them forward, then resumed the regular backward scan. The patc

[HACKERS] optimizing vacuum truncation scans

After a large bulk load aborted near the end, I decided to vacuum the main table so as to not leave a huge chunk of free space in the middle of it, before re-running the bulk load. This vacuum took a frustratingly long time, as the backwards scan over the table to truncate the space did not trigge