Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
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-Jürgen Schönig 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
Bruce Momjian <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
Hans-Jürgen Schönig 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
Tom Lane wrote: > =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
On Mon, 21 Jul 2003, [ISO-8859-1] Hans-Jürgen Schönig 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
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. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
"Hans-Jürgen Schönig" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org