digoal
2012-02-14
PostgreSQL , monitor , 批量监控 , sar , os , operation system stats
当你管理的服务器越来越多的时候, 哪个服务器才是你最需要关注的?
通过各个监控维度的排行, 可以知道你到底需要关注或者优先关注哪些服务器.
这个通过nagios,cacti等监控软件当然是可以做到的.
不过不太灵活, 因为服务器的配置各不一样, 关注的维度也不一样, PostgreSQL数据库的递归调用和窗口函数可以很好的运用来输出各种各样的统计数据, 有了数据也就利于展现了。
OS : RHEL5
DB : PostgreSQL 9.1.2
假设我这里有1000台跑了mongoDB, PostgreSQL, Oracle, MySQL等数据库的服务器需要将它们的SAR报告集中到一台PostgreSQL数据库中.
集中之后对报告进行分析, 找出需要关注的服务器来.
我这里举了一个简单的例子, 收集前一天sar的统计报告. 如下 :
1. sar -b
反映系统的每秒读写IO请求等, 详情
-b Report I/O and transfer rate statistics. The following values are displayed:
tps
Total number of transfers per second that were issued to physical devices. A transfer is an I/O
request to a physical device. Multiple logical requests can be combined into a single I/O request
to the device. A transfer is of indeterminate size.
rtps
Total number of read requests per second issued to physical devices.
wtps
Total number of write requests per second issued to physical devices.
bread/s
Total amount of data read from the devices in blocks per second. Blocks are equivalent to sec-
tors with 2.4 kernels and newer and therefore have a size of 512 bytes. With older kernels, a
block is of indeterminate size.
bwrtn/s
Total amount of data written to devices in blocks per second.
2. sar -B
反映每秒系统写入或从磁盘读出的page数, 详情
-B Report paging statistics. The following values are displayed:
pgpgin/s
Total number of kilobytes the system paged in from disk per second. Note: With old kernels
(2.2.x) this value is a number of blocks per second (and not kilobytes).
pgpgout/s
Total number of kilobytes the system paged out to disk per second. Note: With old kernels
(2.2.x) this value is a number of blocks per second (and not kilobytes).
fault/s
Number of page faults (major + minor) made by the system per second (post 2.5 kernels only).
This is not a count of page faults that generate I/O, because some page faults can be resolved
without I/O.
majflt/s
Number of major faults the system has made per second, those which have required loading a memory
page from disk (post 2.5 kernels only).
3. sar -c
反映系统每秒创建的进程数, 如果这个数字很大可能是应用程序连接数据库是短连接, 并且请求数据库频繁, 而PostgreSQL采用的是客户端连接过来fork新进程然后这个新进程与客户端进行交互的模式, 因此这种情况会造成数据库服务器大量的关闭和创建进程, sar -c能反映这种情况. 使用短连接还有一个坏处就是当系统中使用到sequence, 并且这个sequence被大量的这种短连接进程请求, 那么它设置的sequence cache没有效果并且会造成大量的跳号.
详情
-c Report process creation activity.
proc/s
Total number of processes created per second.
4. sar -q
反映系统的负载, 详情
-q Report queue length and load averages. The following values are displayed:
runq-sz
Run queue length (number of processes waiting for run time).
plist-sz
Number of processes and threads in the process list.
ldavg-1
System load average for the last minute.
ldavg-5
System load average for the past 5 minutes.
ldavg-15
System load average for the past 15 minutes.
5. sar -r
反映系统的内存和SWAP的使用情况, 详情
-r Report memory and swap space utilization statistics. The following values are displayed:
kbmemfree
Amount of free memory available in kilobytes.
kbmemused
Amount of used memory in kilobytes. This does not take into account memory used by the kernel
itself.
%memused
Percentage of used memory.
kbbuffers
Amount of memory used as buffers by the kernel in kilobytes.
kbcached
Amount of memory used to cache data by the kernel in kilobytes.
kbswpfree
Amount of free swap space in kilobytes.
kbswpused
Amount of used swap space in kilobytes.
%swpused
Percentage of used swap space.
kbswpcad
Amount of cached swap memory in kilobytes. This is memory that once was swapped out, is swapped
back in but still also is in the swap area (if memory is needed it doesn’t need to be swapped out
again because it is already in the swap area. This saves I/O).
6. sar -R
反映每秒被free的内存, 新增给buffer的内存, 新增给cache的内存. 详情
-R Report memory statistics. The following values are displayed:
frmpg/s
Number of memory pages freed by the system per second. A negative value represents a number of
pages allocated by the system. Note that a page has a size of 4 kB or 8 kB according to the
machine architecture.
bufpg/s
Number of additional memory pages used as buffers by the system per second. A negative value
means fewer pages used as buffers by the system.
campg/s
Number of additional memory pages cached by the system per second. A negative value means fewer
pages in the cache.
7. sar -u
反映系统CPU在user, nice, system, iowait, steal, idle的分配比例. 详情
-u Report CPU utilization. The following values are displayed:
%user
Percentage of CPU utilization that occurred while executing at the user level (application).
%nice
Percentage of CPU utilization that occurred while executing at the user level with nice priority.
%system
Percentage of CPU utilization that occurred while executing at the system level (kernel).
%iowait
Percentage of time that the CPU or CPUs were idle during which the system had an outstanding disk
I/O request.
%steal
Show the percentage of time spent in involuntary wait by the virtual CPU or CPUs while the hyper-
visor was servicing another virtual processor.
%idle
Percentage of time that the CPU or CPUs were idle and the system did not have an outstanding disk
I/O request.
Note: On SMP machines a processor that does not have any activity at all (0.00 for every field) is a
disabled (offline) processor.
8. sar -v
inode, file 或其他内核表的报告, 详情
-v Report status of inode, file and other kernel tables. The following values are displayed:
dentunusd
Number of unused cache entries in the directory cache.
file-sz
Number of used file handles.
inode-sz
Number of used inode handlers.
super-sz
Number of super block handlers allocated by the kernel.
%super-sz
Percentage of allocated super block handlers with regard to the maximum number of super block
handlers that Linux can allocate.
dquot-sz
Number of allocated disk quota entries.
%dquot-sz
Percentage of allocated disk quota entries with regard to the maximum number of cached disk quota
entries that can be allocated.
rtsig-sz
Number of queued RT signals.
%rtsig-sz
Percentage of queued RT signals with regard to the maximum number of RT signals that can be
queued.
9. sar -w
反映每秒上下文的切换数量, 详情
-w Report system switching activity.
cswch/s
Total number of context switches per second.
10. sar -W
反映SWAP每秒被换进或换出的数量, 详情
-W Report swapping statistics. The following values are displayed:
pswpin/s
Total number of swap pages the system brought in per second.
pswpout/s
Total number of swap pages the system brought out per second.
我这里使用的是sar用户和sar库以及tbs_sar表空间, 首先初始化数据库 :
create role sar nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'DIGOAL';
create tablespace tbs_sar owner digoal location '/home/sar/tbs_sar';
create database sar with owner digoal template template0 encoding 'UTF8' tablespace tbs_sar;
grant all on database sar to sar;
grant all on tablespace tbs_sar to sar;
\c sar sar
create schema sar authorization sar;
创建序列, 函数 和表 :
create sequence seq_server_id start with 1 increment by 1;
存放Server信息, 本例只为说明方法, 所以这个表设计得比较简单, 实际使用当中可以加入其他字段, 如IDC, 维护人, 项目名称等。
create table server(
id int primary key,
ip inet not null unique,
info text);
根据IP地址获取ServerID的函数, 没有则新分配
create or replace function get_server_id (i_ip inet) returns int as $BODY$
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
$BODY$ language plpgsql;
根据ServerID获取IP的函数
create or replace function get_ip (i_id int) returns inet as $BODY$
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
$BODY$ language plpgsql;
根据ServerID获取服务器info的函数
create or replace function get_info (i_id int) returns text as $BODY$
declare
v_info text;
begin
select info into v_info from server where id=i_id;
return v_info;
exception
when others then
return 'no info';
end
$BODY$ language plpgsql;
统计昨天未收集到SAR日志的函数
create or replace function get_server_nodata_yesterday() returns setof text as $BODY$
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||', '||s1.info 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||', '||s1.info 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||', '||s1.info 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||', '||s1.info 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||', '||s1.info 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||', '||s1.info 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||', '||s1.info 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||', '||s1.info 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||', '||s1.info 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||', '||s1.info 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
$BODY$ language plpgsql;
sar信息存放表 :
create table sar_io
(server_id int not null,
s_date date not null,
s_time time not null,
tps numeric,
rtps numeric,
wtps numeric,
bread_p_s numeric,
bwrtn_p_s numeric,
unique(server_id,s_date,s_time));
create table sar_page
(server_id int not null,
s_date date not null,
s_time time not null,
pgpgin_p_s numeric,
pgpgout_p_s numeric,
fault_p_s numeric,
majflt_p_s numeric,
unique(server_id,s_date,s_time));
create table sar_proc
(server_id int not null,
s_date date not null,
s_time time not null,
proc_p_s numeric,
unique(server_id,s_date,s_time));
create table sar_load
(server_id int not null,
s_date date not null,
s_time time not null,
runq_sz numeric,
plist_sz numeric,
ldavg_1 numeric,
ldavg_5 numeric,
ldavg_15 numeric,
unique(server_id,s_date,s_time));
create table sar_mem_swap
(server_id int not null,
s_date date not null,
s_time time not null,
kbmemfree numeric,
kbmemused numeric,
percnt_memused numeric,
kbbuffers numeric,
kbcached numeric,
kbswpfree numeric,
kbswpused numeric,
percnt_swpused numeric,
kbswpcad numeric,
unique(server_id,s_date,s_time));
create table sar_mem
(server_id int not null,
s_date date not null,
s_time time not null,
frmpg_p_s numeric,
bufpg_p_s numeric,
campg_p_s numeric,
unique(server_id,s_date,s_time));
create table sar_cpu
(server_id int not null,
s_date date not null,
s_time time not null,
percnt_user numeric,
percnt_nice numeric,
percnt_system numeric,
percnt_iowait numeric,
percnt_steal numeric,
percnt_idle numeric,
unique(server_id,s_date,s_time));
create table sar_inode
(server_id int not null,
s_date date not null,
s_time time not null,
dentunusd numeric,
file_sz numeric,
inode_sz numeric,
super_sz numeric,
percnt_super_sz numeric,
dquot_sz numeric,
percnt_dquot_sz numeric,
rtsig_sz numeric,
percnt_rtsig_sz numeric,
unique(server_id,s_date,s_time));
create table sar_context
(server_id int not null,
s_date date not null,
s_time time not null,
cswch_p_s numeric,
unique(server_id,s_date,s_time));
create table sar_swap
(server_id int not null,
s_date date not null,
s_time time not null,
pswpin_p_s numeric,
pswpout_p_s numeric,
unique(server_id,s_date,s_time));
收集用到了PostgreSQL的psql程序, 所以需要在系统中安装PostgreSQL客户端. 安装过程略.
假设PostgreSQL数据库的连接信息如下, IP 10.10.10.1 , Port 1931 , DBNAME sar , USER sar , PASSWORD DIGOAL
配置 ~/.pgpass文件
10.10.10.1:1931:sar:sar:DIGOAL
chmod 400 ~/.pgpass
编写sar_collect.sh脚本, 用于收集昨天的SAR报告
vi /home/postgres/sar_collect.sh
#!/bin/bash
# 环境变量, 数据库连接, 避免风暴随机等待60秒内
. /home/postgres/.bash_profile
DB_URL="-h 10.10.10.1 -p 1931 -U sar -d sar"
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"
# sar -c, sar_proc proc/s
SQL=`sar -c -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"
# 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, kbswpfree, kbswpused, percnt_swpused, kbswpcad) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$3",",$4",",$5",",$6",",$7",",$8",",$9",",$10",",$11");"}'`
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"
# 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, super_sz, percnt_super_sz, dquot_sz, percnt_dquot_sz, rtsig_sz, percnt_rtsig_sz) values('$SERVER_ID', '$SAR_DATE',","\47"$1$2"\47,",$3",",$4",",$5",",$6",",$7",",$8",",$9",",$10",",$11");"}'`
psql $DB_URL -c "$SQL"
# 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,",$3");"}'`
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"
# Author : Digoal.Zhou
# THE END
修改权限 :
chmod 500 sar_collect.sh
新建执行计划,
crontab -e
1 2 * * * /home/postgres/sar_collect.sh
# 昨天15分钟最大负载排名前10,后一条为平均值排行
# 负载过高需要关注这些服务器上运行的数据库和业务是否健康, 例如是否需要建索引, 是否需要使用绑定变量等.
select get_ip(server_id),* 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;
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,s_date order by ldavg_15 desc limit 10;
# 昨天最大读请求数排名前10,后一条为平均值排行
# 读请求过高需要关注这些服务器上运行的数据库和业务是否健康, 例如是否需要建索引, 是否需要加内存, 是否需要对存储性能扩容等.
select get_ip(server_id),* 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;
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,s_date order by rtps desc limit 10;
# 昨天最大写请求数排名前10,后一条为平均值排行
# 写请求过高需要关注这些服务器上运行的数据库和业务是否健康, 例如是否需要减少索引, 是否需要使用异步IO, 是否需要对存储性能进行扩容等.
select get_ip(server_id),* 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;
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,s_date order by wtps desc limit 10;
# 昨天最大iowait排名前10,后一条为平均值排行
# iowait过高需要关注这些服务器上运行的数据库和业务是否健康, 例如是否需要加内存, 是否需要将常用数据放入内存, 是否需要对存储性能进行扩容等.
select get_ip(server_id),* 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;
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,s_date order by percnt_iowait desc limit 10;
# 昨天最大swap页进出排名前10,后一条为平均值排行
# swap也进出过高需要关注这些服务器上运行的数据库和业务是否健康, 例如是否需要加内存, 是否需要将常用数据放入内存等.
select get_ip(server_id),* 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;
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,s_date order by pswpin_out_p_s desc limit 10;
# 昨天最大SWAP使用比例前10,后一条为平均值排行
# SWAP使用率过高需要关注这些服务器上运行的数据库和业务是否健康, 例如是否需要加内存, 是否需要调整数据库参数, 是否需要使用大页等.
select get_ip(server_id),* 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;
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,s_date order by percnt_swpused desc limit 10;
# 昨天每秒新建进程排名前10,后一条为平均值排行
# 每秒新建进程数过高需要关注这些服务器上运行的数据库和业务是否健康, 例如是否需要加个数据库连接池使用长连接, Oracle是否需要使用共享连接, 应用程序是否可以将短连接改成长连接的模式等.
select get_ip(server_id),* 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;
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,s_date order by proc_p_s desc limit 10;
报告如图 :
发送邮件脚本 :
#!/bin/bash
. /home/postgres/.bash_profile
EMAIL="digoal@126.com"
echo -e `date +%F\ %T` >/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---- 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---- 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
# Author : Digoal.Zhou
# THE END
配置mutt环境, 假设数据库编码为UTF-8, 否则中文可能出错.
vi ~/.muttrc
set envelope_from=yes
set from=digoal@126.com
set realname="德哥"
set use_from=yes
set charset="UTF-8"
1. 展现可以通过WEB形式来做, 这里只是举了个简单的收集和统计的例子, 未设计WEB开发.
2. 1000台服务器一天收集的这些sar日志数据量约200MB. 按照现在的硬盘容量, 放几年没问题.
man sar
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
