autovacuum doesnt run on the pg_toast_id table
| От | Mariel Cherkassky |
|---|---|
| Тема | autovacuum doesnt run on the pg_toast_id table |
| Дата | |
| Msg-id | CA+t6e1nd6odb2pzSASzzsUx6pTmTQ7EExjFuyRkmVh8cdm8QFw@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: autovacuum doesnt run on the pg_toast_id table
(Justin Pryzby <pryzby@telsasoft.com>)
Re: autovacuum doesnt run on the pg_toast_id table (Alvaro Herrera <alvherre@2ndquadrant.com>) |
| Список | 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 ?
В списке pgsql-performance по дате отправления: