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