N1QL run fast in one node and slow in other node of couchbase cluster with 2 nodes

Couchbase Server
1

Hi,

I’m using Couchbase 4.5.1 cluster with 2 nodes: node1, node 2 (both are VPS 8GB RAM, 4CPU, SSD)
When system perform a query, CB will do balance but I notice that, for the same query like this:
SELECT id FROM default t
use index(ads_dc01_idx)
WHERE type=‘Ads’ and loaiTin = 0 AND place.diaChinh.codeTinh=‘HN’ AND place.diaChinh.codeHuyen='15’
ORDER BY ngayDangTin DESC, timeModified DESC LIMIT 100 OFFSET 0

when run on node1 took 2400ms but when run on node2 took only 500ms. After look into it more closely I found out seem this is due to the index belong to node2:
203.162.13.177:8091 ads_dc01_idx

Can you advice how to duplicate this index to node1 to speedup the query speed from node1 ?

Thank you

Regards,

2

You should not run index and query services on the same node. Add a third index node.

3

You can créate a duplicate index in the other no de, while you add new nodes.
There si a option to créate a index on a específic node. Couchbase allow to have duplicate indexes.

See this link with an example.

http://developer.couchbase.com/documentation/server/current/architecture/global-secondary-indexes.html.

Arturo

4

For example, I have node3 dedicated for index service, so in that case, the query from node1, or node2 will always 2400ms, right ?

5

  • If I remove “order by” and “limit” condition, it will return 8000 records, so I think 2seconds here is the time to copy 8000 records from “index service node” to “query node”, that why if “index service” and “query service” on the same node, the speed is signification difference

  • I can create duplicate index now on both nodes, but the issue is query engine some time choose the index from Node1, sometime from Node2, so still have same issue

Any advice ?

6

Hello,
According CB doc:
" When identical index definitions on separate nodes are available, N1QL queries use the round-robin algorithm to load balance the index scan operations"

Then , you can run the query from node1 and executed on node2 index service, then the rows need to be transferred between nodes using network resources, then increase latency.

I dont’ know if is posible to set a Affinity flag or Hint at query level, to say to the query engine to execute the query completely on one node, using local services and then avoid network transfers between nodes.

This increase latency happen always or only en some times?
Which networking latency/bandwith you have?
Are using this network other types of applications that can use a lot of network resources?

Also you can optimize networking resources between nodes.

Best regards
Arturo

7

Can you post your queries and indexes?

And as suggested earlier, you need to keep the indexes and queries on separate nodes.

8
  • This is my query:
    SELECT id, gia, loaiTin, dienTich, soPhongNgu, soTang, soPhongTam, image, place, giaM2, loaiNhaDat, huongNha, ngayDangTin
    FROM default t
    WHERE type=‘Ads’ and loaiTin = 0 AND place.diaChinh.codeTinh=‘HN’ AND place.diaChinh.codeHuyen='15’
    ORDER BY ngayDangTin DESC, timeModified DESC
    LIMIT 100 OFFSET 0
  • This is my index:
    CREATE INDEX ads_dc01_idx ON default(loaiTin,((place.diaChinh).codeTinh),((place.diaChinh).codeHuyen),((place.geo).lat),((place.geo).lon),loaiNhaDat,gia,dienTich,ngayDangTin,soPhongNgu,soPhongTam,soTang,huongNha,giaM2,id,image,place) WHERE (type = “Ads”)

As I understand, because CB does not support DESC ordering, so I guess it will load all data that match type, loaiTin, place.diaChinh by index (8000 records) into memory then do the sorting, because of loading 8000 records (about 10MB) from node1 to node2, it will take sometime (2 seconds) , that why local index only took 0.5s but remote index took 2.5s

For network speed between 2 servers/nodes:
Accepted connection from 203.162.13.133, port 42858
[ 5] local 203.162.13.177 port 5201 connected to 203.162.13.133 port 42860
[ ID] Interval Transfer Bandwidth
[ 5] 0.00-1.00 sec 2.16 MBytes 18.1 Mbits/sec
[ 5] 1.00-2.00 sec 2.45 MBytes 20.5 Mbits/sec
[ 5] 2.00-3.00 sec 2.33 MBytes 19.6 Mbits/sec
[ 5] 3.00-4.00 sec 2.31 MBytes 19.4 Mbits/sec
[ 5] 4.00-5.00 sec 2.31 MBytes 19.4 Mbits/sec
[ 5] 5.00-6.00 sec 2.31 MBytes 19.4 Mbits/sec
[ 5] 6.00-7.00 sec 2.32 MBytes 19.5 Mbits/sec
[ 5] 7.00-8.00 sec 2.31 MBytes 19.4 Mbits/sec
[ 5] 8.00-9.00 sec 2.32 MBytes 19.5 Mbits/sec
[ 5] 9.00-10.00 sec 2.26 MBytes 19.0 Mbits/sec
[ 5] 10.00-10.04 sec 84.8 KBytes 19.0 Mbits/sec

I’m trying to setup a gagabit network interface

9

Couchbase does support DESC ordering. It is just not yet optimized by pushing down to the indexer.

What are the data types of ngayDangTin and timeModified?

10

They are both string datatype

11

What is the meaning of ngayDangTin, and why does it need to be descending? I’m trying to find a workaround.

12

You want to use -(ngayDangTin) indexing to workaround for DESC right ?

Actually, this is only one example, my application is realestate searching app, user can perform searching by: price, area, numOfBedroom, numOfBathroom, city, district, dateCreated. Then ordering by : price, area, numOfBedroom, numOfBathroom, dateCreated

There are alot of combination here to create covering order (to cover desc/asc also), I tried to create somes, but the CB engine sometime choose the wrong index also ?

13

Ok, got it. You have the correct information and workaround.

14

I changed my network interface to 10Gbs, and now both 2 nodes are only took 0.5s. I will create dedicated index service node also as you suggested and change the setting to MOI to speed up it more. Thank so much all of you

1 Like