写了一个日常对 greenplum 数据库进行检查,记录数据库当前的整体情况,需自己修改数据库信息与邮件发送配置才可正常使用。
#!/bin/bash
source ~/.bash_profile
source /usr/local/greenplum-cc-web/gpcc_path.sh
source /usr/local/greenplum-db/greenplum_path.sh
export database_name=
export database_port=
export local_dest=/home/gpadmin/csh
cd $local_dest
export mailfrom=
export maillist=
export mailsmtp=
export mailxu=
export mailxp=
export num=`psql -d "$database_name" -p "$database_port" -c "copy (select count(*) from pg_stat_activity where current_query <> '<IDLE>' and usename='bi_develop') to stdout"`
if [ "$num" -gt 8 ]
then
exit
fi
echo "<HTML>" > $local_dest/daily_check.html
echo "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#FAF0E6; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#C2C2C2; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style>" >> $local_dest/daily_check.html
echo "<BODY>" >> $local_dest/daily_check.html
#当前集群的磁盘使用情况
echo " Data Disk Usage " >> $local_dest/daily_check.html
psql -d $database_name -p $database_port -c "copy (select distinct hostname from gp_segment_configuration order by 1) to stdout" > $local_dest/host_info.txt
gpssh -f $local_dest/host_info.txt df -h | grep /data | grep -v 192 > $local_dest/disk_info.txt
echo "<table width=90% border=1 align=center>" >> $local_dest/daily_check.html
echo "<tr>" >> $local_dest/daily_check.html
echo "<th>host_name</th>" >> $local_dest/daily_check.html
echo "<th>disk_name</th>" >> $local_dest/daily_check.html
echo "<th>total_size</th>" >> $local_dest/daily_check.html
echo "<th>used_size</th>" >> $local_dest/daily_check.html
echo "<th>free_size</th>" >> $local_dest/daily_check.html
echo "<th>usage</th>" >> $local_dest/daily_check.html
echo "<th>mount_dir</th>" >> $local_dest/daily_check.html
echo "</tr>" >> $local_dest/daily_check.html
while read diskinfo
do
host_name=`echo "$diskinfo" | sed 's/\[//g' | sed 's/\]//g' | awk {'print $1'}`
disk_name=`echo "$diskinfo" | sed 's/\[//g' | sed 's/\]//g' | awk {'print $2'}`
total_size=`echo "$diskinfo" | sed 's/\[//g' | sed 's/\]//g' | awk {'print $3'}`
used_size=`echo "$diskinfo" | sed 's/\[//g' | sed 's/\]//g' | awk {'print $4'}`
free_size=`echo "$diskinfo" | sed 's/\[//g' | sed 's/\]//g' | awk {'print $5'}`
usage=`echo "$diskinfo" | sed 's/\[//g' | sed 's/\]//g' | awk {'print $6'}`
mount_dir=`echo "$diskinfo" | sed 's/\[//g' | sed 's/\]//g' | awk {'print $7'}`
echo "<tr>" >> $local_dest/daily_check.html
echo "<td>$host_name</td>" >> $local_dest/daily_check.html
echo "<td>$disk_name</td>" >> $local_dest/daily_check.html
echo "<td>$total_size</td>" >> $local_dest/daily_check.html
echo "<td>$used_size</td>" >> $local_dest/daily_check.html
echo "<td>$free_size</td>" >> $local_dest/daily_check.html
echo "<td>$usage</td>" >> $local_dest/daily_check.html
echo "<td>$mount_dir</td>" >> $local_dest/daily_check.html
echo "</tr>" >> $local_dest/daily_check.html
done < $local_dest/disk_info.txt
echo "</table>" >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
echo " Data Disk Free " >> $local_dest/daily_check.html
psql -T 'width=90% border=1 align=center' -H -d $database_name -p $database_port -c "SELECT gp_disk_free.dfsegment AS segment_id,
gp_disk_free.dfhostname AS hostname,
gp_disk_free.dfdevice AS diskname,
round(gp_disk_free.dfspace ::numeric * 1.0 / 1024 ::numeric / 1024
::numeric,
2) AS free_gb,
round(gp_disk_free.dfspace ::numeric * 1.0 / 1024 ::numeric / 1024
::numeric / 1024 ::numeric,
2) AS free_tb
FROM ONLY gp_toolkit.gp_disk_free
ORDER BY gp_disk_free.dfsegment" | sed '$d' | sed '$d' >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#查看各个数据库的大小
echo " Database Size List " >> $local_dest/daily_check.html
psql -T 'width=90% border=1 align=center' -H -d $database_name -p $database_port -c "select datname,round(pg_database_size(datname)*1.0/1024/1024/1024,2) size_gb from pg_database order by 2 desc" | sed '$d' | sed '$d' >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#查看各个节点的启动时间
echo " Instance Runtime Information " >> $local_dest/daily_check.html
psql -t -d $database_name -p $database_port -c "select hostname,port from gp_segment_configuration where role = 'p' order by 1" |sed 's/[[:space:]]//g'|awk -F '|' {'print $1","$2'}|sed '$d' > $local_dest/segment_info.txt
echo "<table width=90% border=1 align=center>" >> $local_dest/daily_check.html
echo "<tr valign=top>" >> $local_dest/daily_check.html
echo "<th>host_name</th>" >> $local_dest/daily_check.html
echo "<th>start_time</th>" >> $local_dest/daily_check.html
echo "<th>run_time</th>" >> $local_dest/daily_check.html
echo "</tr>" >> $local_dest/daily_check.html
while read param
do
export seg_host=`echo "$param"|awk -F ',' {'print $1'}`
export seg_port=`echo "$param"|awk -F ',' {'print $2'}`
PGOPTIONS="-c gp_session_role=utility" psql -t -h "$seg_host" -p "$seg_port" -d $database_name -c "select '$seg_host' hostname,pg_postmaster_start_time(),now()-pg_postmaster_start_time() run_time" | sed '$d' > $local_dest/seg_info.txt
while read seg
do
host_name=`echo "$seg" | awk -F '|' {'print $1'}`
start_time=`echo "$seg" | awk -F '|' {'print $2'}`
run_time=`echo "$seg" | awk -F '|' {'print $3'}`
echo "<tr>" >> $local_dest/daily_check.html
echo "<td>$host_name</td>" >> $local_dest/daily_check.html
echo "<td>$start_time</td>" >> $local_dest/daily_check.html
echo "<td>$run_time</td>" >> $local_dest/daily_check.html
echo "</tr>" >> $local_dest/daily_check.html
done < $local_dest/seg_info.txt
done < $local_dest/segment_info.txt
echo "</table>" >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#当前集群instance状态
echo " Instance Status Information " >> $local_dest/daily_check.html
psql -d $database_name -p $database_port -T 'width=90% border=1 align=center' -H -c "select content,role,preferred_role,mode,status,hostname from gp_segment_configuration order by 1,2" | sed '$d' | sed '$d' >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#当前数据库版本信息
echo $database_name" Version Information " >> $local_dest/daily_check.html
psql -d $database_name -p $database_port -T 'width=90% border=1 align=center' -H -c "select version()" | sed '$d' | sed '$d'>> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#数据库表膨胀情况
echo $database_name" Table Needed Vacuum " >> $local_dest/daily_check.html
psql -d $database_name -p $database_port -T 'width=90% border=1 align=center' -H -c "select * from gp_toolkit.gp_bloat_diag order by bdirelpages desc" | sed '$d'| sed '$d' >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#数据量月增长情况
psql -d $database_name -p $database_port -c "create table csh_datasize (ctime timestamp,datname varchar(30),datasize numeric(10,2)) distributed randomly"
psql -d $database_name -p $database_port -c " copy (select now(),datname,round(pg_database_size(datname)*1.0/1024/1024/1024,2) size_gb from pg_database order by 3 desc) to stdout" > $local_dest/datasize.txt
psql -d $database_name -p $database_port -c "copy public.csh_datasize from '$local_dest/datasize.txt'"
#数据库数量
export db_num=`psql -d "$database_name" -p "$database_port" -c "copy (select count(*) from pg_database) to stdout"`
echo $database_name" Last Half Year Data Growth Every Month " >> $local_dest/daily_check.html
psql -d $database_name -p $database_port -T 'width=90% border=1 align=center' -H -c "SELECT to_char(size.ctime, 'YYYY-MM' ::text) AS month,
datname,sum(size.day_ext) AS mon_ext_gb
FROM (SELECT csh_datasize.ctime,
datname,
csh_datasize.datasize -
(lag(csh_datasize.datasize, 1, 0) OVER(partition by datname ORDER BY csh_datasize.ctime)) AS day_ext
FROM csh_datasize) size
GROUP BY to_char(size.ctime, 'YYYY-MM' ::text), datname
ORDER BY to_char(size.ctime, 'YYYY-MM' ::text),datname DESC limit 6*$db_num " | sed '$d'|sed '$d' >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#数据量日增长情况
echo $database_name" Last Week Data Growth Every Day " >> $local_dest/daily_check.html
psql -d $database_name -p $database_port -T 'width=90% border=1 align=center' -H -c "SELECT to_date(csh_datasize.ctime,'yyyy-mm-dd') day_time,
csh_datasize.datasize -
(lag(csh_datasize.datasize,1,0) OVER(ORDER BY csh_datasize.ctime)) AS day_ext_gb
FROM csh_datasize where datname = '$database_name'
ORDER BY csh_datasize.ctime DESC limit 7*$db_num " | sed '$d' | sed '$d' >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#表数据增长量
echo $database_name" Table Data Growth " >> $local_dest/daily_check.html
psql -d $database_name -p $database_port -c "create table public.csh_table_size (ctime timestamp,taboid oid,tabsize bigint,tabidx_size numeric,schemaname name,tablename name) distributed randomly "
psql -d $database_name -p $database_port -c " copy (SELECT now(),sotaid.sotaidoid, sotaid.sotaidtablesize, sotaid.sotaididxsize, fn.fnnspname AS sotaidschemaname, fn.fnrelname AS sotaidtablename FROM ( SELECT now(),sotd.sotdoid AS sotaidoid, sotd.sotdsize + sotd.sotdtoastsize + sotd.sotdadditionalsize AS sotaidtablesize, CASE WHEN soati.soatisize IS NULL THEN 0::numeric ELSE soati.soatisize END AS sotaididxsize FROM gp_toolkit.gp_size_of_table_disk sotd LEFT JOIN gp_toolkit.gp_size_of_all_table_indexes soati ON sotd.sotdoid = soati.soatioid) sotaid JOIN gp_toolkit.__gp_fullname fn ON sotaid.sotaidoid = fn.fnoid) to stdout" > $local_dest/tablesize.txt
#psql -d $database_name -p $database_port -c "delete from public.csh_table_size where ctime <= current_date - interval'30 day'"
psql -d $database_name -p $database_port -c "copy public.csh_table_size from '$local_dest/tablesize.txt'"
psql -d $database_name -p $database_port -T 'width=90% border=1 align=center' -H -c "select a.ctime time,a.schemaname schemaname,a.tablename tablename,abs(a.tabsize-b.tabsize)/1024/1024 table_size_dif_mb,abs(a.tabidx_size-b.tabidx_size) indx_size_def from (select * from public.csh_table_size where (select to_date(ctime,'yyyy-mm-dd') from public.csh_table_size)=current_date) a,(select * from public.csh_table_size where (select to_date(ctime,'yyyy-mm-dd') from public.csh_table_size)=current_date - interval'1 day') b where a.taboid=b.taboid order by table_size_dif_mb desc limit 100 " | sed '$d'| sed '$d' >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#当前数据库大于50GB的表
echo $database_name" Big Table(Size More Than 50G) " >> $local_dest/daily_check.html
psql -d $database_name -p $database_port -T 'width=90% border=1 align=center' -H -c "Select a.schema_name, a.table_name, a.tablesize_GB, a.indexsize_GB
from (select tabs.nspname as schema_name,
COALESCE(parts.tablename, tabs.relname) AS table_name,
ROUND(SUM(sotaidtablesize) / 1024 / 1024 / 1024, 3) AS tablesize_GB,
ROUND(SUM(sotaididxsize) / 1024 / 1024 / 1024, 3) AS indexsize_GB
FROM gp_toolkit.gp_size_of_table_and_indexes_disk sotd,
(select c.oid, c.relname, n.nspname
from pg_class c, pg_namespace n
where n.oid = c.relnamespace) tabs
LEFT JOIN pg_partitions parts
ON tabs.nspname = parts.schemaname
AND tabs.relname = parts.partitiontablename
where sotd.sotaidoid = tabs.oid
GROUP BY tabs.nspname, COALESCE(parts.tablename, tabs.relname)) a
where a.tablesize_GB >= 50
ORDER by 3 desc" | sed '$d' >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#大表数据倾斜情况
echo $database_name" Date Skew Of Big Table " >> $local_dest/daily_check.html
psql -d $database_name -p $database_port -t -c "Select a.schema_name || '.' || a.table_name
from (select tabs.nspname as schema_name,
COALESCE(parts.tablename, tabs.relname) AS table_name,
ROUND(SUM(sotaidtablesize) / 1024 / 1024 / 1024, 3) AS tablesize_GB,
ROUND(SUM(sotaididxsize) / 1024 / 1024 / 1024, 3) AS indexsize_GB
FROM gp_toolkit.gp_size_of_table_and_indexes_disk sotd,
(select c.oid, c.relname, n.nspname
from pg_class c, pg_namespace n
where n.oid = c.relnamespace) tabs
LEFT JOIN pg_partitions parts
ON tabs.nspname = parts.schemaname
AND tabs.relname = parts.partitiontablename
where sotd.sotaidoid = tabs.oid
GROUP BY tabs.nspname, COALESCE(parts.tablename, tabs.relname)) a
where a.tablesize_GB >= 100
ORDER by a.tablesize_GB desc" |sed 's/[[:space:]]//g'|sed '$d' > $local_dest/big_table.txt
while read name
do
echo "$name" >> $local_dest/daily_check.html
psql -d $database_name -p $database_port -T 'width=90% border=1 align=center' -H -c "select gp_segment_id,
count(*),
abs(count(*) - avg(count(*))
over(order by gp_segment_id rows between unbounded
preceding and unbounded following)) / count(*) skew
from $name
group by gp_segment_id
order by 1" | sed '$d' | sed '$d' >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
done < $local_dest/big_table.txt
echo "</br>" >> $local_dest/daily_check.html
#晚间跑数负载情况
echo " System Load(Avg Ten Minutes) " >> $local_dest/daily_check.html
psql -d gpperfmon -T 'width=90% border=1 align=center' -H -c "select t.date||' '||t.hour||':'||t.minute||':00' time,t.cpu_user,t.cpu_sys,t.mem_used,t.iops,t.disk_r_mb,t.disk_w_mb
from (select current_date as date,
to_char(ctime,'hh24') as hour,
substr(to_char(ctime,'mi'),1,1)||'0' as minute,
round(avg(cpu_user) ::numeric, 2) cpu_user,
round(avg(cpu_sys) ::numeric, 2) cpu_sys,
round(avg(mem_actual_used) / 1024 / 1024 / 1024 ::numeric, 2) mem_used,
round(avg(disk_ro_rate) ::numeric, 2) iops,
round(avg(disk_rb_rate) / 1024 / 1024 ::numeric, 2) disk_r_mb,
round(avg(disk_wb_rate) / 1024 / 1024 ::numeric, 2) disk_w_mb
from system_history
where ctime >= current_date
and ctime <= current_timestamp
and hostname <> 'mdw'
and hostname <> 'smdw'
group by current_date,
to_char(ctime,'hh24'),
substr(to_char(ctime,'mi'),1,1)||'0') t
order by 1 " | sed '$d' | sed '$d' >> $local_dest/daily_check.html
echo "</br>" >> $local_dest/daily_check.html
#与前一天负载对比
echo " System Load Different With Yesterday" >> $local_dest/daily_check.html
psql -d gpperfmon -T 'width=90% border=1 align=center' -H -c "select a.time,
abs(a.cpu_user - b.cpu_user) cpu_user_dif,
abs(a.cpu_sys - b.cpu_sys) cpu_sys_dif,
abs(a.mem_used - b.mem_used) mem_used_dif,
abs(a.iops - b.iops) iops_dif,
abs(a.disk_r_mb - b.disk_r_mb) disk_r_mb_dif,
abs(a.disk_w_mb - b.disk_w_mb) disk_w_mb_dif
from (select t.date || ' ' || t.hour || ':' || t.minute || ':00' time,
t.cpu_user,
t.cpu_sys,
t.mem_used,
t.iops,
t.disk_r_mb,
t.disk_w_mb
from (select current_date as date,
to_char(ctime, 'hh24') as hour,
substr(to_char(ctime, 'mi'), 1, 1) || '0' as minute,
round(avg(cpu_user) ::numeric, 2) cpu_user,
round(avg(cpu_sys) ::numeric, 2) cpu_sys,
round(avg(mem_actual_used) / 1024 / 1024 / 1024
::numeric,
2) mem_used,
round(avg(disk_ro_rate) ::numeric, 2) iops,
round(avg(disk_rb_rate) / 1024 / 1024 ::numeric, 2) disk_r_mb,
round(avg(disk_wb_rate) / 1024 / 1024 ::numeric, 2) disk_w_mb
from system_history
where ctime >= current_date
and ctime <= current_timestamp
and hostname <> 'mdw'
and hostname <> 'smdw'
group by current_date,
to_char(ctime, 'hh24'),
substr(to_char(ctime, 'mi'), 1, 1) || '0') t) a,
(select t.date || ' ' || t.hour || ':' || t.minute || ':00' time,
t.cpu_user,
t.cpu_sys,
t.mem_used,
t.iops,
t.disk_r_mb,
t.disk_w_mb
from (select current_date as date,
to_char(ctime, 'hh24') as hour,
substr(to_char(ctime, 'mi'), 1, 1) || '0' as minute,
round(avg(cpu_user) ::numeric, 2) cpu_user,
round(avg(cpu_sys) ::numeric, 2) cpu_sys,
round(avg(mem_actual_used) / 1024 / 1024 / 1024
::numeric,
2) mem_used,
round(avg(disk_ro_rate) ::numeric, 2) iops,
round(avg(disk_rb_rate) / 1024 / 1024 ::numeric, 2) disk_r_mb,
round(avg(disk_wb_rate) / 1024 / 1024 ::numeric, 2) disk_w_mb
from system_history
where ctime >= current_date - interval
'1 day'
and ctime <= current_timestamp - interval
'1 day'
and hostname <> 'mdw'
and hostname <> 'smdw'
group by current_date,
to_char(ctime, 'hh24'),
substr(to_char(ctime, 'mi'), 1, 1) || '0') t) b
where a.time = b.time
order by 1" | sed '$d' | sed '$d' >> $local_dest/daily_check.html
echo "</BODY>" >> $local_dest/daily_check.html
echo "</HTML>" >> $local_dest/daily_check.html
#发送邮件
./sendEmail.pl -f ${mailfrom} -t ${maillist} -u "gp58 $database_name daily check report" -s ${mailsmtp} -xu ${mailxu} -xp ${mailxp} -a daily_check.html -o message-file=$local_dest/daily_check.html