Re: [HACKERS] Compacting a relation
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. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Compacting a relation
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. 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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Compacting a relation
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\.))); -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
