| Hey Daniel, This may be entirely unrelated to your query, but we’ve previously experienced issues with 9.4 and crazy multixact members growth. After digging into the issue, we found the culprit was code that would perform the following actions: begin; for query in many_queries: savepoint <x>; select * from table where id=‘myid' for update; update table increment(counter) where id=‘myid'; release savepoint; commit; While I found it very difficult to find documentation on the purpose of multixacts, and I’m certain to have critical misunderstandings around their workings, it seemed that Postgres was inferring each new savepoint as a new potential transaction that would lock the given row, and each of those transactions needed to be added to the on-going multixact members list. The members list (I believe) is immutable, and adding a new member requires construction of a new members list, and so your multixact members offset will grow quadratically with the number of times you lock your row. Where we were locking repeatedly in a large loop, we were seeing huge multixact members growth that would trigger the override for multixact vacuums, regardless of disabling or configuring your autovacuum. If you’re continually seeing multixact members growth that outpaces your vacuums, then I suggest having a scan for the above usage pattern, or alternatively upgrading. We couldn’t replicate the growth in 9.5 and above, as presumably newer Postgres versions correctly no-op when a transaction tries locking a row when its parent is already present in the multixact. This comes with the disclaimer of a Postgres amateur, it simply seemed relevant to your situation. Lawrence
|
