Обсуждение: [MASSMAIL]Autovacuum------Doubts

Поиск
Список
Период
Сортировка

[MASSMAIL]Autovacuum------Doubts

От
jaya kumar
Дата:

Hi Team,

The below updated parameter value has been set currently in postgresql.conf file.

autovacuum_analyze_threshold and autovacuum_vacuum_threshold both parameter values set from 50 to 20.

Created one table with 100 records. First, we deleted 21 records. after an autovacuum does not happen automatically. After again I deleted another 20. Out of 100 we delectated 40 records then the autovacuum happened automatically. As per the configuration Autocacuum will automatically delete 21 records in the table but my case does not happen.

Can someone check and explain this.




DatabaseName=# show autovacuum;
 autovacuum
------------
 on
(1 row)

DatabaseName=# show autovacuum_max_workers;
 autovacuum_max_workers
------------------------
 3
(1 row)

DatabaseName=# show autovacuum_naptime;
 autovacuum_naptime
--------------------
 1min
(1 row)

DatabaseName=# show autovacuum_vacuum_threshold;
 autovacuum_vacuum_threshold
-----------------------------
 20
(1 row)

DatabaseName=# show autovacuum_vacuum_insert_threshold;
 autovacuum_vacuum_insert_threshold
------------------------------------
 1000
(1 row)

DatabaseName=# show autovacuum_analyze_threshold;
 autovacuum_analyze_threshold
------------------------------
 20
(1 row)

DatabaseName=# show autovacuum_vacuum_scale_factor;
 autovacuum_vacuum_scale_factor
--------------------------------
 0.1
(1 row)

DatabaseName=# show autovacuum_vacuum_insert_scale_factor;
 autovacuum_vacuum_insert_scale_factor
---------------------------------------
 0.2
(1 row)

DatabaseName=# show autovacuum_analyze_scale_factor;
 autovacuum_analyze_scale_factor
---------------------------------
 0.1
(1 row)

DatabaseName=# show autovacuum_freeze_max_age;
 autovacuum_freeze_max_age
---------------------------
 200000000
(1 row)

DatabaseName=#


DatabaseName=# select count(*) from emp10;
 count
-------
   100
(1 row)
DatabaseName=# select n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup
DatabaseName-# from pg_stat_all_tables where schemaname='public' and relname='emp10';
 n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+------------+------------
       100 |         0 |        20 |             0 |         80 |         20
(1 row)

DatabaseName=# delete from emp10 where eno between 25 and 40;
DELETE 15
DatabaseName=# select n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup
DatabaseName-# from pg_stat_all_tables where schemaname='public' and relname='emp10';
 n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+------------+------------
       100 |         0 |        40 |             0 |         60 |         40
(1 row)

DatabaseName-# from pg_stat_all_tables where schemaname='public' and relname='emp10';
 n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+------------+------------
       100 |         0 |        40 |             0 |         60 |          0
(1 row)

DatabaseName=# SELECT schemaname||'.'||relname AS table_name
DatabaseName-#        ,last_vacuum
DatabaseName-#        ,last_autovacuum
DatabaseName-#        ,last_analyze
DatabaseName-#        , last_autoanalyze
DatabaseName-# FROM pg_stat_user_tables
DatabaseName-# where relname in ('emp10');
  table_name  | last_vacuum |        last_autovacuum        | last_analyze |       last_autoanalyze
--------------+-------------+-------------------------------+--------------+------------------------------
 public.emp10 |             | 2024-04-08 09:03:25.072008+00 |              | 2024-04-08 09:03:25.07849+00
(1 row)

DatabaseName=#


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

Re: Autovacuum------Doubts

От
Laurenz Albe
Дата:
On Mon, 2024-04-08 at 14:57 +0530, jaya kumar wrote:
> autovacuum_analyze_threshold and autovacuum_vacuum_threshold both parameter
> values set from 50 to 20.
>
> Created one table with 100 records. First, we deleted 21 records. after an
> autovacuum does not happen automatically. After again I deleted another 20.
> Out of 100 we delectated 40 records then the autovacuum happened automatically.
> As per the configuration Autocacuum will automatically delete 21 records
> in the table but my case does not happen.
>
> Can someone check and explain this.

