Using the Couchbase 4.1.0 server (and 4.5), the Java SDK 2.2.8 (also tried with 2.2.7, 2.3.1 and 2.3.3), I have a query using a secondary index that works fine when I run my code locally and even through CBQ (CBQ takes about 3 ms) on the AWS server. However, when I run my application in AWS, I get a TimeOutException , and this is only one request that is disconnected, others not. See below for more details.
It may be worth noting that my Couchbase setup has 3 buckets.
Doc example:
"bucketName": { "userName": "User_A", "MessageContent": "This is a message", "docType": "msg", "ParentMsgId": "1234", "MsgType": "test", "expireTimestamp": 1454975772613, "publishTimestamp": 1455322362028, "id": "145826845", "urls": [], "subject": "this is a subject", "type": 1, "GroupId": "Group_1" }
Secondary Index:
CREATE INDEX `indexName` ON `bucketName`(`ParentMsgId`,`docType`,`publishTimestamp`) USING GSI
Example query retrieved from N1qlQuery#n1ql()
{"statement": "select count(*) as msgCount from bucketName where ParentMsgId is not missing and docType = 'msg' and ParentMsgId IN $parentId and publishTimestamp between $startTime and $endTime ","$endTime":1470726861816, "$startTime":1470640461816, "$parenIds":["fa11845b-9ea5-4778-95fe-e7206843c69b"] }
Java code
public static final String COUNT_STATEMENT = "select count(*) as count " + "from bucketName " + "where ParentMsgId is not missing " + "and docType = 'msg' " + "and ParentMsgId IN $parentIds " + "and publishTimestamp between $startTime and $endTime"; public int getCountForDuration(Long startTime, Long endTime, Collection<String> parentIds){ List<String> idList = new ArrayList<>(parentIds); JsonObject placeHolders = JsonObject.create() .put("parentIds", JsonArray.from(idList)) .put("startTime", startTime) .put("endTime", endTime); N1qlQuery query = N1qlQuery.parameterized(COUNT_STATEMENT, placeHolders) N1qlQueryResult result = bucket.query(query); ... }
Request Explain Result
cbq> explain select count(*) as msgCount from bucketName where ParentMsgId is not missing and docType = 'msg' and ParentMsgId IN ["01b88f7f-4de6-4daa-9562-a2c902e818ad"] and publishTimestamp between 1466445409000 and 1466531809000; { "requestID": "61afcf02-3b3d-4c8a-aec6-b76c4c1f7b17", "signature": "json", "results": [ { "#operator": "Sequence", "~children": [ { "#operator": "IndexScan", "index": "indexName", "keyspace": "bucketName", "namespace": "default", "spans": [ { "Range": { "High": [ "successor(\"01b88f7f-4de6-4daa-9562-a2c902e818ad\")" ], "Inclusion": 1, "Low": [ "\"01b88f7f-4de6-4daa-9562-a2c902e818ad\"" ] } } ], "using": "gsi" }, { "#operator": "Parallel", "~child": { "#operator": "Sequence", "~children": [ { "#operator": "Fetch", "keyspace": "bucketName", "namespace": "default" }, { "#operator": "Filter", "condition": "(((((`bucketName`.`ParentMsgId`) is not missing) and ((`bucketName`.`docType`) = \"msg\")) and ((`bucketName`.`ParentMsgId`) in [\"01b88f7f-4de6-4daa-9562-a2c902e818ad\"])) and ((`bucketName`.`publishTimestamp`) between 1466445409000 and 1466531809000))" }, { "#operator": "InitialGroup", "aggregates": [ "count(*)" ], "group_keys": [] } ] } }, { "#operator": "IntermediateGroup", "aggregates": [ "count(*)" ], "group_keys": [] }, { "#operator": "FinalGroup", "aggregates": [ "count(*)" ], "group_keys": [] }, { "#operator": "Parallel", "~child": { "#operator": "Sequence", "~children": [ { "#operator": "InitialProject", "result_terms": [ { "as": "msgCount", "expr": "count(*)" } ] }, { "#operator": "FinalProject" } ] } } ] } ], "status": "success", "metrics": { "elapsedTime": "2.748194ms", "executionTime": "2.660232ms", "resultCount": 1, "resultSize": 3274 } }
Magazines
java.lang.Thread.run(Thread.java:745) org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:533) org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:598) org.eclipse.jetty.server.nio.BlockingChannelConnector$BlockingChannelEndPoint.run(BlockingChannelConnector.java:293) org.eclipse.jetty.server.BlockingHttpConnection.handle(BlockingHttpConnection.java:50) org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:218) org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:582) org.eclipse.jetty.server.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:919) org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:441) org.eclipse.jetty.server.Server.handle(Server.java:349) org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:110) org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:149) org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117) org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:875) org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:186) org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:409) org.ops4j.pax.web.service.jetty.internal.HttpServiceContext.doHandle(HttpServiceContext.java:117) org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:941) org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:227) org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:483) org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119) org.ops4j.pax.web.service.jetty.internal.HttpServiceServletHandler.doHandle(HttpServiceServletHandler.java:70) org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:480) org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:547) org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:201) javax.servlet.http.HttpServlet.service(HttpServlet.java:693) org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:150) org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:225) org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:130) org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:194) org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:214) org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:237) org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121) org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:262) org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:94) org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58) org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:89) org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:168) org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96) org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:180) java.lang.reflect.Method.invoke(Method.java:498) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) biz.te2.core.services.beacon.impl.BeaconResource.getVenuesBeaconData(BeaconResource.java:105) xxx.xxx.xxx.getBeaconHealthForRangeAndVenue(BeaconHealthServiceImpl.java:40) xxx.xxx.xxx..getAllMessagesCount(BeaconHealthServiceImpl.java:80) com.sun.proxy.$Proxy146.getMessageCountForDuration(Unknown Source) org.apache.aries.proxy.impl.ProxyHandler.invoke(ProxyHandler.java:78) org.apache.aries.proxy.impl.DefaultWrapper.invoke(DefaultWrapper.java:31) org.apache.aries.proxy.impl.ProxyHandler$1.invoke(ProxyHandler.java:50) java.lang.reflect.Method.invoke(Method.java:498) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) xxx.xxx.xxx.getMessageCountForDuration(MessageCouchbaseRepo.java:364) xxx.xxx.xxx.getN1qlQueryRows(MessageCouchbaseRepo.java:372) com.couchbase.client.java.CouchbaseBucket.query(CouchbaseBucket.java:582) com.couchbase.client.java.CouchbaseBucket.query(CouchbaseBucket.java:656) com.couchbase.client.java.util.Blocking.blockForSingle(Blocking.java:74) java.util.concurrent.CountDownLatch.await(CountDownLatch.java:277) java.util.concurrent.locks.AbstractQueuedSynchronizer.tryAcquireSharedNanos(AbstractQueuedSynchronizer.java:1328) java.util.concurrent.locks.AbstractQueuedSynchronizer.doAcquireSharedNanos(AbstractQueuedSynchronizer.java:1037) java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
This is an example request that works fine, on request.
public static final String EXPERIENCE_ID_STATEMENT = "Select id " + "from read as exp " + "where id is not missing " + "and docType = 'experience' " + "and venueId = $venueId " + "and exp.rule.poiIds is not missing " + "and any poi in exp.rule.poiIds satisfies poi = $poiId end";
The only thing that is unique in this query and others is to use the IN clause and get the fields through a parameterized JsonArray .
There are no network delays. I do not think this is a problem, as other requests work, and they are essentially connected by a chain called one after the other (I also tested the execution of this request alone, and it still runs very slowly).
The app and CB are on AWS. I tested them both on the same AWS server, and on different servers, and in both cases there is a problem. I have a client on AWS, not AWS, both have a problem. To the client, I mean the mechanism that invokes my application. It still gets a timeout when the request is called.
My couchbase magazine magazines are here. s3.amazonaws.com/cb-customers/TE2/