Greenplum DCA performance monitoring SQL

SQL to monitor DCA performance. We will use gpperfmon database tables and views to create monitoring SQL.

The gadget spec URL could not be found

===================================================================================================

1. SQL Counts

===================================================================================================

SELECT

date_trunc('hour', ctime) AS sample_time,
avg(queries_total) AS queries_total,
min(queries_total) AS queries_total_min,
max(queries_total) AS queries_total_max,
avg(queries_running) AS queries_running,
min(queries_running) AS queries_running_min,
max(queries_running) AS queries_running_max,
avg(queries_queued) AS queries_queued,
min(queries_queued) AS queries_queued_min,
max(queries_queued) AS queries_queued_max
FROM database_history
GROUP BY sample_time
order by sample_time desc ;
===================================================================================================

2. System Utilizations

===================================================================================================

select ctime,

===================================================================================================3.3

===================================================================================================

select date_trunc('hour', ctime) ctime_hour,

min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(100 - cpu_idle)::numeric,2) as avg_cpu_used,
round(min(100 - cpu_idle)::numeric,2) as min_cpu_used,
round(max(100 - cpu_idle)::numeric,2) as max_cpu_used,
round(avg(load2)::numeric,2) as avg_load2,
round(min(load2)::numeric,2) as min_load2,
round(max(load2)::numeric,2) as max_load2,
round(avg(disk_rb_rate)::numeric,2) avg_disk_rb_rate,
round(min(disk_rb_rate)::numeric,2) min_disk_rb_rate,
round(max(disk_rb_rate)::numeric,2) max_disk_rb_rate,
round(avg(disk_wb_rate)::numeric,2) avg_disk_wb_rate,
round(min(disk_wb_rate)::numeric,2) min_disk_wb_rate,
round(max(disk_wb_rate)::numeric,2) max_disk_wb_rate,
round(avg(net_rb_rate)::numeric,2) avg_net_rb_rate,
round(min(net_rb_rate)::numeric,2) min_net_rb_rate,
round(max(net_rb_rate)::numeric,2) max_net_rb_rate,
round(avg(net_wb_rate)::numeric,2) avg_net_wb_rate,
round(min(net_wb_rate)::numeric,2) min_net_wb_rate,
round(max(net_wb_rate)::numeric,2) max_net_wb_rate,
round(avg(mem_actual_used)/power(1024,3)::numeric,2) avg_mem_actual_used_gb,
round(min(mem_actual_used)/power(1024,3)::numeric,2) min_mem_actual_used_gb,
round(max(mem_actual_used)/power(1024,3)::numeric,2) max_mem_actual_used_gb,
round(avg(swap_used)::numeric,2) avg_swap_used,
round(min(swap_used)::numeric,2) min_swap_used,
round(max(swap_used)::numeric,2) max_swap_used
from system_history
where ctime >= '2014-01-01'::timestamp
group by date_trunc('hour', ctime);

===================================================================================================

SELECT

===================================================================================================

SELECT

hostname,
filesystem,
ctime,
total_bytes/(1024*1024*1024) filessytem_capacity_in_GB,
bytes_used/(1024*1024*1024) as total_used_in_GB,
bytes_available/(1024*1024*1024) as total_available_GB
FROM diskspace_now
order by hostname,filesystem,ctime desc ;
===================================================================================================

===================================================================================================

select ctime,

date_trunc('hour', ctime) AS ctime_hour,
hostname,
to_char(ctime, 'hh') as "hour",
to_char(ctime, 'Month') as "month",
to_char(ctime, 'dd') as "day",
to_char(ctime, 'Day') weekday,
1 - cpu_idle as cpu_used,
load2,
disk_rb_rate,
disk_wb_rate,
net_rb_rate,
net_wb_rate,
mem_actual_used,
swap_used
from system_history
where ctime >= current_date order by ctime limit 120;
===================================================================================================

===================================================================================================

select count(*) from system_history;

select * from gp_toolkit.gp_resqueue_status;
select rsqname, rsqcountlimit as active_statement_limit, rsqcostlimit as maximum_cost from gp_toolkit.gp_resqueue_status;
select * from system_history where ctime >= current_date order by ctime limit 120;
===================================================================================================

===================================================================================================

SELECT

===================================================================================================

SELECT
hostname,
filesystem,
ctime,
total_bytes/(1024*1024*1024) filessytem_capacity_in_GB,
bytes_used/(1024*1024*1024) as total_used_in_GB,
bytes_available/(1024*1024*1024) as total_available_GB
FROM diskspace_now
order by hostname,filesystem,ctime desc ;
===================================================================================================
10.

===================================================================================================
select ctime,
date_trunc('hour', ctime) AS ctime_hour,
hostname,
to_char(ctime, 'hh') as "hour",
to_char(ctime, 'Month') as "month",
to_char(ctime, 'dd') as "day",
to_char(ctime, 'Day') weekday,
1 - cpu_idle as cpu_used,
load2,
disk_rb_rate,
disk_wb_rate,
net_rb_rate,
net_wb_rate,
mem_actual_used,
swap_used
from system_history
where ctime >= current_date order by ctime limit 120;
===================================================================================================

11.

===================================================================================================

select * from gp_toolkit.gp_resqueue_status;

select rsqname, rsqcountlimit as active_statement_limit, rsqcostlimit as maximum_cost from gp_toolkit.gp_resqueue_status;
select query_text,db,username,ctime,ccnt,tsubmit,tstart,tfinish,status,rows_out,cpu_elapsed,rsqname,rqppriority from queries_history limit 10;

===================================================================================================
12.

===================================================================================================

-- Running SQL

select query_text,db,username,ctime,ccnt,tsubmit,tstart,tfinish,status,rows_out,cpu_elapsed,rsqname,rqppriority from queries_history ;

===================================================================================================
13.

===================================================================================================

-- Queries by User

select username, db, query_text, to_char(ctime, 'Day') as weekday, count(*)
from queries_history where ctime >= '2014-04-25'::timestamp
group by username, db, query_text, weekday;
===================================================================================================

14.

===================================================================================================

-- Last 1 week query by user and database

select ctime, to_char(ctime, 'Day') as weekday, query_text,username, db, count(*)
from queries_history
where query_text is not NULL
and username not in ('bdacc_capsvc')
and db not in ('gpperfmon')
and ctime between localtimestamp- interval '1 week' and localtimestamp
group by username, db, ctime, weekday, query_text
order by ctime asc;
===================================================================================================

15.

===================================================================================================

select ctime, to_char(ctime, 'Day') as weekday, query_text,username, db, tstart, tfinish, tfinish-tstart as run_time, rows_out,cpu_elapsed
from queries_history
where query_text is not NULL
and username not in ('bdacc_capsvc')
and db not in ('gpperfmon')
and ctime between date_trunc('day',localtimestamp- interval '2 week') and date_trunc('day',localtimestamp)
--group by username, db, ctime, weekday, query_text
order by ctime asc;

===================================================================================================
16.

===================================================================================================

select localtimestamp, localtimestamp- interval '1 week', date_trunc('day',localtimestamp), date_trunc('day',localtimestamp- interval '1 week');

===================================================================================================

17.

===================================================================================================

18.

===================================================================================================

SELECT
date_trunc('hour', ctime) AS sample_time,
avg(queries_total) AS queries_total,
min(queries_total) AS queries_total_min,
max(queries_total) AS queries_total_max,
avg(queries_running) AS queries_running,
min(queries_running) AS queries_running_min,
max(queries_running) AS queries_running_max,
avg(queries_queued) AS queries_queued,
min(queries_queued) AS queries_queued_min,
max(queries_queued) AS queries_queued_max
FROM database_history
GROUP BY sample_time
order by sample_time desc ;
===================================================================================================

19.

===================================================================================================

SELECT
hostname,
filesystem,
ctime,
total_bytes/(1024*1024*1024) filessytem_capacity_in_GB,
bytes_used/(1024*1024*1024) as total_used_in_GB,
bytes_available/(1024*1024*1024) as total_available_GB
FROM diskspace_now
order by hostname,filesystem,ctime desc ;

select * from system_history where ctime >= current_date order by ctime limit 120;

===================================================================================================
20.

===================================================================================================

select ctime,
date_trunc('hour', ctime) AS ctime_hour,
hostname,
to_char(ctime, 'hh') as "hour",
to_char(ctime, 'Month') as "month",
to_char(ctime, 'dd') as "day",
to_char(ctime, 'Day') weekday,
1 - cpu_idle as cpu_used,
load2,
disk_rb_rate,
disk_wb_rate,
net_rb_rate,
net_wb_rate,
mem_actual_used,
swap_used
from system_history
where ctime >= current_date order by ctime limit 120;

