Re: About Autovacuum Query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Ron Johnson,

 

Thanks so much for your update. As per my understanding

 

If table size increases, then autovacuum_vacuum_threshold can decide whether to trigger a VACUUM or not.

 

If I will set autovacuum_analyze_scale_factor==>0.1 means (table level 10% n_dead_tup activity happens). It will perform VACUUM automatically.

 

Same time set autovacuum_vacuum_scale_factor===>0.1 parameter value.

 

I will set the postgresql.conf file. Hole tables VACUUM will happen automatically. If I will set the above, if any performance issues will occur? 


On Wed, Apr 3, 2024 at 8:34 PM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote:
https://www.postgresql.org/docs/14/runtime-config-autovacuum.html

autovacuum_vacuum_scale_factor (floating point)

Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size). This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.


On Wed, Apr 3, 2024 at 10:33 AM jaya kumar <kumardba27@xxxxxxxxx> wrote:
Hi Ron Johnson,

Thanks for your update. Here, I have one doubt.

If we will down autovacuum_vacuum_threshold value from 50 to 30 or 20 means. Autovacuum will run automatically then delete n_dead_tup.

As you suggested to down autovacuum_vacuum_scale_factor down to something like 0.05.

Can you explain more based on your update. I will implement the task to our database.

On Wed, Apr 3, 2024 at 6:50 PM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote:
On Wed, Apr 3, 2024 at 5:55 AM jaya kumar <kumardba27@xxxxxxxxx> wrote:
Hi Team,

In my database Autovacuum is enabled and sets the below updated value in the Autovacuum related parameter. Here my question is after performing Autovacuum on a daily basis the below value is coming in n_dead_tup. If I will manually Autovacuum then it's fine.

Can someone help me to solve my request? The below current parameter setting updated
 
databaseName=# show autovacuum;
 autovacuum
------------
 on
(1 row)

databaseName=# show autovacuum_vacuum_threshold;
 autovacuum_vacuum_threshold
-----------------------------
 50
(1 row)

databaseName=# show autovacuum_vacuum_scale_factor;
 autovacuum_vacuum_scale_factor
--------------------------------
 0.2
(1 row)

databaseName=# show autovacuum_vacuum_cost_delay;
 autovacuum_vacuum_cost_delay
------------------------------
 2ms
(1 row)

databaseName=#



databaseName=# /
          tblnam          | n_dead_tup |  pfrag
--------------------------+------------+---------
   applicationusername.tablename1 |      52625 | 5262500
   applicationusername.tablename2 |      23538 | 2353800
   applicationusername.tablename3 |       3290 |  329000
   applicationusername.tablename4 |       3092 |  309200
   applicationusername.tablename5 |       2262 |  226200
   applicationusername.tablename6 |       2110 |  211000
   applicationusername.tablename7 |        678 |   67800
   applicationusername.tablename8 |        491 |   49100
   applicationusername.tablename9 |        431 |   43100
   applicationusername.tablenam10 |        351 |   35100
   applicationusername.tablenam11 |        188 |   18800
   applicationusername.tablenam12 |        150 |   15000

Drop  autovacuum_vacuum_scale_factor down to something like 0.05.

autovacuum_analyze_scale_factor, too.


--
Thanks & Regards,
Jayakumar.S
+91-9840864439.


--
Thanks & Regards,
Jayakumar.S
+91-9840864439.

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux