Re: autovacuum big table taking hours and sometimes seconds

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>, pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: autovacuum big table taking hours and sometimes seconds
Date: 2019-02-06 11:16:54
Message-ID: 95c2859e3b9b80597a5bf23788b567ac586d068c.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Wed, 2019-02-06 at 12:29 +0200, Mariel Cherkassky wrote:
> Hi,
> I have a table with a bytea column and its size is huge and thats why postgres created a toasted table for that column.
> The original table contains about 1K-10K rows but the toasted can contain up to 20M rows.
> I assigned the next two settings for the toasted table :
> alter table orig_table set (toast.autovacuum_vacuum_scale_factor = 0);
> alter table orig_table set (toast.autovacuum_vacuum_threshold =10000);
>
> Therefore I hoped that after deletion of 10K rows from the toasted table autovacuum will launch vacuum on the toasted table.
> From the logs I see that sometimes the autovacuum is running once in a few hours (3-4 hours) and sometimes it runs even every few minutes.
> Now I wanted to check if only depends on the thresholds and on the frequency of the deletes/updates on the table ?
> In some cases the autovacuum is taking a few hours (4+) it finishes and then immediatly is starting to run vacuum again on the table :
>
> Now the question is how to handle or tune it ? Is there any change that I need to increase the cost_limit / cost_delay ?

Maybe configuring autovacuum to run faster will help:

alter table orig_table set (toast.autovacuum_vacuum_cost_limit = 2000);

Or, more extreme:

alter table orig_table set (toast.autovacuum_vacuum_cost_delay = 0);

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message David Rowley 2019-02-06 13:05:42 Re: autovacuum big table taking hours and sometimes seconds
Previous Message Mariel Cherkassky 2019-02-06 10:29:06 autovacuum big table taking hours and sometimes seconds