-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.
   
   

   
   

    
     -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).
   
   

   
   

    
     -c Report process creation activity.
   
   
   
   

    
     
   
   
   
   

    
     proc/s
   
   
   
   

    
     Total number of processes created per second.
   
   

   
   

    
     -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.
   
   

   
   

    
     -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).
   
   

   
   

    
     -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.
   
   

   
   

    
     
    
     -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.
   
   

   
   

    
     
    
     -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.
   
   

   
   

    
     -w Report system switching activity.
   
   
   
   

    
     
   
   
   
   

    
     cswch/s
   
   
   
   

    
     Total number of context switches per second.
   
   

   
   

    
     -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.
   
   

   
   

    
    create role sar nosuperuser nocreatedb nocreaterole noinherit login encrypted password @H_411_2301@'DIGOAL';
   
   
   
   

    
    create tablespace tbs_sar owner digoal location @H_411_2301@'/home/sar/tbs_sar';
   
   
   
   

    
    create database sar with owner digoal template template0 encoding @H_411_2301@'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;

   
   

    
    create table server(
   
   
   
   

    
    id int primary key,
   
   
   
   

    
    ip inet not null unique,
   
   
   
   

    
    info text);
   
   

   
   

    
    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(@H_411_2301@'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;
   
   

   
   

    
    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 @H_411_2301@'0.0.0.0/0'::inet;
   
   
   
   

    
    end
   
   
   
   

    
    $BODY$ language plpgsql;
   
   

   
   

    
    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 @H_411_2301@'no info';
   
   
   
   

    
    end
   
   
   
   

    
    $BODY$ language plpgsql;
   
   

   
   

    
    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,
   
   
   
   

    
    pgpgin_p_s numeric,
   
   
   
   

    
    pgpgout_p_s numeric,
   
   
   
   

    
    fault_p_s numeric,
   
   
   
   

    
    majflt_p_s numeric,s_time));
   
   
   
   

    
     
   
   
   
   

    
    create table sar_proc
   
   
   
   

    
    (server_id int not null,
   
   
   
   

    
    proc_p_s numeric,s_time));
   
   
   
   

    
     
   
   
   
   

    
    create table sar_load
   
   
   
   

    
    (server_id int not null,
   
   
   
   

    
    runq_sz numeric,
   
   
   
   

    
    plist_sz numeric,
   
   
   
   

    
    ldavg_1 numeric,
   
   
   
   

    
    ldavg_5 numeric,
   
   
   
   

    
    ldavg_15 numeric,s_time));
   
   
   
   

    
     
   
   
   
   

    
    create table sar_mem_swap
   
   
   
   

    
    (server_id int not null,
   
   
   
   

    
    kbmemfree numeric,
   
   
   
   

    
    kbmemused numeric,
   
   
   
   

    
    percnt_memused numeric,
   
   
   
   

    
    kbbuffers numeric,
   
   
   
   

    
    kbcached numeric,
   
   
   
   

    
    kbswpfree numeric,
   
   
   
   

    
    kbswpused numeric,
   
   
   
   

    
    percnt_swpused numeric,
   
   
   
   

    
    kbswpcad numeric,s_time));
   
   
   
   

    
     
   
   
   
   

    
    create table sar_mem
   
   
   
   

    
    (server_id int not null,
   
   
   
   

    
    frmpg_p_s numeric,
   
   
   
   

    
    bufpg_p_s numeric,
   
   
   
   

    
    campg_p_s numeric,s_time));
   
   
   
   

    
     
   
   
   
   

    
    create table sar_cpu
   
   
   
   

    
    (server_id int not null,
   
   
   
   

    
    percnt_user numeric,
   
   
   
   

    
    percnt_nice numeric,
   
   
   
   

    
    percnt_system numeric,
   
   
   
   

    
    percnt_iowait numeric,
   
   
   
   

    
    percnt_steal numeric,
   
   
   
   

    
    percnt_idle numeric,s_time));
   
   
   
   

    
     
   
   
   
   

    
    create table sar_inode
   
   
   
   

    
    (server_id int 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,s_time));
   
   
   
   

    
     
   
   
   
   

    
    create table sar_context
   
   
   
   

    
    (server_id int not null,
   
   
   
   

    
    cswch_p_s numeric,s_time));
   
   
   
   

    
     
   
   
   
   

    
    create table sar_swap
   
   
   
   

    
    (server_id int not null,
   
   
   
   

    
    pswpin_p_s numeric,
   
   
   
   

    
    pswpout_p_s numeric,s_time));
   
   

