Re: Autovacuum------Doubts

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(dot)albe(at)cybertec(dot)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.