Re: integrate pg_upgrade analyze_new_cluster.sh into vacuumdb

On Tue, Jan 21, 2014 at 9:06 AM, Oskari Saarenmaa <os(at)ohmu(dot)fi> wrote:

> 09.01.2014 05:15, Peter Eisentraut kirjoitti:
>
> pg_upgrade creates a script analyze_new_cluster.{sh|bat} that runs
>> vacuumdb --analyze-only in three stages with different statistics target
>> settings to get a fresh cluster analyzed faster. I think this behavior
>> is also useful for clusters or databases freshly created by pg_restore
>> or any other loading mechanism, so it's suboptimal to have this
>> constrained to pg_upgrade.
>>
>
> I think the three stage analyze is a wrong solution to the "slow analyze"
> problem.

It certainly is not the best possible solution. But it might be the best
one that can be arrived at within a reasonable amount of time.

If we really want to micromanage the process we could put a lot of work
into it. Take the case of a small table of about 300 pages. We read the
table 3 times (separated by enough time that it is probably no longer
cached), first keeping one tuple per page, then 10, then 100. Instead it
should probably just jump directly to sampling at statistics target of 100
and then forget those small tables, at least if all we are concerned about
is IO costs. (Since the selected tuples are sorted once for each column,
there might be a CPU reason to take a small sample at the first pass, if we
are more concerned with CPU than IO.)

But I do wonder what experience people have with the 3 stage process, how
useful is it empirically? If you can't open the database for general use
until the 3rd phase is done, then you would just jump to doing that stage,
rather than working through all 3 of them. If you can open the database
and muddle through without statistics for a while, why not muddle through
for the little bit longer that it would take to collect the full set right
off the bat, rather than making intermediate passes?

So I agree that the current system in not optimal. But this patch is just
moving existing behavior from a less general location to a more general
one, so I don't think it should be held hostage to improvements that could
theoretically be made but which no one has offered to do. I wouldn't want
to put in a change that forces users to learn something new for 9.4 only to
have it completely redone in 9.5 and then make them learn that. But the
documentation and training burden of this change seems small enough that I
wouldn't worry about that. (On the other hand, the benefit of the change
also seems pretty small.)

In my experience most of the analyze time goes to reading random blocks
> from the disk but we usually use only a small portion of that data (1 row
> per block.)
>
> If we were able to better utilize the data we read we could get good
> statistics with a lot less IO than we currently need. This was discussed
> in length at
> http://www.postgresql.org/message-id/CAM-w4HOjRbNPMW=
> SHjHw_Qfapcuu5Ege1tMdR0ZQU+kqX8Qeug(at)mail(dot)gmail(dot)com but it hasn't turned
> into patches so far.

I don't think it is an accident that it hasn't turned into patches. You
can't change the laws of statistics just by wanting it badly enough. Our
current sampling method is already insufficiently random. We aren't going
to fix things by making it even less random. But I guess that that is an
empirical question again, have most statistics problems been due to the
sample being insufficiently large, or insufficiently random? (Or
insufficiently recent?)

There could be a different stage-by-stage approach where
default_statistics_target is fixed but in the first pass you just take the
first 30,000 rows in each table, and then in second pass you take a random
30,000 rows. But the knobs to do that currently do not exist, and I doubt
they would be welcomed if their only use is to support pg_upgrade. So that
idea is not a blocker to this patch, either.

Cheers,

Jeff