We have migrated to new Google sites!
-- check missing stats report
select * from gp_toolkit.gp_stats_missing;
-- check bloat diagnosis report
select * from gp_toolkit.gp_bloat_diag;
-- check tables with random distribution key
select table_owner,table_name, COALESCE(distribution_keys,'DISTRIBUTED RANDOMLY') distribution_keys
from
(
SELECT pgn.nspname as table_owner,
pgc.relname as table_name,
pga.attname as distribution_keys
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) as a where COALESCE(distribution_keys,'DISTRIBUTED RANDOMLY')='DISTRIBUTED RANDOMLY';
-- Check data distribution skew by calculating the coefficient of variation (CV) for the data stored on each segment.
select * from gp_toolkit.gp_skew_coefficients;
-- check data distribution skew by calculating the percentage of the system that is idle during a table scan, which is an indicator of processing data skew.
select * from gp_toolkit.gp_skew_idle_fractions;
-- check table not analyzed in last 3 days
SELECT
n.nspname,
c.relname,
a.statime,
'analyze '||n.nspname||'.'||c.relname||';'
FROM
pg_class c INNER JOIN pg_namespace n
ON (c.relnamespace = n.oid)
LEFT OUTER JOIN pg_partitions pgp
ON (
pgp.schemaname = n.nspname
AND pgp.tablename = c.relname
)
LEFT OUTER JOIN (
SELECT DISTINCT
objid,
statime
FROM
pg_stat_last_operation
WHERE
staactionname in ('ANALYZE')
) a
ON (a.objid = c.oid)
WHERE
n.nspname NOT IN ('pg_catalog','information_schema','gp_toolkit')
and c.relname not like 'stg%' and c.relname not like 'swap%'
AND relkind in ('r')
AND c.relstorage = 'h'
and date_trunc('day',statime) < date_trunc('day',localtimestamp- interval '3 day')
ORDER BY reltuples DESC;
-- check worst performing SQL
\c gpperfmon
select sh.ctime,query_text,username, db,rsqname,
avg(tfinish-tstart) as run_time,
--date_part('hour',tfinish - tstart)*60+ date_part('minute',tfinish - tstart)
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 '2 days') and date_trunc('day',localtimestamp)
and sh.ctime=qh.ctime
--and to_char(sh.ctime, 'dd')=28
--and username not in ('gpmon')
and db not in ('gpperfmon')
and date_part('hour',tfinish - tstart)*60+ date_part('minute',tfinish - tstart)> 30
group by sh.ctime,query_text,username, db,rsqname;