使用Couchbase服务器4.1.0（和4.5），Java SDK 2.2.8（也尝试使用2.2.7,2.3.1和2.3.3），我有一个利用二级索引的查询，当我运行我的代码时它运行正常本地甚至通过AWS服务器上的CBQ（CBQ大约需要3毫秒） . 但是，在AWS上运行我的应用程序时，我得到一个 TimeOutException ，它只有一个查询超时，其他查询没有 . 详情见下文 .
值得注意的是我的Couchbase设置有3个桶 .
示例文档：
"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"
}
二级指数：
CREATE INDEX `indexName` ON `bucketName`(`ParentMsgId`,`docType`,`publishTimestamp`) USING GSI
从 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代码
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);
...
}
查询解释结果
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
}
}
日志
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)
这是一个根据请求工作正常的查询示例 .
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";
这个查询与其他查询唯一不同的是它使用 IN 子句并通过参数化 JsonArray 接收字段 .
没有网络延迟 . 我不认为这是一个问题，因为其他查询正在工作，它们基本上是一个接一个地链接（我还测试了单独运行此查询，它仍然执行速度极慢） .
App和CB都在AWS上 . 我已经在同一台AWS服务器和不同服务器上进行了测试，在这两种情况下都存在问题 . 我在AWS上有一个客户端，而不是在AWS上，两者都有问题 . 客户端我指的是一种调用我的应用程序的机制 . 调用查询时，它仍会超时 .
我的collectinfo couchbase日志在这里s3.amazonaws.com/cb-customers/TE2/
2 回答
我发现它与IN子句中值的参数化有关 . 当我从查询中删除参数化时，我能够像CBQ一样快地运行 . 我唯一的选择是删除参数化 . 我试图调整二级索引稍微移动
ParentMsgId到字段列表的末尾这对我的情况没有帮助 .从couchbase rep诊断出的潜在问题 .
您可以检查/发布此查询的EXPLAIN输出 . 另外，检查服务器上的查询/索引日志（并且没有网络延迟） .
AWS中的客户端/应用程序和CB /服务器都是??
并发布在此设置中工作的其他查询 . 这个查询不起作用的独特之处是什么？
-Prasad