Use PostgreSQL collect and analyze Operation System statistics

最新推荐文章于 2024-09-20 14:04:23 发布
最新推荐文章于 2024-09-20 14:04:23 发布
阅读量919 收藏
点赞数
分类专栏： 数据库 文章标签： postgresql system statistics server date null
当你管理的服务器越来越多的时候, 哪个服务器才是你最需要关注的?
通过各个监控维度的排行, 可以知道你到底需要关注或者优先关注哪些服务器.
这个通过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));

三、在需要收集sar报告的操作系统中配置如下程序用来收集sar信息 :
收集用到了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

四、然后看看几条简单的报告SQL, 使用row_number窗口函数  : 

   
   

    
    

     
     # 昨天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;
报告如图 :

Use PostgreSQL collect and analyze Operation System statistics - 德哥@Digoal - The Heart,The World.
Use PostgreSQL collect and analyze Operation System statistics - 德哥@Digoal - The Heart,The World.
Use PostgreSQL collect and analyze Operation System statistics - 德哥@Digoal - The Heart,The World.

Use PostgreSQL collect and analyze Operation System statistics - 德哥@Digoal - The Heart,The World.
五、最后通过邮件将报告发送给自己 :
发送邮件脚本 : 

  
  

   
   

    
    #!/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
postgresql命令行
javadatong的博客
05-30 831
连接数据库, 默认的用户和数据库是postgres psql -U user -d dbname \c dbname 切换数据库,相当于mysql的use dbname \l 列举数据库，相当于mysql的show databases \dt 列举表，相当于show tables \d tblname 查看表结构，相当于desc tblname,show columns ...
PostgreSQL 源码解读（12）- 插入数据#11（exec_simple_query）
08-09 1832
本文简单介绍了PG插入数据部分的源码，主要内容包括exec_simple_query函数的实现逻辑，该函数位于src/backend/tcop/postgres.c文件中。 一、源码解读 exec_si...
linux下使用sar收集性能数据
渴死的火烈鸟
08-21 275
使用一般用户user执行，利用crontab每天0点1分调用脚本执行性能数据输出到指定文件。9CsCkfkh0 CNOUG博客首页v;q4uQ ~P S2E 设定收集数据的文件存放目录为/sarlog，编写脚本文件sarlog.sh的内容为：CNOUG博客首页$un2N3ol G{v CNOUG博客首页6E9\!g6ezDB'N5V . /home/user/.bash_pro...
PostgreSQL Oracle 兼容性之 - PL/SQL FORALL, BULK COLLECT
weixin_34032792的博客
06-15 1211
Oracle PL/SQL 开发的童鞋，一定对O家的bulk批量处理的性能很是赞赏吧。 但是PostgreSQL用户请不要垂涎，作为学院派和工业界的一颗璀璨明珠。 开源数据库PostgreSQL，也有对应的批量处理策略哦，而且看起来性能完全不输Oracle。 下面是一组LOOP和BULK的性能测试数据 一起来耍耍吧，先看看Oracle怎么耍的...
【PostgreSQL】PostgreSQL SQL语句整理：掌握核心技能
最新发布
h1773655323的博客
09-20 474
在数据库管理的世界里，PostgreSQL以其强大的功能和灵活性而闻名。作为一名数据库开发者或者数据分析师，熟练掌握SQL语句是必不可少的。在本文中，我们将梳理一系列常用的PostgreSQL SQL语句，帮助你更高效地管理你的数据库。
mysql/hive/postgresql/impala用sql进行分组中字符串聚合拼接的写法
建模分析师胡选来
11-07 1751
mysql: group_concat(distinct switch order by seq_no separator '->'))，支持分组内order by hive: concat_ws('->', collect_list(switch)) postgresql: string_agg(distinct switch, '->'order by seq_no) imp...
在Oracle中分析AWR报告的步骤 Steps to Analyze AWR Report in Oracle
点滴的幸福
01-28 745
April 06, 2015 Steps to Analyze AWR Report in Oracle   AWR - Automatic workload repository is a collection of persistent system performance statistics owned by SYS. It resides in SYSAUX tablespace....
Python for Reading and Writing Large Datasets: Best Practices for MySQL Performance Optimization
# Python Read and Write Large Datasets: Best Practices for MySQL Performance Optimization ![Python Read and Write Large Datasets: Best Practices for MySQL Performance Optimization](*** *** *** `...
如何在Ubuntu 20.04上安装和使用TimescaleDB
08-18 1074
The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program. 作者选择了COVID-19救济基金来接受捐赠，这是Write for DOnations计划的一部分。 介绍 (Introduction) Many applicatio...
PdShell16扫描内容不符合数据库规范整合
weixin_44364212的博客
08-10 840
@PdShell16扫描内容不符合数据库规范整合 PdShell16扫描内容不符合数据库规范整合 根据需求，需要扫描数据库字段与关键字有冲突问题 首先我通过vb脚本实现扫描 因为PdShell16扫描只支持vb 首先我们打开PdShell16进行操作扫描所有ER图 然后通过我们写的vb脚本施行扫描 '****************************************************************************** '* File: name2comme
【遇见offer】微软专场直播强势来袭！快来领取你的专属面试福利吧~
遇见OFFER
07-23 7328
千呼万唤始出来，微软的直播招聘专场终于来啦！这次微软公司可是诚意满满，在四座大城市开放了三十余个正式职位，等你来投递。 不是什么时候都有机会进微软的哦！如果你有一颗近距离体验大厂工作的心，就快点把握机会，预约直播吧！（当然你可以在直播中看到微软的幕后大佬们！） 戳下面链接，看岗位、投简历！ https://marketing.csdn.net/questions/Q2007091412518779288 【关于招聘专场直播】 直播时间：7月30日 13：00-17：30 直播奖品：进直播间，参与互动即有机会
NBOrganizers.plist
05-06
NBOrganizers.plist
postgresql之List
happytree001的博客
06-20 2523
postgresql功能丰富的List
postgresql 集合类型_PostgreSQL源码里面的List结构
weixin_39744384的博客
01-13 2006
PostgreSQL中广泛使用了一种List数据结构，该结构是一个单链表。其定义在pg_list.h和list.c文件中：typedef struct ListCell ListCell;typedef struct List{NodeTagtype;/* T_List, T_IntList, or T_OidList */intlength;ListCell *head;ListCell ...
hive的collect_set在presto中的替代
zhuchunyan_aijia的博客
11-30 3383
Hive： collect_set 转为数组并去重，concat_ws 将数组用逗号间隔连接成字符串 select user_id , concat_ws(',', collect_set(order_id)) as order_ids from tmp.tmp_row_to_col where 1 = 1 group by user_id ; Presto： array_agg 转为数组，array_distinct 去重，array_join 将数组用逗号间隔连接成字符串 sele..
PostgreSQL数据库查询执行——Set命令
肥叔菌的博客
11-30 5059
SET命令用于改变运行时参数。语法如下： SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT } SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT } 函数 set_config 提供了等效的功能； 见第 9.27.1 节。 此外，可以更新 pg_settings 系统视图以执行等效的 SET。 Synopsi
PostgreSQL 10 - 分组集合和排序集合
weixin_43364172的博客
12-26 4828
PostgreSQL 10 - 分组集合和排序集合grouping sets先加载例子数据使用分组集合性能组合grouping sets和FILTER使用ordered sets理解假设聚合 grouping sets 很多人都熟悉GROUP BY和HAVING。但是，你熟悉CUBE、ROLLUP和GROUPING SETS吗？ 先加载例子数据 我们使用BP能源报告的数据。 数据结构是 CREAT...
在plsql dev中结果集为（collection) 如何处理
weixin_30505225的博客
07-27 1177
​日常工作中，如果存储过程出参是out 一个数组的方式，在plsql dev中变量显示为 （collection) ，没法查看里面的结果集，上网查了一下解决方式：1. 监视窗口中增加具体基本元素可以，不能整体显示，如显示数组a(1)，在监视窗口添加a(1)即可2. DBMS_OUTPUT，在赋予一个结果集后，可以增加一个部分 1234-- 通过 dbms_output.put_line 方式逐...
postgresql常用命令
阿义的专栏
05-31 905
postgresql常用命令 http://luobeng.blogbus.com/logs/106326674.html 1.createdb 数据库名称 产生数据库 2.dropdb 数据库名称 删除数据库 3.CREATE USER 用户名称 创建用户 4.drop User 用户名称 删除用户 5.SELECT usename FROM pg_use
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值