Vegard Bønes <vegard.bones@met.no> wrote:
> I tried increasing max_pred_locks_per_transaction by a factor 20,
> and that seems to have helped.
Good to know. After I sent my response I was hoping you wouldn't
take it to imply that increating it by a factor of 10 was
necessarily the useful maximum. It depends mostly on the maximum
number of different pages within a single table you want to track
at a finer-grained resolution than the relation level. If you have
transactions which read a lot of pages from a single table, this
might need to go pretty high.
> is there any reason, beside memory concerns, not to have a very
> high value for max_pred_locks_per_transaction?
No, it is strictly a question of how much memory will be allocated
to the shared memory segment for the purpose of tracking these.
Well, I should say that we have some O(N^2) behavior in tracking
read-write conflicts that should probably be improved -- so if a
large number of predicate locks results in a large number of
read-write conflicts, performance could suffer. Whether you run
into that depends a lot on your workload. I have seen one report
of such an effect in one academic paper which compared various
techniques for implementing truly serializable transactions. The
PostgreSQL implementation still scaled better than any of the
benchmarked alternatives, but they did hit a point where a large
percentage of the time was spent in that area. Limiting the number
of active connections with a transaction-based connection pooler
(like pgbouncer configured in transaction mode) is currently your
best defence against hitting the wall on this issue.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company