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
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@xxxxxxxxxxx> 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@xxxxxxxxxxx> 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.
