OK,我修改下dimension试试。 谢谢！
-----邮件原件----- 发件人: ShaoFeng Shi [mailto:[email protected]] 发送时间: 2016年7月1日 13:23 收件人: [email protected] 抄送: [email protected] 主题: 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 仇同心 <[email protected]>: > 少峰，您好： > two "distinct count" measures, are HyperLogLog counter。 > > 一、 group by b.dim_month_name 这个是derived measure。 > > 测试了下，如果where 条件是月，group by 周 查询时间是66秒， where 条件是周，group by > 日，查询时间是9秒 > > 如果where 条件是年，group by 月 ；where 条件是上半年，group by 季度或者月 > 都会内存溢出错误。 > > Hbase的heap size大小也调到了32GB。kylin.query.cube.visit.timeout.times > 调到了10 > > 二、如果group by的是nomal维度则查询很快 > > 三、如果增加hbase的regionderver是否可解决此问题 ？ > > > 谢谢~ > > > > -----邮件原件----- > 发件人: ShaoFeng Shi [mailto:[email protected]] > 发送时间: 2016年7月1日 9:32 > 收件人: dev > 抄送: [email protected] > 主题: Re: kylin查询，报超时异常:Timeout visiting cube! > > hi tongxing, > > The root cause is OutOfMemory: > > Caused by: java.lang.OutOfMemoryError > > at > java.io.ByteArrayOutputStream.hugeCapacity(ByteArrayOutputStream.java: > 123) > > at > java.io.ByteArrayOutputStream.grow(ByteArrayOutputStream.java:117) > > at > java.io.ByteArrayOutputStream.ensureCapacity(ByteArrayOutputStream.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 仇同心 <[email protected]>: > > > 大家好： > > Kylin查询时报超时异常,sql是： > > select b.dim_month_name,sum(a.ordr_amt) as 订单金额, > > sum(a.pay_amt) as 支付金额,count(*) as 订单数, > > count(distinct a.user_pin)as 用户数,count(distinct a.is_new) as 新用户数 > > from dmt.dmt_mem_vip_tx_ordr_det_i_d a left join dim.dim_day b on > > a.pay_time=b.dim_day_txdate left join dim.dim_kylin_mem_type_buss c > > on a.mem_type=c.mem_type left join dim.dim_kylin_mem_type_source d > > on a.mem_type_source=d.mem_type_source > > where b.dim_year_name='2016年' > > group by b.dim_month_name > > order by b.dim_month_name asc > > LIMIT 50000 > > > > > > 错误日志为： > > 016-06-30 17:11:36,149 ERROR [http-bio-7070-exec-10] > > controller.QueryController:209 : Exception when execute sql > > java.sql.SQLException: Error while executing SQL "select > > b.dim_month_name,sum(a.ordr_amt) as 订单金额, > > sum(a.pay_amt) as 支付金额,count(*) as 订单数, > > count(distinct a.user_pin)as 用户数,count(distinct a.is_new) as 新用户数 > > from dmt.dmt_mem_vip_tx_ordr_det_i_d a left join dim.dim_day b on > > a.pay_time=b.dim_day_txdate left join dim.dim_kylin_mem_type_buss c > > on a.mem_type=c.mem_type left join dim.dim_kylin_mem_type_source d > > on a.mem_type_source=d.mem_type_source > > where b.dim_year_name='2016年' > > group by b.dim_month_name > > order by b.dim_month_name asc > > LIMIT 50000": Timeout visiting cube! > > at > > org.apache.calcite.avatica.Helper.createException(Helper.java:56) > > at > > org.apache.calcite.avatica.Helper.createException(Helper.java:41) > > at > > > org.apache.calcite.avatica.AvaticaStatement.executeInternal(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
