Re: BUG #6311: Performance degradation after upgrade - Mailing list pgsql-bugs

From Kevin Grittner
Subject Re: BUG #6311: Performance degradation after upgrade
Date
Msg-id 4ED21FE8020000250004351A@gw.wicourts.gov
Whole thread Raw
In response to BUG #6311: Performance degradation after upgrade  ("Roye Cohen" <roye.cohen@sap.com>)
List pgsql-bugs
"Roye Cohen"  wrote:

> I found a degradation in physical memory used on DB server (~200%
> degradation)

What are you measuring, and what were the numbers?

> and a small degradation in response times on every transaction
> monitored in comparison to 9.0.

What transaction isolation level was used?  Since you have a sap.com
email address, I assume you're probably oriented to larger shops,
which in my experience are more inclined to use stricter transaction
isolation levels (for very good reasons, IMV).  If the transactions
were set to the SERIALIZABLE transaction isolation level, we would
expect slightly slower performance in 9.1 versus 9.0, because it now
provides truly serializable behavior, unlike earlier versions of
PostgreSQL or any production release of Oracle.

If this is the case, you can fall back to legacy behavior by using
the REPEATABLE READ transaction isolation level instead of
SERIALIZABLE.  For further information see these links:

http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html

http://wiki.postgresql.org/wiki/SSI

If this is the cause, please let us know.  I would appreciate knowing
what kind of numbers you're seeing, with a description of the type of
load.  If you can confirm that this is not happening at a less strict
transaction isolation level, like REPEATABLE READ, we can clear this
as "not a bug", since you don't get truly serializable transactions
without cost -- that's why less strict levels exist.

-Kevin

pgsql-bugs by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: BUG #6311: Performance degradation after upgrade
Next
From: Simon Riggs
Date:
Subject: Re: BUG #6307: intarray extention gin index does not work with Hot standby