On 11/12/2012 01:31 PM, Jeff Janes wrote:
On Mon, Nov 12, 2012 at 10:38 AM, Lists <lists@xxxxxxxxxxxxxxxxxx> wrote:
On 11/10/2012 02:21 PM, Jeff Janes wrote:
On Fri, Nov 9, 2012 at 4:28 PM, Lists <lists@xxxxxxxxxxxxxxxxxx> 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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