The threshold is

  autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows

So since you have set autovacuum_vacuum_scale_factor to 0.1 and there are 100 rows,
you need to delete 20 + 100 * 0.1 = 30 rows before autovacuum kicks in.

Yours,
Laurenz Albe

Re: Autovacuum------Doubts

От
jaya kumar
Дата:

Hi Laurenz,

 

I am not clear. Could you explain again. If I delete 20 records out of 100. It means Autocacumm should run but not run automatically. As you provide a solution, the method should be deleted but does not happen.

 

Total my table count=100

 

Delete record=30

 

 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows ?

  o/p: 20+0.1*100=2010?

 

 

DatabaseName=# show autovacuum_vacuum_threshold;

 autovacuum_vacuum_threshold

-----------------------------

 20

(1 row)

 

DatabaseName=# show autovacuum_vacuum_scale_factor;

 autovacuum_vacuum_scale_factor

--------------------------------

 0.1

(1 row)

 

DatabaseName=# select count(*) from emp9;

 count

-------

   100

(1 row)

 

 

DatabaseName=# delete from emp9 where eno between 1 and 30;

 

DatabaseName=#  SELECT schemaname || '.' || relname as tblnam, n_dead_tup, (n_dead_tup::float) * 100 as pfrag from pg_stat_user_tables where n_dead_tup > 0 and n_live_tup > 0 order by pfrag desc;

   tblnam    | n_dead_tup | pfrag

-------------+------------+-------

 public.emp9 |         30 |  3000

(1 row)

 

 

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows

 

20+

 

DatabaseName=# SELECT schemaname||'.'||relname AS table_name

DatabaseName-#        ,last_vacuum

DatabaseName-#        ,last_autovacuum

DatabaseName-#        ,last_analyze

DatabaseName-#        , last_autoanalyze

DatabaseName-# FROM pg_stat_user_tables

DatabaseName-# where relname in ('emp9');

 table_name  | last_vacuum | last_autovacuum | last_analyze |       last_autoanalyze

-------------+-------------+-----------------+--------------+-------------------------------

 public.emp9 |             |                 |              | 2024-04-08 10:15:25.949394+00

(1 row)

 

DatabaseName=# select n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup

DatabaseName-# from pg_stat_all_tables where schemaname='public' and relname='emp9';

 n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup

-----------+-----------+-----------+---------------+------------+------------

       100 |         0 |        30 |             0 |         70 |         30

(1 row)

 

DatabaseName=#


On Mon, Apr 8, 2024 at 3:15 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2024-04-08 at 14:57 +0530, jaya kumar wrote:
> autovacuum_analyze_threshold and autovacuum_vacuum_threshold both parameter
> values set from 50 to 20.
>
> Created one table with 100 records. First, we deleted 21 records. after an
> autovacuum does not happen automatically. After again I deleted another 20.
> Out of 100 we delectated 40 records then the autovacuum happened automatically.
> As per the configuration Autocacuum will automatically delete 21 records
> in the table but my case does not happen.
>
> Can someone check and explain this.

The threshold is

  autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows

So since you have set autovacuum_vacuum_scale_factor to 0.1 and there are 100 rows,
you need to delete 20 + 100 * 0.1 = 30 rows before autovacuum kicks in.

Yours,
Laurenz Albe


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

Re: Autovacuum------Doubts

От
Laurenz Albe
Дата:
On Mon, 2024-04-08 at 16:25 +0530, jaya kumar wrote:
> I am not clear.

I can confirm that.

> Could you explain again. If I delete 20 records out of 100. It means Autocacumm should run
> but not run automatically. As you provide a solution, the method should be deleted but does not happen.

If you delete 20 rows, autovacuum should not run.

> Delete record=30
>  
>  autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows ?
>   o/p: 20+0.1*100=2010?

I don't know what an o/p is, but that calculation is wrong.

