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. Hi, I do think this is a great blo... 룸싸롱 01시 43분
  2. Thanks in favor of sharing such a... 리니지 프리서버 01 20,
  3. 안녕하세요^^ 배그핵
  4. 안녕하세요^^ 도움이 되셨다니, 저... sunshiny
  5. 정말 큰 도움이 되었습니다.. 감사합... 사랑은

Recent Trackbacks

  1. invoice printing and mailing invoice printing and mailing 20 01
  2. cabo san lucas packages cabo san lucas packages 20 01
  3. london relocation services fees london relocation services fees 20 01
  4. printing and mailing companies printing and mailing companies 20 01
  5. Web Site Web Site 19 01

Calendar

«   01 2020   »
      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 2819309 HIT
TODAY 100 HIT
YESTERDAY 1318 HIT