Обсуждение: 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