On Tue, Feb 11, 2014 at 05:51:36PM -0200, Claudio Freire wrote:
> > We track relpages and relallvisible, which seems like a more direct measure.
> > Once analyze is done (which is already triggered by inserts) and sets those,
> > it could fire a vacuum based on the ratio of those values, or the aut
On Tue, Feb 11, 2014 at 4:13 PM, Jeff Janes wrote:
>>
>> Do we want to track the number of inserts in statistics and trigger an
>> auto-vacuum after a specified number of inserts?
>
>
> We track relpages and relallvisible, which seems like a more direct measure.
> Once analyze is done (which is al
On Tue, Feb 11, 2014 at 9:12 AM, Bruce Momjian wrote:
> On Tue, Feb 11, 2014 at 11:28:36AM -0500, Robert Haas wrote:
> > A sequential scan will set hint bits and will prune the page, but
> > pruning the page doesn't ever mark it all-visible; that logic is
> > entirely in vacuum. If that could be
On Tue, Feb 11, 2014 at 01:54:48PM -0500, Tom Lane wrote:
> Bruce Momjian writes:
> > On Tue, Feb 11, 2014 at 07:31:03PM +0100, Andres Freund wrote:
> >> I am not saying it shouldn't be improved, I just don't see the point of
> >> bringing it up while everyone is busy with the last CF and claiming
On 2014-02-11 13:41:46 -0500, Bruce Momjian wrote:
> Wait longer for what? Anti-xid-wraparound vacuum?
Yes.
> Is using VACUUM for these cases documented? Should it be?
No idea, it seems to be part of at least part of the folkloric
knowledge, from what I see at clients.
> > I am not saying it
Bruce Momjian writes:
> On Tue, Feb 11, 2014 at 07:31:03PM +0100, Andres Freund wrote:
>> I am not saying it shouldn't be improved, I just don't see the point of
>> bringing it up while everyone is busy with the last CF and claiming it
>> is unusable and that stating that it is surprisising that n
On Tue, Feb 11, 2014 at 07:31:03PM +0100, Andres Freund wrote:
> On 2014-02-11 13:23:19 -0500, Bruce Momjian wrote:
> > On Tue, Feb 11, 2014 at 06:54:10PM +0100, Andres Freund wrote:
> > > On 2014-02-11 12:12:13 -0500, Bruce Momjian wrote:
> > > > Yes, that pretty much sums it up. We introduced in
On 2014-02-11 13:23:19 -0500, Bruce Momjian wrote:
> On Tue, Feb 11, 2014 at 06:54:10PM +0100, Andres Freund wrote:
> > On 2014-02-11 12:12:13 -0500, Bruce Momjian wrote:
> > > Yes, that pretty much sums it up. We introduced index-only scans in 9.2
> > > (2012) but they still seem to be not usable
On Tue, Feb 11, 2014 at 06:54:10PM +0100, Andres Freund wrote:
> On 2014-02-11 12:12:13 -0500, Bruce Momjian wrote:
> > Yes, that pretty much sums it up. We introduced index-only scans in 9.2
> > (2012) but they still seem to be not usable for insert-only workloads
> > two years later. Based on c
On 2014-02-11 12:12:13 -0500, Bruce Momjian wrote:
> Yes, that pretty much sums it up. We introduced index-only scans in 9.2
> (2012) but they still seem to be not usable for insert-only workloads
> two years later. Based on current progress, it doesn't look like this
> will be corrected until 9.
On Tue, Feb 11, 2014 at 11:28:36AM -0500, Robert Haas wrote:
> A sequential scan will set hint bits and will prune the page, but
> pruning the page doesn't ever mark it all-visible; that logic is
> entirely in vacuum. If that could be made cheap enough to be
> negligible, it might well be worth do
On Tue, Feb 11, 2014 at 10:56 AM, Bruce Momjian wrote:
> Right now, as I remember, only vacuum sets the visibility bits. If we
> don't want to make vacuum trigger for insert-only workloads, can we set
> pages all-visible more often?
>
> Is there a reason that a sequential scan, which does do page
On Mon, Feb 3, 2014 at 11:55:34AM -0500, Robert Haas wrote:
> > Robert, where are we on this? Should I post a patch?
>
> I started working on this at one point but didn't finish the
> implementation, let alone the no-doubt-onerous performance testing
> that will be needed to validate whatever we
First, thanks for this thoughtful email.
On Tue, Feb 4, 2014 at 7:14 PM, Jeff Janes wrote:
> On Mon, Feb 3, 2014 at 8:55 AM, Robert Haas wrote:
>> I've also had some further thoughts about the right way to drive
>> vacuum scheduling. I think what we need to do is tightly couple the
>> rate at w
On Mon, Feb 3, 2014 at 8:55 AM, Robert Haas wrote:
> I've also had some further thoughts about the right way to drive
> vacuum scheduling. I think what we need to do is tightly couple the
> rate at which we're willing to do vacuuming to the rate at which we're
> incurring "vacuum debt". That
On Fri, Jan 31, 2014 at 10:22 PM, Bruce Momjian wrote:
> On Thu, Sep 19, 2013 at 02:39:43PM -0400, Robert Haas wrote:
>> Right now, whether or not to autovacuum is the rest of a two-pronged
>> test. The first prong is based on number of updates and deletes
>> relative to table size; that triggers
On Thu, Sep 19, 2013 at 02:39:43PM -0400, Robert Haas wrote:
> Right now, whether or not to autovacuum is the rest of a two-pronged
> test. The first prong is based on number of updates and deletes
> relative to table size; that triggers a regular autovacuum. The
> second prong is based on age(re
On Fri, Sep 20, 2013 at 11:51 AM, Andres Freund wrote:
> On 2013-09-20 11:30:26 -0400, Robert Haas wrote:
>> On Thu, Sep 19, 2013 at 6:59 PM, Andres Freund
>> wrote:
>> > The reason I suggested keeping track of the xids of unremovable tuples
>> > is that the current logic doesn't handle that at
On 2013-09-20 11:30:26 -0400, Robert Haas wrote:
> On Thu, Sep 19, 2013 at 6:59 PM, Andres Freund wrote:
> > The reason I suggested keeping track of the xids of unremovable tuples
> > is that the current logic doesn't handle that at all. We just
> > unconditionally set n_dead_tuples to zero after
On Thu, Sep 19, 2013 at 6:59 PM, Andres Freund wrote:
> The reason I suggested keeping track of the xids of unremovable tuples
> is that the current logic doesn't handle that at all. We just
> unconditionally set n_dead_tuples to zero after a vacuum even if not a
> single row could actually be cle
On 2013-09-19 14:39:43 -0400, Robert Haas wrote:
> On Tue, Sep 17, 2013 at 7:10 PM, Andres Freund wrote:
> > I generally think the current logic for triggering VACUUMs via
> > autovacuum doesn't really make all that much sense in the days where we
> > have the visibility map.
>
> Right now, wheth
Robert Haas wrote:
> Right now, whether or not to autovacuum is the rest of a two-pronged
> test. The first prong is based on number of updates and deletes
> relative to table size; that triggers a regular autovacuum. The
> second prong is based on age(relfrozenxid) and triggers a
> non-page-s
On Tue, Sep 17, 2013 at 7:10 PM, Andres Freund wrote:
> I generally think the current logic for triggering VACUUMs via
> autovacuum doesn't really make all that much sense in the days where we
> have the visibility map.
Right now, whether or not to autovacuum is the rest of a two-pronged
test. T
On 9/17/13 6:10 PM, Andres Freund wrote:
What if we maintained XID stats for ranges of pages in a separate
>fork? Call it the XidStats fork. Presumably the interesting pieces
>would be min(xmin) and max(xmax) for pages that aren't all visible. If
>we did that at a granularity of, say, 1MB worth o
On 2013-09-17 11:37:35 -0500, Jim Nasby wrote:
> On 9/7/13 12:34 AM, Andres Freund wrote:
> >What I was thinking of was to keep track of the oldest xids on pages
> >that cannot be marked all visible. I haven't thought about the
> >statistics part much, but what if we binned the space between
> >[Re
On 9/7/13 12:34 AM, Andres Freund wrote:
What I was thinking of was to keep track of the oldest xids on pages
that cannot be marked all visible. I haven't thought about the
statistics part much, but what if we binned the space between
[RecentGlobalXmin, ->nextXid) into 10 bins and counted the num
On Mon, Sep 9, 2013 at 9:33 PM, Jeff Janes wrote:
> On Sun, Sep 8, 2013 at 8:49 PM, Amit Kapila wrote:
>> On Mon, Sep 9, 2013 at 2:35 AM, Jeff Janes wrote:
>>> I thought it was well known, but maybe I was overly optimistic. I've
>>> considered IOS to be mostly useful for data mining work on rea
On Sun, Sep 8, 2013 at 12:47:35AM +0200, Andres Freund wrote:
> Hi,
>
> On 2013-09-07 12:50:59 -0400, Bruce Momjian wrote:
> > That seems very complicated. I think it would be enough to record the
> > current xid at the time of the vacuum, and when testing for later
> > vacuums, if that saved xi
On Sun, Sep 8, 2013 at 8:49 PM, Amit Kapila wrote:
> On Mon, Sep 9, 2013 at 2:35 AM, Jeff Janes wrote:
>> I thought it was well known, but maybe I was overly optimistic. I've
>> considered IOS to be mostly useful for data mining work on read-mostly
>> tables, which you would probably vacuum manu
On Mon, Sep 9, 2013 at 2:35 AM, Jeff Janes wrote:
> On Thu, Sep 5, 2013 at 7:00 PM, Bruce Momjian wrote:
>> On Thu, Sep 5, 2013 at 09:10:06PM -0400, Robert Haas wrote:
>>> On Thu, Sep 5, 2013 at 8:14 PM, Bruce Momjian wrote:
>>> > Actually, I now realize it is more complex than that, and worse.
On Thu, Sep 5, 2013 at 7:00 PM, Bruce Momjian wrote:
> On Thu, Sep 5, 2013 at 09:10:06PM -0400, Robert Haas wrote:
>> On Thu, Sep 5, 2013 at 8:14 PM, Bruce Momjian wrote:
>> > Actually, I now realize it is more complex than that, and worse. There
>> > are several questions to study to understan
Hi,
On 2013-09-07 12:50:59 -0400, Bruce Momjian wrote:
> That seems very complicated. I think it would be enough to record the
> current xid at the time of the vacuum, and when testing for later
> vacuums, if that saved xid is earlier than the RecentGlobalXmin, and
> there have been no inserts/up
On Sat, Sep 7, 2013 at 07:34:49AM +0200, Andres Freund wrote:
> > The idea of using RecentGlobalXmin to see how much _work_ has happened
> > since the last vacuum is interesting, but it doesn't handle read-only
> > transactions; I am not sure how they can be tracked. You make a good
> > point th
On 2013-09-06 20:29:08 -0400, Bruce Momjian wrote:
> On Sat, Sep 7, 2013 at 12:26:23AM +0200, Andres Freund wrote:
> > I wonder if we shouldn't trigger most vacuums (not analyze!) via unset
> > fsm bits. Perhaps combined with keeping track of RecentGlobalXmin to
>
> Fsm bits? FSM tracks the free
On Fri, Sep 6, 2013 at 06:36:47PM +0200, Andres Freund wrote:
> On 2013-09-06 12:30:56 -0400, Bruce Momjian wrote:
> > > I am not sure I understand this though. What would be the point to go
> > > and set all visible and not do the rest of the vacuuming work?
> > >
> > > I think triggering vacuumi
On Sat, Sep 7, 2013 at 12:26:23AM +0200, Andres Freund wrote:
> > So, what should trigger an auto-vacuum vacuum for these workloads?
> > Rather than activity, which is what normally drives autovacuum, it is
> > lack of activity that should drive it, combined with a high VM cleared
> > bit percent
On Fri, Sep 6, 2013 at 01:01:59PM -0400, Bruce Momjian wrote:
> This December 2012 thread by Andrew Dunstan shows he wasn't aware that a
> manual VACUUM was required for index-only scans. That thread ended with
> us realizing that pg_upgrade's ANALYZE runs will populate
> pg_class.relallvisible.
On 2013-09-06 13:01:59 -0400, Bruce Momjian wrote:
> On Fri, Sep 6, 2013 at 06:36:47PM +0200, Andres Freund wrote:
> > On 2013-09-06 12:30:56 -0400, Bruce Momjian wrote:
> > > > I am not sure I understand this though. What would be the point to go
> > > > and set all visible and not do the rest of
On 2013-09-06 15:13:30 -0400, Bruce Momjian wrote:
> On Fri, Sep 6, 2013 at 01:01:59PM -0400, Bruce Momjian wrote:
> > This December 2012 thread by Andrew Dunstan shows he wasn't aware that a
> > manual VACUUM was required for index-only scans. That thread ended with
> > us realizing that pg_upgr
On 9/5/13 8:29 PM, Gavin Flower wrote:
How about a 'VACUUM AFTER' command (part of the 'BEGIN' transaction syntax?)
that would:
1. only be valid in a transaction
2. initiate a vacuum after the current transaction completed
3. defer any vacuum triggered due to other criteria
If the transacti
On 9/6/13 2:13 PM, Bruce Momjian wrote:
On Fri, Sep 6, 2013 at 01:01:59PM -0400, Bruce Momjian wrote:
This December 2012 thread by Andrew Dunstan shows he wasn't aware that a
manual VACUUM was required for index-only scans. That thread ended with
us realizing that pg_upgrade's ANALYZE runs wil
On Fri, Sep 6, 2013 at 03:08:54PM +0200, Andres Freund wrote:
> On 2013-09-06 01:22:36 -0400, Alvaro Herrera wrote:
> > I think it's shortsighted to keep thinking of autovacuum as just a way
> > to run VACUUM and ANALYZE. We have already discussed work items that
> > need to be done separately, s
On 2013-09-06 12:30:56 -0400, Bruce Momjian wrote:
> > I am not sure I understand this though. What would be the point to go
> > and set all visible and not do the rest of the vacuuming work?
> >
> > I think triggering vacuuming by scanning the visibility map for the
> > number of unset bits and us
On 09/06/2013 03:12 PM, Andres Freund wrote:
> On 2013-09-06 13:38:56 +0200, Hannu Krosing wrote:
>> On 09/06/2013 09:23 AM, Dimitri Fontaine wrote:
>>> Alvaro Herrera writes:
I'm not sure if we need to expose all these new maintenance actions as
SQL commands.
>>> I strongly think we sho
On 2013-09-06 13:38:56 +0200, Hannu Krosing wrote:
> On 09/06/2013 09:23 AM, Dimitri Fontaine wrote:
> > Alvaro Herrera writes:
> >> I'm not sure if we need to expose all these new maintenance actions as
> >> SQL commands.
> > I strongly think we should, if only for diagnostic purposes.
> It woul
On 2013-09-06 01:22:36 -0400, Alvaro Herrera wrote:
> I think it's shortsighted to keep thinking of autovacuum as just a way
> to run VACUUM and ANALYZE. We have already discussed work items that
> need to be done separately, such as truncating the last few empty pages
> on a relation that was vac
On 09/06/2013 09:23 AM, Dimitri Fontaine wrote:
> Alvaro Herrera writes:
>> I'm not sure if we need to expose all these new maintenance actions as
>> SQL commands.
> I strongly think we should, if only for diagnostic purposes.
It would be much easier and more flexible to expose them
as pg_*() fun
Alvaro Herrera writes:
> I'm not sure if we need to expose all these new maintenance actions as
> SQL commands.
I strongly think we should, if only for diagnostic purposes. Also to
adapt to some well defined workloads that the automatic system is not
designed to handle.
Regards,
--
Dimitri Font
Bruce Momjian escribió:
> Ideas
> -
>
> I think we need to detect tables that do not have VM bits set and try to
> determine if they should be vacuumed. If a table has most of its VM
> bits set, there in need to vacuum it for VM bit setting.
I think it's shortsighted to keep thinking of aut
On Thu, Sep 5, 2013 at 09:10:06PM -0400, Robert Haas wrote:
> On Thu, Sep 5, 2013 at 8:14 PM, Bruce Momjian wrote:
> > Actually, I now realize it is more complex than that, and worse. There
> > are several questions to study to understand when pg_class.relallvisible
> > is updated (which is used
On 06/09/13 13:10, Robert Haas wrote:
On Thu, Sep 5, 2013 at 8:14 PM, Bruce Momjian wrote:
Actually, I now realize it is more complex than that, and worse. There
are several questions to study to understand when pg_class.relallvisible
is updated (which is used to determine if index-only scans
On Thu, Sep 5, 2013 at 8:14 PM, Bruce Momjian wrote:
> Actually, I now realize it is more complex than that, and worse. There
> are several questions to study to understand when pg_class.relallvisible
> is updated (which is used to determine if index-only scans are a good
> optimization choice),
On Wed, Sep 4, 2013 at 04:56:55PM -0400, Bruce Momjian wrote:
> > "Add a column pg_class.relallvisible to remember the number of pages
> > that were all-visible according to the visibility map as of the last
> > VACUUM
> > (or ANALYZE, or some other operations that update pg_class.relpages).
> > U
On Thu, Dec 13, 2012 at 03:31:06PM +, Peter Geoghegan wrote:
> On 13 December 2012 03:51, Tom Lane wrote:
> > ANALYZE does not set that value, and is not going to start doing so,
> > because it doesn't scan enough of the table to derive a trustworthy
> > value.
>
> I'm slightly surprised by y
On 13 December 2012 03:51, Tom Lane wrote:
> ANALYZE does not set that value, and is not going to start doing so,
> because it doesn't scan enough of the table to derive a trustworthy
> value.
I'm slightly surprised by your remarks here, because the commit
message where the relallvisible column w
On Thu, Dec 13, 2012 at 09:40:40AM +, Simon Riggs wrote:
> On 13 December 2012 03:51, Tom Lane wrote:
>
> >> Yes, this does seem like a problem for upgrades from 9.2 to 9.3? We can
> >> have pg_dump --binary-upgrade set these, or have ANALYZE set it. I
> >> would prefer the later.
> >
> >
On 13 December 2012 03:51, Tom Lane wrote:
>> Yes, this does seem like a problem for upgrades from 9.2 to 9.3? We can
>> have pg_dump --binary-upgrade set these, or have ANALYZE set it. I
>> would prefer the later.
>
> ANALYZE does not set that value, and is not going to start doing so,
> beca
On Thu, Dec 13, 2012 at 9:21 AM, Tom Lane wrote:
> Bruce Momjian writes:
>> On Wed, Dec 12, 2012 at 05:27:39PM -0500, Andrew Dunstan wrote:
>>> Actually, the table had been analysed but not vacuumed, so this
>>> kinda begs the question what will happen to this value on
>>> pg_upgrade? Will people
Bruce Momjian writes:
> On Wed, Dec 12, 2012 at 05:27:39PM -0500, Andrew Dunstan wrote:
>> Actually, the table had been analysed but not vacuumed, so this
>> kinda begs the question what will happen to this value on
>> pg_upgrade? Will people's queries suddenly get slower until
>> autovacuum kicks
On Wed, Dec 12, 2012 at 05:27:39PM -0500, Andrew Dunstan wrote:
>
> On 12/12/2012 05:12 PM, Andrew Dunstan wrote:
> >
> >On 12/12/2012 04:32 PM, Tom Lane wrote:
> >>Andrew Dunstan writes:
> >>>A client is testing a migration from 9.1 to 9.2, and has found that a
> >>>large number of queries run m
60 matches
Mail list logo
