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 03:20:07 UTC
答复: kylin查询，报超时异常:Timeout visiting cube!
少峰，您好：
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.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 仇同心 <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(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
Re: 答复: 答复: kylin查询，报超时异常:Timeout visiting cube!
Posted by ShaoFeng Shi <sh...@apache.org>.
yes, "hierarchy" is a good option for such dimensions;
2016-07-04 16:56 GMT+08:00 仇同心 <qi...@jd.com>:
> 少峰，您好：
> "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.
>
> define "month" and "quarter" as normal dimension so to reduce the post
> aggregation.
> 您的意思是：设置 年、上半年、季度、月、周、日 为normal dimension。那么在aggregation groups
> 时是否需要设置年、上半年、季度、月、周、日为Hierarchies dimensions？
>
> 谢谢！
>
>
> -----邮件原件-----
> 发件人: 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
>
--
Best regards,
Shaofeng Shi
Re: 答复: 答复: kylin查询，报超时异常:Timeout visiting cube!
Posted by ShaoFeng Shi <sh...@apache.org>.
yes, "hierarchy" is a good option for such dimensions;
2016-07-04 16:56 GMT+08:00 仇同心 <qi...@jd.com>:
> 少峰，您好：
> "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.
>
> define "month" and "quarter" as normal dimension so to reduce the post
> aggregation.
> 您的意思是：设置 年、上半年、季度、月、周、日 为normal dimension。那么在aggregation groups
> 时是否需要设置年、上半年、季度、月、周、日为Hierarchies dimensions？
>
> 谢谢！
>
>
> -----邮件原件-----
> 发件人: 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
>
--
Best regards,
Shaofeng Shi
答复: 答复: kylin查询，报超时异常:Timeout visiting cube!
Posted by 仇同心 <qi...@jd.com>.
少峰，您好：
"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.
define "month" and "quarter" as normal dimension so to reduce the post aggregation.
您的意思是：设置 年、上半年、季度、月、周、日 为normal dimension。那么在aggregation groups 时是否需要设置年、上半年、季度、月、周、日为Hierarchies dimensions？
谢谢！
-----邮件原件-----
发件人: 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
答复: 答复: kylin查询，报超时异常:Timeout visiting cube!
Posted by 仇同心 <qi...@jd.com>.
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
答复: 答复: kylin查询，报超时异常:Timeout visiting cube!
Posted by 仇同心 <qi...@jd.com>.
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
答复: 答复: kylin查询，报超时异常:Timeout visiting cube!
Posted by 仇同心 <qi...@jd.com>.
少峰，您好：
"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.
define "month" and "quarter" as normal dimension so to reduce the post aggregation.
您的意思是：设置 年、上半年、季度、月、周、日 为normal dimension。那么在aggregation groups 时是否需要设置年、上半年、季度、月、周、日为Hierarchies dimensions？
谢谢！
-----邮件原件-----
发件人: 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
Re: 答复: kylin查询，报超时异常:Timeout visiting cube!
Posted by ShaoFeng Shi <sh...@apache.org>.
"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.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 仇同心 <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(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
>
--
Best regards,
Shaofeng Shi
Re: 答复: kylin查询，报超时异常:Timeout visiting cube!
Posted by ShaoFeng Shi <sh...@apache.org>.
"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.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 仇同心 <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(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
>
--
Best regards,
Shaofeng Shi