===================================================================================================
21.

===================================================================================================

select count(*) from system_history;
select * from gp_toolkit.gp_resqueue_status;
select rsqname, rsqcountlimit as active_statement_limit, rsqcostlimit as maximum_cost from gp_toolkit.gp_resqueue_status;
select query_text,db,username,ctime,ccnt,tsubmit,tstart,tfinish,status,rows_out,cpu_elapsed,rsqname,rqppriority from queries_history limit 10;
select * from queries_history limit 10;

===================================================================================================
22.

===================================================================================================

-- Running SQL

select query_text,db,username,ctime,ccnt,tsubmit,tstart,tfinish,status,rows_out,cpu_elapsed,rsqname,rqppriority from queries_history ;

-- Queries by User

select username, db, query_text, to_char(ctime, 'Day') as weekday, count(*)
from queries_history where ctime >= '2014-04-25'::timestamp
group by username, db, query_text, weekday;

-- Last 1 week query by user and database

select ctime, to_char(ctime, 'Day') as weekday, query_text,username, db, count(*)
from queries_history
where query_text is not NULL
and username not in ('bdacc_capsvc')
and db not in ('gpperfmon')
and ctime between localtimestamp- interval '1 week' and localtimestamp
group by username, db, ctime, weekday, query_text
order by ctime asc;

select ctime, to_char(ctime, 'Day') as weekday, query_text,username, db, tstart, tfinish, tfinish-tstart as run_time, rows_out,cpu_elapsed
from queries_history
where query_text is not NULL
and username not in ('bdacc_capsvc')
and db not in ('gpperfmon')
and ctime between date_trunc('day',localtimestamp- interval '2 week') and date_trunc('day',localtimestamp)
--group by username, db, ctime, weekday, query_text
order by ctime asc;

select localtimestamp, localtimestamp- interval '1 week', date_trunc('day',localtimestamp), date_trunc('day',localtimestamp- interval '1 week');

select ctime, to_char(ctime, 'Day') as weekday, query_text,username, db, tstart, tfinish, tfinish-tstart as run_time, rows_out,cpu_elapsed as
cpu_usage_in_sec, skew_cpu
from queries_history
where query_text is not NULL
and username not in ('bdacc_capsvc')
and db not in ('gpperfmon')
and ctime between date_trunc('day',localtimestamp- interval '2 week') and date_trunc('day',localtimestamp)
--group by username, db, ctime, weekday, query_text
order by ctime desc , run_time desc;

select ctime, to_char(ctime, 'Day') as weekday, query_text,username, db, tstart, tfinish, tfinish-tstart as run_time, rows_out,cpu_elapsed as
cpu_usage_in_sec, skew_cpu
from queries_history
where query_text is not NULL
and username not in ('bdacc_capsvc')
and db not in ('gpperfmon')
and ctime between date_trunc('day',localtimestamp- interval '2 week') and date_trunc('day',localtimestamp)
--group by username, db, ctime, weekday, query_text
order by run_time desc limit 10;

================================================================================================================
23.

================================================================================================================

1. What is running?

List of Queries in Last 7 days
select to_char(ctime, 'Day') as weekday, to_char(ctime, 'dd') as dayofmonth , to_char(ctime, 'month') as month ,to_char(ctime, 'yyyy') as year ,

count(1),rsqname
from queries_history
where query_text is not NULL
and username not in ('bdacc_capsvc')
and db not in ('gpperfmon')
and ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by weekday,dayofmonth,month,year,rsqname
order by dayofmonth;

================================================================================================================
24.

================================================================================================================

2. What was running?

select ctime, to_char(ctime, 'Day') as weekday,to_char(ctime, 'dd') as dayofmonth , to_char(ctime, 'month') as month ,to_char(ctime, 'yyyy') as year,
query_text,username, db, rsqname,tstart, tfinish, tfinish-tstart as run_time, rows_out,cpu_elapsed as cpu_usage_in_sec, skew_cpu
from queries_history
where query_text is not NULL
and username not in ('bdacc_capsvc')
and db not in ('gpperfmon')
and ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
and to_char(ctime, 'dd')=28
order by ctime desc , run_time desc;

