pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum just stop vacuuming specific table for 7 hours
3+ messages / 2 participants
[nested] [flat]
* autovacuum just stop vacuuming specific table for 7 hours
@ 2019-03-06 16:47 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mariel Cherkassky @ 2019-03-06 16:47 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have the next relation in my db : A(id int, info bytea,date timestamp).
Every cell in the info column is very big and because of that there is a
toasted table with the data of the info column (pg_toast.pg_toast_123456).
The relation contains the login info for every user that logs into the
session and cleaned whenever the user disconnected. In case the use doesnt
logoff, during the night we clean login info that is older then 3 days.
The toasted table grew very fast (more then 50M+ rows per week) and thats
why I set the next autovacuum settings for the table :
Options:
toast.autovacuum_vacuum_scale_factor=0
toast.autovacuum_vacuum_threshold=10000
toast.autovacuum_vacuum_cost_limit=10000
toast.autovacuum_vacuum_cost_delay=5
Those settings helped but the table still grey very much. I wrote a script
that monitored some metadata about the table (pg_stat_all_tables,count(*)
from orig and toasted table). I let the system monitor the table for a week
and I found out the next info :
Autovacuum was running great during the whole week and whenever it reached
10k records in the toasted table it started vacuuming the table. *However,
The db grew dramatically during a period of 7 hours in a specific day. In
those 7 hours the table contained more then 10k (and kept increasing) but
the autovacuum didnt vacuum the table*. I saw that during those 7 hours
autovacuum didnt run and as a result of that the table grew to its max
size(the current size).
an example of an autovacuum run on the toasted table :
automatic vacuum of table "db.pg_toast.pg_toast_123456": index scans: 1
pages: 0 removed, 1607656 remain
tuples: 6396770 removed, 33778 remain
buffer usage: 1743021 hits, 3281298 misses, 3217528 dirtied
avg read rate: 2.555 MiB/s, avg write rate: 2.505 MiB/s
system usage: CPU 98.44s/54.02u sec elapsed 10034.34 sec
the vacuum hits/misses/dirtied are set to default (1,10,20)
autovacuum workers - 16
maintenance_work_mem - 200MB
130GB RAM , 23 CPU
Can anyone explain why suddenly the autovacuum should stop working for that
long period ?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: autovacuum just stop vacuuming specific table for 7 hours
@ 2019-03-06 17:05 Justin Pryzby <pryzby@telsasoft.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Justin Pryzby @ 2019-03-06 17:05 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance
On Wed, Mar 06, 2019 at 06:47:21PM +0200, Mariel Cherkassky wrote:
> Those settings helped but the table still grey very much. I wrote a script
> that monitored some metadata about the table (pg_stat_all_tables,count(*)
> from orig and toasted table). I let the system monitor the table for a week
> and I found out the next info :
> Autovacuum was running great during the whole week and whenever it reached
> 10k records in the toasted table it started vacuuming the table. *However,
> The db grew dramatically during a period of 7 hours in a specific day. In
> those 7 hours the table contained more then 10k (and kept increasing) but
> the autovacuum didnt vacuum the table*. I saw that during those 7 hours
> autovacuum didnt run and as a result of that the table grew to its max
> size(the current size).
Does pg_stat_all_tables show that the table ought to have been vacuumed ?
SELECT * FROM pg_stat_sys_tables WHERE relid='pg_toast.pg_toast_123456'::regclass;
Compare with relpages, reltuple FROM pg_class
What postgres version ?
Justin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: autovacuum just stop vacuuming specific table for 7 hours
@ 2019-03-06 17:16 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mariel Cherkassky @ 2019-03-06 17:16 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance
The PostgreSQL version is 9.6.
I dont have access to the machine right now so I will check tomorrow.
Basically those values should be the same because they are updated by the
autovacuum process right ?
Any idea what else to check ? During the week last_autovacuum (in
pg_stat_all_tables) were updated every hour. Only during those problematic
7 hours it wasnt updated.
בתאריך יום ד׳, 6 במרץ 2019 ב-19:05 מאת Justin Pryzby <
pryzby@telsasoft.com>:
> On Wed, Mar 06, 2019 at 06:47:21PM +0200, Mariel Cherkassky wrote:
> > Those settings helped but the table still grey very much. I wrote a
> script
> > that monitored some metadata about the table (pg_stat_all_tables,count(*)
> > from orig and toasted table). I let the system monitor the table for a
> week
> > and I found out the next info :
>
> > Autovacuum was running great during the whole week and whenever it
> reached
> > 10k records in the toasted table it started vacuuming the table.
> *However,
> > The db grew dramatically during a period of 7 hours in a specific day. In
> > those 7 hours the table contained more then 10k (and kept increasing) but
> > the autovacuum didnt vacuum the table*. I saw that during those 7 hours
> > autovacuum didnt run and as a result of that the table grew to its max
> > size(the current size).
>
> Does pg_stat_all_tables show that the table ought to have been vacuumed ?
>
> SELECT * FROM pg_stat_sys_tables WHERE
> relid='pg_toast.pg_toast_123456'::regclass;
>
> Compare with relpages, reltuple FROM pg_class
>
> What postgres version ?
>
> Justin
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-03-06 17:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-06 16:47 autovacuum just stop vacuuming specific table for 7 hours Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-03-06 17:05 ` Justin Pryzby <pryzby@telsasoft.com>
2019-03-06 17:16 ` 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