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 AS waiting_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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
