少峰，您好：
two "distinct count" measures, are HyperLogLog counter。
一、 group by b.dim_month_name 这个是derived measure。
测试了下，如果where 条件是月，group by 周 查询时间是66秒， where 条件是周，group by 日，查询时间是9秒
如果where 条件是年，group by 月 ；where 条件是上半年，group by 季度或者月 都会内存溢出错误。
Hbase的heap size大小也调到了32GB。kylin.query.cube.visit.timeout.times 调到了10
二、如果group by的是nomal维度则查询很快
三、如果增加hbase的regionderver是否可解决此问题 ？
谢谢~
-----邮件原件-----
发件人: ShaoFeng Shi [mailto:[email protected]]
发送时间: 2016年7月1日 9:32
收件人: dev
抄送: [email protected]
主题: Re: kylin查询，报超时异常:Timeout visiting cube!
hi tongxing,
The root cause is OutOfMemory:
Caused by: java.lang.OutOfMemoryError
at
java.io.ByteArrayOutputStream.hugeCapacity(ByteArrayOutputStream.java:123)
at
java.io.ByteArrayOutputStream.grow(ByteArrayOutputStream.java:117)
at
java.io.ByteArrayOutputStream.ensureCapacity(ByteArrayOutputStream.java:93)
at
java.io.ByteArrayOutputStream.write(ByteArrayOutputStream.java:153)
at
org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.CubeVisitService.visitCube(CubeVisitService.java:
You query uses two "distinct count" measures, are they HyperLogLog counter or
Bitmap counter?
2016-06-30 18:09 GMT+08:00 仇同心 <[email protected]>:
> 大家好：
> Kylin查询时报超时异常,sql是：
> select b.dim_month_name,sum(a.ordr_amt) as 订单金额,
> sum(a.pay_amt) as 支付金额,count(*) as 订单数,
> count(distinct a.user_pin)as 用户数,count(distinct a.is_new) as 新用户数
> from dmt.dmt_mem_vip_tx_ordr_det_i_d a left join dim.dim_day b on
> a.pay_time=b.dim_day_txdate left join dim.dim_kylin_mem_type_buss c on
> a.mem_type=c.mem_type left join dim.dim_kylin_mem_type_source d on
> a.mem_type_source=d.mem_type_source
> where b.dim_year_name='2016年'
> group by b.dim_month_name
> order by b.dim_month_name asc
> LIMIT 50000
>
>
> 错误日志为：
> 016-06-30 17:11:36,149 ERROR [http-bio-7070-exec-10]
> controller.QueryController:209 : Exception when execute sql
> java.sql.SQLException: Error while executing SQL "select
> b.dim_month_name,sum(a.ordr_amt) as 订单金额,
> sum(a.pay_amt) as 支付金额,count(*) as 订单数,
> count(distinct a.user_pin)as 用户数,count(distinct a.is_new) as 新用户数
> from dmt.dmt_mem_vip_tx_ordr_det_i_d a left join dim.dim_day b on
> a.pay_time=b.dim_day_txdate left join dim.dim_kylin_mem_type_buss c on
> a.mem_type=c.mem_type left join dim.dim_kylin_mem_type_source d on
> a.mem_type_source=d.mem_type_source
> where b.dim_year_name='2016年'
> group by b.dim_month_name
> order by b.dim_month_name asc
> LIMIT 50000": Timeout visiting cube!
> at
> org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at
> org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:143)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
> at
> org.apache.kylin.rest.service.QueryService.execute(QueryService.java:361)
> at
> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:273)
> at
> org.apache.kylin.rest.service.QueryService.query(QueryService.java:121)
> at
> org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f.invoke(<generated>)
> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
> at
> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
> at
> org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$5d1a2567.query(<generated>)
> at
> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(Quer
> yController.java:192)
>
>
> Caught exception in thread pool-8-thr
> ead-2:
> java.lang.RuntimeException: <sub-thread for GTScanRequest c66d2a5>
> Error when visiting cubes by endpoint
> at
> org.apache.kylin.storage.hbase.cube.v2.CubeHBaseEndpointRPC$1.run(CubeHBaseEndpointRPC.java:345)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: java.net.SocketTimeoutException: callTimeout=60000,
> callDuration=109489: row '^@^@' on table 'KYLIN_UVD9MY6HD P' at
> region=KYLIN_UVD9MY6HDP,,1467269837242.b987ac977cedbc87773275794711120
> d., hostname=jxq-23-197-78.h.chinabank.com.c
> n,16020,1464677435379, seqNum=2
> at
> org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRetryingCaller.java:169)
> at
> org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel.callExecService(RegionCoprocessorRpcChannel.java:107)
> at
> org.apache.hadoop.hbase.ipc.CoprocessorRpcChannel.callMethod(CoprocessorRpcChannel.java:56)
> at
> org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.generated.
> CubeVisitProtos$CubeVisitService$Stub.v
> isitCube(CubeVisitProtos.java:4225)
> at
> org.apache.kylin.storage.hbase.cube.v2.CubeHBaseEndpointRPC$2.call(Cub
> eHBaseEndpointRPC.java:393)
> at
> org.apache.kylin.storage.hbase.cube.v2.CubeHBaseEndpointRPC$2.call(CubeHBaseEndpointRPC.java:389)
> at org.apache.hadoop.hbase.client.HTable$15.call(HTable.java:1736)
> ... 4 more
> Caused by: java.io.IOException: java.io.IOException
> at org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:2169)
> at org.apache.hadoop.hbase.ipc.CallRunner.run(CallRunner.java:107)
> at
> org.apache.hadoop.hbase.ipc.RpcExecutor.consumerLoop(RpcExecutor.java:133)
> at
> org.apache.hadoop.hbase.ipc.RpcExecutor$1.run(RpcExecutor.java:108)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: java.lang.OutOfMemoryError
> at
> java.io.ByteArrayOutputStream.hugeCapacity(ByteArrayOutputStream.java:123)
> at
> java.io.ByteArrayOutputStream.grow(ByteArrayOutputStream.java:117)
> at
> java.io.ByteArrayOutputStream.ensureCapacity(ByteArrayOutputStream.java:93)
> at
> java.io.ByteArrayOutputStream.write(ByteArrayOutputStream.java:153)
> at
> org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.CubeVisitService.visitCube(CubeVisitService.java:
> 304)
> at
> org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.generated.
> CubeVisitProtos$CubeVisitService.callMe
> thod(CubeVisitProtos.java:4164)
> at
> org.apache.hadoop.hbase.regionserver.HRegion.execService(HRegion.java:7483)
> at
> org.apache.hadoop.hbase.regionserver.RSRpcServices.execServiceOnRegion(RSRpcServices.java:1891)
> at
> org.apache.hadoop.hbase.regionserver.RSRpcServices.execService(RSRpcServices.java:1873)
> at
> org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientService$2.callBlockingMethod(ClientProtos.java:
> 32389)
> at org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:2127)
> ... 4 more
>
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
> at
> org.apache.hadoop.ipc.RemoteException.instantiateException(RemoteException.java:106)
> at
> org.apache.hadoop.ipc.RemoteException.unwrapRemoteException(RemoteException.java:95)
> at
> org.apache.hadoop.hbase.protobuf.ProtobufUtil.getRemoteException(ProtobufUtil.java:326)
> at
> org.apache.hadoop.hbase.protobuf.ProtobufUtil.execService(ProtobufUtil.java:1622)
> at
> org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel$1.call(RegionCoprocessorRpcChannel.java:104)
> at
> org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel$1.call(RegionCoprocessorRpcChannel.java:94)
> at
> org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRetryingCaller.java:136)
> ... 10 more
> Caused by:
> org.apache.hadoop.hbase.ipc.RemoteWithExtrasException(java.io.IOException):
> java.io.IOException
> at
> org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:2169)
> at org.apache.hadoop.hbase.ipc.CallRunner.run(CallRunner.java:107)
> at
> org.apache.hadoop.hbase.ipc.RpcExecutor.consumerLoop(RpcExecutor.java:133)
> at
> org.apache.hadoop.hbase.ipc.RpcExecutor$1.run(RpcExecutor.java:108)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: java.lang.OutOfMemoryError
> at
> java.io.ByteArrayOutputStream.hugeCapacity(ByteArrayOutputStream.java:123)
> at
> java.io.ByteArrayOutputStream.grow(ByteArrayOutputStream.java:117)
> at
> java.io.ByteArrayOutputStream.ensureCapacity(ByteArrayOutputStream.java:93)
> at
> java.io.ByteArrayOutputStream.write(ByteArrayOutputStream.java:153)
> at
> org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.CubeVisitService.visitCube(CubeVisitService.java:
> 304)
> at
> org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.generated.
> CubeVisitProtos$CubeVisitService.callMe
> thod(CubeVisitProtos.java:4164)
> at
> org.apache.hadoop.hbase.regionserver.HRegion.execService(HRegion.java:7483)
> at
> org.apache.hadoop.hbase.regionserver.RSRpcServices.execServiceOnRegion(RSRpcServices.java:1891)
> at
> org.apache.hadoop.hbase.regionserver.RSRpcServices.execService(RSRpcServices.java:1873)
> at
> org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientService$2.callBlockingMethod(ClientProtos.java:
> 32389)
> at org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:2127)
> ... 4 more
>
> at
> org.apache.hadoop.hbase.ipc.RpcClientImpl.call(RpcClientImpl.java:1235)
> at
> org.apache.hadoop.hbase.ipc.AbstractRpcClient.callBlockingMethod(Abstr
> actRpcClient.java:222
> t
> org.apache.hadoop.hbase.ipc.AbstractRpcClient$BlockingRpcChannelImplem
> entation.callBlockingMethod(AbstractRpc
> Client.java:323)
> at
> org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientService$
> BlockingStub.execService(ClientProtos.j
> ava:32855)
> at
> org.apache.hadoop.hbase.protobuf.ProtobufUtil.execService(ProtobufUtil.java:1618)
> ... 13 more
>
> 目前cube size : 1.5GB source records :8亿
> 版本： hadoop2.6 hive -hive-1.2.1 hbase 1.1.5 kylin 1.5.2
>
> 还有问题就是都有哪些点可以优化查询？？？
>
> 谢谢！
>
>
>
>
>
--
Best regards,
Shaofeng Shi
