"Compacting" a relation

Started by Peter Eisentrautabout 19 years ago15 messages

peter_e@gmx.net

about 19 years ago

vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT,
there is no indication anywhere how "compacting" is supposed to be achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be
processed effectively by a user.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Hannu Krosing

hannu@skype.net

about 19 years ago

In reply to: Peter Eisentraut (#1)

Re: "Compacting" a relation

Ühel kenal päeval, K, 2006-11-29 kell 11:19, kirjutas Peter Eisentraut:

vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT,
there is no indication anywhere how "compacting" is supposed to be achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be
processed effectively by a user.

I once had an online/concurrent/non-locking compacting script, which did
for each const_pk_col_with_largest_ctid staring starting from end of
relation

UPDATE rel
SET pk_col=pk_col
WHERE pk_col = const_pk_col_with_largest_ctid

until the tuple moved to another page as determined by

SELECT ctid FROM rel where pk_col=const_pk_col_with_largest_ctid

if the tuple moved to a larger page number then it was time for another
lazy vacuum.

this compacted the live data in the table and if done enough times, the
lazy vacuum did actually shorten the file.

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

Tom Lane

tgl@sss.pgh.pa.us

about 19 years ago

In reply to: Peter Eisentraut (#1)

Re: "Compacting" a relation

Peter Eisentraut <peter_e@gmx.net> writes:

vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT,
there is no indication anywhere how "compacting" is supposed to be achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be
processed effectively by a user.

So change it ...

regards, tom lane

Jim Nasby

decibel@decibel.org

about 19 years ago

In reply to: Hannu Krosing (#2)

Re: "Compacting" a relation

On Nov 29, 2006, at 2:29 AM, Hannu Krosing wrote:

Ühel kenal päeval, K, 2006-11-29 kell 11:19, kirjutas Peter
Eisentraut:

vacuumlazy.c contains a hint "Consider compacting this relation"
but AFAICT,
there is no indication anywhere how "compacting" is supposed to be
achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the
hint can be
processed effectively by a user.

I once had an online/concurrent/non-locking compacting script,
which did
for each const_pk_col_with_largest_ctid staring starting from end of
relation

UPDATE rel
SET pk_col=pk_col
WHERE pk_col = const_pk_col_with_largest_ctid

until the tuple moved to another page as determined by

SELECT ctid FROM rel where pk_col=const_pk_col_with_largest_ctid

if the tuple moved to a larger page number then it was time for
another
lazy vacuum.

Larger or smaller?

There's a TODO about allowing control over what pages in a relation
you get back from FSM that would make this a lot easier. In the case
of a bloated table, you'd want to have the FSM favor handing out
pages at the beginning of the heap. If you combined that with a
special mode where new tuples would not be created on any page in the
last X percent of the heap, it would be trivial to clean up a bloated
table. Theoretically, you might be able to apply the same kind of
technique to cleaning up a bloated index.

BTW, the other reason to allow selecting where the FSM hands out data
is for keeping a table clustered. You might also be able to keep
indexes in a more optimal order on-disk (as I understand it, over
time the physical order of an index can become very different from
the index order).
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Hannu Krosing

hannu@skype.net

about 19 years ago

In reply to: Jim Nasby (#4)

Re: "Compacting" a relation

Ühel kenal päeval, P, 2006-12-03 kell 22:14, kirjutas Jim Nasby:

On Nov 29, 2006, at 2:29 AM, Hannu Krosing wrote:

Ühel kenal päeval, K, 2006-11-29 kell 11:19, kirjutas Peter
Eisentraut:

vacuumlazy.c contains a hint "Consider compacting this relation"
but AFAICT,
there is no indication anywhere how "compacting" is supposed to be
achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the
hint can be
processed effectively by a user.

I once had an online/concurrent/non-locking compacting script,
which did
for each const_pk_col_with_largest_ctid staring starting from end of
relation

UPDATE rel
SET pk_col=pk_col
WHERE pk_col = const_pk_col_with_largest_ctid

until the tuple moved to another page as determined by

SELECT ctid FROM rel where pk_col=const_pk_col_with_largest_ctid

if the tuple moved to a larger page number then it was time for
another
lazy vacuum.

Larger or smaller?

Larger, smaller is the expected behaviour without vacuum.

There's a TODO about allowing control over what pages in a relation
you get back from FSM that would make this a lot easier. In the case
of a bloated table, you'd want to have the FSM favor handing out
pages at the beginning of the heap. If you combined that with a
special mode where new tuples would not be created on any page in the
last X percent of the heap, it would be trivial to clean up a bloated
table. Theoretically, you might be able to apply the same kind of
technique to cleaning up a bloated index.

BTW, the other reason to allow selecting where the FSM hands out data
is for keeping a table clustered.

FSM is consulted only in case the new tuple does not fit on the same
page as old. for clustering putposes, this should also be optional.

You might also be able to keep
indexes in a more optimal order on-disk (as I understand it, over
time the physical order of an index can become very different from
the index order).
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

NOTICE: This communication contains privileged or other confidential
information. If you have received it in error, please advise the sender
by reply email and immediately delete the message and any attachments
without copying or disclosing the contents.

Bruce Momjian

bruce@momjian.us

almost 19 years ago

In reply to: Tom Lane (#3)

Re: "Compacting" a relation

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT,
there is no indication anywhere how "compacting" is supposed to be achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be
processed effectively by a user.

So change it ...

New message is:

errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Simon Riggs

simon@2ndquadrant.com

almost 19 years ago

In reply to: Bruce Momjian (#6)

Re: "Compacting" a relation

On Sat, 2007-02-03 at 22:11 -0500, Bruce Momjian wrote:

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT,
there is no indication anywhere how "compacting" is supposed to be achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be
processed effectively by a user.

So change it ...

New message is:

errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));

The change of wording may be appropriate, but it is triggered when

if (vacrelstats->tot_free_pages > MaxFSMPages)

So if you VACUUM a 15+GB table and it has only 1% freespace then it will
still generate this message. Hopefully you'd agree that the message
would be inappropriate in that case.

It's also inappropriate because this message is generated *prior* to
doing lazy_truncate_heap(), which could easily remove lots of empty
pages anyhow. That might reduce it to less than MaxFSMPages anyhow, so
it can currently be triggered in wholly inappropriate situations.

So I suggest that we move this wording after lazy_truncate_heap() in
lazy_vacuum_rel() *and* we alter the hint so that it only suggests
VACUUM FULL if the table has 20% fragmentation, whatever its size.

Happy to drop a patch for this, if people agree.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Bruce Momjian

bruce@momjian.us

almost 19 years ago

In reply to: Simon Riggs (#7)

Re: "Compacting" a relation

Simon Riggs wrote:

On Sat, 2007-02-03 at 22:11 -0500, Bruce Momjian wrote:

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT,
there is no indication anywhere how "compacting" is supposed to be achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be
processed effectively by a user.

So change it ...

New message is:

errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));

The change of wording may be appropriate, but it is triggered when

if (vacrelstats->tot_free_pages > MaxFSMPages)

So if you VACUUM a 15+GB table and it has only 1% freespace then it will
still generate this message. Hopefully you'd agree that the message
would be inappropriate in that case.

Interesting. So if you have 1% free on a 15GB table, and that doesn't
fit into the free space, we emit the message. I would think the hint
is accurate, though. Are you saying they should increase FSM and not do
VACUUM FULL in those cases? Should we recommend the fsm increase before
the VACUUM FULL?

It's also inappropriate because this message is generated *prior* to
doing lazy_truncate_heap(), which could easily remove lots of empty
pages anyhow. That might reduce it to less than MaxFSMPages anyhow, so
it can currently be triggered in wholly inappropriate situations.

Yes, we should move the test if there is a better place.

So I suggest that we move this wording after lazy_truncate_heap() in
lazy_vacuum_rel() *and* we alter the hint so that it only suggests
VACUUM FULL if the table has 20% fragmentation, whatever its size.

Interesting. OK, so we have two message, one recommends both, and the
other just FSM increase.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Simon Riggs

simon@2ndquadrant.com

almost 19 years ago

In reply to: Simon Riggs (#7)

2 attachment(s)

Re: [previously on HACKERS] "Compacting" a relation

On Mon, 2007-02-05 at 11:55 +0000, Simon Riggs wrote:

On Sat, 2007-02-03 at 22:11 -0500, Bruce Momjian wrote:

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT,
there is no indication anywhere how "compacting" is supposed to be achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be
processed effectively by a user.

So change it ...

New message is:

errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));

The change of wording may be appropriate, but it is triggered when

if (vacrelstats->tot_free_pages > MaxFSMPages)

So if you VACUUM a 15+GB table and it has only 1% freespace then it will
still generate this message. Hopefully you'd agree that the message
would be inappropriate in that case.

It's also inappropriate because this message is generated *prior* to
doing lazy_truncate_heap(), which could easily remove lots of empty
pages anyhow. That might reduce it to less than MaxFSMPages anyhow, so
it can currently be triggered in wholly inappropriate situations.

So I suggest that we move this wording after lazy_truncate_heap() in
lazy_vacuum_rel() *and* we alter the hint so that it only suggests
VACUUM FULL if the table has 20% fragmentation, whatever its size.

Happy to drop a patch for this, if people agree.

Enclose 2 versions:
v1 - move test and WARNING
v2 - move test and WARNING, plus adjust hint according to relation size

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Attachments:

vac_hint.v1.patchtext/x-patch; charset=UTF-8; name=vac_hint.v1.patchDownload

Index: src/backend/commands/vacuumlazy.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.83
diff -c -r1.83 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c	4 Feb 2007 03:10:55 -0000	1.83
--- src/backend/commands/vacuumlazy.c	5 Feb 2007 17:19:55 -0000
***************
*** 180,185 ****
--- 180,192 ----
  	/* Update shared free space map with final free space info */
  	lazy_update_fsm(onerel, vacrelstats);
  
+ 	if (vacrelstats->tot_free_pages > MaxFSMPages)
+ 		ereport(WARNING,
+ 				(errmsg("relation \"%s.%s\" contains more than \"max_fsm_pages\" pages with useful free space",
+ 						get_namespace_name(RelationGetNamespace(onerel)),
+ 						RelationGetRelationName(onerel)),
+ 				 errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));
+ 
  	/* Update statistics in pg_class */
  	vac_update_relstats(RelationGetRelid(onerel),
  						vacrelstats->rel_pages,
***************
*** 507,519 ****
  					   vacrelstats->tot_free_pages,
  					   empty_pages,
  					   pg_rusage_show(&ru0))));
