Обсуждение: BUG #6311: Performance degradation after upgrade

The following bug has been logged online:

Bug reference:      6311
Logged by:          Roye Cohen
Email address:      roye.cohen@sap.com
PostgreSQL version: 9.1.1
Operating system:   Linux Redhat
Description:        Performance degradation after upgrade
Details:

Hi,

I am a performance engineer. I was conducting a comparison from postgres 9.0
to 9.1.1 in order to upgrade to the newest version.
I found a degradation in physical memory used on DB server (~200%
degradation) and a small degradation in  response times on every transaction
monitored in comparison to 9.0.

The test was done on HP LoadRunner with 300 concurrent users executing a
flow of events.

•The exact sequence of steps is hard to describe but it is important to
state that the executions were exactly the same. Default postgres settings
were used (settings were not changed after installing postgres).


OS Version: Linux 2.6.18-164.el5 x86_64

I am at your service if you have any further inqueries.

Best regards,

Roye

Roye Cohen wrote:
>
> The following bug has been logged online:
>
> Bug reference:      6311
> Logged by:          Roye Cohen
> Email address:      roye.cohen@sap.com
> PostgreSQL version: 9.1.1
> Operating system:   Linux Redhat
> Description:        Performance degradation after upgrade
> Details:
>
> Hi,
>
> I am a performance engineer. I was conducting a comparison from postgres 9.0
> to 9.1.1 in order to upgrade to the newest version.
> I found a degradation in physical memory used on DB server (~200%
> degradation) and a small degradation in  response times on every transaction
> monitored in comparison to 9.0.

I am confused by the word "degradation".  You mean that Postgres 9.1
used 3x more memory than PG 9.0?  How much slower was it?

> The test was done on HP LoadRunner with 300 concurrent users executing a
> flow of events.
>
> The exact sequence of steps is hard to describe but it is important to
> state that the executions were exactly the same. Default postgres settings
> were used (settings were not changed after installing postgres).
>
>
> OS Version: Linux 2.6.18-164.el5 x86_64
>
> I am at your service if you have any further inqueries.

We have certainly never heard of a report like this between PG 9.0 and
9.1, so I am confused.  Was their any change in the client interface
software between the two tests?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

"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