Message-ID: <42e852495e19c1de2e734a666ae30d9b3e37abe1.camel@cybertec.at>
Subject: Re: Autovacuum------Doubts
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: jaya kumar <kumardba27@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Date: Mon, 08 Apr 2024 16:38:08 +0200
In-Reply-To: 
 <CACd4L3vi0iRzSesQUDcMLGBJPpVOkLVoXBx=KGqzmpSGHUUudA@mail.gmail.com>
References: 
	<CACd4L3t3a7LHgJDysnKY6DQ1cBC6aeVhV=p3cVF3gOAtfUGvDA@mail.gmail.com>
	 <70109b120e8f26fa29c1d0003a729639a5dec196.camel@cybertec.at>
	 <CACd4L3vi0iRzSesQUDcMLGBJPpVOkLVoXBx=KGqzmpSGHUUudA@mail.gmail.com>
Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual;
 keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk
 bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC
 GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b
 lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1
 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ
 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59
 +AnW5l0HsQcY9ntFL/zEBOaonjdJf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.50.4 (3.50.4-1.fc39) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/42e852495e19c1de2e734a666ae30d9b3e37abe1.camel%40cybertec.at>
Precedence: bulk

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 Auto=
cacumm should run
> but not run automatically. As you provide a solution, the method should b=
e deleted but does not happen.

If you delete 20 rows, autovacuum should not run.

> Delete record=3D30
> =C2=A0
> =C2=A0autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * numb=
er of table rows ?
> =C2=A0 o/p: 20+0.1*100=3D2010?

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

> DatabaseName=3D# show autovacuum_vacuum_threshold;
> =C2=A0autovacuum_vacuum_threshold
> -----------------------------
> =C2=A020
> (1 row)
> =C2=A0
> DatabaseName=3D# show autovacuum_vacuum_scale_factor;
> =C2=A0autovacuum_vacuum_scale_factor
> --------------------------------
> =C2=A00.1
> (1 row)
> =C2=A0
> DatabaseName=3D# select count(*) from emp9;
> =C2=A0count
> -------
> =C2=A0=C2=A0 100
> (1 row)
>
> DatabaseName=3D# 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=E2=80=AFPM Laurenz Albe <laurenz.albe@cyberte=
c.at> wrote:
> > On Mon, 2024-04-08 at 14:57 +0530, jaya kumar wrote:
> > > autovacuum_analyze_threshold and autovacuum_vacuum_threshold both par=
ameter
> > > values set from 50 to 20.
> > >=20
> > > Created one table with 100 records. First, we deleted 21 records. aft=
er an
> > > autovacuum does not happen automatically. After again I deleted anoth=
er 20.
> > > Out of 100 we delectated 40 records then the autovacuum happened auto=
matically.
> > > As per the configuration Autocacuum will automatically delete 21 reco=
rds
> > > in the table but my case does not happen.
> > >=20
> > > Can someone check and explain this.
> >=20
> > The threshold is
> >=20
> > =C2=A0 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n=
umber of table rows
> >=20
> > So since you have set autovacuum_vacuum_scale_factor to 0.1 and there a=
re 100 rows,
> > you need to delete 20 + 100 * 0.1 =3D 30 rows before autovacuum kicks i=
n.

Please bottom-post.

Yours,
Laurenz Albe