Re: issue log message to suggest VACUUM FULL if a table is nearly empty

On Mon, Mar 31, 2014 at 12:35 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Wed, Mar 26, 2014 at 11:32 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Sun, Mar 9, 2014 at 5:28 PM, Wang, Jing <jingw(at)fast(dot)au(dot)fujitsu(dot)com> wrote:
>>> Enclosed is the patch to implement the requirement that issue log message to
>>> suggest VACUUM FULL if a table is nearly empty.
>>>
>>> The requirement comes from the Postgresql TODO list.
>>>
>>> If the relpage of the table > RELPAGES_VALUES_THRESHOLD(default 1000) then
>>> the table is considered to be large enough.
>>>
>>> If the free_space/total_space > FREESPACE_PERCENTAGE_THRESHOLD(default 0.5)
>>> then the table is considered to have large numbers of unused rows.
>>
>> I'm not sure that we want people to automatically VF a table just
>> because it's 2x bloated. Doesn't it depend on the table size? And in
>> sort of a funny way, too, like, if the tables is small, 2x bloat is
>> not wasting much disk space, but getting rid of it is probably easy,
>> so maybe you should - but if the table is a terabyte, even 50% bloat
>> might be pretty intolerable, but whether it makes sense to try to get
>> rid of it depends on your access pattern. I'm not really too sure
>> whether it makes sense to try to make an automated recommendation
>> here, or maybe only in egregious cases.
>
> I think here main difficulty is to decide when it will be considered good
> to display such a message. As you said, that it depends on access pattern
> whether 50% bloat is tolerable or not, so one way could be to increase the
> bloat limit and table size threshold to higher value (bloat - 80%,
> table_size = 500M) where it would make sense to recommend VF for all cases
> or another way could be to consider using some auto vacuum threshold parameter
> like autovacuum_vacuum_scale_factor to calculate threshold value for issuing
> this message. I think parameter like scale factor can make sense as to an extent
> this parameter is an indicative of how much dead space percentage is tolerable
> for user.

I don't think there's a very direct relationship between those things.
One of the problems we repeatedly encounter is that the scale factor
only governs when the table becomes eligible to be vacuumed; once that
happens, it takes some amount of time - ideally <1 minute but more if
all workers are busy or if autovacuum_naptime has unfortunately been
increased - for the vacuum to start, and then more time after that for
the vacuum to finish. I think the latter is really the kicker. Even
if your system is relatively well-tuned, a big table takes a long time
to vacuum, and you're going to continue accumulating bloat while the
vacuum is running.

Another aspect of my ambivalence about this is that VACUUM FULL tends
to get overused as it is. If we start making automated
recommendations in that direction, it might cause people to lean that
way even further, which would not, on the whole, be a good thing. On
the other hand, if the table is 80% dead space, it's a pretty good bet
that a VACUUM FULL is needed. Even there, though, the VACUUM FULL may
be a pretty temporary fix unless the user also fixes the underlying
issue that caused the table bloat to accumulate in the first place.
Sometimes bloat is caused by a one-off issue, like one long-running
query. But sometimes it's caused by something systematic, like
setting the cost limit too low or the nap time too high. Just telling
the user to run VACUUM FULL is likely to make the user conclude that
"PostgreSQL sucks, I have to keep running VACUUM FULL all the time,
taking a full-table lock". Of course, really giving the user a useful
level of information here is probably impractical in a log message
anyway, but that doesn't mean giving them too little information to do
something useful is better.

Yet another thing that bothers me about this is that the table might
already be getting vacuumed very frequently. If you start getting
this message from autovac once per minute, you're going to think
that's pretty stupid - especially after you try VACUUM FULL and the
problem comes right back because of constant update pressure.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company