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 <[EMAIL PROTECTED]> 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

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 ----
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 ----
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to