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
