Horton
Menu

Thread

  1. PostgreSQL 7.3.3 and Intel C compiler

    Hans-Juergen Schoenig <hs@cybertec.at> — 2003-07-05T05:48:53Z

    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

  2. Re: PostgreSQL 7.3.3 and Intel C compiler

    Gaetano Mendola <mendola@bigfoot.com> — 2003-07-06T02:48:49Z

    "Hans-Jürgen Schönig" <hs@cybertec.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

  3. Re: PostgreSQL 7.3.3 and Intel C compiler

    Sergey <sergey@optimaltec.com> — 2003-07-06T03:25:15Z

    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.
>

  4. Re: PostgreSQL 7.3.3 and Intel C compiler

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-21T03:56:51Z

    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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  5. Re: PostgreSQL 7.3.3 and Intel C compiler

    Hans-Juergen Schoenig <hs@cybertec.at> — 2003-07-21T15:57:40Z

    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

  6. Re: PostgreSQL 7.3.3 and Intel C compiler

    Gavin Sherry <swm@linuxworld.com.au> — 2003-07-22T05:47:40Z

    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

  7. Re: PostgreSQL 7.3.3 and Intel C compiler

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-07-22T06:04:33Z

    =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.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

  8. Re: PostgreSQL 7.3.3 and Intel C compiler

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-22T16:01:33Z

    Tom Lane wrote:
> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs@cybertec.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  9. Re: PostgreSQL 7.3.3 and Intel C compiler

    Hans-Juergen Schoenig <hs@cybertec.at> — 2003-07-22T16:52:17Z

    > 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

  10. Re: PostgreSQL 7.3.3 and Intel C compiler

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-22T16:56:28Z

    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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  11. Re: PostgreSQL 7.3.3 and Intel C compiler

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-07-22T18:15:21Z

    Bruce Momjian <pgman@candle.pha.pa.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

  12. Re: PostgreSQL 7.3.3 and Intel C compiler

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-22T20:23:39Z

    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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  13. Re: PostgreSQL 7.3.3 and Intel C compiler

    Hans-Juergen Schoenig <hs@cybertec.at> — 2003-07-22T20:24:28Z

    > 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

  14. Unsubscribe

    Steven Vajdic <svajdic@asc.corp.mot.com> — 2003-07-23T04:37:29Z