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

Started by Randy Fickerabout 14 years ago8 messagesgeneral

randyficker@gmail.com

about 14 years ago

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!

Marti Raudsepp

marti@juffo.org

about 14 years ago

In reply to: Randy Ficker (#1)

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

On Fri, Mar 9, 2012 at 19:16, Randy Ficker <randyficker@gmail.com> wrote:

Most writing transactions are using the REPEATABLE READ isolation
level (the SERIALIZABLE level is not used at all).

Are you 100% sure about this? A major thing that changed in 9.1 was
implementation for proper SERIALIZABLE isolation, which could indeed
cause the sort of errors you described. Previously, asking for
SERIALIZABLE level gave you REPEATABLE READ.

As far as I can tell, the max_pred_locks_per_transaction setting is
irrelevant for isolation levels lower than SERIALIZABLE.

What's your default_transaction_isolation set to?

Regards,
Marti

Randy Ficker

randyficker@gmail.com

about 14 years ago

In reply to: Marti Raudsepp (#2)

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

Hey Marti,

I almost replied that yes, I was 100% sure, since I know my code requests the REPEATABLE READ level. However, I figured before I replied, I should double-check the SQL statements that were being sent to Postgres.

Then I found this gem in Npgsql:

if (isolation == IsolationLevel.RepeatableRead || isolation == IsolationLevel.Serializable || isolation == IsolationLevel.Snapshot)
{
commandText.Append("SERIALIZABLE");
}

*headslap*. I know this code is fine for 8, but I still would not have expected this code to exist in the driver itself instead of just letting Postgres do the switch. I guess Npgsql says right on their front page "Works with Postgresql 7.x and 8.x" so I shouldn't have assumed it'd behave correctly with 9.

So you're right, it turns out I was using SERIALIZABLE after all. I'm going to fix this right away. Thanks for the reply!

-----Original Message-----
From: Marti Raudsepp [mailto:marti@juffo.org]
Sent: Friday, March 09, 2012 9:41 AM
To: Randy Ficker
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 9.1 causing "out of shared memory" error and higher serialization conflicts

On Fri, Mar 9, 2012 at 19:16, Randy Ficker <randyficker@gmail.com> wrote:

Most writing transactions are using the REPEATABLE READ isolation
level (the SERIALIZABLE level is not used at all).

Are you 100% sure about this? A major thing that changed in 9.1 was implementation for proper SERIALIZABLE isolation, which could indeed cause the sort of errors you described. Previously, asking for SERIALIZABLE level gave you REPEATABLE READ.

As far as I can tell, the max_pred_locks_per_transaction setting is irrelevant for isolation levels lower than SERIALIZABLE.

What's your default_transaction_isolation set to?

Regards,
Marti

Francisco Figueiredo Jr.

francisco@npgsql.org

about 14 years ago

In reply to: Randy Ficker (#3)

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

Thanks for the heads up.

I'll fix that in Npgsql so it sends the correct isolation level when
running on 9.1+

Sent from my Android phone
On Mar 9, 2012 3:27 PM, "Randy Ficker" <randyficker@gmail.com> wrote:

Show quoted text

Hey Marti,

I almost replied that yes, I was 100% sure, since I know my code requests
the REPEATABLE READ level. However, I figured before I replied, I should
double-check the SQL statements that were being sent to Postgres.

Then I found this gem in Npgsql:

if (isolation == IsolationLevel.RepeatableRead || isolation ==
IsolationLevel.Serializable || isolation == IsolationLevel.Snapshot)
{
commandText.Append("SERIALIZABLE");
}

*headslap*. I know this code is fine for 8, but I still would not have
expected this code to exist in the driver itself instead of just letting
Postgres do the switch. I guess Npgsql says right on their front page
"Works with Postgresql 7.x and 8.x" so I shouldn't have assumed it'd behave
correctly with 9.

So you're right, it turns out I was using SERIALIZABLE after all. I'm
going to fix this right away. Thanks for the reply!

-----Original Message-----
From: Marti Raudsepp [mailto:marti@juffo.org]
Sent: Friday, March 09, 2012 9:41 AM
To: Randy Ficker
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 9.1 causing "out of shared memory" error and higher
serialization conflicts

On Fri, Mar 9, 2012 at 19:16, Randy Ficker <randyficker@gmail.com> wrote:

Most writing transactions are using the REPEATABLE READ isolation
level (the SERIALIZABLE level is not used at all).

Are you 100% sure about this? A major thing that changed in 9.1 was
implementation for proper SERIALIZABLE isolation, which could indeed cause
the sort of errors you described. Previously, asking for SERIALIZABLE level
gave you REPEATABLE READ.

As far as I can tell, the max_pred_locks_per_transaction setting is
irrelevant for isolation levels lower than SERIALIZABLE.

What's your default_transaction_isolation set to?

Regards,
Marti

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Randy Ficker

randyficker@gmail.com

about 14 years ago

In reply to: Francisco Figueiredo Jr. (#4)

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

After deploying a fixed version of Npgsql, the error frequency went straight back down to the 8.4 level. Awesome!

Thanks for the quick replies guys!

From: francisco.figueiredo.jr@gmail.com [mailto:francisco.figueiredo.jr@gmail.com] On Behalf Of Francisco Figueiredo Jr.
Sent: Friday, March 09, 2012 10:36 AM
To: Randy Ficker
Cc: pgsql-general@postgresql.org; Marti Raudsepp
Subject: Re: [GENERAL] 9.1 causing "out of shared memory" error and higher serialization conflicts

Thanks for the heads up.

I'll fix that in Npgsql so it sends the correct isolation level when running on 9.1+

Sent from my Android phone

On Mar 9, 2012 3:27 PM, "Randy Ficker" <randyficker@gmail.com> wrote:

Hey Marti,

Then I found this gem in Npgsql:

if (isolation == IsolationLevel.RepeatableRead || isolation == IsolationLevel.Serializable || isolation == IsolationLevel.Snapshot)
{
commandText.Append("SERIALIZABLE");
}

So you're right, it turns out I was using SERIALIZABLE after all. I'm going to fix this right away. Thanks for the reply!

On Fri, Mar 9, 2012 at 19:16, Randy Ficker <randyficker@gmail.com> wrote:

Most writing transactions are using the REPEATABLE READ isolation
level (the SERIALIZABLE level is not used at all).

Are you 100% sure about this? A major thing that changed in 9.1 was implementation for proper SERIALIZABLE isolation, which could indeed cause the sort of errors you described. Previously, asking for SERIALIZABLE level gave you REPEATABLE READ.

As far as I can tell, the max_pred_locks_per_transaction setting is irrelevant for isolation levels lower than SERIALIZABLE.

What's your default_transaction_isolation set to?

Regards,
Marti

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Francisco Figueiredo Jr.

francisco@npgsql.org

about 14 years ago

In reply to: Randy Ficker (#5)

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

Would you mind to fill a bug report about that and also provide your fix so
we can apply in the main codebase?
Thanks in advance!

Sent from my Android phone
On Mar 9, 2012 5:12 PM, "Randy Ficker" <randyficker@gmail.com> wrote:

Show quoted text

After deploying a fixed version of Npgsql, the error frequency went
straight back down to the 8.4 level. Awesome!****

** **

Thanks for the quick replies guys!****

** **

*From:* francisco.figueiredo.jr@gmail.com [mailto:
francisco.figueiredo.jr@gmail.com] *On Behalf Of *Francisco Figueiredo Jr.
*Sent:* Friday, March 09, 2012 10:36 AM
*To:* Randy Ficker
*Cc:* pgsql-general@postgresql.org; Marti Raudsepp
*Subject:* Re: [GENERAL] 9.1 causing "out of shared memory" error and
higher serialization conflicts****

** **

Thanks for the heads up. ****

I'll fix that in Npgsql so it sends the correct isolation level when
running on 9.1+ ****

Sent from my Android phone****

On Mar 9, 2012 3:27 PM, "Randy Ficker" <randyficker@gmail.com> wrote:****

Hey Marti,

I almost replied that yes, I was 100% sure, since I know my code requests
the REPEATABLE READ level. However, I figured before I replied, I should
double-check the SQL statements that were being sent to Postgres.

Then I found this gem in Npgsql:

if (isolation == IsolationLevel.RepeatableRead || isolation ==
IsolationLevel.Serializable || isolation == IsolationLevel.Snapshot)
{
commandText.Append("SERIALIZABLE");
}

*headslap*. I know this code is fine for 8, but I still would not have
expected this code to exist in the driver itself instead of just letting
Postgres do the switch. I guess Npgsql says right on their front page
"Works with Postgresql 7.x and 8.x" so I shouldn't have assumed it'd behave
correctly with 9.

So you're right, it turns out I was using SERIALIZABLE after all. I'm
going to fix this right away. Thanks for the reply!

-----Original Message-----
From: Marti Raudsepp [mailto:marti@juffo.org]
Sent: Friday, March 09, 2012 9:41 AM
To: Randy Ficker
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 9.1 causing "out of shared memory" error and higher
serialization conflicts

On Fri, Mar 9, 2012 at 19:16, Randy Ficker <randyficker@gmail.com> wrote:

Most writing transactions are using the REPEATABLE READ isolation
level (the SERIALIZABLE level is not used at all).

Are you 100% sure about this? A major thing that changed in 9.1 was
implementation for proper SERIALIZABLE isolation, which could indeed cause
the sort of errors you described. Previously, asking for SERIALIZABLE level
gave you REPEATABLE READ.

As far as I can tell, the max_pred_locks_per_transaction setting is
irrelevant for isolation levels lower than SERIALIZABLE.

What's your default_transaction_isolation set to?

Regards,
Marti

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general****

Randy Ficker

randyficker@gmail.com

about 14 years ago

In reply to: Francisco Figueiredo Jr. (#6)

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

Filed: http://pgfoundry.org/tracker/index.php?func=detail <http://pgfoundry.org/tracker/index.php?func=detail&aid=1011174&group_id=1000140&atid=590> &aid=1011174&group_id=1000140&atid=590

From: francisco.figueiredo.jr@gmail.com [mailto:francisco.figueiredo.jr@gmail.com] On Behalf Of Francisco Figueiredo Jr.
Sent: Friday, March 09, 2012 12:41 PM
To: Randy Ficker
Cc: Marti Raudsepp; pgsql-general@postgresql.org
Subject: RE: [GENERAL] 9.1 causing "out of shared memory" error and higher serialization conflicts

Would you mind to fill a bug report about that and also provide your fix so we can apply in the main codebase?
Thanks in advance!

Sent from my Android phone

On Mar 9, 2012 5:12 PM, "Randy Ficker" <randyficker@gmail.com> wrote:

After deploying a fixed version of Npgsql, the error frequency went straight back down to the 8.4 level. Awesome!

Thanks for the quick replies guys!

Thanks for the heads up.

I'll fix that in Npgsql so it sends the correct isolation level when running on 9.1+

Sent from my Android phone

On Mar 9, 2012 3:27 PM, "Randy Ficker" <randyficker@gmail.com> wrote:

Hey Marti,

Then I found this gem in Npgsql:

if (isolation == IsolationLevel.RepeatableRead || isolation == IsolationLevel.Serializable || isolation == IsolationLevel.Snapshot)
{
commandText.Append("SERIALIZABLE");
}

So you're right, it turns out I was using SERIALIZABLE after all. I'm going to fix this right away. Thanks for the reply!

On Fri, Mar 9, 2012 at 19:16, Randy Ficker <randyficker@gmail.com> wrote:

Most writing transactions are using the REPEATABLE READ isolation
level (the SERIALIZABLE level is not used at all).

Are you 100% sure about this? A major thing that changed in 9.1 was implementation for proper SERIALIZABLE isolation, which could indeed cause the sort of errors you described. Previously, asking for SERIALIZABLE level gave you REPEATABLE READ.

As far as I can tell, the max_pred_locks_per_transaction setting is irrelevant for isolation levels lower than SERIALIZABLE.

What's your default_transaction_isolation set to?

Regards,
Marti

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Francisco Figueiredo Jr.

francisco@npgsql.org

about 14 years ago

In reply to: Randy Ficker (#7)

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

Thanks!
I'll make the changes to Npgsql source code.

Sent from my Android phone
On Mar 9, 2012 7:17 PM, "Randy Ficker" <randyficker@gmail.com> wrote:

Show quoted text

Filed:
http://pgfoundry.org/tracker/index.php?func=detail&aid=1011174&group_id=1000140&atid=590
****

** **

*From:* francisco.figueiredo.jr@gmail.com [mailto:
francisco.figueiredo.jr@gmail.com] *On Behalf Of *Francisco Figueiredo Jr.
*Sent:* Friday, March 09, 2012 12:41 PM
*To:* Randy Ficker
*Cc:* Marti Raudsepp; pgsql-general@postgresql.org
*Subject:* RE: [GENERAL] 9.1 causing "out of shared memory" error and
higher serialization conflicts****

** **

Would you mind to fill a bug report about that and also provide your fix
so we can apply in the main codebase?
Thanks in advance! ****

Sent from my Android phone****

On Mar 9, 2012 5:12 PM, "Randy Ficker" <randyficker@gmail.com> wrote:****

After deploying a fixed version of Npgsql, the error frequency went
straight back down to the 8.4 level. Awesome!****

****

Thanks for the quick replies guys!****

****

*From:* francisco.figueiredo.jr@gmail.com [mailto:
francisco.figueiredo.jr@gmail.com] *On Behalf Of *Francisco Figueiredo Jr.
*Sent:* Friday, March 09, 2012 10:36 AM
*To:* Randy Ficker
*Cc:* pgsql-general@postgresql.org; Marti Raudsepp
*Subject:* Re: [GENERAL] 9.1 causing "out of shared memory" error and
higher serialization conflicts****

****

Thanks for the heads up. ****

I'll fix that in Npgsql so it sends the correct isolation level when
running on 9.1+ ****

Sent from my Android phone****

On Mar 9, 2012 3:27 PM, "Randy Ficker" <randyficker@gmail.com> wrote:****

Hey Marti,

I almost replied that yes, I was 100% sure, since I know my code requests
the REPEATABLE READ level. However, I figured before I replied, I should
double-check the SQL statements that were being sent to Postgres.

Then I found this gem in Npgsql:

if (isolation == IsolationLevel.RepeatableRead || isolation ==
IsolationLevel.Serializable || isolation == IsolationLevel.Snapshot)
{
commandText.Append("SERIALIZABLE");
}

*headslap*. I know this code is fine for 8, but I still would not have
expected this code to exist in the driver itself instead of just letting
Postgres do the switch. I guess Npgsql says right on their front page
"Works with Postgresql 7.x and 8.x" so I shouldn't have assumed it'd behave
correctly with 9.

So you're right, it turns out I was using SERIALIZABLE after all. I'm
going to fix this right away. Thanks for the reply!

-----Original Message-----
From: Marti Raudsepp [mailto:marti@juffo.org]
Sent: Friday, March 09, 2012 9:41 AM
To: Randy Ficker
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 9.1 causing "out of shared memory" error and higher
serialization conflicts

On Fri, Mar 9, 2012 at 19:16, Randy Ficker <randyficker@gmail.com> wrote:

Most writing transactions are using the REPEATABLE READ isolation
level (the SERIALIZABLE level is not used at all).

Are you 100% sure about this? A major thing that changed in 9.1 was
implementation for proper SERIALIZABLE isolation, which could indeed cause
the sort of errors you described. Previously, asking for SERIALIZABLE level
gave you REPEATABLE READ.

As far as I can tell, the max_pred_locks_per_transaction setting is
irrelevant for isolation levels lower than SERIALIZABLE.

What's your default_transaction_isolation set to?

Regards,
Marti

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general****

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

Attachments: