Postgresql – Postgres 9.6.6 on Ubuntu 16.04, lots of errors

autovacuumlockingpostgresql-9.6

We're running perfsonar on a group of our servers, and while doing some maintenance work found that we

  • get giant logfiles under /var/log/postgresql
  • table bloat on the database of the perfsonar scheduler

I assume the toolkit is using table locking when it does ANYTHING on the database, and always cancels the autovacuum run:

2019-04-07 06:33:43 NZST [471-1] ERROR:  canceling autovacuum task
2019-04-07 06:33:43 NZST [471-2] CONTEXT:  automatic vacuum of table "pscheduler.public.run"
2019-04-07 06:34:33 NZST [981-1] ERROR:  canceling autovacuum task
2019-04-07 06:34:33 NZST [981-2] CONTEXT:  automatic vacuum of table "pscheduler.public.run"

A manual run of vacuum analyze roughly halved the use of disk space under /var/lib/postgresql/9.6/main/base/ …

I've found several web pages talking about autovacuum tuning, but I'm wondering if it wouldn't make sense to disable autovacuum and run a vacuum analyze from a cron job.

Is there a best practice/recommendation for this kind of situation?

And maybe ask the perfsonar project to clean up their code in the long run ;}

Best Answer

Having talked about this on freenode (thanks Andrew!) I modified two of the default settings in postgresql.conf, and that made the issues go away without causing other ill effects.

autovacuum_vacuum_cost_delay = 0ms
deadlock_timeout = 5s

I think the consensus is still that pscheduler's attempts to get table locks so frequently is poor design, but the workaround above seems to be doing the trick for the time being.

Related Question