We use a Postgresql 10 database server to import nearly 1 million user files every day. To keep the databases easier to handle (backup, maintenance) we create a new database every week. The database from the previous weeks are vacuumed, backuped and no further write operations are performed on them. In total we have 17 databases with > 7 million imported files (500GB) in each. That are 120 million files which leads to 240 million transactions in total. (To import one file we need two transactions.)
Some days ago, we detected that Postgresql starts the autovacuum process with the ‘prevent wraparound’ option on the oldest databases. Postgresql starts only those vacuums and not the occasional vacuums (clean-up and maintenance tasks) to analyze the current week's database. Due to the additional IO load, we have performance problems with select statements on the current database.
Is there a way to prevent this behavior on the old databases? We tried a vacuum freeze to reset the relevant counters but they start growing immediately when our import is running.
We fetch these counters with the following query:
SELECT
oid::regclass::text AS table, --relfrozenxid,
age(relfrozenxid) AS xid_age,
mxid_age(relminmxid) AS mxid_age,
least(
(SELECT setting::int
FROM pg_settings
WHERE name = 'autovacuum_freeze_max_age') - age(relfrozenxid),
(SELECT setting::int
FROM pg_settings
WHERE name = 'autovacuum_multixact_freeze_max_age') - mxid_age(relminmxid)
) AS tx_before_wraparound_vacuum,
pg_size_pretty(pg_total_relation_size(oid)) AS size,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum
FROM pg_class
WHERE relfrozenxid != 0
AND oid > 16384
ORDER BY tx_before_wraparound_vacuum;
Edit
After further investigation, we have read about visibility maps in the 10.x documentation. A freeze run does not touch the XID but set a flag in this map for indication. So we hope that any old database, where the wraparound or freeze vacuum was running, is never touched again by any autovacuum in the future.
autovacuum
is disabled, the system will launch autovacuum processes if necessary to prevent transaction ID wraparound. Could you explain why you have120 million files = 240 million transaction
? – Luan Huynh Jul 26 '18 at 13:16alter
your table and increase the auto-vacuum's threshold asautovacuum_freeze_max_age
. I don't think you can disable that auto-vacuum. – Luan Huynh Jul 26 '18 at 13:37We tried a vacuum freeze to reset the relevant counters but they start growing immediately when our import is running.
Meaning your triedvacuum freeze
in old database, but when you import into new database, the stats has increased again, right ? – Luan Huynh Jul 26 '18 at 13:40