> DatabaseName=# show autovacuum_vacuum_threshold;
>  autovacuum_vacuum_threshold
> -----------------------------
>  20
> (1 row)
>  
> DatabaseName=# show autovacuum_vacuum_scale_factor;
>  autovacuum_vacuum_scale_factor
> --------------------------------
>  0.1
> (1 row)
>  
> DatabaseName=# select count(*) from emp9;
>  count
> -------
>    100
> (1 row)
>
> DatabaseName=# delete from emp9 where eno between 1 and 30;

Try deleting 31 or 32 rows.
Statistics are not necessarily 100% accurate.

> On Mon, Apr 8, 2024 at 3:15 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Mon, 2024-04-08 at 14:57 +0530, jaya kumar wrote:
> > > autovacuum_analyze_threshold and autovacuum_vacuum_threshold both parameter
> > > values set from 50 to 20.
> > >
> > > Created one table with 100 records. First, we deleted 21 records. after an
> > > autovacuum does not happen automatically. After again I deleted another 20.
> > > Out of 100 we delectated 40 records then the autovacuum happened automatically.
> > > As per the configuration Autocacuum will automatically delete 21 records
> > > in the table but my case does not happen.
> > >
> > > Can someone check and explain this.
> >
> > The threshold is
> >
> >   autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows
> >
> > So since you have set autovacuum_vacuum_scale_factor to 0.1 and there are 100 rows,
> > you need to delete 20 + 100 * 0.1 = 30 rows before autovacuum kicks in.

Please bottom-post.

Yours,
Laurenz Albe

Re: Autovacuum------Doubts

От
jaya kumar
Дата:
Hi Laurenz,

Thanks for your update. Kindly check the below update.


I have changed the below 3 parameters.




DatabaseName=# show autovacuum_vacuum_threshold;
 autovacuum_vacuum_threshold
-----------------------------
 20
(1 row)

DatabaseName=# show autovacuum_analyze_threshold;
 autovacuum_analyze_threshold
------------------------------
 20
(1 row)

DatabaseName=# show autovacuum_vacuum_scale_factor;
 autovacuum_vacuum_scale_factor
--------------------------------
 0.1
(1 row)

DatabaseName=#


As per my understanding.

Table total count=100

If I deleted 20 records. Remaining 80 rows but auto vacuum is not happening.  While I deleted 35 rows then only Autovacuuum are happen.

20% tuples value came.  As I defined the current setting in the parameter file. Autovacuum  should start automatically. Am I correct?

May I know how you are telling the below method calculation. Can you explain please.

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows

I checked the link below.

https://www.postgresql.org/docs/14/runtime-config-autovacuum.html

On Mon, Apr 8, 2024 at 8:08 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2024-04-08 at 16:25 +0530, jaya kumar wrote:
> I am not clear.

I can confirm that.

> Could you explain again. If I delete 20 records out of 100. It means Autocacumm should run
> but not run automatically. As you provide a solution, the method should be deleted but does not happen.

If you delete 20 rows, autovacuum should not run.

> Delete record=30
>  
>  autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows ?
>   o/p: 20+0.1*100=2010?

I don't know what an o/p is, but that calculation is wrong.

> DatabaseName=# show autovacuum_vacuum_threshold;
>  autovacuum_vacuum_threshold
> -----------------------------
>  20
> (1 row)
>  
> DatabaseName=# show autovacuum_vacuum_scale_factor;
>  autovacuum_vacuum_scale_factor
> --------------------------------
>  0.1
> (1 row)
>  
> DatabaseName=# select count(*) from emp9;
>  count
> -------
>    100
> (1 row)
>
> DatabaseName=# delete from emp9 where eno between 1 and 30;

Try deleting 31 or 32 rows.
Statistics are not necessarily 100% accurate.

