Re: integrate pg_upgrade analyze_new_cluster.sh into vacuumdb

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.

Therefore, I suggest to add this functionality into the vacuumdb
program. The attached patch shows how this could work. I have added an
option --analyze-in-stages (stupid name) that triggers the same logic as
the pg_upgrade script.

There are some details to be considered about who pg_upgrade would call
this. For example, would we keep creating the script and just have the
script call vacuumdb with the new option, or would we skip the script
altogether and just print a message from pg_upgrade? Also, pg_upgrade
contains logic to run a vacuum (not only analyze) in the final run when
upgrading from PostgreSQL <8.4 to deal with the freespace map. Not sure
how to adapt that; maybe just keep the script and run a non-analyze
vacuum after the analyze.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> 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.

> Therefore, I suggest to add this functionality into the vacuumdb
> program.

Seems reasonable.

> There are some details to be considered about who pg_upgrade would call
> this. For example, would we keep creating the script and just have the
> script call vacuumdb with the new option, or would we skip the script
> altogether and just print a message from pg_upgrade? Also, pg_upgrade
> contains logic to run a vacuum (not only analyze) in the final run when
> upgrading from PostgreSQL <8.4 to deal with the freespace map. Not sure
> how to adapt that; maybe just keep the script and run a non-analyze
> vacuum after the analyze.

I don't think this vacuumdb feature should deal with any
version-conversion issues. So it sounds like the thing to do is keep the
wrapper script, which will give us a place to put any such special actions
without having to kluge up vacuumdb's behavior. That'll avoid breaking
scripts that users might've built for using pg_upgrade, too.

regards, tom lane

On Wed, Jan 8, 2014 at 10:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> 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.
>
>> Therefore, I suggest to add this functionality into the vacuumdb
>> program.
>
> Seems reasonable.
>
>> There are some details to be considered about who pg_upgrade would call
>> this. For example, would we keep creating the script and just have the
>> script call vacuumdb with the new option, or would we skip the script
>> altogether and just print a message from pg_upgrade? Also, pg_upgrade
>> contains logic to run a vacuum (not only analyze) in the final run when
>> upgrading from PostgreSQL <8.4 to deal with the freespace map. Not sure
>> how to adapt that; maybe just keep the script and run a non-analyze
>> vacuum after the analyze.
>
> I don't think this vacuumdb feature should deal with any
> version-conversion issues. So it sounds like the thing to do is keep the
> wrapper script, which will give us a place to put any such special actions
> without having to kluge up vacuumdb's behavior. That'll avoid breaking
> scripts that users might've built for using pg_upgrade, too.

I guess I don't see what's wrong with kludging up vacuumdb. It's not
like that's a very complicated utility; what will be hurt by a few
more options?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Jan 8, 2014 at 10:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I don't think this vacuumdb feature should deal with any
>> version-conversion issues. So it sounds like the thing to do is keep the
>> wrapper script, which will give us a place to put any such special actions
>> without having to kluge up vacuumdb's behavior. That'll avoid breaking
>> scripts that users might've built for using pg_upgrade, too.

> I guess I don't see what's wrong with kludging up vacuumdb. It's not
> like that's a very complicated utility; what will be hurt by a few
> more options?

Carrying kluges forever, and exposing them to users' view. The particular
example Peter gave was only relevant to upgrades from 8.4; why would we be
putting code into vacuumdb now for that, and expecting to support it
forevermore? What if the code to fix up something doesn't even *work*
unless we're updating from version M.N? Putting such code into vacuumdb
means you have to make it bulletproof against other invocation
circumstances, and document what it does (since it's a user-visible
switch), and just in general greatly increases the development overhead.

regards, tom lane