- 
- 	if (vacrelstats->tot_free_pages > MaxFSMPages)
- 		ereport(WARNING,
- 				(errmsg("relation \"%s.%s\" contains more than \"max_fsm_pages\" pages with useful free space",
- 						get_namespace_name(RelationGetNamespace(onerel)),
- 						relname),
- 				 errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));
  }
  
  
--- 514,519 ----

vac_hint.v2.patchtext/x-patch; charset=UTF-8; name=vac_hint.v2.patchDownload

Index: src/backend/commands/vacuumlazy.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.83
diff -c -r1.83 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c	4 Feb 2007 03:10:55 -0000	1.83
--- src/backend/commands/vacuumlazy.c	5 Feb 2007 17:10:16 -0000
***************
*** 66,71 ****
--- 66,73 ----
  #define REL_TRUNCATE_MINIMUM	1000
  #define REL_TRUNCATE_FRACTION	16
  
+ /* multiplier for acceptable fragmentation before WARNING */
+ #define REL_FRAGMENTATION_FACTOR 5
  
  typedef struct LVRelStats
  {
***************
*** 180,185 ****
--- 182,196 ----
  	/* Update shared free space map with final free space info */
  	lazy_update_fsm(onerel, vacrelstats);
  
+ 	if (vacrelstats->tot_free_pages > MaxFSMPages)
+ 		ereport(WARNING,
+ 				(errmsg("relation \"%s.%s\" contains more than \"max_fsm_pages\" pages with useful free space",
+ 						get_namespace_name(RelationGetNamespace(onerel)),
+ 						RelationGetRelationName(onerel)),
+ 				 errhint("Consider%sincreasing the configuration parameter \"max_fsm_pages\".",
+ 						(REL_FRAGMENTATION_FACTOR * vacrelstats->tot_free_pages > vacrelstats->rel_pages 
+ 							? " using VACUUM FULL on this relation or ": " "))));
+ 
  	/* Update statistics in pg_class */
  	vac_update_relstats(RelationGetRelid(onerel),
  						vacrelstats->rel_pages,
***************
*** 507,519 ****
  					   vacrelstats->tot_free_pages,
  					   empty_pages,
  					   pg_rusage_show(&ru0))));
