Re: double vacuum in initdb

initdb currently does

PG_CMD_PUTS("ANALYZE;\nVACUUM FULL;\nVACUUM FREEZE;\n");

FREEZE is now part of FULL, so this seems redundant. Also, ANALYZE can
be run as part of VACUUM. So this could be

PG_CMD_PUTS("VACUUM FULL ANALYZE;\n");

There has been some concerns about time spent in initdb in test suites,
which is why I looked into this. In testing, this change can shave off
between 10% and 20% of the run time of initdb, so it would be kind of
useful.

The last change to this was

commit 66cd8150636e48a8f143560136a25ec5eb355d8c
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Mon Nov 29 03:05:03 2004 +0000

Clean up initdb's error handling so that it prints something more
useful than just \'failed\' when there's a problem. Per gripe from
Chris Albertson.

In an unrelated change, use VACUUM FULL; VACUUM FREEZE; rather than
a single VACUUM FULL FREEZE command, to respond to my worries of a
couple days ago about the reliability of doing this in one go.

That was a long time ago. Is that still applicable?

On Wed, Dec 10, 2014 at 8:50 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> In an unrelated change, use VACUUM FULL; VACUUM FREEZE; rather than
> a single VACUUM FULL FREEZE command, to respond to my worries of a
> couple days ago about the reliability of doing this in one go.
>
> That was a long time ago. Is that still applicable?

Gosh, I hope not. Note that that was back when we still had old-style
VACUUM FULL, which was significantly more fragile than what we've got
now, I think...

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

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> initdb currently does
> PG_CMD_PUTS("ANALYZE;\nVACUUM FULL;\nVACUUM FREEZE;\n");
> FREEZE is now part of FULL, so this seems redundant. Also, ANALYZE can
> be run as part of VACUUM. So this could be
> PG_CMD_PUTS("VACUUM FULL ANALYZE;\n");

Merging the ANALYZE step would save few cycles, and it'd probably result
in the contents of pg_statistic being at least partly unfrozen at the end
of the process, so please don't go that far.

> There has been some concerns about time spent in initdb in test suites,
> which is why I looked into this. In testing, this change can shave off
> between 10% and 20% of the run time of initdb, so it would be kind of
> useful.

> The last change to this was

> commit 66cd8150636e48a8f143560136a25ec5eb355d8c
> Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Date: Mon Nov 29 03:05:03 2004 +0000

> Clean up initdb's error handling so that it prints something more
> useful than just \'failed\' when there's a problem. Per gripe from
> Chris Albertson.

> In an unrelated change, use VACUUM FULL; VACUUM FREEZE; rather than
> a single VACUUM FULL FREEZE command, to respond to my worries of a
> couple days ago about the reliability of doing this in one go.

> That was a long time ago. Is that still applicable?

Probably not; what I was on about was

http://www.postgresql.org/message-id/12179.1101591711@sss.pgh.pa.us

which certainly isn't a case that exists anymore since we got rid
of the old VACUUM FULL implementation. So I think we could go to

PG_CMD_PUTS("ANALYZE;\nVACUUM FULL FREEZE;\n");

without any degradation of the intended results.

Another idea would be to drop the FULL part and make this

PG_CMD_PUTS("ANALYZE;\nVACUUM FREEZE;\n");

which would speed initdb but it would also lose the testing angle
of being sure that we can VACUUM FULL all system catalogs. OTOH,
I don't immediately see why we shouldn't test that somewhere in the
regression tests rather than in every initdb. (I think part of the
argument for the forced FULL was to make sure we broke anything that
thought system catalogs had fixed relfilenodes, but that should be
pretty well a done deal by now.)

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I think we could go to
> PG_CMD_PUTS("ANALYZE;\nVACUUM FULL FREEZE;\n");
>
> without any degradation of the intended results.
>
> Another idea would be to drop the FULL part and make this
>
> PG_CMD_PUTS("ANALYZE;\nVACUUM FREEZE;\n");

We want to finish with VACUUM FREEZE without the FULL, unless we
don't care about missing visibility maps and free space maps.

[ initdb and start the cluster ]

server started
kgrittn(at)Kevin-Desktop:~/pg/master$ find Debug/data -type f | xargs ls -l >~/ls1

kgrittn(at)Kevin-Desktop:~/pg/master$ psql -c "vacuum freeze;" postgres
VACUUM
kgrittn(at)Kevin-Desktop:~/pg/master$ find Debug/data -type f | xargs ls -l >~/ls2

kgrittn(at)Kevin-Desktop:~/pg/master$ psql -c "vacuum full freeze;" postgres
VACUUM
kgrittn(at)Kevin-Desktop:~/pg/master$ find Debug/data -type f | xargs ls -l >~/ls3
kgrittn(at)Kevin-Desktop:~/pg/master$ grep _fsm <~/ls1 | wc -l
116
kgrittn(at)Kevin-Desktop:~/pg/master$ grep _fsm <~/ls2 | wc -l
119
kgrittn(at)Kevin-Desktop:~/pg/master$ grep _fsm <~/ls3 | wc -l
80
kgrittn(at)Kevin-Desktop:~/pg/master$ grep _vm <~/ls1 | wc -l
116
kgrittn(at)Kevin-Desktop:~/pg/master$ grep _vm <~/ls2 | wc -l
117
kgrittn(at)Kevin-Desktop:~/pg/master$ grep _vm <~/ls3 | wc -l
77

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

On Thu, Dec 11, 2014 at 11:44 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> We want to finish with VACUUM FREEZE without the FULL, unless we
> don't care about missing visibility maps and free space maps.

Oh, good point. I had forgotten about that issue.

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

On 12/11/14 11:44 AM, Kevin Grittner wrote:
> We want to finish with VACUUM FREEZE without the FULL, unless we
> don't care about missing visibility maps and free space maps.

Why would we care, and if we do, why does VACUUM FULL remove them?

You can also run plain VACUUM after FULL to put the maps back.

But the documentation is apparently missing details about this.

On Thu, Dec 11, 2014 at 08:35:43PM -0500, Peter Eisentraut wrote:
> On 12/11/14 11:44 AM, Kevin Grittner wrote:
> > We want to finish with VACUUM FREEZE without the FULL, unless we
> > don't care about missing visibility maps and free space maps.

I have create the attached initdb patch to update this.

> Why would we care, and if we do, why does VACUUM FULL remove them?
>
> You can also run plain VACUUM after FULL to put the maps back.
>
> But the documentation is apparently missing details about this.

It is a long-standing TODO item I tried to fix, but couldn't:

Allow VACUUM FULL and CLUSTER to update the visibility map

index-only scans : abnormal heap fetches after VACUUM FULL
http://www.postgresql.org/message-id/20130112191404.255800@gmx.com

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

+ Everyone has their own god. +

On Fri, Mar 20, 2015 at 10:59:41PM -0400, Bruce Momjian wrote:
> On Thu, Dec 11, 2014 at 08:35:43PM -0500, Peter Eisentraut wrote:
> > On 12/11/14 11:44 AM, Kevin Grittner wrote:
> > > We want to finish with VACUUM FREEZE without the FULL, unless we
> > > don't care about missing visibility maps and free space maps.
>
> I have create the attached initdb patch to update this.

Patch applied.

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

+ Everyone has their own god. +