Vertica Data Collector


참고 문서 : https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/DATA_COLLECTOR.htm


v_monitor.query_requests 뷰 쿼리

v_monitor.query_requests 뷰 쿼리는 시스템 및 사용자의 모든 쿼리를 보여주는 VIEW입니다.

VIEW쿼리는 4개의 테이블이 조인된 결과를 출력합니다.

v_internal.dc_requests_issued

v_internal.dc_requests_completed

v_internal.dc_errors

v_internal.dc_resource_acquisitions)


v_monitor.query_requests 에서 조회되는 결과는, DATA_COLLECTOR의 컴포넌트 속성(Memory, Disk, Time) 설정값에 따라서 이전 데이터(오래된 데이터 순으로)가 정리됩니다.

 

# System VIEW 조회 : v_monitor.query_requests 뷰 생성 쿼리 출력

SELECT query_string

           FROM   v_internal.vs_system_views

           WHERE  view_schema = 'v_monitor'

           AND view_name = 'query_requests' ;

 

query_string

-------------------------------------------------------------------------------------

create view "v_monitor"."query_requests" as select ri.node_name,

      ri.user_name,

      ri.session_id,

      ri.request_id,

      ri.transaction_id,

      ri.statement_id,

      ri.request_type,

      replace(replace(ri.request, E'\n', ' '), E'\t', ' ') as request,

      ri.label as request_label,

      ri.search_path,

      round(ra.memory_mb, 2) as memory_acquired_mb,

      rc.success,

      de.error_count,

      ri.time                                   as start_timestamp,

      rc.time                                   as end_timestamp,

      datediff('millisecond', ri.time, rc.time) as request_duration_ms,

      rc.time IS NULL                           as is_executing

from

    v_internal.dc_requests_issued ri

    LEFT OUTER JOIN v_internal.dc_requests_completed rc USING (node_name, session_id, request_id)

    LEFT OUTER JOIN (select node_name,

                       session_id,

                       request_id,     

                       count(*) as error_count

                from v_internal.dc_errors

                where error_level >= 20

                group by 1,2,3) de USING (node_name, session_id, request_id)

    LEFT OUTER JOIN (select node_name,

                       transaction_id,

                       statement_id,

                      max(memory_kb)/1024::float as memory_mb

                from v_internal.dc_resource_acquisitions

                where result = 'Granted'

                group by 1,2,3) ra

                USING (node_name, transaction_id, statement_id)

;

 

(1 row)

 

# System Table 조회(VIEW(is_view) 여부 확인)

SELECT table_schema, table_name, table_description, is_view

FROM   v_internal.vs_system_tables

WHERE table_name ILIKE '%query_requests%'

ORDER BY table_schema, table_name;

 

table_schema |   table_name   |     table_description      | is_view

--------------+----------------+----------------------------+---------

 v_monitor   | query_requests | User-issued query requests | t

(1 row)

 


Data Collector 조회 및 설정

# v_monitor.query_requests 뷰 쿼리에 포함된 컴포넌트

RequestsCompleted

RequestsIssued

Errors

ResourceAcquisitions

 

# 컴포넌트 조회

SELECT DISTINCT component, description

           FROM data_collector

           ORDER BY 1 ASC;

 

component         |              description                          

------------------------+-----------------------------------------------------

Errors              | History of all errors+warnings encountered

RequestsCompleted  | History of all SQL requests completed

RequestsIssued      | History of all SQL requests issued

ResourceAcquisitions  | History of all resource acquisitions

 

# 각 컴포넌트 설정값 조회 : GET_DATA_COLLECTOR_POLICY

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('RequestsCompleted');

                          GET_DATA_COLLECTOR_POLICY                         

-----------------------------------------------------------------------------

 2000KB kept in memory, 50000KB kept on disk. Time based retention disabled.

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('RequestsIssued');

                          GET_DATA_COLLECTOR_POLICY                         

-----------------------------------------------------------------------------

 2000KB kept in memory, 50000KB kept on disk. Time based retention disabled.

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('Errors');

                          GET_DATA_COLLECTOR_POLICY                         

-----------------------------------------------------------------------------

 1000KB kept in memory, 10000KB kept on disk. Time based retention disabled.

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('ResourceAcquisitions');

                          GET_DATA_COLLECTOR_POLICY                         

-----------------------------------------------------------------------------

 1000KB kept in memory, 10000KB kept on disk. Time based retention disabled.

(1 row)


1. 메모리 및 디스크 저장 사이즈 설정 ( SET_DATA_COLLECTOR_POLICY ), 단위 : KB

문법 : SET_DATA_COLLECTOR_POLICY('component', 'memoryKB', 'diskKB' [,'interval']  )

> DATA_COLLECTOR_POLICY 는 설정 해제 없이, 변경만 가능

 

RequestsIssued 컴포넌트의 메모리에 5MB, 디스크에 100MB 설정

InnoDB=> SELECT SET_DATA_COLLECTOR_POLICY('RequestsIssued', '5000', '100000');

 SET_DATA_COLLECTOR_POLICY

---------------------------

 SET

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('RequestsIssued');

                          GET_DATA_COLLECTOR_POLICY                          

------------------------------------------------------------------------------

 5000KB kept in memory, 100000KB kept on disk. Time based retention disabled.

(1 row)

 


2. 시간에 따른 주기적 설정 ( SET_DATA_COLLECTOR_TIME_POLICY ), 단위 : year, month, hour, minutes

문법 : SET_DATA_COLLECTOR_TIME_POLICY( ['component',] 'interval' )


2-1. v_monitor.query_requests 데이터를 설정된 시간 내에서만 유지( 3분 이내의 데이터만 유지)

InnoDB=> SELECT SET_DATA_COLLECTOR_TIME_POLICY('v_monitor.query_requests', '3 minutes'::interval);

 SET_DATA_COLLECTOR_TIME_POLICY

--------------------------------

 SET

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('RequestsCompleted');  

                      GET_DATA_COLLECTOR_POLICY                       

------------------------------------------------------------------------

 2000KB kept in memory, 50000KB kept on disk. 00:03 hours kept on disk.

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('RequestsIssued');  

                        GET_DATA_COLLECTOR_POLICY                       

-------------------------------------------------------------------------

 5000KB kept in memory, 100000KB kept on disk. 00:03 hours kept on disk.

(1 row)

 

2-2. v_monitor.query_requests 데이터를 3 3개월 3시간 3분 이내의 데이터만 유지

InnoDB=> SELECT SET_DATA_COLLECTOR_TIME_POLICY('v_monitor.query_requests', '3 year 3 month 3 hour 3 minutes'::interval);

 SET_DATA_COLLECTOR_TIME_POLICY

--------------------------------

 SET

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('RequestsCompleted');  

                               GET_DATA_COLLECTOR_POLICY                               

----------------------------------------------------------------------------------------

 2000KB kept in memory, 50000KB kept on disk. 3 years 90 days 03:03 hours kept on disk.

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('RequestsIssued');

                                GET_DATA_COLLECTOR_POLICY                               

-----------------------------------------------------------------------------------------

 5000KB kept in memory, 100000KB kept on disk. 3 years 90 days 03:03 hours kept on disk.

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('Errors');

                              GET_DATA_COLLECTOR_POLICY                               

----------------------------------------------------------------------------------------

 1000KB kept in memory, 10000KB kept on disk. 3 years 90 days 03:03 hours kept on disk.

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('ResourceAcquisitions');

                              GET_DATA_COLLECTOR_POLICY                               

----------------------------------------------------------------------------------------

 1000KB kept in memory, 10000KB kept on disk. 3 years 90 days 03:03 hours kept on disk.

(1 row)

 

2-3. v_monitor.query_requests 에 설정된 Time 설정값 해제

InnoDB=> SELECT SET_DATA_COLLECTOR_TIME_POLICY('v_monitor.query_requests', '-1');

 SET_DATA_COLLECTOR_TIME_POLICY

--------------------------------

 SET

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('RequestsCompleted');

                          GET_DATA_COLLECTOR_POLICY                         

-----------------------------------------------------------------------------

 2000KB kept in memory, 50000KB kept on disk. Time based retention disabled.

(1 row)

 

InnoDB=> SELECT GET_DATA_COLLECTOR_POLICY('RequestsIssued');

                          GET_DATA_COLLECTOR_POLICY                          

------------------------------------------------------------------------------

 5000KB kept in memory, 100000KB kept on disk. Time based retention disabled.

(1 row)

 

※ 위 내용은, 여러 자료를 참고하거나 제가 주관적으로 정리한 것입니다.
   잘못된 정보나 보완이 필요한 부분을, 댓글 또는 메일로 보내주시면 많은 도움이 되겠습니다.
07 22, 2016 09:09 07 22, 2016 09:09


Trackback URL : http://develop.sunshiny.co.kr/trackback/1061

Leave a comment

« Previous : 1 : 2 : 3 : 4 : 5 : 6 : 7 : 8 : ... 648 : Next »

Recent Posts

  1. HDFS - Python Encoding 오류 처리
  2. HP - Vertica ROS Container 관련 오류...
  3. HDFS - Hive 실행시 System Time 오류
  4. HP - Vertica 사용자 쿼리 이력 테이블...
  5. Client에서 HDFS 환경의 데이터 처리시...

Recent Comments

  1. 안녕하세요^^ 배그핵
  2. 안녕하세요^^ 도움이 되셨다니, 저... sunshiny
  3. 정말 큰 도움이 되었습니다.. 감사합... 사랑은
  4. 네, 안녕하세요. 댓글 남겨 주셔서... sunshiny
  5. 감사합니다 많은 도움 되었습니다!ㅎㅎ 프리시퀸스

Recent Trackbacks

  1. Mysql - mysql 설치후 Character set... 멀고 가까움이 다르기 때문 %M

Calendar

«   10 2019   »
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

Bookmarks

  1. 위키피디아
  2. MysqlKorea
  3. 오라클 클럽
  4. API - Java
  5. Apache Hadoop API
  6. Apache Software Foundation
  7. HDFS 생태계 솔루션
  8. DNSBL - Spam Database Lookup
  9. Ready System
  10. Solaris Freeware
  11. Linux-Site
  12. 윈디하나의 솔라나라

Site Stats

TOTAL 2724076 HIT
TODAY 542 HIT
YESTERDAY 589 HIT