Search Postgresql Archives

Re: Question about Vacuum and Replication failures in 9.3.5

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



It 9.3.5 and I did the manual vacuum to try to see where the problem might
be.

On 9/22/14, 4:04 PM, "Adrian Klaver" <adrian.klaver@xxxxxxxxxxx> wrote:

>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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux