버티카 성능 진단

버티카 모니터링할때 어느부분에서 자원을 많이 쓰는지 확인이 가능하다.

일부 원천 데이터자체가 부정확하긴하지만 어디에서 문제가 있는지 감을 잡는데는 도움을 줄 것이다.

SELECT substr(current_statement,1,100) as '수행SQL'

     , eep.path_id

     , eep.operator_name

     , eep.TRANSACTION_id

     , eep.STATEMENT_id

     --, node_name

     , count(DISTINCT operator_id) num_operators

     , count(DISTINCT eep.node_name) num_nodes

     , round(sum(DECODE(counter_name, 'bytes received', counter_value, NULL))/1024^2,2)::float AS 'bytes_received_mb'

     , round(sum(DECODE(counter_name, 'bytes sent', counter_value, NULL))/1024^2,2)::float AS 'bytes_sent_mb'

     , round(max(DECODE(counter_name, 'execution time (us)', counter_value, NULL))/10^6,2)::float AS 'max_exec_time'

     , round(sum(DECODE(counter_name, 'execution time (us)', counter_value, NULL))/10^6,2)::float AS 'sum_exec_time'

     , sum(DECODE(counter_name, 'rows received', counter_value, NULL)) AS 'rows_received'

     , sum(DECODE(counter_name, 'rows produced', counter_value, NULL)) AS 'rows_produced'

     , round(sum(DECODE(counter_name, 'memory reserved (bytes)', counter_value, NULL))/1024^3,2)::float AS 'memory_reserved_사용량_gb'

     , round(sum(DECODE(counter_name, 'memory allocated (bytes)', counter_value, NULL))/1024^3,2)::float AS 'memory_allocated_사용량_gb'

     , round(SUM(DECODE(counter_name, 'consumer stall (us)', counter_value, NULL))/10^6,2) AS 'cons_stall'

     , round(SUM(DECODE(counter_name, 'producer stall (us)', counter_value, NULL))/10^6,2) AS 'prod_stall'

     , round(SUM(DECODE(counter_name, 'cumulative size of raw temp data (bytes)', counter_value/1024^3, NULL)),2) AS ' temp_data_사용량_gb'      

  FROM execution_engine_profiles eep inner join sessions s

    on eep.session_id = s.session_id 

   and eep.transaction_id = s.transaction_id 

   and eep.statement_id = s.statement_id 

   and eep.node_name=s.node_name

   and current_statement>''

   and current_statement like '%검색키워드%'

 where eep.node_name =(select local_node_name())  

 group by substr(current_statement,1,100),eep.path_id,eep.operator_name,eep.TRANSACTION_id,eep.STATEMENT_id

 order by substr(current_statement,1,100),eep.TRANSACTION_id,eep.STATEMENT_id,eep.path_id,eep.operator_name

종종 위 SQL결과집합과 explain을 조합하여 성능문제를 해결하곤 했다.

8.0.1에서 TEST 하였습니다