Over a million developers have joined DZone.

How Couchbase Won YCSB

See what optimizations Couchbase put in place to enhance performance as much as possible when running YCSB.

· Database Zone

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

We chose to run YCSB and do the other performance work in Couchbase 4.5. Not because it's easy, but because it's hard; because that goal served us to organize and measure our effort and features and hold them to a high standard.

Like all the good benchmarksYCSB is simple to support and run. Most NoSQL can run YCSB benchmarks. The difficulty is in getting those numbers high. Just like the TPC wars in 90s, the NoSQL market is going through its YCSB wars to prove the performance and scalability.

Cihan Biyikoglu has discussed the combination of technologies within Couchbase helping customers scale and perform. In this article, we'll focus on Workload-E in YCSB — How Couchbase is architected and enhanced in Couchbase 4.5 to win this round of YCSB against MongoDB.

Image titleFigure 1 Couchbase Architecture.

To put it simply, Couchbase is a distributed database. Each of the critical services — Data, Query, Index — can be scaled up and scaled out. How you scale is your choice.

We used workload-A and workload-E to measure the performance. In this article, we'll focus on workload-E. The workload-E simulates queries for threaded conversations, where each scan is for the posts in a given thread (assumed to be clustered by thread ID). In all, 95% of the operations are scans and 5% of the operations are inserts to the database.

The short scan operations can be written in SQL with the following query:


SELECT field0, field1, field2, field3,
      field4, field5, field6, field7,
      Field8, field9
FROM  ycsb
WHERE primary_key > "xyz1028"
ORDER BY primary_key
LIMIT 50;



In Couchbase, the YCSB data is represented as the following:


DocumentKey:  "user4052466453699787802"
{
"field0": "(W342\u003e?5f.Q;6S{+;t30h#Vc:_.7\u003e5!~3Q)0?h#\"r77j+H%89:%\u003cb\"Ss:56\"G=94\u003e(Q'!Yi57f+@k;H-%+v4J;.:4U\u00261 *1\u003c=",
"field1": "8/t:3|0,d\"@i6^+452\u0026Ly3108,|+2v?D%,2$%_5$Uc\u0026=l:Zm)Hk3*02Ak*$| ;\"-8r+2*\u003cA?#N#!84%\u0026~6X656!Ms?+`;/`'Cg6",
"field2": "+J;5Nc\u003e5h\"/f1+l?.v.\u003e\"-4p!Rq\u003eY53;\u003e4Pg9We*!6;]}7A)8\u0026`\u003eWg+\\1-Nq?Wk:,\u003e$Ae3\u003c4\u003c7~+\u0026,/ d*Y!5?(=@k\u00269|'F53D!$",
"field3": ",\u003c`'-\u003e;2*1Du#A5\u003cA36/d6%:*!\u003c -*?;4!% )\\o'\"j2,28\u003ez1Ze;Ve:Ci\"+,*'.4^+,9f+Rk7Vo/Y'5h*7n2L398p\u003c=l'+|3Ru7",
"field4": "36t?Po!5.%Jk6\"l':8=48$P5*H/4F/#'4 %0,T}'9z##j6' -\\!!2n958\u003c7d0%r*44\u003e(f/[a?#69]w((f.No+U)0M52+\u003c-D/*W30",
"field5": ",Fu+182 40?$?$:)2:))6$Gg Yk6',\u003e,\"5Cw%9\u0026,'p.^#%7p4Ps/Vy-[;$Ak9!l\u003e%8\u003eI)46l/]g2Go(-\u003e= x;]!\u003c2~\"B!0Bi)\u003c.4",
"field6": "+2h;@m,)0,7n-Iw/W/79b7Y{*% 8\u00268\u003e:r'X/,#08\":7Fm72\u003c/Dw7Ja %l',\"7I+\u003cEe?;4% v%\u0026,$- ;$\u0026,%86 ~3J7\u0026?n*O#2S?8",
"field7": ",/\u003e4 (,L}\u00263h5/j2(,1Q+1A)4$\u003e\u003c+.,;d6@;*Yi;Ou-\u00260%?\"3Ua;\\k8P'$\"d#B7$\"*-S1(8 !Vy*Kg;^e2N);?. Ke1Eg\u003eVo\"R=(",
"field8": "52p\";:\u003eBu\u0026D/0O/ ?0='d3%\"'G+\u003c z.Gg\u003eIy!Xe?D-6$0%Za 1:%(01.6%F/#\u003e\u003c(6\u00265\\)8_i:(\u003c5Oo+?p7+l?Ym6\"|)\u0026\u003c\u003c(\u003e4,,-",
"field9": "#G=($:6X%%Z14,v#6r;#l/9x04*8 h,N3-Za4Da##`')t.\u003e(-(p#C:L%= d(S2H/\"P}/%0/1j/=h0Q1 )2\u003c12(_y#P!$82(\\=!"
}


