Обсуждение: [MASSMAIL]About Autovacuum Query

On Wed, Apr 3, 2024 at 5:55 AM jaya kumar <kumardba27@gmail.com> 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.

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@gmail.com> wrote:

On Wed, Apr 3, 2024 at 5:55 AM jaya kumar <kumardba27@gmail.com> 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.

Lot of autovacuum parameters are dependent to each other, there are hierarchy of parameters.

On Wed, 3 Apr 2024, 20:03 jaya kumar, <kumardba27@gmail.com> 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@gmail.com> wrote:

On Wed, Apr 3, 2024 at 5:55 AM jaya kumar <kumardba27@gmail.com> 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.

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@gmail.com> 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@gmail.com> wrote:

On Wed, Apr 3, 2024 at 5:55 AM jaya kumar <kumardba27@gmail.com> 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.