- 
- 	if (vacrelstats->tot_free_pages > MaxFSMPages)
- 		ereport(WARNING,
- 				(errmsg("relation \"%s.%s\" contains more than \"max_fsm_pages\" pages with useful free space",
- 						get_namespace_name(RelationGetNamespace(onerel)),
- 						relname),
- 				 errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));
  }
  
  
--- 518,523 ----

#10

Bruce Momjian

bruce@momjian.us

almost 19 years ago

In reply to: Simon Riggs (#9)

Re: [previously on HACKERS] "Compacting" a relation

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Simon Riggs wrote:

On Mon, 2007-02-05 at 11:55 +0000, Simon Riggs wrote:

On Sat, 2007-02-03 at 22:11 -0500, Bruce Momjian wrote:

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT,
there is no indication anywhere how "compacting" is supposed to be achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be
processed effectively by a user.

So change it ...

New message is:

errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));

The change of wording may be appropriate, but it is triggered when

if (vacrelstats->tot_free_pages > MaxFSMPages)

So if you VACUUM a 15+GB table and it has only 1% freespace then it will
still generate this message. Hopefully you'd agree that the message
would be inappropriate in that case.

It's also inappropriate because this message is generated *prior* to
doing lazy_truncate_heap(), which could easily remove lots of empty
pages anyhow. That might reduce it to less than MaxFSMPages anyhow, so
it can currently be triggered in wholly inappropriate situations.

