Couchbase server is taking too much time to execute simple n1ql query

SQL++
1

It is observed that Couchbase server is taking too much time to execute simple n1ql query (more than 5 mins).

Following are the details :

  1. couchbase server is used along with Sync_gateway.

  2. The couchbase service is running properly.

  3. Sync_gateway service is also working properly and it is inserting records in couchbase server properly.

  4. Couchbase and Sync_Gateway are deployed on EC2 instance of AWS.

  5. If the AWS instance is stopped and started again the n1ql query speed becomes normal.

  6. My queries are as follows.

    Simple query

    select  a.route_id from buckethelp a  where a.type = 'point'

    Query with some complex logic (regularly used in code)

    select  a.latitude, a.longitude, DATE_FORMAT_STR(a.time, '11:11:11')
as time, a.time as visit_Date,  DATE_FORMAT_STR(a.time, '1111-11-11')
as date, b.user_id, a.route_id, 
case when c.check_out_time != ''  THEN 'True' ELSE 'False' END 
as IsCheckedOut, DATE_FORMAT_STR( a._sync.time_saved,  '1111-11-11 11:11:11')  as time_saved, 
c.check_out_type, c.check_out_time  from buckethelp a  JOIN buckethelp b ON KEYS  a.route_id 
JOIN buckethelp c  ON KEYS b.log_id  where a.type = 'point' and 
DATE_FORMAT_STR(a.time, '1111-11-11') = '2017-11-29'

    When couchbase server performance lowers it takes huge time execute both of the above queries.
    When we restart the EC2 instance on which couchbase is installed, it executes the query in normal time as per expected time.

  7. It is observed that ‘cbq_engine’ process is taking massive CPU time (more than 300 %)

Has anyone faced this problem, what is cause and possible solution on this

2

What is EXPLAIN of the query? What is format do you have for a.time

Try with following index

CREATE INDEX ix1 ON buckethelp(DATE_FORMAT_STR(time, '1111-11-11')) WHERE type = 'point';

3

Hi VSM,

Thanks for the reply.
I have already created index on ‘time’ column as you have suggested. But still getting the problem.
Not able to see EXPLAIN as query taking too long time and workbench is showing message it can not display result after 5 mins.

Is there any possibility of any service of couchbase going down causing this problem. If yes please suggest how find it out.
Is there any possibility of RAM of the machine not sufficient to process the query, as when I restart the VM it works fine.
Using Views instead of queries will help ?

Thanks

4

Following are some more details,

No. of documents around 600 thousand.
Size of each document less than 1 kb,
around 4.5 GB of for the node

Do I need to look for any more system related parameter.

5

Please add EXPLAIN in front of SELECT. It gives execution plan.

As you have 3 level JOINs it takes time.
Also try break the query and see how long it taking.
Example:

SELECT COUNT(1) FROM buckethelp AS a 
where a.type = "point" and 
DATE_FORMAT_STR(a.time, "1111-11-11") = "2017-11-29";
6 
[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "PrimaryScan",
          "index": "#primary",
          "keyspace": "buckethelp",
          "namespace": "default",
          "using": "gsi"
        },
        {
          "#operator": "Fetch",
          "as": "a",
          "keyspace": "buckethelp",
          "namespace": "default"
        },
        {
          "#operator": "Join",
          "as": "b",
          "keyspace": "buckethelp",
          "namespace": "default",
          "on_keys": "(`a`.`route_id`)"
        },
        {
          "#operator": "Join",
          "as": "c",
          "keyspace": "buckethelp",
          "namespace": "default",
          "on_keys": "(`b`.`log_id`)"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "(((`a`.`type`) = \"point\") and (date_format_str((`a`.`time`), \"1111-11-11\") = \"2017-12-05\"))"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "(`a`.`latitude`)"
                  },
                  {
                    "expr": "(`a`.`longitude`)"
                  },
                  {
                    "as": "time",
                    "expr": "date_format_str((`a`.`time`), \"11:11:11\")"
                  },
                  {
                    "as": "visit_Date",
                    "expr": "(`a`.`time`)"
                  },
                  {
                    "as": "date",
                    "expr": "date_format_str((`a`.`time`), \"1111-11-11\")"
                  },
                  {
                    "expr": "(`b`.`user_id`)"
                  },
                  {
                    "expr": "(`a`.`route_id`)"
                  },
                  {
                    "as": "IsCheckedOut",
                    "expr": "case when (not ((`c`.`check_out_time`) = \"\")) then \"True\" else \"False\" end"
                  },
                  {
                    "as": "time_saved",
                    "expr": "date_format_str(((`a`.`_sync`).`time_saved`), \"1111-11-11 11:11:11\")"
                  },
                  {
                    "expr": "(`c`.`check_out_type`)"
                  },
                  {
                    "expr": "(`c`.`check_out_time`)"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
   }
]
7

The query using PrimaryScan. Please create index specified earlier and try it again. What is version of CB?

8

https://blog.couchbase.com/create-right-index-get-right-performance/

9

Thanks VSM for this reply.

Very good points From performance improvement point of view.

But my problem is different, the N1QL query is not returning any record after too much long time, it completely hangs.
When I restart the VM, the query starts executing again.
What can be reason behind that.

I have deleted some records using ‘delete’ query in workbench, is this cause of query hang.
What is standard way of removing records from couchbase - setting ttl value of a record or delete query or any other way (like bucket empty functionality provided by cocuhbase)

10

DELETE should work. What is command you are using and how many records it qualifies to delete. You can also give LIMIT option as part of delete. What version of couchbase you are using.

You can try with 4.6.3 or 4.6.4

11

I am using Delete query e.g.

delete from buckethelp a where (a.type = ‘point’) and DATE_FORMAT_STR(a.time, ‘1111-11-11’) = ‘2017-11-15’

The records deleted around 60 K.

If I run delete query multiple times (e.g. delete data for 4 to 5 days with above query), at the same time Sync_gateway adding documents in the bucket. Does it cause any problem with Couchbase, and because of that the cocuhbase stops executing select queries ?

Couchbase version I am using is 4.6.0

12

First you need to right index to perform the query better. Please follow the above link and verify through EXPLAIN if it is using right index.

You can issue DELETE as many times as you want and it should not be an issue.
Even you can limit number of records and repeat the same statement until mutationCount is 0.

delete from buckethelp a where (a.type = ‘point’) and DATE_FORMAT_STR(a.time, ‘1111-11-11’) = ‘2017-11-15’ LIMIT 10000;