You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by 仇同心 <qi...@jd.com> on 2016/07/01 07:09:59 UTC
答复: 答复: kylin查询，报超时异常:Timeout visiting cube!
OK,我修改下dimension试试。
谢谢！
-----邮件原件-----
发件人: ShaoFeng Shi [mailto:shaofengshi@apache.org]
发送时间: 2016年7月1日 13:23
收件人: user@kylin.apache.org
抄送: dev@kylin.apache.org
主题: Re: 答复: kylin查询，报超时异常:Timeout visiting cube!
"derived" need post aggregation as we know; from Day to Month, it need aggregate 30 times data in memory to result set; For Quarter it need more; So when the measure is "memory-hungry" measure (like distinct count, raw, top-n), it is likely to get the out of memory error; you can try to define "month" and "quarter" as normal dimension so to reduce the post aggregation.
2016-07-01 11:20 GMT+08:00 仇同心 <qi...@jd.com>:
> 少峰，您好：
> 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:shaofengshi@apache.org]
> 发送时间: 2016年7月1日 9:32
> 收件人: dev
> 抄送: user@kylin.apache.org
> 主题: 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.jav
> a: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 仇同心 <qi...@jd.com>:
>
> > 大家好：
> > 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(AvaticaSta
> tement.java:143)
> > at
> >
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatem
> ent.java:186)
> > at
> > org.apache.kylin.rest.service.QueryService.execute(QueryService.java:361)
> > at
> >
> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QuerySe
> rvice.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$DynamicAdvisedInterce
> ptor.intercept(Cglib2AopProxy.java:618)
> > at
> >
> org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$5d1a2567.
> query(<generated>)
> > at
> > org.apache.kylin.rest.controller.QueryController.doQueryWithCache(Qu
> > er
> > 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(Cube
> HBaseEndpointRPC.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.j
> ava: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.b987ac977cedbc877732757947111
> > 20 d., hostname=jxq-23-197-78.h.chinabank.com.c
> > n,16020,1464677435379, seqNum=2
> > at
> >
> org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRe
> tryingCaller.java:169)
> > at
> >
> org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel.callExecServic
> e(RegionCoprocessorRpcChannel.java:107)
> > at
> >
> org.apache.hadoop.hbase.ipc.CoprocessorRpcChannel.callMethod(Coprocess
> orRpcChannel.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(C
> > ub
> > eHBaseEndpointRPC.java:393)
> > at
> >
> org.apache.kylin.storage.hbase.cube.v2.CubeHBaseEndpointRPC$2.call(Cub
> eHBaseEndpointRPC.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.jav
> a: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(RSRpcSe
> rvices.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(NativeConstructo
> rAccessorImpl.java:62)
> > at
> >
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingCo
> nstructorAccessorImpl.java:45)
> > at
> java.lang.reflect.Constructor.newInstance(Constructor.java:408)
> > at
> >
> org.apache.hadoop.ipc.RemoteException.instantiateException(RemoteExcep
> tion.java:106)
> > at
> >
> org.apache.hadoop.ipc.RemoteException.unwrapRemoteException(RemoteExce
> ption.java:95)
> > at
> >
> org.apache.hadoop.hbase.protobuf.ProtobufUtil.getRemoteException(Proto
> bufUtil.java:326)
> > at
> >
> org.apache.hadoop.hbase.protobuf.ProtobufUtil.execService(ProtobufUtil
> .java:1622)
> > at
> >
> org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel$1.call(RegionC
> oprocessorRpcChannel.java:104)
> > at
> >
> org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel$1.call(RegionC
> oprocessorRpcChannel.java:94)
> > at
> >
> org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRe
> tryingCaller.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.jav
> a: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(RSRpcSe
> rvices.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(Abs
> > tr
> > actRpcClient.java:222
> > t
> > org.apache.hadoop.hbase.ipc.AbstractRpcClient$BlockingRpcChannelImpl
> > em entation.callBlockingMethod(AbstractRpc
> > Client.java:323)
> > at
> > org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientServic
> > e$ 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
>
--
Best regards,
Shaofeng Shi