I've run tests (with 50 .. 250 simult users) for some PG project
of my company. 7.1 was 3 times faster than 7.0.3 (fsync) but near
3 times slower than 7.0.3 (nofsync). It was not the best day in
my life - WAL looked like big bottleneck -:(
But finally I've realized that this test makes ~3 FK insertions
... and FK insert means SELECT FOR UPDATE ... and this could
reduce # of commits per fsync.
So, I've run simple test (below) to check this. Seems that 7.1
is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI
triggers is quite bad for performance.
Please take this into account when comparing 7.1 with 7.0.3.
Also, we should add new TODO item: implement dirty reads
and use them in RI triggers.
Vadim
==================================================================
Tables:
PK (p int primary key), p in 1 .. 1000.
FK (f int, foreign key(f) references pk(p)).
First column below - # of users; second - # of FK inserts
per user; next - what values were used in each insert:
either unique (ie there was no users inserting same value -
no waiters on SELECT FOR UPDATE on PK) or some random value
from range.
7.1
50 1000 unique: 250 tps
100 1000 unique: 243 tps50 1000 rand(1 .. 10): 178 tps50 1000 rand(1 .. 5): 108 tps
7.0.3 (nofsync)
50 1000 unique: 157 tps50 1000 rand(1 .. 10): 154 tps50 1000 rand(1 .. 5): 154 tps