Thread: 9.1 causing "out of shared memory" error and higher serialization conflicts
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!