The Couchbase port for YCSB is available at: https://github.com/brianfrankcooper/YCSB/tree/master/couchbase2

Below is the actual query in Couchbase. The meta().id expression in the query refers to the document key of the document. In Couchbase, each document in a bucket will have a unique key, referred to as document key.


SELECT field0, field1, field2, field3,
      field4, field5, field6, field7,
      Field8, field9xz
FROM  ycsb
WHERE meta().id >= "xyz1028"
ORDER BY meta().id
LIMIT 50;


Once you load the data, you simply create the following index. You're ready to run the YCSB workload E.


CREATE INDEX meta_i1 ON ycsb(meta().id);


When we ran this on Couchbase 4.0, we knew we had our work cut out. In this article, we'll walk through the optimizations done in 4.5 to improve this query. What we've done for this query applies to your index design, resource usage, and performance of your application queries.

In 4.5, when you execute this query, here is the plan used:


[
 {
   "plan": {
     "#operator": "Sequence",
     "~children": [
       {
         "#operator": "Sequence",
         "~children": [
           {
             "#operator": "IndexScan",
             "index": "meta_i1",
             "index_id": "bb26497ef8cf5fef",
             "keyspace": "ycsb",
             "limit": "50",
             "namespace": "default",
             "spans": [
               {
                 "Range": {
                   "Inclusion": 1,
                   "Low": [
                     "\"xyz1028\""
                   ]
                 }
               }
             ],
             "using": "gsi"
           },
           {
             "#operator": "Parallel",
             "maxParallelism": 1,
             "~child": {
               "#operator": "Sequence",
               "~children": [
                 {
                   "#operator": "Fetch",
                   "keyspace": "ycsb",
                   "namespace": "default"
                 },
                 {
                   "#operator": "Filter",
                   "condition": "(\"xyz1028\" <= (meta(`ycsb`).`id`))"
                 },
                 {
                   "#operator": "InitialProject",
                   "result_terms": [
                     {
                       "expr": "(`ycsb`.`field0`)"
                     },
                     {
                       "expr": "(`ycsb`.`field1`)"
                     },
                     {
                       "expr": "(`ycsb`.`field2`)"
                     },
                     {
                       "expr": "(`ycsb`.`field3`)"
                     },
                     {
                       "expr": "(`ycsb`.`field4`)"
                     },
                     {
                       "expr": "(`ycsb`.`field5`)"
                     },
                     {
                       "expr": "(`ycsb`.`field6`)"
                     },
                     {
                       "expr": "(`ycsb`.`field7`)"
                     },
                     {
                       "expr": "(`ycsb`.`Field8`)"
                     },
                     {
                       "expr": "(`ycsb`.`field9`)"
                     }
                   ]
                 }
               ]
             }
           }
         ]
       },
       {
         "#operator": "Limit",
         "expr": "50"
       },
       {
         "#operator": "FinalProject"
       }
     ]
   },
   "text": "SELECT field0, field1, field2, field3, \n       field4, field5, field6, field7,\n       Field8, field9\nFROM  ycsb\nWHERE meta().id >= \"xyz1028\" \nORDER BY meta().id\nLIMIT 50;"
 }
]


Each query goes thru multiple layers of execution within the query engine. The query engine orchestrates query execution among multiple indexes and data services, channeling the data through multiple operators.

Image title


Let's take this and explain the performance optimizations, indexing features implemented, exploited to get the best performance

1. Index Scan

Here's the explain snippet on index scan. In this case, we use the index meta_i1, with a predicate specified by the spans and the index scan is expected to return the first 50 qualified keys.  Let's examine each of those decisions in in subsequent sections.

             "#operator": "IndexScan",
             "index": "meta_i1",
             "index_id": "bb26497ef8cf5fef",
             "keyspace": "ycsb",
             "limit": "50",
             "namespace": "default",
             "spans": [
               {
                 "Range": {
                   "Inclusion": 1,
                   "Low": [
                     "\"xyz1028\""
                   ]
                 }
               }
             ],
             "using": "gsi"
           },


2. Index Selection

Index selection in Couchbase is based on the predicates (filters) in the WHERE clause of the statement only. The index selection is made solely based on predicates and not any references to any other clauses like projection, grouping, ordering, etc.

In this statement, the WHERE clause is: meta().id >= "xyz1028". In this case, the match is quite straightforward.  The index meta_i1 is chosen.

This is the very basic case.  There are many subtle things about creating the right index and improving the throughput of the index scans.  Let's discuss one by one.

In Couchbase, you can create many types of indices. We now have a secondary index meta_i1 on the document key (meta().id).  In couchbase, you can create multiple indexes with same set of keys, but with different name.


CREATE INDEX meta_i2 ON ycsb(meta().id);
CREATE INDEX meta_i3 ON ycsb(meta().id);
CREATE INDEX meta_i4 ON ycsb(meta().id);

