Обсуждение: Tables with lots of dead tuples despite autovacuum
We have a few tables that are updated nightly on the order of deleting and inserting 500k records.
I assumed autovacuum would do its thing and clean up the dead tuples, but in looking at pg_stat_user_tables, I notice there are lots and lots of dead tuples, on the order of a 500-600k.
What can I do about this? Why isn't autovacuum cleaning these tables? Is this number of dead tuples acceptable?
Lastly, would it make sense to do a weekly full manual vacuum + analyze?
On 09/12/12 5:00 PM, Wells Oliver wrote: > We have a few tables that are updated nightly on the order > of deleting and inserting 500k records. > > I assumed autovacuum would do its thing and clean up the dead tuples, > but in looking at pg_stat_user_tables, I notice there are lots and > lots of dead tuples, on the order of a 500-600k. > > What can I do about this? Why isn't autovacuum cleaning these tables? > Is this number of dead tuples acceptable? > > Lastly, would it make sense to do a weekly full manual vacuum + analyze? > how old is the oldest transaction in pg_stat_activity ? vacuum can't free any tuples newer than this. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Wed, Sep 12, 2012 at 5:00 PM, Wells Oliver <wellsoliver@gmail.com> wrote: > We have a few tables that are updated nightly on the order of deleting and > inserting 500k records. Out of how many in total? > I assumed autovacuum would do its thing and clean up the dead tuples, but in > looking at pg_stat_user_tables, I notice there are lots and lots of dead > tuples, on the order of a 500-600k. That is one day's worth. If the tables undergo churn once a day, and get vacuum once a day, that seems like a nice steady state. I wouldn't worry about it. Cheers, Jeff