We have migrated to new Google sites!
SQL to monitor DCA performance. We will use gpperfmon database tables and views to create monitoring SQL.
===================================================================================================
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,
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
===================================================================================================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);
===================================================================================================
4.
===================================================================================================
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 ;
===================================================================================================
5.
===================================================================================================
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 ;
===================================================================================================
6.
===================================================================================================
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;
===================================================================================================
7.
===================================================================================================
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;
===================================================================================================
8.
===================================================================================================
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 ;
===================================================================================================
9.
===================================================================================================
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.
===================================================================================================
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 run_time desc;
===================================================================================================
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';
=== 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;