Why Couchbase query performs slower than MySQL?

In MySQL and Couchbase I read data of 1 day and 1 month by using query.

1- day query is

SELECT Temp.Col.001, FROM kami_file1_dat WHERE META().id BETWEEN “1430438400” AND “1430524800”;

30 - days data read query is

SELECT temp.Col.001 FROM kami_file1_dat WHERE META().id BETWEEN “1430438400” AND “1432944000”;

For 1 day query, MySQL takes 40 milliseconds, but Couchbase took only 10 milliseconds. which shows that couch base is faster.:slightly_smiling_face:

And for 1 month data query, MySQL still take 40 milliseconds, But in Couchbase it took 196- 225 milliseconds to display the same data of 1 month by using the same query . .:confused:

Why is Couchbase slow as compared to MySQL?. It thought it would be faster because of using ram.

I am new to this forum as well as to Couchbase. Any suggestion and help will be greatly appreciated. .

The post How to query with Curl in couchbase has info and suggestions.

1 Like

What’s your index definition and query plan?

For covering index, you can increase the scan-batch parameter (5.0) and for KV fetch, you can increase the following parameters up from their defaults: “pipeline-batch”:16,“pipeline-cap”:512

curl -u Administrator:password http://localhost:8093/admin/settings >z.json
Edit the z.json & increase servicers Example:

{"completed-limit":4000,"completed-threshold":1000,"cpuprofile":"","debug"
:false,"keep-alive-length":16384,"loglevel":"INFO","max-parallelism":1,"me
mprofile":"","pipeline-batch":16,"pipeline-cap":512,"request-size-cap":671
08864,"scan-cap":0,"servicers":32,"timeout":0}

CHANGE the relavant values.  
E.g., Your queries are getting large number of documents for each query.
Increase pipeline-batch to 1024 and pipeline-cap: 4096

Do the following:

curl -u Administrator:password http://localhost:8093/admin/settings
-XPOST -d@./z.json
1 Like

@keshav_m I will give it a try and will come back soon to update. thanks a lot for you people help specially @vsr1 and @atom_yang. and @keshav_m thanks for your precious time. :slightly_smiling_face:
I hope I will improve this query somehow to get better results. .

@keshav_m and @vsr1 when I used this command.

curl -u kamil:xxxxxxxxxx http://localhost:8093/admin/settings >z.json

I got this in terminal

% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 282 100 282 0 0 565k 0 --:–:-- --:–:-- --:–:-- 275k

and If I use this command. .

curl -u kamil:xxxxxxxx http://localhost:8093/admin/settings
or
curl -u kamil:xxxxxxxxxx http://localhost:8093/admin/settings -XPOST -d@./z.json

then i get this

{“completed-limit”:4000,“completed-threshold”:1000,“cpuprofile”:"",“debug”:false,“keep-alive-length”:16384,“loglevel”:“INFO”,“max-parallelism”:1,“memprofile”:"",“pipeline-batch”:16,“pipeline-cap”:512,“pretty”:true,“request-size-cap”:67108864,“scan-cap”:0,“servicers”:16,“timeout”:0}.

How Can I change pipeline-batch to 1024 and pipeline-cap: 4096 respectively because maybe it works for me. .I try to look for a config file in /opt/couchbase/ directory but unable to find anything related to changing above mention values…
I would be thankful. .

When curl -u kamil:xxxxxxxxxx http://localhost:8093/admin/settings -XPOST -d@./z.json used the affected values are displayed back to you. These are changed the memory will not be stored any where values will be reset each time query service re-started.

Also you can try this.

curl http://hostname:8093/admin/settings -d '{"pipeline-batch":1024, "pipeline-cap":4096 }' -u user:pword
1 Like

@vsr1 Thanks a lot. :slightly_smiling_face:.I changed the settings, but I am still getting the same timing of 50-60 milliseconds. . I am trying my best to reduce the time. .

Only optimization I can think of is Memory Optimized Index.

1 Like

@vsr1 I am really thankful for your precious time and help. I reinstall couchbase server and select " Memory-Optimized Global Secondary Indexes" instead of using “Standard Global Secondary Indexes” and now timing is 40 - 50 milliseconds which is better than previous. :slightly_smiling_face::slightly_smiling_face: . I will try a little bit more if possible to improve. .
But I am really thankful to you people. And without your help it would be really difficult for me to optimize it so quickly. :grinning:

@zeeshan.k751 Thank you. Welcome to Couchbase community. Look forward to your participation and contributions.

1 Like

@keshav_m Thank you so much. :slightly_smiling_face::slightly_smiling_face: