9.1 causing "out of shared memory" error and higher serialization conflicts

From: "Randy Ficker" <randyficker(at)gmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: 9.1 causing "out of shared memory" error and higher serialization conflicts
Date: 2012-03-09 17:16:01
Message-ID: 078501ccfe18$4dffed30$e9ffc790$@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I recently upgraded my production database from 8.4 to 9.1. Ever since the
upgrade, I'm seeing a ton of "out of shared memory" errors as well as a
drastically increased quantity of serialization conflicts ("could not
serialize access due to read/write dependencies among transactions" error).

The "out of shared memory" error gives a hint of "You might need to increase
max_pred_locks_per_transaction." I first tried doubling this from the
default of 64 to 128, then later doubled it again from 128 to 256, but it
has not had any effect on frequency of the error. I've also tried increasing
shared_buffers from its previous value of 1.5gb to 2.5gb, but this also did
not impact the quantity of the errors either.

Nothing has changed except the Postgres version - the workload, schema, and
hardware are all identical. The errors started immediately after the
Postgres upgrade.

Most writing transactions are using the REPEATABLE READ isolation level (the
SERIALIZABLE level is not used at all). If I graph my number of
serialization conflicts, it's clear that the upgrade from 8.4 to 9.1 on
3/2/2012 had an impact. This graph is the number of serialization conflicts
per day, which averaged about 530/day in 8.4 and averages about 15,000/day
in 9.1:

I'm not sure if the higher serialization conflicts are a side-effect of the
"out of shared memory" error, or if the "out of shared memory" error is a
side-effect of the higher rate of serialization conflicts, but I suspect
it's one or the other.

The "out of shared memory" error never happened in 8.4, but it's happening
an average of 21,000 times per day since the upgrade This server processes
approximately 84 transactions per second, so most transactions are
completing successfully. The errors are seen across all queries - it's not
limited to just one, and not limited to just the writing transactions.
Postgres's log doesn't seem to be showing anything useful other than
recording the errors that occur.

version(): PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by
gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit

Configuration: All of the configuration parameters are the default with the
exception of the following: wal_level is set to archive, and archive_mode
and archive_command are set. As mentioned above, in diagnosing this issue
shared_buffers was increased to 2512MB and max_pred_locks_per_transaction
was increased to 256.

Hardware: The machine has 4gb of ram running on Ubuntu 10.04 in a VM on a
Rackspace Cloud Server.

Does anyone have any ideas on what might be causing these issues? Is there
anything I could try, or any other information I can provide? Any help
would be greatly appreciated!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Selena Deckelmann 2012-03-09 17:17:55 Re: Call for Google Summer of Code (GSoC) 2012: Project ideas?
Previous Message John R Pierce 2012-03-09 17:07:23 Re: How to erase transaction logs on PostgreSQL