We're currently using Postgres 9.4.14 hosting Zabbix 2.4 database. For a while now, the following message started to happens:
< 2017-11-23 15:15:20.037 -03 >HINT: Close open transactions soon to avoid wraparound problems.
< 2017-11-23 15:15:20.048 -03 >WARNING: oldest xmin is far in the past
< 2017-11-23 15:15:20.048 -03 >HINT: Close open transactions soon to avoid wraparound problems.
< 2017-11-23 15:15:20.059 -03 >WARNING: oldest xmin is far in the past
We restarted the datase and after startup the same message start to be happen:
< 2017-11-22 10:25:04.262 -03 >LOG: database system was shut down at 2017-11-22 10:00:04 -03
< 2017-11-22 10:25:04.387 -03 >LOG: MultiXact member wraparound protections are now enabled
< 2017-11-22 10:25:04.389 -03 >LOG: autovacuum launcher started
< 2017-11-22 10:25:04.389 -03 >LOG: database system is ready to accept connections
< 2017-11-22 10:25:15.910 -03 >WARNING: there is no transaction in progress
...
< 2017-11-22 10:25:20.259 -03 >WARNING: oldest xmin is far in the past
< 2017-11-22 10:25:20.259 -03 >HINT: Close open transactions soon to avoid wraparound problems.
Vacuum daemon is running:
postgres@bchlnx361:pg_log $ ps -fea | grep vacuum
postgres 409 403 0 Nov22 ? 00:00:02 postgres: autovacuum launcher process
and there are my postgresql.conf settings..
maintenance_work_mem = 2280MB
autovacuum_work_mem = -1
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 2
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 5000
autovacuum_analyze_threshold = 50000
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.05
autovacuum_freeze_max_age = 1000000000
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1
But tables are'nt being vacuumed, when I've checked on pg_stat_user_tables, all rows has last_Autovacuum with null values, and some tables really need to be vacuumed:
zabbix_db=# select tuple_count, dead_tuple_count from pgstattuple('public.triggers');
tuple_count | dead_tuple_count
-------------+------------------
73657 | 5161183
(1 row)
Someone could help as?
Regards,