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
