On Thu, Nov 1, 2012 at 12:43 PM, Svancara, Randall <rsvancara@wsu.edu> wrote:
> We are using a third party monitoring solution called Zabbix with Postgresql
> 9.1.We are observing lots of sharelock problems possibly as a result of
> multiple, concurrent updates to the specific table called items.
>
>
> First of all, what can I do to reduce lock contention in Postgresql?
The problem you are seeing is with deadlocks, not with sharelocks.
(The fact that transaction waits are implemented as ShareLocks is an
internal implementation, and perhaps the error message should be
changed so as not to leak that to the user).
The problem is not with contention in general, but just the special
type of contention that leads to cyclic dependencies, i.e. deadlocks.
> Surely
> someone is using postgresql in a high transaction environment supporting
> concurrent updates to the same table? Secondly how do I determine what
> would be appropriate in terms of settings for the postgresql.conf to deal
> with this issue. Third, what you suggest to the Zabbix developers to fix
> such a problem?
I don't think there is anything you can do about it in the config file.
If Zabbix retries the transaction when it gets thrown a deadlock
error, then there is not really a problem, just a cluttering of your
log file. Unless they are happening extremely frequently and creating
performance problems.
It is hard to know why Zabbix is doing what they are doing, so hard to
know what to recommend instead. One possibility is that they are
bunching unrelated real-world transactions into fewer database
transactions in an attempt to improve "group commit" performance. In
which case, they should probably not do that and instead turn
synchronous commit off.
Cheers,
Jeff