================================================================================================================
25.

================================================================================================================

select sh.ctime,query_text,username, db,rsqname,
avg(tfinish-tstart) as run_time,
min(to_char(sh.ctime, 'Month')) as "month",
min(to_char(sh.ctime, 'dd')) as "day",
min(to_char(sh.ctime, 'Day')) weekday,
min(to_char(sh.ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(100 - cpu_idle)::numeric,2) as avg_cpu_used,
round(min(100 - cpu_idle)::numeric,2) as min_cpu_used,
round(max(100 - cpu_idle)::numeric,2) as max_cpu_used,
round(avg(load2)::numeric,2) as avg_load2,
round(min(load2)::numeric,2) as min_load2,
round(max(load2)::numeric,2) as max_load2,
round(avg(disk_rb_rate)::numeric,2) avg_disk_rb_rate,
round(min(disk_rb_rate)::numeric,2) min_disk_rb_rate,
round(max(disk_rb_rate)::numeric,2) max_disk_rb_rate,
round(avg(disk_wb_rate)::numeric,2) avg_disk_wb_rate,
round(min(disk_wb_rate)::numeric,2) min_disk_wb_rate,
round(max(disk_wb_rate)::numeric,2) max_disk_wb_rate,
round(avg(net_rb_rate)::numeric,2) avg_net_rb_rate,
round(min(net_rb_rate)::numeric,2) min_net_rb_rate,
round(max(net_rb_rate)::numeric,2) max_net_rb_rate,
round(avg(net_wb_rate)::numeric,2) avg_net_wb_rate,
round(min(net_wb_rate)::numeric,2) min_net_wb_rate,
round(max(net_wb_rate)::numeric,2) max_net_wb_rate,
round(avg(mem_actual_used)/power(1024,3)::numeric,2) avg_mem_actual_used_gb,
round(min(mem_actual_used)/power(1024,3)::numeric,2) min_mem_actual_used_gb,
round(max(mem_actual_used)/power(1024,3)::numeric,2) max_mem_actual_used_gb,
round(avg(swap_used)::numeric,2) avg_swap_used,
round(min(swap_used)::numeric,2) min_swap_used,
round(max(swap_used)::numeric,2) max_swap_used
from system_history sh,queries_history qh
where sh.ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
and sh.ctime=qh.ctime
and to_char(sh.ctime, 'dd')=28
and username not in ('bdacc_capsvc')
and db not in ('gpperfmon')
group by sh.ctime,query_text,username, db,rsqname;

================================================================================================================
26.

================================================================================================================

--- CPU Utilization

select date_trunc('hour', ctime) ctime_hour,
min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(100 - cpu_idle)::numeric,2) as avg_cpu_used,
round(min(100 - cpu_idle)::numeric,2) as min_cpu_used,
round(max(100 - cpu_idle)::numeric,2) as max_cpu_used
from system_history
where ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by date_trunc('hour', ctime)
order by ctime_hour desc;

================================================================================================================
27

================================================================================================================

--- System Utilization

select date_trunc('hour', ctime) ctime_hour,
min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(load2)::numeric,2) as avg_load2,
round(min(load2)::numeric,2) as min_load2,
round(max(load2)::numeric,2) as max_load2
from system_history
where ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by date_trunc('hour', ctime)
order by ctime_hour desc;

================================================================================================================
28

================================================================================================================

--- Disk Read in Bytes per second

select date_trunc('hour', ctime) ctime_hour,
min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(disk_rb_rate)::numeric,2) avg_disk_rb_rate,
round(min(disk_rb_rate)::numeric,2) min_disk_rb_rate,
round(max(disk_rb_rate)::numeric,2) max_disk_rb_rate
from system_history
where ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by date_trunc('hour', ctime)
order by ctime_hour desc;

================================================================================================================
29

================================================================================================================

--- Disk Write in Bytes per Second

select date_trunc('hour', ctime) ctime_hour,
min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(disk_wb_rate)::numeric,2) avg_disk_wb_rate,
round(min(disk_wb_rate)::numeric,2) min_disk_wb_rate,
round(max(disk_wb_rate)::numeric,2) max_disk_wb_rate
from system_history
where ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by date_trunc('hour', ctime)
order by ctime_hour desc;

================================================================================================================
30

================================================================================================================

--- Network Read in Bytes per second

select date_trunc('hour', ctime) ctime_hour,
min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(net_rb_rate)::numeric,2) avg_net_rb_rate,
round(min(net_rb_rate)::numeric,2) min_net_rb_rate,
round(max(net_rb_rate)::numeric,2) max_net_rb_rate
from system_history
where ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by date_trunc('hour', ctime)
order by ctime_hour desc;

================================================================================================================
31

================================================================================================================

--- Network write in Bytes per second

select date_trunc('hour', ctime) ctime_hour,
min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(net_wb_rate)::numeric,2) avg_net_wb_rate,
round(min(net_wb_rate)::numeric,2) min_net_wb_rate,
round(max(net_wb_rate)::numeric,2) max_net_wb_rate
from system_history
where ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by date_trunc('hour', ctime)
order by ctime_hour desc;

================================================================================================================
32

================================================================================================================

--- Network read/write in Bytes per second

select date_trunc('hour', ctime) ctime_hour,
min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(net_rb_rate)::numeric,2) avg_net_rb_rate,
round(avg(net_wb_rate)::numeric,2) avg_net_wb_rate,
round(min(net_rb_rate)::numeric,2) min_net_rb_rate,
round(min(net_wb_rate)::numeric,2) min_net_wb_rate,
round(max(net_rb_rate)::numeric,2) max_net_rb_rate,
round(max(net_wb_rate)::numeric,2) max_net_wb_rate
from system_history
where ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by date_trunc('hour', ctime)
order by ctime_hour desc;

================================================================================================================
33

================================================================================================================

--- Disk read/write in Bytes per second

select date_trunc('hour', ctime) ctime_hour,
min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(disk_rb_rate)::numeric,2) avg_disk_rb_rate,
round(avg(disk_wb_rate)::numeric,2) avg_disk_wb_rate,
round(min(disk_rb_rate)::numeric,2) min_disk_rb_rate,
round(max(disk_wb_rate)::numeric,2) max_disk_wb_rate,
round(max(disk_rb_rate)::numeric,2) max_disk_rb_rate,
round(min(disk_wb_rate)::numeric,2) min_disk_wb_rate
from system_history
where ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by date_trunc('hour', ctime)
order by ctime_hour desc;

================================================================================================================
34

================================================================================================================

--- Memory Utilization

select date_trunc('hour', ctime) ctime_hour,
min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(mem_actual_used)/power(1024,3)::numeric,2) avg_mem_actual_used_gb,
round(min(mem_actual_used)/power(1024,3)::numeric,2) min_mem_actual_used_gb,
round(max(mem_actual_used)/power(1024,3)::numeric,2) max_mem_actual_used_gb
from system_history
where ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by date_trunc('hour', ctime)
order by ctime_hour desc;

================================================================================================================
35

================================================================================================================

--- Swap Utilization

select date_trunc('hour', ctime) ctime_hour,
min(to_char(ctime, 'Month')) as "month",
min(to_char(ctime, 'dd')) as "day",
min(to_char(ctime, 'Day')) weekday,
min(to_char(ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(swap_used)::numeric,2) avg_swap_used,
round(min(swap_used)::numeric,2) min_swap_used,
round(max(swap_used)::numeric,2) max_swap_used
from system_history
where ctime between date_trunc('day',localtimestamp- interval '1 week') and date_trunc('day',localtimestamp)
group by date_trunc('hour', ctime)
order by ctime_hour desc;

================================================================================================================
36

================================================================================================================

---- DCA Helath Monitoring - Error and warning messages in last 4 weeks

select *
from emcconnect_history
where ctime between date_trunc('day',localtimestamp- interval '2 week') and date_trunc('day',localtimestamp)
and severity in ('Warning','Error')
order by ctime desc,severity ;

select *
from health_history
where ctime between date_trunc('day',localtimestamp- interval '4 week') and date_trunc('day',localtimestamp)
and message in ('down')
order by ctime ;

================================================================================================================
37

================================================================================================================

--- Disk Space Monitoring

select
date_trunc('day',ctime),
hostname,
filesystem,
round(total_bytes/power(1024,3) ::numeric,2) as totoal_gb,
round(bytes_used/power(1024,3) ::numeric,2) as used_gb,
round(bytes_available/power(1024,3) ::numeric,2) as available_gb,
(bytes_available::numeric/total_bytes::numeric*100.0)::numeric(4,2) as pct_free
from diskspace_history
where ctime between date_trunc('day',localtimestamp- interval '1 day') and date_trunc('day',localtimestamp)
order by ctime, hostname,filesystem ;

================================================================================================================
38

================================================================================================================

--- Query plan and Motions Monitoring

select
qh.username, qh.db, qh.query_text, to_char(qh.ctime, 'Day') as weekday,ih.*
from iterators_history ih , queries_history qh
where ih.ctime between date_trunc('day',localtimestamp- interval '1 day') and date_trunc('day',localtimestamp)
and ih.tmid=qh.tmid
and ih.ssid=qh.ssid
and ih.ccnt=qh.ccnt

================================================================================================================
39

================================================================================================================

Select * from gp_segment_configuration;

Select * from dynamic_memory_info limit 200;

================================================================================================================
40

================================================================================================================

select
qh.username, qh.db, qh.query_text, to_char(qh.ctime, 'Day') as weekday,ih.*
from iterators_history ih , queries_history qh
where ih.ctime between date_trunc('day',localtimestamp- interval '1 day') and date_trunc('day',localtimestamp)
and ih.tmid=qh.tmid
and ih.ssid=qh.ssid
and ih.ccnt=qh.ccnt

================================================================================================================
41. Create an external table to monitor the user activity

================================================================================================================

CREATE READABLE EXTERNAL WEB TABLE public.gp_log_master_ext
(
event_time timestamp without time zone,
user_name CHARACTER VARYING(100),
database_name CHARACTER VARYING(100),
process_id CHARACTER VARYING(10),
thread_id CHARACTER VARYING(50),
remote_host CHARACTER VARYING(100),
remote_port CHARACTER VARYING(10),
session_start_time timestamp without time zone,
transaction_id INTEGER,
gp_session_id TEXT,
gp_command_count TEXT,
gp_segment TEXT,
slice_id TEXT,
distr_tranx_id TEXT,
local_tranx_id TEXT,
sub_tranx_id TEXT,
event_severity CHARACTER VARYING(10),
sql_state_code CHARACTER VARYING(10),
event_message TEXT,
event_detail TEXT,
event_hint TEXT,
internal_query TEXT,
internal_query_pos INTEGER,
event_context TEXT,
debug_query_string TEXT,
error_cursor_pos INTEGER,
func_name TEXT,
file_name TEXT,
file_line INTEGER,
stack_trace TEXT
)
EXECUTE E'cat $MASTER_DATA_DIRECTORY/pg_log/*.csv' ON MASTER
FORMAT 'CSV' (delimiter ',' null '' escape '"' quote '"')
ENCODING 'WIN1251';

=== User connection Monitoring

CREATE READABLE EXTERNAL WEB TABLE public.gp_log_master_ext
(
event_time timestamp without time zone,
user_name CHARACTER VARYING(100),
database_name CHARACTER VARYING(100),
process_id CHARACTER VARYING(10),
thread_id CHARACTER VARYING(50),
remote_host CHARACTER VARYING(100),
remote_port CHARACTER VARYING(10),
session_start_time timestamp without time zone,
transaction_id INTEGER,
gp_session_id TEXT,
gp_command_count TEXT,
gp_segment TEXT,
slice_id TEXT,
distr_tranx_id TEXT,
local_tranx_id TEXT,
sub_tranx_id TEXT,
event_severity CHARACTER VARYING(10),
sql_state_code CHARACTER VARYING(10),
event_message TEXT,
event_detail TEXT,
event_hint TEXT,
internal_query TEXT,
internal_query_pos INTEGER,
event_context TEXT,
debug_query_string TEXT,
error_cursor_pos INTEGER,
func_name TEXT,
file_name TEXT,
file_line INTEGER,
stack_trace TEXT
)
EXECUTE E'cat $MASTER_DATA_DIRECTORY/pg_log/*.csv' ON MASTER
FORMAT 'CSV' (delimiter ',' null '' escape '"' quote '"')
ENCODING 'WIN1251';

=== Finding distribution keys

SELECT pgn.nspname as schemaname,
pgc.relname as tablename,
pga.attname as distributionkey
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER BY pgn.nspname,
pgc.relname;

Comments