I have obtained more results with YCSB benchmark and built-in connection
pooling.
Explanation of the benchmark and all results for vanilla Postgres and
Mongo are available in Oleg Bartunov presentation about JSON (at the
end of presentation):
http://www.sai.msu.su/~megera/postgres/talks/sqljson-pgconf.eu-2017.pdf
as you can see, Postgres shows significant slow down with increasing
number of connections in case of conflicting updates.
Built-in connection pooling can somehow eliminate this problem:
Workload-B (5% of updates) ops/sec:
Session pool size/clients
250
500
750
1000
0
151511
78078
48742
30186
32
522347
543863
546971
540462
64
736323
770010
763649
763358
128
245167
241377
243322
232482
256
144964
146723
149317
141049
Here the maximum is obtained near 70 backends which corresponds to the
number of physical cores at the target system.
But for workload A (50% of updates), optimum is achieved at much smaller
number of backends, after which we get very fast performance degradation:
Session pool size
kops/sec
16
220
30
353
32
362
40
120
70
53
256
20
Here the maximum is reached at 32 backends and with 70 backends
performance is 6 times worser.
It means that it is difficult to find optimal size of session pool if we
have varying workload.
If we set it too large, then we get high contention of conflicting
update queries, if it is too small, then we do not utilize all system
resource on read-only or not conflicting queries.
Look like we have to do something with Postgres locking mechanism and
may be implement some contention aware scheduler as described here:
http://www.vldb.org/pvldb/vol11/p648-tian.pdf
But this is a different story, not related to built-in connection pooling.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