10.10.10.1:1931:sar:sar:DIGOAL

chmod 400 ~/.pgpass

   
   

    
    
 
     #!/bin/bash # 环境变量,数据库连接,避免风暴随机等待60秒内 . /home/postgres/.bash_profile DB_URL=@H_411_2301@"-h 10.10.10.1 -p 1931 -U sar -d sar" sleep $(($RANDOM%60)) NET_DEV=@H_411_2301@"`/sbin/route -n|grep UG|awk '{print $8}'|head -n 1`" IP_ADDR=@H_411_2301@"'`/sbin/ip addr show $NET_DEV|grep inet|grep "global $NET_DEV$@H_411_2301@"|awk '{print $2}'`'" SAR_FILE=@H_411_2301@"/var/log/sa/sa`date -d -1day +%d`" SAR_DATE=@H_411_2301@"'`date -d -1day +%Y-%m-%d`'" SERVER_ID=@H_411_2301@"`psql -A -t $DB_URL -c "select * from get_server_id($IP_ADDR)@H_411_2301@"`" # sar -b,sar_io tps rtps wtps bread/s bwrtn/s sql=@H_411_2301@`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 @H_411_2301@"$sql" # sar -B,sar_page pgpgin/s pgpgout/s fault/s majflt/s sql=@H_411_2301@`sar -B -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_page(server_id,pgpgin_p_s,pgpgout_p_s,fault_p_s,majflt_p_s) values('$SERVER_ID',$6");"}'` psql $DB_URL -c @H_411_2301@"$sql" # sar -c,sar_proc proc/s sql=@H_411_2301@`sar -c -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_proc(server_id,proc_p_s) values('$SERVER_ID',$3");"}'` psql $DB_URL -c @H_411_2301@"$sql" # sar -q,sar_load runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15 sql=@H_411_2301@`sar -q -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_load(server_id,runq_sz,plist_sz,ldavg_1,ldavg_5,ldavg_15) values('$SERVER_ID',$7");"}'` psql $DB_URL -c @H_411_2301@"$sql" # sar -r,sar_mem_swap kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree kbswpused %swpused kbswpcad sql=@H_411_2301@`sar -r -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_mem_swap(server_id,kbmemfree,kbmemused,percnt_memused,kbbuffers,kbcached,kbswpfree,kbswpused,percnt_swpused,kbswpcad) values('$SERVER_ID',$7",$8",$9",$10",$11");"}'` psql $DB_URL -c @H_411_2301@"$sql" # sar -R,sar_mem frmpg/s bufpg/s campg/s sql=@H_411_2301@`sar -R -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_mem(server_id,frmpg_p_s,bufpg_p_s,campg_p_s) values('$SERVER_ID',$5");"}'` psql $DB_URL -c @H_411_2301@"$sql" # sar -u,sar_cpu %user %nice %system %iowait %steal %idle sql=@H_411_2301@`sar -u -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_cpu(server_id,percnt_user,percnt_nice,percnt_system,percnt_iowait,percnt_steal,percnt_idle) values('$SERVER_ID',$9");"}'` psql $DB_URL -c @H_411_2301@"$sql" # sar -v,sar_inode dentunusd file-sz inode-sz super-sz %super-sz dquot-sz %dquot-sz rtsig-sz %rtsig-sz sql=@H_411_2301@`sar -v -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_inode(server_id,dentunusd,file_sz,inode_sz,super_sz,percnt_super_sz,dquot_sz,percnt_dquot_sz,rtsig_sz,percnt_rtsig_sz) values('$SERVER_ID',$11");"}'` psql $DB_URL -c @H_411_2301@"$sql" # sar -w,sar_context cswch/s sql=@H_411_2301@`sar -w -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_context(server_id,cswch_p_s) values('$SERVER_ID',$3");"}'` psql $DB_URL -c @H_411_2301@"$sql" # sar -W,sar_swap pswpin/s pswpout/s sql=@H_411_2301@`sar -W -f $SAR_FILE|grep -E 'AM[ ]+([0-9]+|\.+|all|-)|PM[ ]+([0-9]+|\.+|all|-)'|awk '{print "insert into sar_swap(server_id,pswpin_p_s,pswpout_p_s) values('$SERVER_ID',$4");"}'` psql $DB_URL -c @H_411_2301@"$sql" 
    
   
   
   
   

    
     
   
   
   
   

    
    # Author : Digoal.Zhou
   
   
   
   

    
    # THE END
   
   

crontab -e

1 2 * * * @H_411_2301@/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),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),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),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),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),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),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),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),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),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),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),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),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;