这篇文章介绍的是取前一天的sar的数据，然后通过邮件的形式发送至个人邮箱，查看相关数据观察数据库服务器的最近一段时间内的性能压力如何，注意这里取的数据不是实时数据，不是实时监控。注意因为要使用psql命令把数据写入到数据库，所以客户端都需要安装postgresql。
1. 数据库部署，这里部署的数据库和表是用来存放sar取的数据，结构如下
创建数据库，创建相关表
sar=> \d+ sar_context
Table "sar.sar_context"
Column | Type | Modifiers | Storage | Stats target | Description
-----------+------------------------+-----------+---------+--------------+-------------
server_id | integer | not null | plain | |
s_date | date | not null | plain | |
s_time | time without time zone | not null | plain | |
cswch_p_s | numeric | | main | |
Indexes:
"sar_context_server_id_s_date_s_time_key" UNIQUE CONSTRAINT, btree (server_id, s_date, s_time)
sar=> \d+ sar_cpu
Table "sar.sar_cpu"
Column | Type | Modifiers | Storage | Stats target | Description
---------------+------------------------+-----------+---------+--------------+-------------
server_id | integer | not null | plain | |
s_date | date | not null | plain | |
s_time | time without time zone | not null | plain | |
percnt_user | numeric | | main | |
percnt_nice | numeric | | main | |
percnt_system | numeric | | main | |
percnt_iowait | numeric | | main | |
percnt_steal | numeric | | main | |
percnt_idle | numeric | | main | |
Indexes:
"sar_cpu_server_id_s_date_s_time_key" UNIQUE CONSTRAINT, btree (server_id, s_date, s_time)
sar=> \d+ sar_inode
Table "sar.sar_inode"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------+------------------------+-----------+---------+--------------+-------------
server_id | integer | not null | plain | |
s_date | date | not null | plain | |
s_time | time without time zone | not null | plain | |
dentunusd | numeric | | main | |
file_sz | numeric | | main | |
inode_sz | numeric | | main | |
super_sz | numeric | | main | |
percnt_super_sz | numeric | | main | |
dquot_sz | numeric | | main | |
percnt_dquot_sz | numeric | | main | |
rtsig_sz | numeric | | main | |
percnt_rtsig_sz | numeric | | main | |
Indexes:
"sar_inode_server_id_s_date_s_time_key" UNIQUE CONSTRAINT, btree (server_id, s_date, s_time)
sar=> \d+ sar_io
Table "sar.sar_io"
Column | Type | Modifiers | Storage | Stats target | Description
-----------+------------------------+-----------+---------+--------------+-------------
server_id | integer | not null | plain | |
s_date | date | not null | plain | |
s_time | time without time zone | not null | plain | |
tps | numeric | | main | |
rtps | numeric | | main | |
wtps | numeric | | main | |
bread_p_s | numeric | | main | |
bwrtn_p_s | numeric | | main | |
Indexes:
"sar_io_server_id_s_date_s_time_key" UNIQUE CONSTRAINT, btree (server_id, s_date, s_time)
sar=> \d+ sar_load
Table "sar.sar_load"
Column | Type | Modifiers | Storage | Stats target | Description
-----------+------------------------+-----------+---------+--------------+-------------
server_id | integer | not null | plain | |
s_date | date | not null | plain | |
s_time | time without time zone | not null | plain | |
runq_sz | numeric | | main | |
plist_sz | numeric | | main | |
ldavg_1 | numeric | | main | |
ldavg_5 | numeric | | main | |
ldavg_15 | numeric | | main | |
Indexes:
"sar_load_server_id_s_date_s_time_key" UNIQUE CONSTRAINT, btree (server_id, s_date, s_time)
sar=> \d+ sar_mem
Table "sar.sar_mem"
Column | Type | Modifiers | Storage | Stats target | Description
-----------+------------------------+-----------+---------+--------------+-------------
server_id | integer | not null | plain | |
s_date | date | not null | plain | |
s_time | time without time zone | not null | plain | |
frmpg_p_s | numeric | | main | |
bufpg_p_s | numeric | | main | |
campg_p_s | numeric | | main | |
Indexes:
"sar_mem_server_id_s_date_s_time_key" UNIQUE CONSTRAINT, btree (server_id, s_date, s_time)
sar=> \d+ sar_mem_swap
Table "sar.sar_mem_swap"
Column | Type | Modifiers | Storage | Stats target | Description
----------------+------------------------+-----------+---------+--------------+-------------
server_id | integer | not null | plain | |
s_date | date | not null | plain | |
s_time | time without time zone | not null | plain | |
kbmemfree | numeric | | main | |
kbmemused | numeric | | main | |
percnt_memused | numeric | | main | |
kbbuffers | numeric | | main | |
kbcached | numeric | | main | |
kbswpfree | numeric | | main | |
kbswpused | numeric | | main | |
percnt_swpused | numeric | | main | |
kbswpcad | numeric | | main | |
Indexes:
"sar_mem_swap_server_id_s_date_s_time_key" UNIQUE CONSTRAINT, btree (server_id, s_date, s_time)
sar=> \d+ sar_page
Table "sar.sar_page"
Column | Type | Modifiers | Storage | Stats target | Description
-------------+------------------------+-----------+---------+--------------+-------------
server_id | integer | not null | plain | |
s_date | date | not null | plain | |
s_time | time without time zone | not null | plain | |
pgpgin_p_s | numeric | | main | |
pgpgout_p_s | numeric | | main | |
fault_p_s | numeric | | main | |
majflt_p_s | numeric | | main | |
Indexes:
"sar_page_server_id_s_date_s_time_key" UNIQUE CONSTRAINT, btree (server_id, s_date, s_time)
sar=> \d+ sar_proc
Table "sar.sar_proc"
Column | Type | Modifiers | Storage | Stats target | Description
-----------+------------------------+-----------+---------+--------------+-------------
server_id | integer | not null | plain | |
s_date | date | not null | plain | |
s_time | time without time zone | not null | plain | |
proc_p_s | numeric | | main | |
Indexes:
"sar_proc_server_id_s_date_s_time_key" UNIQUE CONSTRAINT, btree (server_id, s_date, s_time)
sar=> \d+ sar_swap
Table "sar.sar_swap"
Column | Type | Modifiers | Storage | Stats target | Description
-------------+------------------------+-----------+---------+--------------+-------------
server_id | integer | not null | plain | |
s_date | date | not null | plain | |
s_time | time without time zone | not null | plain | |
pswpin_p_s | numeric | | main | |
pswpout_p_s | numeric | | main | |
Indexes:
"sar_swap_server_id_s_date_s_time_key" UNIQUE CONSTRAINT, btree (server_id, s_date, s_time)
sar=> \d+ server
Table "sar.server"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
ip | inet | not null | main | |
info | text | | extended | |
Indexes:
"server_pkey" PRIMARY KEY, btree (id)
"server_ip_key" UNIQUE CONSTRAINT, btree (ip)
"uk_server_id_ip" UNIQUE CONSTRAINT, btree (id, ip)
sar=> \dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+-------
sar | sar_context | table | sar
sar | sar_cpu | table | sar
sar | sar_inode | table | sar
sar | sar_io | table | sar
sar | sar_load | table | sar
sar | sar_mem | table | sar
sar | sar_mem_swap | table | sar
sar | sar_page | table | sar
sar | sar_proc | table | sar
sar | sar_swap | table | sar
sar | server | table | sar
创建要使用的函数
postgres=# \c sar sar
psql (9.6.1, server 9.3.5)
You are now connected to database "sar" as user "sar".
sar=> \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-----------------------------+------------------+---------------------+--------
sar | get_info | text | i_id integer | normal
sar | get_ip | inet | i_id integer | normal
sar | get_server_id | integer | i_ip inet | normal
sar | get_server_nodata_yesterday | SETOF text | | normal
(4 rows)
sar=> \sf get_info
CREATE OR REPLACE FUNCTION sar.get_info(i_id integer)
RETURNS text
LANGUAGE plpgsql
AS $function$
declare
v_info text;
begin
select coalesce(info,'noinfo') into v_info from server where id=i_id;
return v_info;
exception
when others then
return 'noinfo';
end
$function$
sar=> \sf get_ip
CREATE OR REPLACE FUNCTION sar.get_ip(i_id integer)
RETURNS inet
LANGUAGE plpgsql
AS $function$
declare
v_ip inet;
begin
select ip into v_ip from server where id=i_id;
return v_ip;
exception
when others then
return '0.0.0.0/0'::inet;
end
$function$
sar=> \sf get_server_id
CREATE OR REPLACE FUNCTION sar.get_server_id(i_ip inet)
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare
v_id int;
begin
select id into v_id from server where ip=i_ip;
if not found then
insert into server(id, ip) values(nextval('seq_server_id'::regclass), i_ip);
select id into v_id from server where ip=i_ip;
end if;
return v_id;
exception
when others then
return -1;
end
$function$
sar=> \sf get_server_nodata_yesterday
CREATE OR REPLACE FUNCTION sar.get_server_nodata_yesterday()
RETURNS SETOF text
LANGUAGE plpgsql
AS $function$
declare
v_result text;
begin
perform 1 from (select s1.* from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_context where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null) t;
if found then
return next 'sar_context: ';
return query select s1.ip||', '|| from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_context where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null;
end if;
perform 1 from (select s1.* from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_cpu where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null) t;
if found then
return next 'sar_cpu: ';
return query select s1.ip||', '|| from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_cpu where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null;
end if;
perform 1 from (select s1.* from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_inode where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null) t;
if found then
return next 'sar_inode: ';
return query select s1.ip||', '|| from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_inode where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null;
end if;
perform 1 from (select s1.* from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_io where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null) t;
if found then
return next 'sar_io: ';
return query select s1.ip||', '|| from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_io where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null;
end if;
perform 1 from (select s1.* from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_load where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null) t;
if found then
return next 'sar_load: ';
return query select s1.ip||', '|| from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_load where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null;
end if;
perform 1 from (select s1.* from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_mem where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null) t;
if found then
return next 'sar_mem: ';
return query select s1.ip||', '|| from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_mem where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null;
end if;
perform 1 from (select s1.* from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_mem_swap where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null) t;
if found then
return next 'sar_mem_swap: ';
return query select s1.ip||', '|| from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_mem_swap where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null;
end if;
perform 1 from (select s1.* from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_page where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null) t;
if found then
return next 'sar_page: ';
return query select s1.ip||', '|| from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_page where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null;
end if;
perform 1 from (select s1.* from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_proc where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null) t;
if found then
return next 'sar_proc: ';
return query select s1.ip||', '|| from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_proc where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null;
end if;
perform 1 from (select s1.* from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_swap where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null) t;
if found then
return next 'sar_swap: ';
return query select s1.ip||', '|| from server s1 left outer join
(select * from (select server_id,row_number() over (partition by server_id order by s_date desc) from sar_swap where s_date=current_date-1) t1
where row_number=1) t2 on (s1.id=t2.server_id) where t2.server_id is null;
end if;
return;
end
$function$
2. 客户端部署脚本
每天3点1分收集数据写入到数据库，注意这里收集的是前一天的数据
crontab -l
1 3 * * * /home/postgres/sar_collect.sh
#!/bin/bash
# 环境变量, 数据库连接, 避免风暴随机等待60秒内
. /home/postgres/.bash_profile
DB_URL="-h xxxxxx -p xxx -U xxx -d xxx"
sleep $(($RANDOM%60))
NET_DEV="`/sbin/route -n|grep UG|awk '{print $8}'|head -n 1`"
IP_ADDR="'`/sbin/ip addr show $NET_DEV|grep inet|grep "global $NET_DEV$"|awk '{print $2}'`'"
SAR_FILE="/var/log/sa/sa`date -d -1day +%d`"
SAR_DATE="'`date -d -1day +%Y-%m-%d`'"
SERVER_ID="`psql -A -t $DB_URL -c "select * from get_server_id($IP_ADDR)"`"
# sar -b, sar_io tps rtps wtps bread/s bwrtn/s
SQL=`sar -b -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_io(server_id, s_date, s_time, tps, rtps, wtps, bread_p_s, bwrtn_p_s) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$3",",$4",",$5",",$6",",$7");"}'`
psql $DB_URL -c "$SQL"
# sar -B, sar_page pgpgin/s pgpgout/s fault/s majflt/s
SQL=`sar -B -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_page(server_id, s_date, s_time, pgpgin_p_s, pgpgout_p_s, fault_p_s, majflt_p_s) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$3",",$4",",$5",",$6");"}'`
psql $DB_URL -c "$SQL"
# CentOS 6,x sar -w, sar_proc proc/s
SQL=`sar -w -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_proc(server_id, s_date, s_time, proc_p_s) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$3");"}'`
psql $DB_URL -c "$SQL"
# sar -q, sar_load runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15
SQL=`sar -q -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_load(server_id, s_date, s_time, runq_sz, plist_sz, ldavg_1, ldavg_5, ldavg_15) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$3",",$4",",$5",",$6",",$7");"}'`
psql $DB_URL -c "$SQL"
# CentOS 6.x sar -r, sar_mem_swap kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree kbswpused %swpused kbswpcad
SQL=`sar -r -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_mem_swap(server_id, s_date, s_time, kbmemfree, kbmemused, percnt_memused, kbbuffers, kbcached) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$3",",$4",",$5",",$6",",$7");"}'`
psql $DB_URL -c "$SQL"
# sar -R, sar_mem frmpg/s bufpg/s campg/s
SQL=`sar -R -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_mem(server_id, s_date, s_time, frmpg_p_s, bufpg_p_s, campg_p_s) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$3",",$4",",$5");"}'`
psql $DB_URL -c "$SQL"
# sar -u, sar_cpu %user %nice %system %iowait %steal %idle
SQL=`sar -u -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_cpu(server_id, s_date, s_time, percnt_user, percnt_nice, percnt_system, percnt_iowait, percnt_steal, percnt_idle) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$4",",$5",",$6",",$7",",$8",",$9");"}'`
psql $DB_URL -c "$SQL"
# CentOS 6.x sar -v, sar_inode dentunusd file-sz inode-sz super-sz %super-sz dquot-sz %dquot-sz rtsig-sz %rtsig-sz
SQL=`sar -v -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_inode(server_id, s_date, s_time, dentunusd, file_sz, inode_sz) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$3",",$4",",$5");"}'`
psql $DB_URL -c "$SQL"
# CentOS 6.x sar -w, sar_context cswch/s
SQL=`sar -w -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_context(server_id, s_date, s_time, cswch_p_s) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$4");"}'`
psql $DB_URL -c "$SQL"
# sar -W, sar_swap pswpin/s pswpout/s
SQL=`sar -W -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_swap(server_id, s_date, s_time, pswpin_p_s, pswpout_p_s) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$3",",$4");"}'`
psql $DB_URL -c "$SQL"
3. 此脚本部署在数据库服务器端，取相关的数据写入到sar_report.log文件，每天以邮件方式发送此文件
crontab -l
5 9 * * * /home/postgres/script/sar_report.sh
cat /home/postgres/script/sar_report.sh
#!/bin/bash
. /home/postgres/.bash_profile
EMAIL="xxx@"
echo -e `date +%F\ %T` >/tmp/sar_report.log
echo -e "\n---- DailyAvgValue TOP10: ----\n" >>/tmp/sar_report.log
echo -e "\n1. ldavg_15 TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(ldavg_15),2) ldavg_15 from sar_load where s_date=current_date-1 group by server_id order by ldavg_15 desc limit 10;" >>/tmp/sar_report.log
echo -e "\n2. rtps TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(rtps),2) rtps from sar_io where s_date=current_date-1 group by server_id order by rtps desc limit 10;" >>/tmp/sar_report.log
echo -e "\n3. wtps TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(wtps),2) wtps from sar_io where s_date=current_date-1 group by server_id order by wtps desc limit 10;" >>/tmp/sar_report.log
echo -e "\n4. iowait TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(percnt_iowait),2) percnt_iowait from sar_cpu where s_date=current_date-1 group by server_id order by percnt_iowait desc limit 10;" >>/tmp/sar_report.log
echo -e "\n5. swap_page_in_out TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(pswpin_p_s+pswpout_p_s),2) pswpin_out_p_s from sar_swap where s_date=current_date-1 group by server_id order by pswpin_out_p_s desc limit 10;" >>/tmp/sar_report.log
echo -e "\n6. swap_usage TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(percnt_swpused),2) percnt_swpused from sar_mem_swap where s_date=current_date-1 group by server_id order by percnt_swpused desc limit 10;" >>/tmp/sar_report.log
echo -e "\n7. newproc_p_s TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(proc_p_s),2) proc_p_s from sar_proc where s_date=current_date-1 group by server_id order by proc_p_s desc limit 10;" >>/tmp/sar_report.log
echo -e "\n---- WeeklyAvgValue TOP10: ----\n" >>/tmp/sar_report.log
echo -e "\n1. ldavg_15 TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(ldavg_15),2) ldavg_15 from sar_load where s_date<=current_date-1 and s_date>=current_date-7 group by server_id order by ldavg_15 desc limit 10;" >>/tmp/sar_report.log
echo -e "\n2. rtps TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(rtps),2) rtps from sar_io where s_date<=current_date-1 and s_date>=current_date-7 group by server_id order by rtps desc limit 10;" >>/tmp/sar_report.log
echo -e "\n3. wtps TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(wtps),2) wtps from sar_io where s_date<=current_date-1 and s_date>=current_date-7 group by server_id order by wtps desc limit 10;" >>/tmp/sar_report.log
echo -e "\n4. iowait TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(percnt_iowait),2) percnt_iowait from sar_cpu where s_date<=current_date-1 and s_date>=current_date-7 group by server_id order by percnt_iowait desc limit 10;" >>/tmp/sar_report.log
echo -e "\n5. swap_page_in_out TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(pswpin_p_s+pswpout_p_s),2) pswpin_out_p_s from sar_swap where s_date<=current_date-1 and s_date>=current_date-7 group by server_id order by pswpin_out_p_s desc limit 10;" >>/tmp/sar_report.log
echo -e "\n6. swap_usage TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(percnt_swpused),2) percnt_swpused from sar_mem_swap where s_date<=current_date-1 and s_date>=current_date-7 group by server_id order by percnt_swpused desc limit 10;" >>/tmp/sar_report.log
echo -e "\n7. newproc_p_s TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),round(avg(proc_p_s),2) proc_p_s from sar_proc where s_date<=current_date-1 and s_date>=current_date-7 group by server_id order by proc_p_s desc limit 10;" >>/tmp/sar_report.log
echo -e "\n---- DailyMaxValue TOP10: ----\n" >>/tmp/sar_report.log
echo -e "\n1. ldavg_15 TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),s_date,s_time,runq_sz,plist_sz,ldavg_1,ldavg_5,ldavg_15 from (select *,row_number() over (partition by server_id order by ldavg_15 desc) from sar_load where s_date=current_date-1) t where row_number=1 order by ldavg_15 desc limit 10;" >>/tmp/sar_report.log
echo -e "\n2. rtps TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),s_date,s_time,tps,rtps,wtps,bread_p_s,bwrtn_p_s from (select *,row_number() over (partition by server_id order by rtps desc) from sar_io where s_date=current_date-1) t where row_number=1 order by rtps desc limit 10;" >>/tmp/sar_report.log
echo -e "\n3. wtps TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),s_date,s_time,tps,rtps,wtps,bread_p_s,bwrtn_p_s from (select *,row_number() over (partition by server_id order by wtps desc) from sar_io where s_date=current_date-1) t where row_number=1 order by wtps desc limit 10;" >>/tmp/sar_report.log
echo -e "\n4. iowait TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),s_date,s_time,percnt_user,percnt_nice,percnt_system,percnt_iowait,percnt_steal,percnt_idle from (select *,row_number() over (partition by server_id order by percnt_iowait desc) from sar_cpu where s_date=current_date-1) t where row_number=1 order by percnt_iowait desc limit 10;" >>/tmp/sar_report.log
echo -e "\n5. swap_page_in_out TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),s_date,s_time,pswpin_p_s,pswpout_p_s from (select *,row_number() over (partition by server_id order by pswpin_p_s+pswpout_p_s desc) from sar_swap where s_date=current_date-1) t where row_number=1 order by pswpin_p_s+pswpout_p_s desc limit 10;" >>/tmp/sar_report.log
echo -e "\n6. swap_usage TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),s_date,s_time,kbmemfree,kbmemused,percnt_memused,kbbuffers,kbcached,kbswpfree,kbswpused,percnt_swpused,kbswpcad from (select *,row_number() over (partition by server_id order by percnt_swpused desc) from sar_mem_swap where s_date=current_date-1) t where row_number=1 order by percnt_swpused desc limit 10;" >>/tmp/sar_report.log
echo -e "\n7. newproc_p_s TOP10 :\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select get_info(server_id),get_ip(server_id),s_date,s_time,proc_p_s from (select *,row_number() over (partition by server_id order by proc_p_s desc) from sar_proc where s_date=current_date-1) t where row_number=1 order by proc_p_s desc limit 10;" >>/tmp/sar_report.log
echo -e "\n---- get_server_nodata_yesterday: ----\n" >>/tmp/sar_report.log
psql -h 127.0.0.1 sar sar -c "select * from get_server_nodata_yesterday();" >>/tmp/sar_report.log
cat /tmp/sar_report.log|mutt -s "`date +$F` DB Servers RS Consume Top10" $EMAIL
