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