It is observed that Couchbase server is taking too much time to execute simple n1ql query (more than 5 mins).
Following are the details :
couchbase server is used along with Sync_gateway.
The couchbase service is running properly.
Sync_gateway service is also working properly and it is inserting records in couchbase server properly.
Couchbase and Sync_Gateway are deployed on EC2 instance of AWS.
If the AWS instance is stopped and started again the n1ql query speed becomes normal.
My queries are as follows.
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.
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