Re: Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum)

On 11/12/2012 01:31 PM, Jeff Janes wrote:
> On Mon, Nov 12, 2012 at 10:38 AM, Lists <lists@benjamindsmith.com> wrote:
>> On 11/10/2012 02:21 PM, Jeff Janes wrote:
>>> On Fri, Nov 9, 2012 at 4:28 PM, Lists <lists@benjamindsmith.com> wrote:
>>>
>>>> 2) It was sheer chance that I discovered the need to reindex prior to
>>>> vacuum
>>>> in order to get the disk space back.
>>> As of 9.0, a "vacuum full" inherently does a reindex, so doing an
>>> explicit one is neither necessary nor beneficial.
>>>
>>> I don't know if your discovery is based on a non-full vacuum, or on an
>>> older server.
>>
>> I can only state that merely doing a "vacuum full" or "vacuum full $tables"
>> sequentially did not free the space, whereas the sequential  reindex $table,
>> each followed immediately by a vacuum full $table) did.
> With what version?
[root@alpha ~]# rpm -qi postgresql91-server
Name        : postgresql91-server          Relocations: (not relocatable)
Version     : 9.1.5                             Vendor: (none)
Release     : 3PGDG.rhel6                   Build Date: Sun 02 Sep 2012
12:13:18 PM UTC
Install Date: Wed 12 Sep 2012 03:04:41 AM UTC      Build Host:
koji-sl6-x86-64-pg91
Group       : Applications/Databases        Source RPM:
postgresql91-9.1.5-3PGDG.rhel6.src.rpm
Size        : 15191132                         License: PostgreSQL
Signature   : DSA/SHA1, Sun 02 Sep 2012 12:13:24 PM UTC, Key ID
1f16d2e1442df0f8
URL         : http://www.postgresql.org/

>
>
>> If you'd like I can
>> easily recreate the scenario by simply not "cleaning up" one of the DB
>> servers until it bloats up and make available (limit distribution) a binary
>> copy of the database (EG: rsync the /var/lib/pgsql/ filesystem late at
>> night) in order to help identify why it didn't work as expected.
> Do you think can make an easily script-able way to re-create the
> resistant bloat?  That would be better than trying to disseminate
> binary files, I think.
It would only be better if it actually created the situation that caused
the space to not be freed. But, until you know the actual cause of a
problem, I've found that it's often not productive to create simulations
that may or may not be actually related to the problem.

>
> What I did was just create and drop temp tables in a tight loop, with
> autovacuum off, and then once pg_attribute got good and bloated, did a
> vacuum full as the database owner or superuser.
Based on my understanding, if your loop included an intermittent schema
change from within a transaction it might better approximate my actual
scenario. Merely creating temp tables and then dropping them would
create lots of activity "at the end" of the table which would free
correctly. This still does not explain why reindex $table works when
reindex is supposedly implicit in the vacuum.

>>> If all of your long-lived objects were created before pg_attribute got
>>> bloated and so the bloat was due only to short-lived objects, then
>>> non-full vacuum (if run often enough) should eventually be able to
>>> return that space as the short-lived objects near the end start to go
>>> away.  However, if even a single long-live object finds itself at the
>>> end of the table, then only a vacuum full will ever be able to reclaim
>>> that space.
>>>
>> Since the time period involved (weeks/months) would have included both a
>> large number of created/destroyed temp tables and occasionally altered
>> persistent objects it would appear that the full option a very good idea, at
>> least periodically.
> If you can prevent the extreme bloat from occurring in the first
> place, then the "end" of the table would not be so far away from its
> desired size that it needs to get reset by a vacuum full.
>
> If you find your self in need of a vacuum full, then you should do
> one.  But you should ask yourself what went wrong that you got into
> that situation in the first place.
>
I agree; this is why my questions on enabling autovacuum in a related
thread.