HP - Vertica 사용자 쿼리 이력 테이블 (v_monitor.query_requests) 정보
Posted 07 22, 2016 09:09, Filed under: BigData/HP-Vertica(MPP)
Print 
PDFVertica Data Collector
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) |
잘못된 정보나 보완이 필요한 부분을, 댓글 또는 메일로 보내주시면 많은 도움이 되겠습니다.
"BigData / HP-Vertica(MPP)" 분류의 다른 글
| HP - Vertica ROS Container 관련 오류 및 설정 (0) | 2016/08/09 |
Trackback URL : http://develop.sunshiny.co.kr/trackback/1061