On Thu, Jan 9, 2014 at 12:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Jan 8, 2014 at 10:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I don't think this vacuumdb feature should deal with any
>>> version-conversion issues. So it sounds like the thing to do is keep the
>>> wrapper script, which will give us a place to put any such special actions
>>> without having to kluge up vacuumdb's behavior. That'll avoid breaking
>>> scripts that users might've built for using pg_upgrade, too.
>
>> I guess I don't see what's wrong with kludging up vacuumdb. It's not
>> like that's a very complicated utility; what will be hurt by a few
>> more options?
>
> Carrying kluges forever, and exposing them to users' view. The particular
> example Peter gave was only relevant to upgrades from 8.4; why would we be
> putting code into vacuumdb now for that, and expecting to support it
> forevermore? What if the code to fix up something doesn't even *work*
> unless we're updating from version M.N? Putting such code into vacuumdb
> means you have to make it bulletproof against other invocation
> circumstances, and document what it does (since it's a user-visible
> switch), and just in general greatly increases the development overhead.

I was referring to the analyze-in-stages logic, which is not specific
to 8.4. I don't see a reason not to put that into vacuumdb.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I was referring to the analyze-in-stages logic, which is not specific
> to 8.4. I don't see a reason not to put that into vacuumdb.

Right, that's Peter's core proposal, which I agreed with. The issue
was what to do with some other steps that pg_upgrade sometimes sticks
into the analyze_new_cluster.sh script.

regards, tom lane

On Thu, Jan 9, 2014 at 01:03:08PM -0500, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > I was referring to the analyze-in-stages logic, which is not specific
> > to 8.4. I don't see a reason not to put that into vacuumdb.
>
> Right, that's Peter's core proposal, which I agreed with. The issue
> was what to do with some other steps that pg_upgrade sometimes sticks
> into the analyze_new_cluster.sh script.

I assume pg_upgrade would just tell the user what vacuumdb command to
run, rather than create a script to call it. If they have to run two
commands, we will output the two commands.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> I assume pg_upgrade would just tell the user what vacuumdb command to
> run, rather than create a script to call it. If they have to run two
> commands, we will output the two commands.

Why would we change the operational procedure of "run this script
after pg_upgrade"? It just complicates life for users.

Bear in mind also that some may have scripted the call of the script
already, so you'd be breaking their scripts; to achieve what?

regards, tom lane

On Fri, Jan 10, 2014 at 08:15:53PM -0500, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > I assume pg_upgrade would just tell the user what vacuumdb command to
> > run, rather than create a script to call it. If they have to run two
> > commands, we will output the two commands.
>
> Why would we change the operational procedure of "run this script
> after pg_upgrade"? It just complicates life for users.

If it is one command, why use a script? Just give them the command.

> Bear in mind also that some may have scripted the call of the script
> already, so you'd be breaking their scripts; to achieve what?

Uh, I didn't think enough people scripted pg_upgrade to be worth it.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Fri, 2014-01-10 at 20:21 -0500, Bruce Momjian wrote:
> > Bear in mind also that some may have scripted the call of the script
> > already, so you'd be breaking their scripts; to achieve what?
>
> Uh, I didn't think enough people scripted pg_upgrade to be worth it.

I think pg_upgrade has so many options that you must script it. But it
changes between every release anyway, so the scripts are rarely for
eternity.

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> On Fri, Jan 10, 2014 at 08:15:53PM -0500, Tom Lane wrote:
>> Why would we change the operational procedure of "run this script
>> after pg_upgrade"? It just complicates life for users.

> If it is one command, why use a script? Just give them the command.

Because it won't necessarily always be just one command. I don't
think we need to complicate life for users (or even more, their
scripts) by making them actually read what pg_upgrade prints there.

The point of Peter's proposal is that the multi-step-database-analyze
process might be useful outside the context of pg_upgrade, so it's
worth extracting that as a utility. It doesn't follow that we need
to change the way users *of pg_upgrade itself* invoke the process.

regards, tom lane

Here is a patch for the commit fest.

I have left the pg_upgrade script and just put in the new call instead
of the three old ones.

For the 8.3 case, which calls for a real vacuum in addition to the
analyzes, I have simply added another vacuumdb call to the script. This
technically makes the script do more work than before, where the vacuum
was part of the final analyze, but I don't see an elegant way to do it
otherwise.

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. 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@mail.gmail.com
but it hasn't turned into patches so far.

/ Oskari

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

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> 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?