So I suggest that we move this wording after lazy_truncate_heap() in
lazy_vacuum_rel() *and* we alter the hint so that it only suggests
VACUUM FULL if the table has 20% fragmentation, whatever its size.

Happy to drop a patch for this, if people agree.

Enclose 2 versions:
v1 - move test and WARNING
v2 - move test and WARNING, plus adjust hint according to relation size

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

[ Attachment, skipping... ]

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

#11

Bruce Momjian

bruce@momjian.us

almost 19 years ago

In reply to: Simon Riggs (#9)

1 attachment(s)

Re: [previously on HACKERS] "Compacting" a relation

I applied the optional VACUUM FULL version, but modified to code to say
20% rather than a factor of 5, attached.

---------------------------------------------------------------------------

Simon Riggs wrote:

On Mon, 2007-02-05 at 11:55 +0000, Simon Riggs wrote:

On Sat, 2007-02-03 at 22:11 -0500, Bruce Momjian wrote:

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT,
there is no indication anywhere how "compacting" is supposed to be achieved.
I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be
processed effectively by a user.

So change it ...

New message is:

errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));

The change of wording may be appropriate, but it is triggered when

if (vacrelstats->tot_free_pages > MaxFSMPages)

So if you VACUUM a 15+GB table and it has only 1% freespace then it will
still generate this message. Hopefully you'd agree that the message
would be inappropriate in that case.

