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. 



В списке pgsql-general по дате отправления:

Предыдущее
От: Francisco Olarte
Дата:
Сообщение: Re: Getting following error in using cursor to fetch the records froma large table in c language(current transaction is aborted, commands ignoreduntil end of transaction block)
Следующее
От: Michael Lewis
Дата:
Сообщение: Re: Declarative Range Partitioning Postgres 11