From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | autovacuum doesnt run on the pg_toast_id table |
Date: | 2019-01-17 17:28:52 |
Message-ID: | CA+t6e1nd6odb2pzSASzzsUx6pTmTQ7EExjFuyRkmVh8cdm8QFw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hey,
I have a table with 3 columns and one of those columns is bytea type
A(int,int,bytea).
Every row that I insert is pretty big and thats why postgresql decided to
save that column in a toasted table(pg_toasted_Aid). I had a lot of bloat
issues with that table so I set the vacuum_threshold of the original
table(A) into 0.05. Usually the A table has about 1000+ rows but the
toasted table has more then 25M . Now, I realized from the autovacuum
logging, that when autovacuum runs on the original table (A) it doesn't
necessary run on the toasted table and this is very weird.
I tried to set the same threshold for the toasted table but got an error
that it is a catalog table and therefore permission is denied.
2019-01-17 12:04:15 EST db116109 ERROR: permission denied:
"pg_toast_13388392" is a system catalog
2019-01-17 12:04:15 EST db116109 STATEMENT: alter table
pg_toast.pg_toast_13388392 set (autovacuum_vacuum_scale_factor=0.05);
An example for the autovacuum run :
2019-01-17 00:00:51 EST 15652 LOG: automatic vacuum of table
"db1.public.A": index scans: 1
pages: 0 removed, 117 remain
tuples: 142 removed, 1466 remain
buffer usage: 162 hits, 34 misses, 29 dirtied
avg read rate: 1.356 MiB/s, avg write rate: 1.157 MiB/s
--
2019-01-17 00:07:51 EST 25666 LOG: automatic vacuum of table
"db1.public.A": index scans: 1
pages: 0 removed, 117 remain
tuples: 144 removed, 1604 remain
buffer usage: 157 hits, 41 misses, 27 dirtied
avg read rate: 1.651 MiB/s, avg write rate: 1.087 MiB/s
--
*2019-01-17 00:12:39 EST 3902 LOG: automatic vacuum of table
"db1.pg_toast.pg_toast_13388392": index scans: 17*
* pages: 459 removed, 25973888 remain*
* tuples: 45130560 removed, 54081616 remain*
* buffer usage: 30060044 hits, 43418591 misses, 37034834 dirtied*
* avg read rate: 2.809 MiB/s, avg write rate: 2.396 MiB/s*
--
2019-01-17 00:13:51 EST 2684 LOG: automatic vacuum of table
"db1.public.A": index scans: 1
pages: 0 removed, 117 remain
tuples: 122 removed, 1470 remain
buffer usage: 152 hits, 41 misses, 30 dirtied
avg read rate: 2.981 MiB/s, avg write rate: 2.181 MiB/s
--
2019-01-17 00:19:51 EST 10935 LOG: automatic vacuum of table
"db1.public.A": index scans: 1
pages: 0 removed, 117 remain
tuples: 120 removed, 1471 remain
buffer usage: 145 hits, 41 misses, 28 dirtied
avg read rate: 3.637 MiB/s, avg write rate: 2.484 MiB/s
--
2019-01-17 00:42:51 EST 24385 LOG: automatic vacuum of table
"db1.public.A": index scans: 1
pages: 0 removed, 117 remain
tuples: 130 removed, 1402 remain
buffer usage: 175 hits, 76 misses, 34 dirtied
Any idea why the autovacuum doesnt vacuum both tables ?
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Pryzby | 2019-01-17 17:46:51 | Re: autovacuum doesnt run on the pg_toast_id table |
Previous Message | Laurenz Albe | 2019-01-17 09:49:37 | Re: No matching tables have ever been vacuumed |