It's also inappropriate because this message is generated *prior* to
doing lazy_truncate_heap(), which could easily remove lots of empty
pages anyhow. That might reduce it to less than MaxFSMPages anyhow, so
it can currently be triggered in wholly inappropriate situations.

So I suggest that we move this wording after lazy_truncate_heap() in
lazy_vacuum_rel() *and* we alter the hint so that it only suggests
VACUUM FULL if the table has 20% fragmentation, whatever its size.

Happy to drop a patch for this, if people agree.

Enclose 2 versions:
v1 - move test and WARNING
v2 - move test and WARNING, plus adjust hint according to relation size

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

[ Attachment, skipping... ]

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Attachments:

/rtmp/difftext/x-diffDownload

Index: src/backend/commands/vacuumlazy.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.83
diff -c -c -r1.83 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c	4 Feb 2007 03:10:55 -0000	1.83
--- src/backend/commands/vacuumlazy.c	21 Feb 2007 22:13:59 -0000
***************
*** 180,185 ****
--- 180,195 ----
  	/* Update shared free space map with final free space info */
  	lazy_update_fsm(onerel, vacrelstats);
  
+ 	if (vacrelstats->tot_free_pages > MaxFSMPages)
+ 		ereport(WARNING,
+ 				(errmsg("relation \"%s.%s\" contains more than \"max_fsm_pages\" pages with useful free space",
+ 						get_namespace_name(RelationGetNamespace(onerel)),
+ 						RelationGetRelationName(onerel)),
+ 				 errhint("Consider%sincreasing the configuration parameter \"max_fsm_pages\".",
+ 						/* Only suggest VACUUM FULL if 20% free */
+ 						(vacrelstats->tot_free_pages > vacrelstats->rel_pages * 0.20
+ 							? " using VACUUM FULL on this relation or ": " "))));
+ 
  	/* Update statistics in pg_class */
  	vac_update_relstats(RelationGetRelid(onerel),
  						vacrelstats->rel_pages,
***************
*** 507,519 ****
  					   vacrelstats->tot_free_pages,
  					   empty_pages,
  					   pg_rusage_show(&ru0))));
- 
- 	if (vacrelstats->tot_free_pages > MaxFSMPages)
- 		ereport(WARNING,
- 				(errmsg("relation \"%s.%s\" contains more than \"max_fsm_pages\" pages with useful free space",
- 						get_namespace_name(RelationGetNamespace(onerel)),
- 						relname),
- 				 errhint("Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\".")));
  }
  
  
--- 517,522 ----

#12

Alvaro Herrera

alvherre@commandprompt.com

almost 19 years ago

In reply to: Bruce Momjian (#11)

Re: [previously on HACKERS] "Compacting" a relation

Bruce Momjian wrote:

I applied the optional VACUUM FULL version, but modified to code to say
20% rather than a factor of 5, attached.

String construction does not work well with translations; please
reformulate this.

+ 				 errhint("Consider%sincreasing the configuration parameter \"max_fsm_pages\".",
+ 						/* Only suggest VACUUM FULL if 20% free */
+ 						(vacrelstats->tot_free_pages > vacrelstats->rel_pages * 0.20
+ 							? " using VACUUM FULL on this relation or ": " "))));

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

#13

Bruce Momjian

bruce@momjian.us

almost 19 years ago

