pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Cluster 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