1

We are using postgres 9.2 version on Centos operating system. We have around 1300+ tables.We have following auto vacuum settings are enables. Still few of the tables(84 tables) which are always busy are not vacuumed.Dead tuples in those tables are more than 5000. Due to that tables are bloating and observed few areas has performance degradation. 

autovacuum = on                             
log_autovacuum_min_duration = 100       
autovacuum_max_workers = 5  
autovacuum_naptime = 1min  
autovacuum_vacuum_threshold = 40                        
autovacuum_analyze_threshold = 20                       
autovacuum_vacuum_scale_factor = 0.1    
autovacuum_analyze_scale_factor = 0.05  
autovacuum_freeze_max_age = 200000000                       
autovacuum_vacuum_cost_delay = 30ms                     
autovacuum_vacuum_cost_limit = 1200     

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms        # 0-100 milliseconds
#vacuum_cost_page_hit = 1       # 0-10000 credits
#vacuum_cost_page_miss = 10     # 0-10000 credits
#vacuum_cost_page_dirty = 20    # 0-10000 credits
vacuum_cost_limit = 200         # 1-10000 credits

In order to avoid the table bloating and performance degradation,we would like to set the ' autovacuum_vacuum_scale_factor'(zero) and 'autovacuum_vacuum_threshold ' (200) settings for the busy tables as below. Please let me know is there any adverse effect on DB if I set autovacuum scale factor to zero for certain tables. If yes, what is the effect and how to test.

ALTER TABLE cmdevice SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 200);

Kindly let me know the role of autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit settings .

Raghavendra
  • 519
  • 1
  • 11
  • 25

1 Answers1

1

That measure will not be effective. While it will cause autovacuum to run on the table all the time, it will not make it faster.

First try to raise autovacuum_vacuum_cost_limit which determines after how much work autovacuum will “take a nap” to 2000 or so.

If that does not do the trick, lower autovacuum_vacuum_cost_delay which specifies the duration of that nap to 0. That's the best you can do.

You can change the settings on only the tables with high activity using ALTER TABLE ... SET (...).

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Are you advising me, not to use the ALTER TABLE table SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 200); – Raghavendra Aug 13 '18 at 10:47
  • Yes. All that will do is keep autovacuum running all the time, with no chance of completing. – Laurenz Albe Aug 13 '18 at 10:55
  • Thank you very much for your update. As you suggested , I have set the 'autovacuum_vacuum_cost_limit' value as 2000 and 'autovacuum_vacuum_cost_delay' value to zero. But The tables which contains deadtuples more than 1000 are not at all cleaned even after an hour. Incase if I apply the ALTER TABLE table SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 1000); then dead rows are removed from the specified table immediately. Kindly clarify me what to do now. – Raghavendra Aug 13 '18 at 11:51
  • It does not matter if the dead tuples don't get cleaned up immediately. The important thing is that they get cleaned up fast. If a tables with 1000000 rows has 5000 dead tuples, that is fine. – Laurenz Albe Aug 13 '18 at 12:36
  • I would like to change in 'postgres.conf' file as autovacuum_vacuum_scale_factor=0.01 and autovacuum_vacuum_threshold = 100. Kindly let me know your views.(globally) – Raghavendra Aug 13 '18 at 13:11
  • Bad, bad idea; same as above. Leave those two parameters alone. – Laurenz Albe Aug 13 '18 at 13:36
  • We have around 1300+ tables. Out of that high activity tables are approximately 80. For those tables I am going to use ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 5000);. Please let me know your views. I have gone through several documents but I am still have confusion related to "autovacuum_naptime" and "autovacuum_vacuum_cost_delay". Could you clarifiy me with an example. – Raghavendra Aug 17 '18 at 01:07
  • I repeat: do not change `autovacuum_vacuum_threshold` and `autovacuum_vacuum_scale_factor`. Set `autovacuum_vacuum_cost_delay` to 0 for these tables. You *may* increase `autovacuum_max_workers` slightly with that many tables. – Laurenz Albe Aug 17 '18 at 05:10
  • Sorry to truouble you once again. I would like to understand more. What it means keeping "autovacuum_vacuum_cost_delay" to 0 for certain tables. Could you clarify me. – Raghavendra Aug 21 '18 at 01:50
  • That means `ALTER TABLE mybusytable SET (autovacuum_vacuum_cost_delay = 0)`. – Laurenz Albe Aug 21 '18 at 05:36