Thread: Are my autovacuum settings too aggressive for this table?
Hello list, DB1=# select version(); -[ RECORD 1 ]---------------------------------------------------------------------------------------------------- version | PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit I am sure this question has come up before, I have scoured the documentation and I think I have a good grasp on the autovacuum. I wanted to ask the team if my settings for this particular table are too aggressive, I have the following setwhich is producing a vacuum analyze multiple times a day. I think the defaults out of the box were not aggressive enough, so I went with the following on the global level, I willpossibly move to table level if needed. I tried to show the stats below of a 10 minute interval during peak time. Anypush in the right direction is appreciated, I want my tables analyzed and vacuumed but do not want to over do it. Therest of the autovacuum settings are default. I know the stats are estimates so here is my calculations. Live tuples = 19,766,480 Analyze scale factor = 0.001 Analyze thresh = 5000 Thresh + live_tuples * factor = 24,766 So an autovacuum analyze should trigger around 24K tuples modified, is this to little or too much? Same goes for autvacuumvacuum, is it too aggressive? #------------------------------------------------------------------------------ # AUTOVACUUM #------------------------------------------------------------------------------ autovacuum_naptime = 1h autovacuum_vacuum_threshold = 10000 autovacuum_analyze_threshold = 5000 autovacuum_vacuum_scale_factor = 0.002 autovacuum_analyze_scale_factor = 0.001 DB1=# \x Expanded display is on. DB1=# select now(); -[ RECORD 1 ]---------------------- now | 2019-11-01 14:35:51.893328-04 DB1=# select * from pg_stat_user_tables where relname = 'members'; -[ RECORD 1 ]-------+------------------------------ relid | 18583 schemaname | public relname | members seq_scan | 129 seq_tup_read | 2036932707 idx_scan | 546161742 idx_tup_fetch | 1670607103 n_tup_ins | 46742 n_tup_upd | 35704112 n_tup_del | 0 n_tup_hot_upd | 31106485 n_live_tup | 19766480 n_dead_tup | 1844251 n_mod_since_analyze | 15191 last_vacuum | 2019-10-13 15:42:06.043385-04 last_autovacuum | 2019-11-01 12:24:45.575283-04 last_analyze | 2019-10-13 15:42:17.370086-04 last_autoanalyze | 2019-11-01 12:25:17.181133-04 vacuum_count | 2 autovacuum_count | 15 analyze_count | 2 autoanalyze_count | 17 DB1=# select now(); -[ RECORD 1 ]---------------------- now | 2019-11-01 14:45:10.845269-04 DB1=# select * from pg_stat_user_tables where relname = 'members'; -[ RECORD 1 ]-------+------------------------------ relid | 18583 schemaname | public relname | members seq_scan | 129 seq_tup_read | 2036932707 idx_scan | 546171120 idx_tup_fetch | 1670615505 n_tup_ins | 46742 n_tup_upd | 35705068 n_tup_del | 0 n_tup_hot_upd | 31107303 n_live_tup | 19766480 n_dead_tup | 1844881 n_mod_since_analyze | 16147 last_vacuum | 2019-10-13 15:42:06.043385-04 last_autovacuum | 2019-11-01 12:24:45.575283-04 last_analyze | 2019-10-13 15:42:17.370086-04 last_autoanalyze | 2019-11-01 12:25:17.181133-04 vacuum_count | 2 autovacuum_count | 15 analyze_count | 2 autoanalyze_count | 17 Thanks for your time, Jason Ralph This message contains confidential information and is intended only for the individual named. If you are not the named addresseeyou should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if youhave received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteedto be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete,or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contentsof this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copyversion.
My thinking is opposite from what you have. I consider it important to very aggressive on autovacuum because it only ever does the required amount of work. If a tiny amount of work is needed, it does only that and is done. Assuming it doesn't cause I/O concerns, do it as often as possible to minimize the growth of bloat and maximize the reuse of the space already allocated to that relation.
On the subject of analyze, the statistics are unlikely to be impacted significantly by inserting about 24k rows to a table with almost 20 million already. With default_statistics_target at 100, what are the chances those new rows will even be included in the sample? I don't know the math, but given each run of analyze does the same ALL the work each and every time it runs, it seems prudent to do them a little less often than autovacuum anyway. Regardless though, autoanalyze is a small amount of work that it does each time.
Michael Lewis <mlewis@entrata.com> writes: >My thinking is opposite from what you have. I consider it important to very aggressive on autovacuum because it only everdoes the required amount of >work. If a tiny amount of work is needed, it does only that and is done. Assuming it doesn'tcause I/O concerns, do it as often as possible to minimize the> growth of bloat and maximize the reuse of the spacealready allocated to that relation. Excellent, I am not seeing any I/O concerns, and it seems to be keeping up now, so I will keep this setting unless someoneelse points out another suggestion. >On the subject of analyze, the statistics are unlikely to be impacted significantly by inserting about 24k rows to a tablewith almost 20 million already. >With default_statistics_target at 100, what are the chances those new rows will evenbe included in the sample? I don't know the math, but given each >run of analyze does the same ALL the work each andevery time it runs, it seems prudent to do them a little less often than autovacuum anyway. >Regardless though, autoanalyzeis a small amount of work that it does each time. I agree, this is excellent advice, I overlooked the fact that this is a sample and the new rows may not even be includedin this sample. I will adjust accordingly. -----Original Message----- From: Jason Ralph <jralph@affinitysolutions.com> Sent: Friday, November 1, 2019 2:59 PM To: pgsql-general@lists.postgresql.org Cc: Jason Ralph <jralph@affinitysolutions.com> Subject: Are my autovacuum settings too aggressive for this table? Hello list, DB1=# select version(); -[ RECORD 1 ]---------------------------------------------------------------------------------------------------- version | PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit I am sure this question has come up before, I have scoured the documentation and I think I have a good grasp on the autovacuum. I wanted to ask the team if my settings for this particular table are too aggressive, I have the following setwhich is producing a vacuum analyze multiple times a day. I think the defaults out of the box were not aggressive enough, so I went with the following on the global level, I willpossibly move to table level if needed. I tried to show the stats below of a 10 minute interval during peak time. Anypush in the right direction is appreciated, I want my tables analyzed and vacuumed but do not want to over do it. Therest of the autovacuum settings are default. I know the stats are estimates so here is my calculations. Live tuples = 19,766,480 Analyze scale factor = 0.001 Analyze thresh = 5000 Thresh + live_tuples * factor = 24,766 So an autovacuum analyze should trigger around 24K tuples modified, is this to little or too much? Same goes for autvacuumvacuum, is it too aggressive? #------------------------------------------------------------------------------ # AUTOVACUUM #------------------------------------------------------------------------------ autovacuum_naptime = 1h autovacuum_vacuum_threshold = 10000 autovacuum_analyze_threshold = 5000 autovacuum_vacuum_scale_factor = 0.002 autovacuum_analyze_scale_factor = 0.001 DB1=# \x Expanded display is on. DB1=# select now(); -[ RECORD 1 ]---------------------- now | 2019-11-01 14:35:51.893328-04 DB1=# select * from pg_stat_user_tables where relname = 'members'; -[ RECORD 1 ]-------+------------------------------ relid | 18583 schemaname | public relname | members seq_scan | 129 seq_tup_read | 2036932707 idx_scan | 546161742 idx_tup_fetch | 1670607103 n_tup_ins | 46742 n_tup_upd | 35704112 n_tup_del | 0 n_tup_hot_upd | 31106485 n_live_tup | 19766480 n_dead_tup | 1844251 n_mod_since_analyze | 15191 last_vacuum | 2019-10-13 15:42:06.043385-04 last_autovacuum | 2019-11-01 12:24:45.575283-04 last_analyze | 2019-10-13 15:42:17.370086-04 last_autoanalyze | 2019-11-01 12:25:17.181133-04 vacuum_count | 2 autovacuum_count | 15 analyze_count | 2 autoanalyze_count | 17 DB1=# select now(); -[ RECORD 1 ]---------------------- now | 2019-11-01 14:45:10.845269-04 DB1=# select * from pg_stat_user_tables where relname = 'members'; -[ RECORD 1 ]-------+------------------------------ relid | 18583 schemaname | public relname | members seq_scan | 129 seq_tup_read | 2036932707 idx_scan | 546171120 idx_tup_fetch | 1670615505 n_tup_ins | 46742 n_tup_upd | 35705068 n_tup_del | 0 n_tup_hot_upd | 31107303 n_live_tup | 19766480 n_dead_tup | 1844881 n_mod_since_analyze | 16147 last_vacuum | 2019-10-13 15:42:06.043385-04 last_autovacuum | 2019-11-01 12:24:45.575283-04 last_analyze | 2019-10-13 15:42:17.370086-04 last_autoanalyze | 2019-11-01 12:25:17.181133-04 vacuum_count | 2 autovacuum_count | 15 analyze_count | 2 autoanalyze_count | 17 Thanks for your time, Jason Ralph This message contains confidential information and is intended only for the individual named. If you are not the named addresseeyou should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if youhave received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteedto be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete,or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contentsof this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copyversion.
On Fri, 2019-11-01 at 18:58 +0000, Jason Ralph wrote: > autovacuum_naptime = 1h > autovacuum_vacuum_threshold = 10000 > autovacuum_analyze_threshold = 5000 > autovacuum_vacuum_scale_factor = 0.002 > autovacuum_analyze_scale_factor = 0.001 These seem to be crazy settings. Only once an hour you test if a table needs autovacuum, and then you configure autovacuum to process tables all the time. There are only three autovacuum workers, so most of the tables won't get vacuumed. Please reset all these values to their default. If you want autovacuum to be more aggressive for a single table, run ALTER TABLE x SET (autovacuum_vacuum_cost_delay = 2); Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Live tuples = 19,766,480
Analyze scale factor = 0.001
Analyze thresh = 5000
Thresh + live_tuples * factor = 24,766
So an autovacuum analyze should trigger around 24K tuples modified, is this to little or too much?
This seems too much to me. Was there a specific problem occurring that spurred this change? If many of the tuple modifications are occurring on a certain subset of the data which changes the distribution in an important way, then maybe this would be justified. (But maybe partitioning between active and largely inactive would be a better solution)
Same goes for autvacuum vacuum, is it too aggressive?
#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------
autovacuum_naptime = 1h
autovacuum_vacuum_threshold = 10000
autovacuum_analyze_threshold = 5000
autovacuum_vacuum_scale_factor = 0.002
autovacuum_analyze_scale_factor = 0.001
The analyze settings don't seem defensible to me, but maybe you can make a case for them by referring to problems that were showing up in particular queries.
The naptime seems particularly indefensible. If you think you overdid it with some changes, then you should back off those changes. Not randomly whack around other settings in an attempt to compensate for the first ones, without having some identifiable theory which supports this. If you do have such a theory, you haven't told us what it might be.
If index-only-scans (and hence rel_allvisible) are particularly important to you, then the autovac settings might make sense. However, this is unlikely to be true at the global level, but rather on a table by table basis. But, I don't see the point in setting autovacuum_vacuum_scale_factor = 0.002. If it needs a drastic decrease (to pair with a well-considered increase in autovacuum_vacuum_threshold) why would 99% be the correct decrease, rather than 100%? A nice thing about exact 0 is it stands out as being intentional, as opposed to a typo or a mistranslation between percentage and fraction.
Cheers,
Jeff