We have migrated to new Google sites!
--SELECT 'You are now connected to database '|| current_database()||' as user '||current_user as Connection_details;
-- 1. Check total no of segments in the DCA
Select count(*) as total_segments_in_the_database from gp_segment_configuration;
-- 2. check_GP_server_segment status
select dbid, content, role, preferred_role, mode, status, port,hostname, address from gp_segment_configuration where role = 'p' order by dbid;
--3. check storage utilization per segment server
\c gpperfmon
select ctime,hostname,filesystem,pg_size_pretty(total_bytes) as total_bytes,pg_size_pretty(bytes_used) as used_bytes, pg_size_pretty(bytes_available) as avail_bytes, (round((bytes_used * 100)/total_bytes::numeric,2))||'%' as pct_used from diskspace_now order by (round((bytes_used * 100)/total_bytes::numeric,2)) desc;
-- 4. Disk Space Availability
select total_tb,used_tb, available_tb, round((used_tb*100)/total_tb::numeric,2) as pct_used, round((available_tb*100)/total_tb::numeric,2) as pct_available from
(
select
sum(round((bytes_used / power(1024,4))::numeric,2)) as used_tb,
sum(round((bytes_available / power(1024,4))::numeric,2)) as available_tb,
sum(round((total_bytes / power(1024,4))::numeric,2)) as total_tb
from
(
select date_trunc('hour', ctime) ctime_hour,
to_char(ctime, 'Month') as "month",
to_char(ctime, 'ddd') as "day",
to_char(ctime, 'Day') as weekday,
to_char(ctime, 'hh24') as "hour",
hostname,
filesystem,
bytes_used,
bytes_available,
total_bytes,
row_number() over (partition by to_char(ctime, 'ddd'), hostname order by total_bytes desc) space_used_rank
from diskspace_now
where filesystem in ('/data1','/data2','/disk1','/disk2')
and hostname like 'sdw%' or hostname like 'sachi%'
) a
where space_used_rank = 1
group by day
) as b;