Tom, agreed - it looks like we dug the hole and got ourselves into it.
But I still want to understand why.
It looks like we have rather small table on the host where I see the
slowness. And all other tables have triggers that will update one row
in that small table. The small table contains single entry per table.
The thing is, when I scan pg_locks - I can pretty much see everything
waiting for lock to access that table. To grab pg_lock output, I'm
using this view:
SELECT waiting.locktype AS waiting_locktype, waiting.relation::regclass AS waiting_table,
waiting_stm.current_query AS waiting_query, waiting.mode AS waiting_mode, waiting.pid
ASwaiting_pid, other.locktype AS other_locktype, other.relation::regclass AS other_table,
other_stm.current_query AS other_query, other.mode AS other_mode, other.pid AS
other_pid, other.granted AS other_granted
FROM pg_catalog.pg_locks AS waiting
JOIN pg_catalog.pg_stat_activity AS waiting_stm ON ( waiting_stm.procpid = waiting.pid )
JOIN pg_catalog.pg_locks AS other ON ( ( waiting."database" = other."database" AND
waiting.relation = other.relation ) OR waiting.transactionid = other.transactionid )
JOIN pg_catalog.pg_stat_activity AS other_stm ON ( other_stm.procpid = other.pid )
WHERE NOT waiting.granted
AND waiting.pid <> other.pid AND other_stm.query_start < now() -
interval '14 hours' AND other_stm.current_query NOT LIKE '<IDLE>';
And yes, some updates are there for longer then 14 hours.
Now, there's two of those queries in particular - both updating just a
single row. Stuck for over 14 hours (2 days now actually).
I simply cannot believe that single table in the middle of things will
lock stuff up so much.
Also, on the subject of prepared transactions (2PC), the "select *
from pg_prepared_xacts ;" query simply does not reveal anything,
despite the fact that I know that there should be at least two of
those open.
Unless it only list saved transactions, not a transaction in the
middle of operation.
I need these 2PC transactions, in order to achieve something close to
multi-master replication.
But what I think I'll target first, is the triggers updating that
single table on my 'main master'. Unless you guys can suggest
something better.
