Thread: autovacuum just stop vacuuming specific table for 7 hours
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 ?
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
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