autovacuum big table taking hours and sometimes seconds

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: autovacuum big table taking hours and sometimes seconds
Date: 2019-02-06 10:29:06
Message-ID: CA+t6e1n9CDKmzAU=ksOV56+5NUQwmUTh5oKsdexkbCx5ioceBw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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 :

2019-01-29 *07:10:58* EST 14083 LOG: automatic vacuum of table
"db.pg_toast.pg_toast_14430": index scans: 3

pages: 1672 removed, 7085056 remain

tuples: 6706885 removed, 2023847 remain

buffer usage: 4808221 hits, 6404148 misses, 6152603 dirtied

avg read rate: 2.617 MiB/s, avg write rate: 2.514 MiB/s

system usage: CPU 148.65s/70.06u sec elapsed 19119.55 sec

This run took 19119 sec ~ 5 hours

2019-01-29 *10:05:45* EST 11985 LOG: automatic vacuum of table
"db.pg_toast.pg_toast_14430": index scans: 2

pages: 2752 removed, 7082304 remain

tuples: 3621620 removed, 1339540 remain

buffer usage: 2655076 hits, 3506964 misses, 3333423 dirtied

avg read rate: 2.638 MiB/s, avg write rate: 2.508 MiB/s

system usage: CPU 71.22s/37.65u sec elapsed 10384.93 sec

this run took 10384 sec ~ 2.88 hours.

the diff between the summaries is 3 hours and the second run took 2.88
hours which means that the autovacuum launched vacuum on the table a few
minutes after the first vacuum has finished.

In addition, as I said sometimes if runs very often :

2019-02-04 09:26:23 EST 14735 LOG: automatic vacuum of table
"db.pg_toast.pg_toast_14430": index scans: 1

pages: 1760 removed, 11149568 remain

tuples: 47870 removed, 4929452 remain

buffer usage: 200575 hits, 197394 misses, 24264 dirtied

avg read rate: 5.798 MiB/s, avg write rate: 0.713 MiB/s

system usage: CPU 1.55s/1.38u sec elapsed 265.96 sec

2019-02-04 09:32:57 EST 26171 LOG: automatic vacuum of table
"db.pg_toast.pg_toast_14430": index scans: 1

pages: 2144 removed, 11147424 remain

tuples: 55484 removed, 4921526 remain

buffer usage: 196811 hits, 209267 misses, 34471 dirtied

avg read rate: 5.459 MiB/s, avg write rate: 0.899 MiB/s

system usage: CPU 1.73s/1.54u sec elapsed 299.50 sec

Now the question is how to handle or tune it ? Is there any change that I
need to increase the cost_limit / cost_delay ?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Laurenz Albe 2019-02-06 11:16:54 Re: autovacuum big table taking hours and sometimes seconds
Previous Message Laurenz Albe 2019-02-06 08:38:42 Re: How can sort performance be so different