Konstantin>I do not have explanation of performance degradation in case of
particular workload.

A) Mongo Java Client uses a connection-pool of 100 connections by default.
That is it does not follow "connection per client" (in YCSB terms), but it
is capped by 100 connections. I think it can be adjusted by adding
?maxPoolSize=100500 or ?maxpoolsize=100500 to the Mongo URL

I wonder if you could try to vary that parameter and see if it changes
Mongo results.

B) There's a bug in JDBC client of YCSB (it might affect PostgreSQL
results, however I'm not sure if the impact would be noticeable). The
default configuration is readallfields=true, however Jdbc client just
discards the results instead of accessing the columns. I've filed
https://github.com/brianfrankcooper/YCSB/issues/1087 for that.

C) I might miss something, however my local (Macbook) benchmarks show that
PostgreSQL 9.6 somehow uses Limit->Sort->BitmapScan kind of plans.
I have picked a "bad" userid value via auto_explain.
Jdbc client uses prepared statements, so a single bind might spoil the
whole thing causing bad plans for all the values afterwards.
Does it make sense to disable bitmap scan somehow?

For instance:

explain (analyze, buffers) select * From usertable where
YCSB_KEY>='user884845140610037639' order by YCSB_KEY limit 100;
                                                              QUERY PLAN
 Limit  (cost=320.99..321.24 rows=100 width=1033) (actual time=1.408..1.429
rows=100 loops=1)
   Buffers: shared hit=140
   ->  Sort  (cost=320.99..321.33 rows=135 width=1033) (actual
time=1.407..1.419 rows=100 loops=1)
         Sort Key: ycsb_key
         Sort Method: quicksort  Memory: 361kB
         Buffers: shared hit=140
         ->  Bitmap Heap Scan on usertable  (cost=9.33..316.22 rows=135
width=1033) (actual time=0.186..0.285 rows=167 loops=1)
               Recheck Cond: ((ycsb_key)::text >=
               Heap Blocks: exact=137
               Buffers: shared hit=140
               ->  Bitmap Index Scan on usertable_pkey  (cost=0.00..9.29
rows=135 width=0) (actual time=0.172..0.172 rows=167 loops=1)
                     Index Cond: ((ycsb_key)::text >=
                     Buffers: shared hit=3
 Planning time: 0.099 ms
 Execution time: 1.460 ms


explain (analyze, buffers) select * From usertable where
YCSB_KEY>='user184845140610037639' order by YCSB_KEY limit 100;
                                                                QUERY PLAN
 Limit  (cost=0.28..89.12 rows=100 width=1033) (actual time=0.174..0.257
rows=100 loops=1)
   Buffers: shared hit=102
   ->  Index Scan using usertable_pkey on usertable  (cost=0.28..2154.59
rows=2425 width=1033) (actual time=0.173..0.246 rows=100 loops=1)
         Index Cond: ((ycsb_key)::text >= 'user184845140610037639'::text)
         Buffers: shared hit=102
 Planning time: 0.105 ms
 Execution time: 0.277 ms


Reply via email to