[jira] [Commented] (KYLIN-5268) kylin4.0.1服务每天都会内存溢出java.lang.OutOfMemoryError: Java heap space

["zhenkuan_zhang (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/KYLIN-5268?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17606831#comment-17606831 ] 

zhenkuan_zhang commented on KYLIN-5268:
---------------------------------------

2022-09-15 21:46:54,588 INFO  [BadQueryDetector] service.BadQueryDetector:204 : Slow query has been running 1328.617 seconds (project:KPY_RANGER, thread: 0x42, user:h26, query id:fb8d154f-726c-3f74-bfba-486b484952a9) -- SELECT "自訂 SQL 查詢"."FAIL" AS "FAIL" FROM (....）"自訂 SQL 查詢"
2022-09-15 21:46:54,647 DEBUG [BadQueryDetector] badquery.BadQueryHistoryManager:65 : Loaded 50 Bad Query(s)
2022-09-15 21:46:54,690 INFO  [localhost-startStop-1-EventThread] state.ConnectionStateManager:228 : State change: LOST
2022-09-15 21:46:54,692 ERROR [localhost-startStop-1-EventThread] imps.CuratorFrameworkImpl:557 : Background operation retry gave up
org.apache.zookeeper.KeeperException$ConnectionLossException: KeeperErrorCode = ConnectionLoss
	at org.apache.zookeeper.KeeperException.create(KeeperException.java:99)
	at org.apache.curator.framework.imps.CuratorFrameworkImpl.checkBackgroundRetry(CuratorFrameworkImpl.java:728)
	at org.apache.curator.framework.imps.CuratorFrameworkImpl.processBackgroundOperation(CuratorFrameworkImpl.java:516)
	at org.apache.curator.framework.imps.BackgroundSyncImpl$1.processResult(BackgroundSyncImpl.java:53)
	at org.apache.zookeeper.ClientCnxn$EventThread.processEvent(ClientCnxn.java:609)
	at org.apache.zookeeper.ClientCnxn$EventThread.run(ClientCnxn.java:498)
2022-09-15 21:46:54,707 INFO  [BadQueryDetector] service.BadQueryDetector:192 : Problematic thread 0x42 Query fb8d154f-726c-3f74-bfba-486b484952a9-66, query id: fb8d154f-726c-3f74-bfba-486b484952a9
	at org.apache.spark.sql.execution.SparkPlan$$anon$1.next(SparkPlan.scala:284)
	at org.apache.spark.sql.execution.SparkPlan$$anon$1.next(SparkPlan.scala:278)
	at scala.collection.Iterator$class.foreach(Iterator.scala:893)
	at org.apache.spark.sql.execution.SparkPlan$$anon$1.foreach(SparkPlan.scala:278)
	at org.apache.spark.sql.execution.SparkPlan$$anonfun$executeCollect$1.apply(SparkPlan.scala:300)
	at org.apache.spark.sql.execution.SparkPlan$$anonfun$executeCollect$1.apply(SparkPlan.scala:299)
	at scala.collection.IndexedSeqOptimized$class.foreach(IndexedSeqOptimized.scala:33)
	at scala.collection.mutable.ArrayOps$ofRef.foreach(ArrayOps.scala:186)
	at org.apache.spark.sql.execution.SparkPlan.executeCollect(SparkPlan.scala:299)
	at org.apache.spark.sql.Dataset.org$apache$spark$sql$Dataset$$collectFromPlan(Dataset.scala:3389)

2022-09-15 21:46:54,708 INFO  [BadQueryDetector] service.BadQueryDetector:204 : Slow query has been running 1328.68 seconds 

> kylin4.0.1服务每天都会内存溢出java.lang.OutOfMemoryError: Java heap space
> ---------------------------------------------------------------
>
>                 Key: KYLIN-5268
>                 URL: https://issues.apache.org/jira/browse/KYLIN-5268
>             Project: Kylin
>          Issue Type: Bug
>            Reporter: zhenkuan_zhang
>            Priority: Major
>         Attachments: kylin_query.log
>
>
> Hi，最近发现我们的kylin服务非常不稳定，三个节点，每天都会有节点出现内存溢出的状况，jvm从16G调到48G也同样会内存溢出。通过分析Dump 出 java_pid34184.hprof文件，发现内存会被两个查询完全占满导致jvm内存溢出，sql是通过tableau发送来的。sql如下（该事实表行数上亿）：
> 1.
> SELECT "自訂 SQL 查詢"."FAIL" AS "FAIL" FROM (   
>   SELECT DT  TEST_DATE,TEST_STATION_CODE,SUBSTRING(TEST_LINE,1,6) TEST_FLOOR,TEST_LINE,CONFIG,REGION,LOCALLIZATION,
>   SUBSTRING(ATL FROM  1 for POSITION('*-' IN ATL from 1)-1 ) VENDERATL,SUBSTRING(ATL FROM POSITION('-' IN ATL FROM 5)+1 FOR 3) WEEKATL,SUBSTRING(ATL FROM POSITION('-' IN ATL FROM 6)+1 FOR 4) DATEATL,SUBSTRING(ATL FROM POSITION('*-' IN ATL FROM 1)+7 FOR 4) EEEEATL ,    
>   INPUT,FAIL,RETEST FROM BI_DW.KPY_FACT_SUM_DATA
>   ) "自訂 SQL 查詢"
> 2.
> SELECT "自訂 SQL 查詢"."RETEST" AS "RETEST" FROM (   
>   SELECT DT  TEST_DATE,TEST_STATION_CODE,SUBSTRING(TEST_LINE,1,6) TEST_FLOOR,TEST_LINE,CONFIG,REGION,LOCALLIZATION,
>   SUBSTRING(ATL FROM  1 for POSITION('*-' IN ATL from 1)-1 ) VENDERATL,SUBSTRING(ATL FROM POSITION('-' IN ATL FROM 5)+1 FOR 3) WEEKATL,SUBSTRING(ATL FROM POSITION('-' IN ATL FROM 6)+1 FOR 4) DATEATL,SUBSTRING(ATL FROM POSITION('*-' IN ATL FROM 1)+7 FOR 4) EEEEATL ,    
>   INPUT,FAIL,RETEST FROM BI_DW.KPY_FACT_SUM_DATA
>   ) "自訂 SQL 查詢"
> 一方面我们在与tableau方联系，沟通为什么会发出这么奇怪的sql。
> 另一方面希望得到kylin的支持，看看有没有什么解决方法，
> 比如kylin.query.force-limit=1000 可以为 select * from table 加上 limit 1000，有没有办法限制 select a from table。
> 或者有没有其他的解决方法。



--
This message was sent by Atlassian Jira
(v8.20.10#820010)