6

How I can calculate Query time and Query Execution plan in Couchbase.Is there any Utilities like Oracle Explain plan and tkprof in Couchbase db?

edit:

I am trying to see which database performs best for my data. So i am trying to experiment with mysql, mongodb, couchbase. I have tried with three different number of entries 10k, 20k, 40k entries.

With mysql, i can see the query time using "set profiling =1". with this settings i ran queries under three scenarios 1) without indexing primary key, 2) after indexing primary key 3) running the same query second time ( to see the effect of query caching)

Similarly i ran same tests with mongodb and summarized my results in a table format. I would want to run same tests with couchbase to see how well it would perform. I tried to search over net but couldn't find anything which i can follow to get similar results.

Below is the table i have (all times are in milli seconds). the second row with braces() shows the query time for second run.

Records Count       Mysql           MongoDB         CouchBase
        ___________________     _______________     ___________
        Without   | With        Without | With      With Index
        Index     | Index       Index   | Index 

10K     62.27325  | 8.537       3311    | 33
        (33.3135) | (3.27825)       (7) | (0)




20K     108.4075  | 23.238      132 | 39    
        (80.90525)| (4.576)     (17)    | (0)



40K     155.074  | 26.26725     48  | 10
        (110.42) | (10.037)     (42)    | (0)

For couchbase i would want to know both the performance when retrieving a document using its key( similar function as memcahed). Also the query time using its views.

Vijay
  • 924
  • 1
  • 12
  • 27
  • Another thing to add, in your table above both Mysql and MongoDB support querying without indexes, without a view (index) in Couchbase you can only access data by knowing the relevant key. – scalabilitysolved Dec 07 '13 at 12:50
  • In your results table, the more rows there are, the faster MongoDB is. This cannot be corect - queries with 40K records cannot be orders of magnitude faster than 10K records on the same HW? – John Little May 29 '14 at 11:43

1 Answers1

11

You have to understand that couchbase works differently to RDBMS's such as Oracle. Couchbase offers two ways for you to retrieve your data:

1) Key lookup, you know the key(s) of the document(s) that you want to retrieve.

2) Define Map Reduce jobs called Views which create indexes allowing you to query your data on attributes other than the key.

Couchbase documents are always consistent but views are not and are eventually consistent (although you have the ability to change this).

As the couchbase documentation states

Views are updated when the document data is persisted to disk. There is a delay between creating or updating the document, and the document being updated within the view.

So query time really depends on a variety of factors, can the view data be stale? How large is the data emitted from the index, and what is the current workload and db size? Couchbase provides the following 3 flags for working with views and how you want to access the data. False means the index has to be updated before returning the result, therefore it can potentially be slow.

  • false : Force a view update before returning data
  • ok : Allow stale views
  • update_after : Allow stale view, update view after it has been accessed

Please check out the official document for more in depth answers http://docs.couchbase.com/couchbase-manual-2.2/#views-and-indexes

Also you can check out this interesting article on caching views http://blog.couchbase.com/caching-queries-couchbase-high-performance

Currently in development at Couchbase is N1QL, effectively the couchbase version of SQL, this will have the EXPLAIN statement available, this won't be released until late 2014 I believe.

A blog post introducing N1QL http://blog.couchbase.com/n1ql-it-makes-cents

A cheat sheet for N1QL http://www.couchbase.com/communities/sites/default/files/Couchbase-N1QL-CheatSheet.pdf

And where you can download the dev preview if you want to play with N1QL http://www.couchbase.com/communities/n1ql

Also checkout the cb stats tool http://docs.couchbase.com/couchbase-manual-2.2/#cbstats-tool it gives a high level overview of persistence rates,updates,key misses etc.

scalabilitysolved
  • 2,473
  • 1
  • 26
  • 31