2011/5/29 Tom Lane :
> =?ISO-8859-1?Q?C=E9dric_Villemain?=
> writes:
>> 2011/5/29 Tom Lane :
>>> OK, do you like the attached version of that logic? (Other fragments
>>> of the patch as before.)
>
>> The idea was that remove only one page from the VACUUM will prevent
>> relfrozenxid update and r
On Sun, May 29, 2011 at 10:35 PM, Tom Lane wrote:
> Pavan Deolasee writes:
>> I am sorry if I sounded terse above. But my gripe is that sometimes we
>> are too reluctant to listen to ideas and insist on producing some hard
>> numbers first which might take significant efforts. But we are not
>> e
Pavan Deolasee writes:
> I am sorry if I sounded terse above. But my gripe is that sometimes we
> are too reluctant to listen to ideas and insist on producing some hard
> numbers first which might take significant efforts. But we are not
> equally strict when such changes are introduced initially.
On Sun, May 29, 2011 at 9:27 PM, Tom Lane wrote:
> Pavan Deolasee writes:
>> On Sun, May 29, 2011 at 8:10 PM, Tom Lane wrote:
>>> That would require proof, not just suggestion. Skipping pages will
>>> defeat the OS read-ahead algorithm, and so could easily cost more than
>>> reading them.
>
>>
Pavan Deolasee writes:
> On Sun, May 29, 2011 at 8:10 PM, Tom Lane wrote:
>> That would require proof, not just suggestion. Skipping pages will
>> defeat the OS read-ahead algorithm, and so could easily cost more than
>> reading them.
> My worry is what we have right now is also based on just a
On Sun, May 29, 2011 at 8:10 PM, Tom Lane wrote:
> =?ISO-8859-1?Q?C=E9dric_Villemain?=
> writes:
>> 2011/5/29 Tom Lane :
>>> OK, do you like the attached version of that logic? (Other fragments
>>> of the patch as before.)
>
>> The idea was that remove only one page from the VACUUM will prevent
=?ISO-8859-1?Q?C=E9dric_Villemain?= writes:
> 2011/5/29 Tom Lane :
>> OK, do you like the attached version of that logic? (Other fragments
>> of the patch as before.)
> The idea was that remove only one page from the VACUUM will prevent
> relfrozenxid update and reltuples (and relpages) update.
2011/5/29 Tom Lane :
> Greg Stark writes:
>> On Sat, May 28, 2011 at 12:01 PM, Tom Lane wrote:
>>> I also found that Greg was right in thinking that it would help if we
>>> tweaked lazy_scan_heap to not always scan the first
>>> SKIP_PAGES_THRESHOLD-1 pages even if they were
>>> all_visible_accor
Greg Stark writes:
> On Sat, May 28, 2011 at 12:01 PM, Tom Lane wrote:
>> I also found that Greg was right in thinking that it would help if we
>> tweaked lazy_scan_heap to not always scan the first
>> SKIP_PAGES_THRESHOLD-1 pages even if they were
>> all_visible_according_to_vm.
> You fixed the
On Sat, May 28, 2011 at 12:01 PM, Tom Lane wrote:
> I also found that Greg was right in thinking that it would help if we
> tweaked lazy_scan_heap to not always scan the first
> SKIP_PAGES_THRESHOLD-1 pages even if they were
> all_visible_according_to_vm. That seemed to skew the results if those
Robert Haas writes:
> Still, maybe we don't have a better option. If it were me, I'd add an
> additional safety valve: use your formula if the percentage of the
> relation scanned is above some threshold where there's unlikely to be
> too much skew. But if the percentage scanned is too small, th
Robert Haas writes:
> Still, maybe we don't have a better option. If it were me, I'd add an
> additional safety valve: use your formula if the percentage of the
> relation scanned is above some threshold where there's unlikely to be
> too much skew. But if the percentage scanned is too small, th
On Thu, May 26, 2011 at 5:50 PM, Tom Lane wrote:
> "Kevin Grittner" writes:
>> When we prune or vacuum a page, I don't suppose we have enough
>> information about that page's previous state to calculate a tuple
>> count delta, do we? That would allow a far more accurate number to
>> be maintaine
"Kevin Grittner" writes:
> When we prune or vacuum a page, I don't suppose we have enough
> information about that page's previous state to calculate a tuple
> count delta, do we? That would allow a far more accurate number to
> be maintained than anything suggested so far, as long as we tweak
>
Robert Haas wrote:
> Kevin Grittner wrote:
>> By storing the ratio and one count you make changes to the
>> other count implied and less visible. It seems more
>> understandable and less prone to error (to me, anyway) to keep
>> the two "raw" numbers and calculate the ratio -- and when you
>>
Robert Haas writes:
> On Thu, May 26, 2011 at 12:23 PM, Tom Lane wrote:
>>> Another thought: Couldn't relation_needs_vacanalyze() just scale up
>>> reltuples by the ratio of the current number of pages in the relation
>>> to relpages, just as the query planner does?
>> Hmm ... that would fix Flo
Robert Haas writes:
> Except that's not how it works. At least in the case of ANALYZE, we
> *aren't* counting all the tuples in the table. We're selecting a
> random sample of pages and inferring a tuple density, which we then
> extrapolate to the whole table and store. Then when we pull it bac
On Thu, May 26, 2011 at 2:05 PM, Kevin Grittner
wrote:
>> I'm a bit confused by this - what the current design obfuscates is
>> the fact that reltuples and relpages are not really independent
>> columns; you can't update one without updating the other, unless
>> you want screwy behavior. Replacin
Robert Haas wrote:
> Kevin Grittner wrote:
>> Given how trivial it would be to adjust reltuples to keep its
>> ratio to relpages about the same when we don't have a new "hard"
>> number, but some evidence that we should fudge our previous
>> value, I don't see where this change buys us much. I
On Thu, May 26, 2011 at 1:28 PM, Kevin Grittner
wrote:
> Robert Haas wrote:
>> I think we should really consider replacing reltuples with
>> reltupledensity at some point. I continue to be afraid that using
>> a decaying average in this case is going to end up overweighting
>> the values from so
Robert Haas wrote:
> I think we should really consider replacing reltuples with
> reltupledensity at some point. I continue to be afraid that using
> a decaying average in this case is going to end up overweighting
> the values from some portion of the table that's getting scanned
> repeatedly,
On Thu, May 26, 2011 at 12:23 PM, Tom Lane wrote:
>> Another thought: Couldn't relation_needs_vacanalyze() just scale up
>> reltuples by the ratio of the current number of pages in the relation
>> to relpages, just as the query planner does?
>
> Hmm ... that would fix Florian's immediate issue, an
Robert Haas writes:
> I would feel a lot better about something that is deterministic, like,
> I dunno, if VACUUM visits more than 25% of the table, we use its
> estimate. And we always use ANALYZE's estimate. Or something.
This argument seems to rather miss the point. The data we are working
On Thu, May 26, 2011 at 11:25 AM, Tom Lane wrote:
> I'm still of the opinion that an incremental estimation process like
> the above is a lot saner than what we're doing now, snarky Dilbert
> references notwithstanding. The only thing that seems worthy of debate
> from here is whether we should t
Greg Stark writes:
> On Wed, May 25, 2011 at 9:41 AM, Tom Lane wrote:
>> ... What I'm currently imagining is
>> to do a smoothed moving average, where we factor in the new density
>> estimate with a weight dependent on the percentage of the table we did
>> scan. That is, the calculation goes som
On Wed, May 25, 2011 at 9:41 AM, Tom Lane wrote:
> Yeah, I had been thinking about the latter point. We could be
> conservative and just keep the reported tuple density the same (ie,
> update relpages to the new correct value, while setting reltuples so
> that the density ratio doesn't change).
On Wed, May 25, 2011 at 10:05 PM, Greg Stark wrote:
>> updated_density = old_density + (new_density - old_density) * reliability
>> new_reltuples = updated_density * new_relpages
>
> This amounts to assuming that the pages observed in the vacuum have
> the density observed and the pages that weren
On Wed, May 25, 2011 at 5:54 PM, Tom Lane wrote:
> Robert Haas writes:
>> On Wed, May 25, 2011 at 1:04 PM, Tom Lane wrote:
>>> Because the problem is not specific to TOAST tables. As things
>>> currently stand, we will accept the word of an ANALYZE as gospel even if
>>> it scanned 1% of the tab
Robert Haas writes:
> On Wed, May 25, 2011 at 1:04 PM, Tom Lane wrote:
>> Because the problem is not specific to TOAST tables. As things
>> currently stand, we will accept the word of an ANALYZE as gospel even if
>> it scanned 1% of the table, and completely ignore the results from a
>> VACUUM e
2011/5/25 Alvaro Herrera :
> Excerpts from Cédric Villemain's message of mié may 25 13:24:01 -0400 2011:
>
>> > Well, we only actually need to store one number, because you can figure
>> > out a much more precise number-of-pages figure with pg_relation_size()
>> > divided by configured page size.
>
Excerpts from Cédric Villemain's message of mié may 25 13:24:01 -0400 2011:
> > Well, we only actually need to store one number, because you can figure
> > out a much more precise number-of-pages figure with pg_relation_size()
> > divided by configured page size.
> I may miss something but we nee
2011/5/25 Alvaro Herrera :
> Excerpts from Kevin Grittner's message of mié may 25 12:37:24 -0400 2011:
>> Tom Lane wrote:
>>
>> > I don't know what client-side code might be looking at
>> > relpages/reltuples.
>>
>> I know that I find reltuples useful for getting an "accurate enough"
>> sense of r
On Wed, May 25, 2011 at 1:15 PM, Robert Haas wrote:
> I agree that if VACUUM scanned 99% of the table, it's probably fine to
> use its numbers. It's also fine to use the numbers from ANALYZE,
> because those pages are chosen randomly. What bothers me is the idea
> of using a small *non-random* s
On Wed, May 25, 2011 at 1:04 PM, Tom Lane wrote:
> [ shrug... ] When you don't have complete information, it's *always*
> the case that you will sometimes make a mistake. That's not
> justification for paralysis, especially not when the existing code is
> demonstrably broken.
>
> What occurs to
Excerpts from Robert Haas's message of mié may 25 12:54:28 -0400 2011:
> I don't know. That's maybe better, but I'd be willing to wager that
> in some cases it will just slow down the rate at which we converge to
> a completely incorrect value, while in other cases it'll fail to
> update the data
Robert Haas writes:
> On Wed, May 25, 2011 at 12:41 PM, Tom Lane wrote:
>> Yeah, I had been thinking about the latter point. We could be
>> conservative and just keep the reported tuple density the same (ie,
>> update relpages to the new correct value, while setting reltuples so
>> that the dens
Excerpts from Kevin Grittner's message of mié may 25 12:37:24 -0400 2011:
> Tom Lane wrote:
>
> > I don't know what client-side code might be looking at
> > relpages/reltuples.
>
> I know that I find reltuples useful for getting an "accurate enough"
> sense of rows in a table (or set of tables
On Wed, May 25, 2011 at 12:41 PM, Tom Lane wrote:
> Yeah, I had been thinking about the latter point. We could be
> conservative and just keep the reported tuple density the same (ie,
> update relpages to the new correct value, while setting reltuples so
> that the density ratio doesn't change).
Robert Haas writes:
> On Wed, May 25, 2011 at 11:47 AM, Tom Lane wrote:
>> 2. Revise the vacuum code so that it doesn't skip updating the pg_class
>> entries. We could have it count the number of pages it skipped, rather
>> than just keeping a bool, and then scale up the rel_tuples count to be
>
Tom Lane wrote:
> I don't know what client-side code might be looking at
> relpages/reltuples.
I know that I find reltuples useful for getting an "accurate enough"
sense of rows in a table (or set of tables) without resorting to
count(*). I'd be OK with any two of pages, tuples, and density;
Alvaro Herrera writes:
> Excerpts from Tom Lane's message of mié may 25 11:47:52 -0400 2011:
>> I can see two basic approaches we might take here:
>>
>> 1. Modify autovacuum to use something from the stats collector, rather
>> than reltuples, to make its decisions. I'm not too clear on why
>> r
On Wed, May 25, 2011 at 11:47 AM, Tom Lane wrote:
> 2. Revise the vacuum code so that it doesn't skip updating the pg_class
> entries. We could have it count the number of pages it skipped, rather
> than just keeping a bool, and then scale up the rel_tuples count to be
> approximately right by as
Excerpts from Tom Lane's message of mié may 25 11:47:52 -0400 2011:
> I think I see what must be going on here: that toast table must contain
> a long run of all-visible-according-to-the-VM pages (which is hardly a
> surprising situation). This results in VACUUM choosing not to update
> the pg_cl
Florian Helmberger writes:
> On 25.05.11 04:47, Tom Lane wrote:
>> Florian Helmberger writes:
>>> I'm running a production database with PostgreSQL 9.0.3 (64-bit) on
>>> Debian 5.0.4 and have an issue with a TOAST table and far to frequent
>>> autovacuum runs.
>>>
>>> I think I've pinned the pro
44 matches
Mail list logo
