Postgresql ShareLock problems.

["Svancara, Randall" <rsvancara@xxxxxxx>]

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?  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?

 

2012-11-01 00:14:23 PDT zabbix zabbix 127.0.0.1 - DETAIL:  Process 5983 waits for ShareLock on transaction 48349033; blocked by process 5992.

        Process 5992 waits for ShareLock on transaction 48349079; blocked by process 5983.

        Process 5983: update items set lastclock=1351754051,lastns=230190945,prevvalue=lastvalue,lastvalue='2.692934' where itemid=200589;

        update items set lastclock=1351754050,lastns=329344694,prevvalue=lastvalue,lastvalue='2080' where itemid=200650;

        update items set lastclock=1351754051,lastns=378134858,prevvalue=lastvalue,lastvalue='1' where itemid=200710;

        update items set lastclock=1351754051,lastns=381995730,prevorgvalue='56',prevvalue=lastvalue,lastvalue='0' where itemid=201150;

        update items set lastclock=1351754051,lastns=378331407,prevvalue=lastvalue,lastvalue='Unit: 0 Slot: 0 Port: 3 Gigabit - Level 0x6000001' where itemid=201300;

        update items set lastclock=1351754051,lastns=380237518,prevorgvalue='0',prevvalue=lastvalue,lastvalue='0' where itemid=201350;

        update items set lastclock=1351754051,lastns=378222316,prevvalue=lastvalue,lastvalue='0' where itemid=201670;

        update items set lastclock=1351754050,lastns=319926666,prevvalue=lastvalue,lastvalue='893' where itemid=201970;

        update items set lastclock=1351754051,lastns=393

        Process 5992: update items set lastclock=1351754043,lastns=980316865,prevvalue=lastvalue,lastvalue='65381720064' where itemid=23343;

        update items set lastclock=1351754044,lastns=392778204,prevvalue=lastvalue,lastvalue='887279616' where itemid=23344;

        update items set lastclock=1351754044,lastns=3196233,prevvalue=lastvalue,lastvalue='1351754044' where itemid=23763;

        update items set lastclock=1351754044,lastns=867856940,prevvalue=lastvalue,lastvalue='92.097214' where itemid=23764;

        update items set lastclock=1351754043,lastns=998890879,prevorgvalue='94217753500',prevvalue=lastvalue,lastvalue='14024' where itemid=23943;

        update items set lastclock=1351754044,lastns=865008900,prevorgvalue='0',prevvalue=lastvalue,lastvalue='0' where itemid=23944;

        update items set lastclock=1351754043,lastns=970666655,prevorgvalue='66951216662',prevvalue=lastvalue,lastvalue='605816' where itemid=24003;

        update items set lastclock=1351754044,lastns=871362232,prevvalue=lastvalue,lastvalue='2715914240' where itemid=24004;

        update items set lastclock=13

2012-11-01 00:14:23 PDT zabbix zabbix 127.0.0.1 - HINT:  See server log for query details.

 

Here is our modifications to the default postgresql.conf file:

listen_addresses = '*'

max_connections = 300                   # (change requires restart)

shared_buffers = 40GB                   # min 128kB  #  WSUIT

work_mem = 1024MB                         # WSU IT

maintenance_work_mem = 512MB            # WSU IT

effective_io_concurrency = 4

synchronous_commit = off                # immediate fsync at commit  #  WSUIT

wal_buffers = 1024MB

checkpoint_segments = 512               # in logfile segments, min 1, 16MB each  #  WSUIT

checkpoint_completion_target = 0.9      # checkpoint target duration, 0.0 - 1.0  #  WSUIT

effective_cache_size = 48GB                                         #  WSUIT

logging_collector = on                  # Enable capturing of stderr and csvlog

log_directory = '/var/log/pg_log'       # directory where log files are written, WSUIT

log_filename = 'postgresql-%a.log'      # log file name pattern,

log_truncate_on_rotation = on           # If on, an existing log file of the

log_rotation_size = 100MB               # Automatic rotation of logfiles will

log_min_error_statement = error         # values in order of decreasing detail:

log_min_duration_statement = 1000       # WSUIT -- Log statements over ten seconds

log_line_prefix = '%t %u %d %h - '                      # special values:

log_lock_waits = on                     # WSUIT - Turn on logging waits

autovacuum_max_workers = 6              # WSUIT -- Increase these for our larger tables

datestyle = 'iso, mdy'

lc_messages = 'en_US.UTF-8'                     # locale for system error message

lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting

lc_numeric = 'en_US.UTF-8'                      # locale for number formatting

lc_time = 'en_US.UTF-8'                         # locale for time formatting

 

postgres=# select version();

                                                   version

--------------------------------------------------------------------------------------------------------------

PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

 

Hardware:
12 cpu cores,

96 GB of RAM

4-15K RPM drives in a RAID 1+0

 

TOP output:

 

top - 12:42:20 up 97 days, 21:17,  8 users,  load average: 1.33, 1.19, 1.14

Tasks: 718 total,   7 running, 711 sleeping,   0 stopped,   0 zombie

Cpu(s): 18.7%us,  1.1%sy,  0.0%ni, 80.1%id,  0.0%wa,  0.0%hi,  0.1%si,  0.0%st

Mem:  99060040k total, 96891404k used,  2168636k free,   220860k buffers

Swap: 16777208k total,  1467392k used, 15309816k free, 85556104k cached

 

You can see the load is really low and virtually no I/O waits.