It's not always a "little bit" of time.  For a description of my
experience with a home-grown 3 stage process before one was built
into pg_upgrade, see this post:

http://www.postgresql.org/message-id/1373465348.51692.YahooMailNeo@web162906.mail.bf1.yahoo.com

Basically, we cut our down time from hours to minutes without
serious impairment of performance.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2014-01-14 22:22:08 -0500, Peter Eisentraut wrote:
> + <listitem>
> + <para>
> + Only calculate statistics for use by the optimizer (no vacuum),
> + like <option>--analyze-only</option>. Run several stages of analyze
> + with different configuration settings, to produce usable statistics
> + faster.
> + </para>
> +
> + <para>
> + This option is useful to analyze a database that was newly populated
> + from a restored dump or by <command>pg_upgrade</command>. This option
> + will try to create some statistics as fast as possible, to make the
> + database usable, and then produce full statistics in the subsequent
> + stages.
> + </para>
> + </listitem>
> + </varlistentry>
> +

If it's intended to be useful independent from pg_upgrade, shouldn't
this document in how many stages it runs?

> + const char *stage_commands[] = {
> + "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
> + "SET default_statistics_target=10; RESET vacuum_cost_delay;",
> + "RESET default_statistics_target;"
> + };
> + const char *stage_messages[] = {
> + gettext_noop("Generating minimal optimizer statistics (1 target)"),
> + gettext_noop("Generating medium optimizer statistics (10 targets)"),
> + gettext_noop("Generating default (full) optimizer statistics (100 targets?)")
> + };

Imo 100 shouldn't be listed here, as it's actually using the database's
default.

This whole thing won't work for relations with per-column statistics
targets btw...

Other than that, it looks fine to me. And certainly nicer than that
chain of shell/bat pg_upgrade used to output.

Greetings,

Andres Freund

On 4 April 2014 16:01, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

>> + const char *stage_commands[] = {
>> + "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
>> + "SET default_statistics_target=10; RESET vacuum_cost_delay;",
>> + "RESET default_statistics_target;"

> This whole thing won't work for relations with per-column statistics
> targets btw...

Yes, agreed. Plus I would note that this makes no difference at all
for very small tables since the sample will be big enough even with
stats_target=1.

ISTM that this is the way ANALYZE should work when run on a table that
has never been analysed before. Let's just do this logic within
ANALYZE and be done.

Suggest logic
if not ANALYZEd before && table is not small && stats_target is
default then AnalyzeInStages()
otherwise just do one ANALYZE pass

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> ISTM that this is the way ANALYZE should work when run on a table that
> has never been analysed before. Let's just do this logic within
> ANALYZE and be done.

Can't. Not unless you intend to make ANALYZE do internal commits
so that its output rows become visible to other transactions before
its done. (Which would be, shall we say, a damn bad idea.)

Even without that implementation problem, I absolutely don't agree
that this is such a great thing that it should become not only the
default but the only obtainable behavior. It would slow down
ANALYZE, and would only be helpful if there is concurrent activity
that would benefit from the stats. There are plenty of scenarios
where that would be giving up something to get nothing.

regards, tom lane

On Sun, Apr 13, 2014 at 10:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> ISTM that this is the way ANALYZE should work when run on a table that
>> has never been analysed before. Let's just do this logic within
>> ANALYZE and be done.
>
> Can't. Not unless you intend to make ANALYZE do internal commits
> so that its output rows become visible to other transactions before
> its done. (Which would be, shall we say, a damn bad idea.)
>
> Even without that implementation problem, I absolutely don't agree
> that this is such a great thing that it should become not only the
> default but the only obtainable behavior. It would slow down
> ANALYZE, and would only be helpful if there is concurrent activity
> that would benefit from the stats. There are plenty of scenarios
> where that would be giving up something to get nothing.

Agreed. I suspect that's also true of the pg_upgrade behavior. Sure,
there may be people for who the database will be immediately usable
with a stats target of 1 or 10, but I bet there are also quite a few
for whom it isn't, or who just wait for the whole thing to be done
anyway before they fire the system up.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Committed, with your suggestions.