Re: PostgreSQL 7.3.3 and Intel C compiler

This week I have done some performance tuning at a customer's office. We
have beaten (demoralized) MS SQL and DB2 in serializable mode and DB2 in
any transaction isolation level :).

By the way: In case of very simple statements SERIALIZABLE is about 3
times faster than READ COMMITTED. I expected it to be faster but I did
not expect this difference.

We have used Peter's fix which makes it possible to compile PostgreSQL
with Intel's C compiler. PostgreSQL built nicely (just some nasty
warnings). We have tries to run our benchmark (mostly simple statements
and cursor work) on this version of PostgreSQL but the database cluster
corrupted almost instantly. Although PostgreSQL compiled nicely we
could not get the system running without corrupting something.
Maybe this behaviour should be varified by somebody else and documented
somewhere (maybe a FAQ?).

Regards,

Hans

--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

"Hans-Jürgen Schönig" <hs(at)cybertec(dot)at> wrote:
> We have used Peter's fix which makes it possible to compile PostgreSQL
> with Intel's C compiler. PostgreSQL built nicely (just some nasty
> warnings). We have tries to run our benchmark (mostly simple statements
> and cursor work) on this version of PostgreSQL but the database cluster
> corrupted almost instantly.

Did you compile it with -O3 optimization ?
If it this the case try with -O2, some times the -O3 optimization generate
inconsistent code.

Regards
Gaetano Mendola

Hi!

I'm sorry I haven't done any research beforehand, but are any such
materials regarding performance tuning available in the canned form on
the 'net for me to show to my boss? I believe statements like this will
greatly promote PostgreSQL if substantiated with concise "executive
summary" articles and maybe some short technical details for
administrators. Thank you!

Sergey.

Hans-Jürgen Schönig wrote:
> This week I have done some performance tuning at a customer's office. We
> have beaten (demoralized) MS SQL and DB2 in serializable mode and DB2 in
> any transaction isolation level :).
>
> By the way: In case of very simple statements SERIALIZABLE is about 3
> times faster than READ COMMITTED. I expected it to be faster but I did
> not expect this difference.
>

Hans-Jrgen Schnig wrote:
> This week I have done some performance tuning at a customer's office. We
> have beaten (demoralized) MS SQL and DB2 in serializable mode and DB2 in
> any transaction isolation level :).
>
> By the way: In case of very simple statements SERIALIZABLE is about 3
> times faster than READ COMMITTED. I expected it to be faster but I did
> not expect this difference.

Why was SERIALIZABLE faster? I know SERIALIZABLE doesn't have the
rollback penalty in read-only queries, but I don't understand why it
would be faster.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Hans-J?rgen Sch?nig wrote:
>
>>This week I have done some performance tuning at a customer's office. We
>>have beaten (demoralized) MS SQL and DB2 in serializable mode and DB2 in
>>any transaction isolation level :).
>>
>>By the way: In case of very simple statements SERIALIZABLE is about 3
>>times faster than READ COMMITTED. I expected it to be faster but I did
>>not expect this difference.
>
>
> Why was SERIALIZABLE faster? I know SERIALIZABLE doesn't have the
> rollback penalty in read-only queries, but I don't understand why it
> would be faster.
>

To be honest I don't have the slightest idea. Maybe it has to do with
snapshotting but I don't know precisely. In case of SERIALIZABLE all
snapshots inside a transaction are the same - maybe this makes the big
difference. I have no other explanation for that.

There is one nifty detail which seems VERY strange to me: If
serializable mode is set in postgresql.conf the system was 3 times
faster (~ 7.5 sec. vs. 2.5sec). If serializable mode was set for every
transaction (using set at the beginning of the transaction) serializable
mode was as fast as read committed.

We have done 90% cursor work and very simple queries (mostly queries
such as "DECLARE CURSOR x FOR SELECT * FROM ... WHERE a = b").
I have no idea why PostgreSQL behaves like that but it seems to be a
really good tweak because in this mode we beat any other database
including SQL server on Windows 2003 (2.9sec) and IBM DB2 on Linux (12.6
seconds).
I am sorry but I cannot provide you the tools we have used because we
have a non disclosure agreement with the customer. I will try to verify
this with my machines and a simple self-made benchmark.

Regards,

Hans

--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

On Mon, 21 Jul 2003, [ISO-8859-1] Hans-Jrgen Schnig wrote:

> > Why was SERIALIZABLE faster? I know SERIALIZABLE doesn't have the
> > rollback penalty in read-only queries, but I don't understand why it
> > would be faster.
> >
>
>
> To be honest I don't have the slightest idea. Maybe it has to do with
> snapshotting but I don't know precisely. In case of SERIALIZABLE all
> snapshots inside a transaction are the same - maybe this makes the big
> difference. I have no other explanation for that.
>
> There is one nifty detail which seems VERY strange to me: If
> serializable mode is set in postgresql.conf the system was 3 times
> faster (~ 7.5 sec. vs. 2.5sec). If serializable mode was set for every
> transaction (using set at the beginning of the transaction) serializable
> mode was as fast as read committed.

Hmm.

>
> We have done 90% cursor work and very simple queries (mostly queries
> such as "DECLARE CURSOR x FOR SELECT * FROM ... WHERE a = b").
> I have no idea why PostgreSQL behaves like that but it seems to be a
> really good tweak because in this mode we beat any other database
> including SQL server on Windows 2003 (2.9sec) and IBM DB2 on Linux (12.6
> seconds).

