pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum------Doubts
10+ messages / 4 participants
[nested] [flat]
* Autovacuum------Doubts
@ 2024-04-08 09:27 jaya kumar <kumardba27@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: jaya kumar @ 2024-04-08 09:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autovacuum------Doubts
@ 2024-04-08 09:45 Laurenz Albe <laurenz.albe@cybertec.at>
parent: jaya kumar <kumardba27@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2024-04-08 09:45 UTC (permalink / raw)
To: jaya kumar <kumardba27@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autovacuum------Doubts
@ 2024-04-08 10:55 jaya kumar <kumardba27@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 10+ messages in thread
From: jaya kumar @ 2024-04-08 10:55 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autovacuum------Doubts
@ 2024-04-08 14:38 Laurenz Albe <laurenz.albe@cybertec.at>
parent: jaya kumar <kumardba27@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2024-04-08 14:38 UTC (permalink / raw)
To: jaya kumar <kumardba27@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autovacuum------Doubts
@ 2024-04-08 15:23 jaya kumar <kumardba27@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 10+ messages in thread
From: jaya kumar @ 2024-04-08 15:23 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autovacuum------Doubts
@ 2024-04-08 15:49 Scott Ribe <scott_ribe@elevated-dev.com>
parent: jaya kumar <kumardba27@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Scott Ribe @ 2024-04-08 15:49 UTC (permalink / raw)
To: jaya kumar <kumardba27@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autovacuum------Doubts
@ 2024-04-08 16:21 Jeff Janes <jeff.janes@gmail.com>
parent: jaya kumar <kumardba27@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Jeff Janes @ 2024-04-08 16:21 UTC (permalink / raw)
To: jaya kumar <kumardba27@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autovacuum------Doubts
@ 2024-04-08 16:35 jaya kumar <kumardba27@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: jaya kumar @ 2024-04-08 16:35 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autovacuum------Doubts
@ 2024-04-08 16:44 Jeff Janes <jeff.janes@gmail.com>
parent: jaya kumar <kumardba27@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jeff Janes @ 2024-04-08 16:44 UTC (permalink / raw)
To: jaya kumar <kumardba27@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autovacuum------Doubts
@ 2024-04-08 17:50 Laurenz Albe <laurenz.albe@cybertec.at>
parent: jaya kumar <kumardba27@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Laurenz Albe @ 2024-04-08 17:50 UTC (permalink / raw)
To: jaya kumar <kumardba27@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2024-04-08 17:50 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-08 09:27 Autovacuum------Doubts jaya kumar <kumardba27@gmail.com>
2024-04-08 09:45 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-04-08 10:55 ` jaya kumar <kumardba27@gmail.com>
2024-04-08 14:38 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-04-08 15:23 ` jaya kumar <kumardba27@gmail.com>
2024-04-08 15:49 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-04-08 17:50 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-04-08 16:21 ` Jeff Janes <jeff.janes@gmail.com>
2024-04-08 16:35 ` jaya kumar <kumardba27@gmail.com>
2024-04-08 16:44 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox