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

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

Re: [HACKERS] optimizing vacuum truncation scans

On 3 August 2015 at 17:18, Jeff Janes jeff.ja...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jul 27, 2015 at 1:40 PM, Simon Riggs si...@2ndquadrant.com wrote: On 22 July 2015 at 17:11, Jeff Janes jeff.ja...@gmail.com wrote: On Wed, Jul 22, 2015 at 6:59 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jun 29, 2015 at 1:54 AM, Jeff Janes jeff.ja...@gmail.com wrote:

Re: [HACKERS] optimizing vacuum truncation scans

On Sat, Jul 25, 2015 at 4:46 AM, Simon Riggs si...@2ndquadrant.com wrote: On 22 July 2015 at 14:59, Robert Haas robertmh...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On 22 July 2015 at 17:11, Jeff Janes jeff.ja...@gmail.com wrote: On Wed, Jul 22, 2015 at 6:59 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jun 29, 2015 at 1:54 AM, Jeff Janes jeff.ja...@gmail.com wrote: Attached is a patch that implements the vm scan for truncation. It introduces

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

Re: [HACKERS] optimizing vacuum truncation scans

On 22 July 2015 at 14:59, Robert Haas robertmh...@gmail.com 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 Wed, Jul 22, 2015 at 12:11 PM, Jeff Janes jeff.ja...@gmail.com 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.

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 6:59 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jun 29, 2015 at 1:54 AM, Jeff Janes jeff.ja...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jun 29, 2015 at 1:54 AM, Jeff Janes jeff.ja...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On 16 July 2015 at 06:51, Haribabu Kommi kommi.harib...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On Thu, Jul 16, 2015 at 11:21 AM, Haribabu Kommi kommi.harib...@gmail.com wrote: On Wed, Jul 15, 2015 at 11:19 PM, Amit Kapila amit.kapil...@gmail.com wrote: One case where this patch can behave differently then current code is, when before taking Exclusive lock on relation for truncation,

Re: [HACKERS] optimizing vacuum truncation scans

On Thu, Jul 16, 2015 at 10:17 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Thu, Jul 16, 2015 at 11:21 AM, Haribabu Kommi kommi.harib...@gmail.com wrote: Here I attached updated patches, 1) without prefetch logic. 2) with combined vm and prefetch logic. I think it is better to just

Re: [HACKERS] optimizing vacuum truncation scans

On Wed, Jul 15, 2015 at 11:19 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Mon, Jun 29, 2015 at 11:24 AM, Jeff Janes jeff.ja...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jun 29, 2015 at 11:24 AM, Jeff Janes jeff.ja...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jul 13, 2015 at 5:16 PM, Haribabu Kommi kommi.harib...@gmail.com wrote: On Mon, Jul 13, 2015 at 12:06 PM, Haribabu Kommi kommi.harib...@gmail.com wrote: On Thu, Jul 9, 2015 at 5:36 PM, Haribabu Kommi kommi.harib...@gmail.com wrote: I will do some performance tests and send you the

Re: [HACKERS] optimizing vacuum truncation scans

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

Re: [HACKERS] optimizing vacuum truncation scans

On Thu, Jul 9, 2015 at 5:36 PM, Haribabu Kommi kommi.harib...@gmail.com 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 vacuum

Re: [HACKERS] optimizing vacuum truncation scans

On Thu, Jul 9, 2015 at 4:37 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Wed, Jul 8, 2015 at 9:46 PM, Haribabu Kommi kommi.harib...@gmail.com wrote: On Mon, Jun 29, 2015 at 3:54 PM, Jeff Janes jeff.ja...@gmail.com wrote: There is still the case of pages which had their visibility bit set

Re: [HACKERS] optimizing vacuum truncation scans

On Wed, Jul 8, 2015 at 9:46 PM, Haribabu Kommi kommi.harib...@gmail.com wrote: On Mon, Jun 29, 2015 at 3:54 PM, Jeff Janes jeff.ja...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Jun 29, 2015 at 3:54 PM, Jeff Janes jeff.ja...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

Jeff Janes jeff.ja...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On Tue, May 26, 2015 at 12:37 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Apr 20, 2015 at 10:18 AM, Jim Nasby jim.na...@bluetreble.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On Mon, Apr 20, 2015 at 10:18 AM, Jim Nasby jim.na...@bluetreble.com 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?

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

Re: [HACKERS] optimizing vacuum truncation scans

On Sun, Apr 19, 2015 at 7:09 PM, Jeff Janes jeff.ja...@gmail.com 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

Re: [HACKERS] optimizing vacuum truncation scans

On Sun, Apr 19, 2015 at 10:38 PM, Jim Nasby jim.na...@bluetreble.com 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

[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

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