Peter Hunčár wrote:
> Hi,
>
> Thank you, yes those are the 'urgent' tables, I'd talk to the developers
> regarding the locks.I too think, there's something 'fishy' going on.
I bet you have a lot of subtransactions -- maybe a plpgsql block with an
EXCEPTION clause that's doing something per-tuple. In certain cases
in 9.3 and 9.4 that can eat a lot of multixacts. 9.5 is much better in
that regard -- I suggest considering an upgrade there.
> Anyway, could it be that autovacuum blocks manual vacuum? Because I ran
> vacuum (full, verbose) and some tables finished quite fast, with huge
> amount of io recorded in the monitoring, but some of them are kind of
> stuck?
Whenever autovacuum is marked "for wraparound", it'll block a manual
vacuum. An autovacuum worker not so marked would get cancelled by the
manual vacuum.
> Which brings me to the second question, how can I cancel autovacuum?
pg_cancel_backend() should do it, regardless of whether it's for
wraparound or not (but if it is, autovacuum will launch another worker
for the same table quickly afterwards).
> One particular table before vacuum full:
>
> relname | relminmxid | table_size
> ------------------------------+------------+------------
> delayed_jobs | 1554151198 | 21 GB
>
> And after vacuum full:
>
> relname | relminmxid | table_size
> --------------+------------+------------
> delayed_jobs | 1554155465 | 6899 MB
>
> Shouldn't be the relminmxid changed after vacuum full, or am I not
> understanding something?
But it did change ... the problem is that it didn't change enough (only
4000 multixacts). Maybe your multixact freeze min age is too high?
Getting rid of 15 GB of bloat is a good side effect, though, I'm sure.
What are the freeze settings?
select name, setting from pg_settings where name like '%vacuum%';
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services