With these indices, the plan would choose the primary index, saving memory and CPU resources.

      {
         "#operator": "Sequence",
         "~children": [
           {
             "#operator": "IndexScan",
             "index": "primary_i1",
             "index_id": "23db838eab4b16ab",
             "keyspace": "ycsb",
             "limit": "50",
             "namespace": "default",
             "spans": [
               {
                 "Range": {
                   "Inclusion": 1,
                   "Low": [
                     "\"xyz1028\""
                   ]
                 }
               }
             ],
             "using": "gsi"
           },


3. LIMIT Pushdown

In pagination queries, it's typical to limit the results what the screen can show. OFFSET and LIMIT keywords in the query help you to paginate through the resultset. From the application point of view, OFFSET and LIMIT is on the resultset of the whole query after all the select-join-sort-project operations are done.

However, if the index scan is being used for filtering the data, data is already ordered on the index keys. Then, if the ORDER BY is on the index keys, ascending and in the same order as the index keys, we can exploit the index ordering to provide the results in the expected order. This is significant, without the pushdown, we need to retrieve all of the qualified documents, sort them and then choose the specific window of results to return.

The primary index matches the predicate in the query. The ORDER BY is ascending by default.  So, the predicate and the LIMIT is pushed down to the index scan. The plan includes the limit field with the number pushed down as well. When you want a high performing pagination queries, the LIMIT and OFFSET should be pushed down to the index scan.


SELECT field0, field1, field2, field3,
      field4, field5, field6, field7,
      Field8, field9xz
FROM  ycsb
WHERE meta().id >= "xyz1028"
ORDER BY meta().id
LIMIT 50;


      {
         "#operator": "Sequence",
         "~children": [
           {
             "#operator": "IndexScan",
             "index": "primary_i1",
             "index_id": "23db838eab4b16ab",
             "keyspace": "ycsb",
             "limit": "50",
             "namespace": "default",
             "spans": [
               {
                 "Range": {
                   "Inclusion": 1,
                   "Low": [
                     "\"xyz1028\""
                   ]
                 }
               }
             ],
             "using": "gsi"
           },


Another thing to notice is that, because the LIMIT is pushed down to index scan, the order, offset, limit operators become unnecessary and are removed from the plan.  The best optimizations simply avoid the work altogether. This is one of them.

If we didn't have this optimization in place, or when the ORDER BY does not follow the index key order, you'd see something like this in the query plan.


        {
         "#operator": "Order",
         "limit": "50",
         "offset": "2500",
         "sort_terms": [
           {
             "expr": "(meta(`ycsb`).`id`)"
           }
         ]
       },
       {
         "#operator": "Offset",
         "expr": "2500"
       },
       {
         "#operator": "Limit",
         "expr": "50"
       },
       {
         "#operator": "FinalProject"
       }
     ]


If the query has both OFFSET and LIMIT, the sum of both are pushed down and the query engine simply skips over the keys required by the OFFSET.

explain SELECT field0, field1, field2, field3,
      field4, field5, field6, field7,
      Field8, field9
FROM  ycsb
WHERE meta().id >= "xyz1028"
ORDER BY meta().id
OFFSET 2500
LIMIT 50;
             "#operator": "IndexScan",
             "index": "primary_i1",
             "index_id": "23db838eab4b16ab",
             "keyspace": "ycsb",
             "limit": "(2500 + 50)",
             "namespace": "default",


3. Index Scan Range

The index scan range specified the by the spans. For this query, the range is (meta().id >= "xyz1028") and is specified by the following.


"spans": [
               {
                 "Range": {
                   "Inclusion": 1,
                   "Low": [
                     "\"xyz1028\""
                   ]
                 }
               }
             ],


4. Type of the Index

In my cluster, I have the default standard global secondary index.  In the plan, we can see this in the field {"using": "gsi"}.  

Couchbase 4.5 introduces memory optimized index. A memory-optimized index uses a novel lock-free skiplist to maintain the index and keeps 100% of the index data in memory. A memory-optimized index has better latency for index scans and can also process the mutations of the data much faster. 

When you install Couchbase cluster, you'd need to select the type indexes you want to create in that cluster: Standard Global Secondary indexes, which uses the forestdb storage engine underneath and Memory-Optimized Global Secondary Index which keeps the full index in memory. More information at: http://developer.couchbase.com/documentation/server/4.5-dp/in-memory-indexes.html


Image title


Summary

The duplicate indices, push down of the LIMIT, avoiding fetching extra keys from the index, avoiding sort — all done in a generalized way —helps optimize this YCSB scan query.  This will help general queries with your application queries as well.

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
nosql ,nosql benchmark ,sql ,json ,couchbase ,mongodb ,ycsb ,performance ,query ,index

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}