attaching vacuum related configuration parameter values to the thread
On Wed, Feb 12, 2025 at 10:14 AM srinivasan s <srinioracledba7@xxxxxxxxx> wrote:
Hi Team,One of the tables in our database suddenly started growing very fast without any changes to the environment. it has grown over 10GB in the last week and this is causing performance issues. We ended up adding an index to solve the performance problem but the table growth didn't stop. It is growing continuously. we are using postgres version 12 on ubuntuWe are running a vacuum analsye on a full database every weekend and an auto vacuum is set up.My observation on the DB so far.1. The table is bloated around 50GB is bloat out of 95GB table sizecurrent_database | schemaname | tablename | tbloat | wastedbytes---------------------+------------+-----------------------------------------------------------------+--------+-------------xyxyxyxy_production | public | xxxxxx | 2.5 | 518947799042. the last autovacuumtable_name | last_autovacuum | last_autoanalyze | last_vacuum | last_analyze------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------xxxxxx | 2025-02-09 03:09:46.938845+00 | 2025-02-07 23:45:11.367977+00 | 2025-02-08 03:12:33.468125+00 | 2025-02-10 21:09:57.461369+00(1 row)3. Also noticed that there is a auto vacuum job running on the table with (to prevent wraparound)I am not sure if this auto vacuum (to prevent wraparound) is progressing, it is running for more than 15 hours and status is active. Will this block the regular auto vacuum?My assumption is the bloated rows on the table are not reused and the table is growing. I am trying to find out the cause and fix it.Looking for some help around this topic, any suggestions or ideas to troubleshoot and fix would be highly appreciated.Thanks,
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart autovacuum,on,,Autovacuum,Starts the autovacuum subprocess.,,sighup,bool,default,,,,on,on,,,f autovacuum_analyze_scale_factor,0.1,,Autovacuum,"Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.",,sighup,real,default,0,100,,0.1,0.1,,,f autovacuum_analyze_threshold,50,,Autovacuum,"Minimum number of tuple inserts, updates, or deletes prior to analyze.",,sighup,integer,default,0,2147483647,,50,50,,,f autovacuum_freeze_max_age,200000000,,Autovacuum,Age at which to autovacuum a table to prevent transaction ID wraparound.,,postmaster,integer,default,100000,2000000000,,200000000,200000000,,,f autovacuum_max_workers,5,,Autovacuum,Sets the maximum number of simultaneously running autovacuum worker processes.,,postmaster,integer,configuration file,1,262143,,3,5,/etc/postgresql/12/main/postgresql.conf,70,f autovacuum_multixact_freeze_max_age,400000000,,Autovacuum,Multixact age at which to autovacuum a table to prevent multixact wraparound.,,postmaster,integer,default,10000,2000000000,,400000000,400000000,,,f autovacuum_naptime,60,s,Autovacuum,Time to sleep between autovacuum runs.,,sighup,integer,default,1,2147483,,60,60,,,f autovacuum_vacuum_cost_delay,10,ms,Autovacuum,"Vacuum cost delay in milliseconds, for autovacuum.",,sighup,real,configuration file,-1,100,,2,10,/etc/postgresql/12/main/postgresql.conf,67,f autovacuum_vacuum_cost_limit,200,,Autovacuum,"Vacuum cost amount available before napping, for autovacuum.",,sighup,integer,configuration file,-1,10000,,-1,200,/etc/postgresql/12/main/postgresql.conf,69,f autovacuum_vacuum_scale_factor,0.15,,Autovacuum,Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.,,sighup,real,configuration file,0,100,,0.2,0.15,/etc/postgresql/12/main/postgresql.conf,71,f autovacuum_vacuum_threshold,50,,Autovacuum,Minimum number of tuple updates or deletes prior to vacuum.,,sighup,integer,default,0,2147483647,,50,50,,,f
