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
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 |