Thread: vacuum and autovacuum - is it good to configure the threshold atTABLE LEVEL?

Hi,

Please suggest me on the following,

1. Is it better to configure autovacuum threshold at table level?
2. Is there any discussions in this forum which I can refer for
understanding vacuum/autovacuum?

Thanks in advance.
Rajan.



-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Hopefully, this[1] will help you.

1] https://www.percona.com/blog/2018/08/10/tuning-autovacuum-in-postgresql-and-autovacuum-internals/

regards,
Amul
On Tue, Nov 27, 2018 at 11:50 AM rajan <vgmonnet@gmail.com> wrote:
>
> Hi,
>
> Please suggest me on the following,
>
> 1. Is it better to configure autovacuum threshold at table level?
> 2. Is there any discussions in this forum which I can refer for
> understanding vacuum/autovacuum?
>
> Thanks in advance.
> Rajan.
>
>
>
> -----
> --
> Thanks,
> Rajan.
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
>

Thanks, amul. I have already gone through this. What I would like to
understand is the performance impact on autovacuum launcher and worker
process when autovacuum is running from configurations done by
*ALTER TABLE autvac_test SET (autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 100);*
 at table level.



-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

On Wed, Nov 28, 2018 at 9:11 AM rajan <vgmonnet@gmail.com> wrote:
>
> Thanks, amul. I have already gone through this. What I would like to
> understand is the performance impact on autovacuum launcher and worker
> process when autovacuum is running from configurations done by
> *ALTER TABLE autvac_test SET (autovacuum_vacuum_scale_factor = 0,
> autovacuum_vacuum_threshold = 100);*
>  at table level.

An answer could be yes or no, something work for me that not necessarily work
for you.

The aforesaid configuration will trigger vacuum at every 150 row update/delete.
It depends on your server load, how frequent 150 row count reaches. Also,
triggering vacuum too frequently is also not that much beneficial, IMO.


Regards,
Amul

thanks for the reply, amul.



-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Hello Amul,

I have a doubt. Please find below the details,

1. autovacuum_vacuum_threshold and autovacuum_analyse_threshold are set at
300
2. autovacuum_vacuum_scale_factor and autovacuum_analyse_scale_face are set
at 300
3. there is table which has only 6 records
4. and the number of updates happened in the table is 944
5. now when I fetch data from pg_stat_user_table, i find that only
last_autoanalyse column is updated with datetime and last_autovacuum column
is not having a value

does this mean only autoanalyse was executed for this table? But when I have
both vacuum_threshold and analyse_threshold set at 300, how can only analyse
run and vacuum does not?

This may be a stupid question. But any answer can help me understand.



-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Apologize for delayed response.

On Mon, Dec 3, 2018 at 12:30 PM rajan <vgmonnet@gmail.com> wrote:
>
> Hello Amul,
>
> I have a doubt. Please find below the details,
>
> 1. autovacuum_vacuum_threshold and autovacuum_analyse_threshold are set at
> 300
> 2. autovacuum_vacuum_scale_factor and autovacuum_analyse_scale_face are set
> at 300
> 3. there is table which has only 6 records
> 4. and the number of updates happened in the table is 944
> 5. now when I fetch data from pg_stat_user_table, i find that only
> last_autoanalyse column is updated with datetime and last_autovacuum column
> is not having a value
>
> does this mean only autoanalyse was executed for this table? But when I have
> both vacuum_threshold and analyse_threshold set at 300, how can only analyse
> run and vacuum does not?
>
That's right auto-vacuumed might not have triggered -- I think, that because of
autovacuum_vacuum_scale_factor setting and most of the time update does not
increase the table size.

Following question might not directly be related to your query but
just curious to know:
Does your table have indexes and by any chance your experiment
updating non-index column?

Regards,
Amul

Thanks for the reply Amul.

Btw, No my table does not has any index.



-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

On Mon, Dec 10, 2018 at 2:51 PM rajan <vgmonnet@gmail.com> wrote:
>
> Thanks for the reply Amul.
>
> Btw, No my table does not has any index.

Thanks for the information.

Regards,
Amul