pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCluster and vacuum performance
5+ messages / 3 participants
[nested] [flat]
* Cluster and vacuum performance
@ 2004-03-09 20:17 Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>
0 siblings, 2 replies; 5+ messages in thread
From: Shea,Dan [CIS] @ 2004-03-09 20:17 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Cluster and vacuum performance
@ 2004-03-09 22:20 Josh Berkus <josh@agliodbs.com>
parent: Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2004-03-09 22:20 UTC (permalink / raw)
To: Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Cluster and vacuum performance
@ 2004-03-09 22:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-03-09 22:57 UTC (permalink / raw)
To: Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Cluster and vacuum performance
@ 2004-03-09 23:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2004-03-09 23:30 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Cluster and vacuum performance
@ 2004-03-10 17:35 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2004-03-10 17:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-03-10 17:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-09 20:17 Cluster and vacuum performance Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>
2004-03-09 22:20 ` Josh Berkus <josh@agliodbs.com>
2004-03-09 23:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-10 17:35 ` Josh Berkus <josh@agliodbs.com>
2004-03-09 22:57 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox