On 09/22/2014 01:42 PM, Joel Avni wrote:
I noticed that tables on my master PostgreSQL server were growing, and
running vacuum full analyze on them actually made them even bigger.
First what version of Postgres are you using?
Second VACUUM FULL is usually not recommended for the reason you found
out and which is documented here:
http://www.postgresql.org/docs/9.3/interactive/sql-vacuum.html
FULL
Selects "full" vacuum, which can reclaim more space, but takes much
longer and exclusively locks the table. This method also requires extra
disk space, since it writes a new copy of the table and doesn't release
the old copy until the operation is complete. Usually this should only
be used when a significant amount of space needs to be reclaimed from
within the table.
"
At the same time, a slave PostgreSQL server had fallen behind in trying
to replicate, and was stuck in constantly looping over ‘started
streaming WAL from primary at…’ and ‘requested WAL segment …. has
already been removed’. Once I stopped running the slave instance, I was
able to manually vacuum the tables, and appears that auto vacuum is now
able to vacuum as well. One table (for instance) dropped from 10Gb down
to 330Mb after this operation. I don’t see anything about auto vacuum
not able to acquire locks while the slave wasn’t able to replicate. I
am unclear why a slave trying to continue streaming would block the auto
vacuum, or is something else at play?
My guess related to the locks your VACUUM FULL was taking, though it
would require more information on what all the various parts where doing
over the time frame.
I did check, and no base backups were in progress at the time this occurred.
Thank you,
Joel Avni
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