> On Mon, Apr 8, 2024 at 3:15 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Mon, 2024-04-08 at 14:57 +0530, jaya kumar wrote:
> > > autovacuum_analyze_threshold and autovacuum_vacuum_threshold both parameter
> > > values set from 50 to 20.
> > >
> > > Created one table with 100 records. First, we deleted 21 records. after an
> > > autovacuum does not happen automatically. After again I deleted another 20.
> > > Out of 100 we delectated 40 records then the autovacuum happened automatically.
> > > As per the configuration Autocacuum will automatically delete 21 records
> > > in the table but my case does not happen.
> > >
> > > Can someone check and explain this.
> >
> > The threshold is
> >
> >   autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows
> >
> > So since you have set autovacuum_vacuum_scale_factor to 0.1 and there are 100 rows,
> > you need to delete 20 + 100 * 0.1 = 30 rows before autovacuum kicks in.

Please bottom-post.

Yours,
Laurenz Albe


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

Re: Autovacuum------Doubts

От
Scott Ribe
Дата:
> On Apr 8, 2024, at 9:23 AM, jaya kumar <kumardba27@gmail.com> wrote:
>
> autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows

20 + 0.1 * 100 = 30, not 2010

Re: Autovacuum------Doubts

От
Jeff Janes
Дата:
On Mon, Apr 8, 2024 at 5:27 AM jaya kumar <kumardba27@gmail.com> wrote:

Hi Team,

The below updated parameter value has been set currently in postgresql.conf file.

autovacuum_analyze_threshold and autovacuum_vacuum_threshold both parameter values set from 50 to 20.

Created one table with 100 records. First, we deleted 21 records. after an autovacuum does not happen automatically. After again I deleted another 20. Out of 100 we delectated 40 records then the autovacuum happened automatically. As per the configuration Autocacuum will automatically delete 21 records in the table but my case does not happen.

If you want vacuuming by autovacuum to be driven entirely by autovacuum_vacuum_threshold, then you should set autovacuum_vacuum_scale_factor to zero.

Cheers,

Jeff

Re: Autovacuum------Doubts

От
jaya kumar
Дата:
Hi Jeff,

Superb. Now it's working fine as I expected.

If I will set autovacuum_vacuum_scale_factor parameter value=0 is fine.

If I set production database autovacuum_vacuum_scale_factor parameter 0. Do I face any database performance issues?

On Mon, Apr 8, 2024 at 9:51 PM Jeff Janes <jeff.janes@gmail.com> wrote:
On Mon, Apr 8, 2024 at 5:27 AM jaya kumar <kumardba27@gmail.com> wrote:

Hi Team,

The below updated parameter value has been set currently in postgresql.conf file.

autovacuum_analyze_threshold and autovacuum_vacuum_threshold both parameter values set from 50 to 20.

Created one table with 100 records. First, we deleted 21 records. after an autovacuum does not happen automatically. After again I deleted another 20. Out of 100 we delectated 40 records then the autovacuum happened automatically. As per the configuration Autocacuum will automatically delete 21 records in the table but my case does not happen.

If you want vacuuming by autovacuum to be driven entirely by autovacuum_vacuum_threshold, then you should set autovacuum_vacuum_scale_factor to zero.

Cheers,

Jeff


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

Re: Autovacuum------Doubts

От
Jeff Janes
Дата:


On Mon, Apr 8, 2024 at 12:35 PM jaya kumar <kumardba27@gmail.com> wrote:
Hi Jeff,

Superb. Now it's working fine as I expected.

If I will set autovacuum_vacuum_scale_factor parameter value=0 is fine.

If I set production database autovacuum_vacuum_scale_factor parameter 0. Do I face any database performance issues?

Yes, your tables will be vacuumed far more than is sensible.  The vacuum will consume resources that would be better used elsewhere, and some tables that need very frequent vacuuming might be starved of it because it is too busy vacuuming other tables which don't really need it.

If zero were generally a better value, it would have been made the default.  This setting is only useful for pedagogical purposes. I have set it to zero on individual tables on occasion, in conjunction with raising their threshold value, when those tables need to keep their visibility map tuned up.

Cheers,

Jeff

Re: Autovacuum------Doubts

От
Laurenz Albe
Дата:
On Mon, 2024-04-08 at 20:53 +0530, jaya kumar wrote:
> May I know how you are telling the below method calculation. Can you explain please.
>
> autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows

See https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM

Yours,
Laurenz Albe