Are you testing the same type of cursors? Cursors do not behave
according to READ COMMITTED principles under Postgres. What are the
results for READ ONLY INSENSITIVE cursors with DB2 and MS SQL?

Thanks,

Gavin

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs(at)cybertec(dot)at> writes:
> There is one nifty detail which seems VERY strange to me: If
> serializable mode is set in postgresql.conf the system was 3 times
> faster (~ 7.5 sec. vs. 2.5sec). If serializable mode was set for every
> transaction (using set at the beginning of the transaction) serializable
> mode was as fast as read committed.

Seems pretty strange to me too. I can believe that taking a new
snapshot for each command (as READ COMMITTED mode does) might take a
significant amount of time, especially if you have a large number of
backends connected. (I think the time to get the snapshot data is
linear in the number of live backends; also there is the possibility
of contention on the PROC array when multiple backends need to fetch
snapshots at the same time.) But if that's where the performance
difference is, it wouldn't matter whether you start in serializable
mode by default or issue a SET command to select it.

> I am sorry but I cannot provide you the tools we have used because we
> have a non disclosure agreement with the customer. I will try to verify
> this with my machines and a simple self-made benchmark.

Please try to create a self-contained test case to demonstrate this
behavior. I'd like to try to profile it...

regards, tom lane

Tom Lane wrote:
> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs(at)cybertec(dot)at> writes:
> > There is one nifty detail which seems VERY strange to me: If
> > serializable mode is set in postgresql.conf the system was 3 times
> > faster (~ 7.5 sec. vs. 2.5sec). If serializable mode was set for every
> > transaction (using set at the beginning of the transaction) serializable
> > mode was as fast as read committed.
>
> Seems pretty strange to me too. I can believe that taking a new
> snapshot for each command (as READ COMMITTED mode does) might take a
> significant amount of time, especially if you have a large number of
> backends connected. (I think the time to get the snapshot data is
> linear in the number of live backends; also there is the possibility
> of contention on the PROC array when multiple backends need to fetch
> snapshots at the same time.) But if that's where the performance
> difference is, it wouldn't matter whether you start in serializable
> mode by default or issue a SET command to select it.

But the snapshots only are grabbing the xids from each proc, right?
Doesn't seem that would take very long.

If this is the bottleneck, maybe we need a shared proc lock.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> But the snapshots only are grabbing the xids from each proc, right?
> Doesn't seem that would take very long.
>
> If this is the bottleneck, maybe we need a shared proc lock.
>

I had a hard day testing and verifying this kind of stuff. We have run
several hundred benchmarks at the customer using many different
settings. SERIALIZABLE was the key to high-performance. I have run
dozens of different benchmarks today (cursors, simple selects,
concurrent stuff, ...). I have not found a difference. I have no idea
why the customer's system was so much faster in SERIALIZABLE mode. They
use a native C++ implementation of the FE/BE protocol but as far as I
have seen their database layer does not care about transaction isolation
too much.
I will continue testing this kind of stuff because this is a very
strange yet important issue.
I will try to get some code from the customer. This is mostly
non-disclosure stuff so I am not sure what we can use. I just wanted to
ask if somebody has a reasonable explanation and if somebody can verify
this behaviour.

Maybe we will find the reason some day :(.
Sorry that I cannot provide more information at the moment.

Regards,

Hans

--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

Hans-Jrgen Schnig wrote:
> > But the snapshots only are grabbing the xids from each proc, right?
> > Doesn't seem that would take very long.
> >
> > If this is the bottleneck, maybe we need a shared proc lock.
> >
>
>
> I had a hard day testing and verifying this kind of stuff. We have run
> several hundred benchmarks at the customer using many different
> settings. SERIALIZABLE was the key to high-performance. I have run
> dozens of different benchmarks today (cursors, simple selects,
> concurrent stuff, ...). I have not found a difference. I have no idea
> why the customer's system was so much faster in SERIALIZABLE mode. They
> use a native C++ implementation of the FE/BE protocol but as far as I
> have seen their database layer does not care about transaction isolation
> too much.

They do the backend protocol using a custom implementation. Why would
they do that?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> But the snapshots only are grabbing the xids from each proc, right?
> Doesn't seem that would take very long.

Yeah, we've never seen any previous indication that grabbing a snapshot
is a bottleneck. Also, I think it uses a shared (read) lock, so there
shouldn't really be contention, unless perhaps they are starting and
stopping backends very frequently as well.

> If this is the bottleneck, maybe we need a shared proc lock.

We need to understand what's happening before speculating about how to
fix it ...

regards, tom lane

Well, it seems it would be easier for them to just improve our existing
libpq and send us the patches, rather than rewriting it from scratch.

---------------------------------------------------------------------------

Hans-Jrgen Schnig wrote:
> > They do the backend protocol using a custom implementation. Why would
> > they do that?
> >
>
>
> It seems as if their implemenation provides 20% more throughput. I
> haven't benchmarked with lib pq personally so I cannot tell you more.
>
>
> Hans
>
>
> --
> Cybertec Geschwinde u Schoenig
> Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
> Tel: +43/2952/30706; +43/664/233 90 75
> www.cybertec.at, www.postgresql.at, kernel.cybertec.at
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> They do the backend protocol using a custom implementation. Why would
> they do that?
>

It seems as if their implemenation provides 20% more throughput. I
haven't benchmarked with lib pq personally so I cannot tell you more.

Hans

--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at