In reply to: Alvaro Herrera (#12)

1 attachment(s)

Re: [previously on HACKERS] "Compacting" a relation

Alvaro Herrera wrote:

Bruce Momjian wrote:

I applied the optional VACUUM FULL version, but modified to code to say
20% rather than a factor of 5, attached.

String construction does not work well with translations; please
reformulate this.
+ 				 errhint("Consider%sincreasing the configuration parameter \"max_fsm_pages\".",
+ 						/* Only suggest VACUUM FULL if 20% free */
+ 						(vacrelstats->tot_free_pages > vacrelstats->rel_pages * 0.20
+ 							? " using VACUUM FULL on this relation or ": " "))));

OK, updated.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Attachments:

/rtmp/difftext/x-diffDownload

Index: src/backend/commands/vacuumlazy.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.84
diff -c -c -r1.84 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c	21 Feb 2007 22:15:21 -0000	1.84
--- src/backend/commands/vacuumlazy.c	21 Feb 2007 22:41:55 -0000
***************
*** 185,194 ****
  				(errmsg("relation \"%s.%s\" contains more than \"max_fsm_pages\" pages with useful free space",
  						get_namespace_name(RelationGetNamespace(onerel)),
  						RelationGetRelationName(onerel)),
! 				 errhint("Consider%sincreasing the configuration parameter \"max_fsm_pages\".",
! 						/* Only suggest VACUUM FULL if 20% free */
! 						(vacrelstats->tot_free_pages > vacrelstats->rel_pages * 0.20
! 							? " using VACUUM FULL on this relation or ": " "))));
  
  	/* Update statistics in pg_class */
  	vac_update_relstats(RelationGetRelid(onerel),
--- 185,194 ----
  				(errmsg("relation \"%s.%s\" contains more than \"max_fsm_pages\" pages with useful free space",
  						get_namespace_name(RelationGetNamespace(onerel)),
  						RelationGetRelationName(onerel)),
! 				 errhint((vacrelstats->tot_free_pages > vacrelstats->rel_pages * 0.20 ?
! 							/* Only suggest VACUUM FULL if 20% free */
! 							"Consider using VACUUM FULL on this relation or increasing the configuration parameter \"max_fsm_pages\"." :
! 							"Consider increasing the configuration parameter \"max_fsm_pages\"."))));
  
  	/* Update statistics in pg_class */
  	vac_update_relstats(RelationGetRelid(onerel),

#14

Alvaro Herrera

alvherre@commandprompt.com

almost 19 years ago

In reply to: Bruce Momjian (#13)

Re: [previously on HACKERS] "Compacting" a relation

Bruce Momjian wrote:

Alvaro Herrera wrote:
Bruce Momjian wrote:

I applied the optional VACUUM FULL version, but modified to code to say
20% rather than a factor of 5, attached.

String construction does not work well with translations; please
reformulate this.
+ 				 errhint("Consider%sincreasing the configuration parameter \"max_fsm_pages\".",
+ 						/* Only suggest VACUUM FULL if 20% free */
+ 						(vacrelstats->tot_free_pages > vacrelstats->rel_pages * 0.20
+ 							? " using VACUUM FULL on this relation or ": " "))));
OK, updated.

Thanks :-)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

#15

Simon Riggs

simon@2ndquadrant.com

almost 19 years ago

In reply to: Alvaro Herrera (#14)

Re: [previously on HACKERS] "Compacting" a relation

On Wed, 2007-02-21 at 21:28 -0300, Alvaro Herrera wrote:

Bruce Momjian wrote:
Alvaro Herrera wrote:
Bruce Momjian wrote:

I applied the optional VACUUM FULL version, but modified to code to say
20% rather than a factor of 5, attached.

String construction does not work well with translations; please
reformulate this.
+ 				 errhint("Consider%sincreasing the configuration parameter \"max_fsm_pages\".",
+ 						/* Only suggest VACUUM FULL if 20% free */
+ 						(vacrelstats->tot_free_pages > vacrelstats->rel_pages * 0.20
+ 							? " using VACUUM FULL on this relation or ": " "))));
OK, updated.
Thanks :-)

Alvaro: point noted for future. Bruce: many thanks.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com