pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum doesnt run on the pg_toast_id table
8+ messages / 3 participants
[nested] [flat]
* autovacuum doesnt run on the pg_toast_id table
@ 2019-01-17 17:28 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Mariel Cherkassky @ 2019-01-17 17:28 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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 ?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: autovacuum doesnt run on the pg_toast_id table
@ 2019-01-17 17:46 Justin Pryzby <pryzby@telsasoft.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2019-01-17 17:46 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, Jan 17, 2019 at 07:28:52PM +0200, Mariel Cherkassky wrote:
...
> 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.
...
> Any idea why the autovacuum doesnt vacuum both tables ?
It *does* vacuum both, just not *necessarily*, as you saw.
The toast is a separate table, so it's tracked separately.
Note that:
|If a table parameter value is set and the
|equivalent <literal>toast.</literal> parameter is not, the TOAST table
|will use the table's parameter value.
You could look in pg_stat_all_tables, to see how frequently the toast is being
autovacuumed relative to its table.
Justin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: autovacuum doesnt run on the pg_toast_id table
@ 2019-01-17 17:52 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2019-01-17 17:52 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On 2019-Jan-17, Mariel Cherkassky wrote:
> 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);
The right way to do this is
alter table main_table set (toast.autovacuum_vacuum_scale_factor = 0.05);
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: autovacuum doesnt run on the pg_toast_id table
@ 2019-01-17 17:58 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 8+ messages in thread
From: Mariel Cherkassky @ 2019-01-17 17:58 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org
I did it for the original table. But I see in the logs that the autovacuun
on the toasted table isn't synced with the autovacuun of the original
table. Therefore I thought that it worth to set it also for the toasted
table. Can you explain why in the logs I see more vacuums of the original
table then the toasted table ? Should they vacuumed together ?
On Jan 17, 2019 7:52 PM, "Alvaro Herrera" <alvherre@2ndquadrant.com> wrote:
On 2019-Jan-17, Mariel Cherkassky wrote:
> 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);
The right way to do this is
alter table main_table set (toast.autovacuum_vacuum_scale_factor = 0.05);
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: autovacuum doesnt run on the pg_toast_id table
@ 2019-01-17 19:09 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2019-01-17 19:09 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On 2019-Jan-17, Mariel Cherkassky wrote:
> I did it for the original table. But I see in the logs that the autovacuun
> on the toasted table isn't synced with the autovacuun of the original
> table. Therefore I thought that it worth to set it also for the toasted
> table. Can you explain why in the logs I see more vacuums of the original
> table then the toasted table ? Should they vacuumed together ?
No, they are processed separately, according to the formula explained in
the documentation.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: autovacuum doesnt run on the pg_toast_id table
@ 2019-01-17 19:59 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 8+ messages in thread
From: Mariel Cherkassky @ 2019-01-17 19:59 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org
But you said that the threshold that is chosen for the toasted table is
identical to the originals table threshold right ? Is that a normal
behavior that the original table has 1000recrods but the toasted has more
than 10m? How can I set a different threshold for the toasted table ? As it
seems right now the threshold for the original table is set to 0.05 and it
it to often for the original but for the toasted table it isn't enough
because it has more then 10 m records..
On Jan 17, 2019 9:09 PM, "Alvaro Herrera" <alvherre@2ndquadrant.com> wrote:
On 2019-Jan-17, Mariel Cherkassky wrote:
> I did it for the original table. But I see in the logs that the autovacuun
> on the toasted table isn't synced with the autovacuun of the original
> table. Therefore I thought that it worth to set it also for the toasted
> table. Can you explain why in the logs I see more vacuums of the original
> table then the toasted table ? Should they vacuumed together ?
No, they are processed separately, according to the formula explained in
the documentation.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: autovacuum doesnt run on the pg_toast_id table
@ 2019-01-17 20:16 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2019-01-17 20:16 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On 2019-Jan-17, Mariel Cherkassky wrote:
> But you said that the threshold that is chosen for the toasted table is
> identical to the originals table threshold right ?
You can configure them identical, or different. Up to you.
> Is that a normal behavior that the original table has 1000recrods but
> the toasted has more than 10m?
Sure -- each large record in the main table is split into many 2kb
records in the toast table.
> How can I set a different threshold for the toasted table ?
Just choose a different value in the command I showed.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: autovacuum doesnt run on the pg_toast_id table
@ 2019-01-17 20:18 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 8+ messages in thread
From: Mariel Cherkassky @ 2019-01-17 20:18 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org
Got it, I didn't see the toast word in the command. Thanks !
On Thu, Jan 17, 2019, 10:17 PM Alvaro Herrera <alvherre@2ndquadrant.com
wrote:
> On 2019-Jan-17, Mariel Cherkassky wrote:
>
> > But you said that the threshold that is chosen for the toasted table is
> > identical to the originals table threshold right ?
>
> You can configure them identical, or different. Up to you.
>
> > Is that a normal behavior that the original table has 1000recrods but
> > the toasted has more than 10m?
>
> Sure -- each large record in the main table is split into many 2kb
> records in the toast table.
>
> > How can I set a different threshold for the toasted table ?
>
> Just choose a different value in the command I showed.
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2019-01-17 20:18 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-17 17:28 autovacuum doesnt run on the pg_toast_id table Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-17 17:46 ` Justin Pryzby <pryzby@telsasoft.com>
2019-01-17 17:52 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-17 17:58 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-17 19:09 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-17 19:59 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-17 20:16 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-01-17 20:18 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox