Thread: Cluster and vacuum performance

I have a few questions about cluster and vacuum.
We have a table that is 56 GB in size and after a purge based on dates 16GB
was made available as reported below.
PWFPM_DEV=# vacuum full verbose analyze forecastelement;
INFO:  vacuuming "public.forecastelement"
INFO:  "forecastelement": found 93351479 removable, 219177133 nonremovable
row versions in 6621806 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 156 to 192 bytes long.
There were 611201 unused item pointers.
Total free space (including removable row versions) is 16296891960 bytes.
1974172 pages are or will become empty, including 26 at the end of the
table.
1990268 pages containing 15794855436 free bytes are potential move
destinations.
CPU 467.29s/48.52u sec elapsed 4121.69 sec.

How can you improve the performance of cluster?
1. BY increasing sort_mem?
2. Does increasing vacuum_mem help?
3. Does checkpoint_segments improve it?

Dan

Dan,

> INFO:  vacuuming "public.forecastelement"
> INFO:  "forecastelement": found 93351479 removable, 219177133 nonremovable

The high number of nonremovable above probably indicates that you have a
transaction being held open which prevents VACUUM from being effective.
Look for long-hung processes and/or transaction management errors in your
client code.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Josh Berkus <josh@agliodbs.com> writes:
>> INFO:  vacuuming "public.forecastelement"
>> INFO:  "forecastelement": found 93351479 removable, 219177133 nonremovable

> The high number of nonremovable above probably indicates that you have a
> transaction being held open which prevents VACUUM from being effective.

You misread it --- "nonremovable" doesn't mean "dead but not removable",
it just means "not removable".  Actually the next line of his log showed
there were zero nonremovable dead tuples, so he's not got any
open-transaction problem.

            regards, tom lane

"Shea,Dan [CIS]" <Dan.Shea@ec.gc.ca> writes:
> How can you improve the performance of cluster?
> 1. BY increasing sort_mem?

Yes.

            regards, tom lane

Tom,

> You misread it --- "nonremovable" doesn't mean "dead but not removable",
> it just means "not removable".  Actually the next line of his log showed
> there were zero nonremovable dead tuples, so he's not got any
> open-transaction problem.

Ooops.  Sorry, Dan.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco