使用PostgreSQL分析操作系统命令及文件

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:使用PostgreSQL分析操作系统命令及文件
通过在Linux安装了sysstat,可以实现自动采集性能数据存放在/var/log/sa中。要开启自动采集,需要开启syssata服务,在centos 7当中使用下列命令:

systemctl enable sysstat
systemctl start sysstat

当然自动采集是10分钟一次,为了实验方便,我修改了配置文件调整成1分钟1次。

[root@db sa]# more /etc/cron.d/sysstat 
# Run system activity accounting tool every 10 minutes
*/1 * * * * root /usr/lib64/sa/sa1 1 1
# 0 * * * * root /usr/lib64/sa/sa1 600 6 &
# Generate a daily summary of process accounting at 23:53
53 23 * * * root /usr/lib64/sa/sa2 -A

接下来我们运行sar -f命令查看自动生成的文件.

[root@db sa]# sar -f sa22 | head -20
Linux 3.10.0-693.21.1.el7.x86_64 (db.gowithme.tw) 	2018年10月22日 	_x86_64_	(1 CPU)

21时48分59秒       LINUX RESTART
21时59分54秒       LINUX RESTART

22时00分01秒     CPU     %user     %nice   %system   %iowait    %steal     %idle
22时01分01秒     all      0.02      0.00      0.20      0.03      0.00     99.75
22时02分01秒     all      0.02      0.00      0.12      0.02      0.00     99.85
22时03分01秒     all      0.00      0.00      0.07      0.02      0.00     99.92
22时04分01秒     all      0.00      0.00      0.07      0.00      0.00     99.93
22时05分01秒     all      0.02      0.00      0.08      0.00      0.00     99.90
22时06分01秒     all      0.00      0.00      0.10      0.02      0.00     99.88
22时07分02秒     all      0.02      0.00      0.07      0.00      0.00     99.92
22时08分01秒     all      0.00      0.00      0.07      0.02      0.00     99.91
22时09分01秒     all      0.02      0.00      0.07      0.02      0.00     99.90
22时10分01秒     all      0.00      0.00      0.07      0.00      0.00     99.93
22时11分01秒     all      0.02      0.00      0.07      0.00      0.00     99.92
22时12分01秒     all      0.00      0.00      0.07      0.02      0.00     99.92
22时13分01秒     all      0.00      0.00      0.08      0.00      0.00     99.92

接下来我们就可以使用postgresql将OS命令的结果存储到变量中.

-bash-4.2$ psql 
psql (10.4)
Type "help" for help.

然后查询变量,就可以查看到os的数据.
postgres=# \set varls `ls -la`
postgres=# select :'varls';
                               ?column?                               
----------------------------------------------------------------------
 total 20                                                            +
 dr-xr-xr-x.  18 root    root     239 Oct 22 20:33 .                 +
 dr-xr-xr-x.  18 root    root     239 Oct 22 20:33 ..                +
 lrwxrwxrwx.   1 root    root       7 Mar 31  2018 bin -> usr/bin    +
 dr-xr-xr-x.   5 root    root    4096 Mar 31  2018 boot              +
 drwxr-xr-x.  18 root    root    3040 Oct 22 21:33 dev               +
 drwxr-xr-x.  76 root    root    8192 Oct 22 21:45 etc               +
 drwxr-xr-x.   3 root    root      21 Mar 31  2018 home              +
 lrwxrwxrwx.   1 root    root       7 Mar 31  2018 lib -> usr/lib    +
 lrwxrwxrwx.   1 root    root       9 Mar 31  2018 lib64 -> usr/lib64+
 drwxr-xr-x.   2 root    root       6 Nov  5  2016 media             +
 drwxr-xr-x.   2 root    root       6 Nov  5  2016 mnt               +
 drwxr-xr-x.   3 root    root      39 Mar 31  2018 opt               +
 dr-xr-xr-x. 113 root    root       0 Oct 22 21:33 proc              +
 dr-xr-x---.   3 root    root     163 Mar 31  2018 root              +
 drwxr-xr-x.  23 root    root     700 Oct 22 21:45 run               +
 lrwxrwxrwx.   1 root    root       8 Mar 31  2018 sbin -> usr/sbin  +
 drwxr-xr-x.   2 root    root       6 Nov  5  2016 srv               +
 dr-xr-xr-x.  13 root    root       0 Oct 22 21:33 sys               +
 drwxrwxrwt.   7 root    root     140 Oct 22 21:45 tmp               +
 drwxr-xr-x.  16 root    root     199 Jun 28 10:50 usr               +
 drwxr-xr-x.   1 vagrant vagrant 4096 Oct 22 21:23 vagrant           +
 drwxr-xr-x.  19 root    root     267 Mar 31  2018 var
(1 row)

接下来我们在把sar的结果放在var_iostat变量里面.

postgres=# \set var_iostat `sar -f /var/log/sa/sa22`
postgres=# select :'var_iostat';
                                            ?column?                                             
-------------------------------------------------------------------------------------------------
 Linux 3.10.0-693.21.1.el7.x86_64 (db.gowithme.tw)       10/22/2018      _x86_64_        (1 CPU)+
                                                                                                +
 09:48:59 PM       LINUX RESTART                                                                +
                                                                                                +
 09:59:54 PM       LINUX RESTART                                                                +
                                                                                                +
 10:00:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle                +
 10:01:01 PM     all      0.02      0.00      0.20      0.03      0.00     99.75                +
 10:02:01 PM     all      0.02      0.00      0.12      0.02      0.00     99.85                +
 10:03:01 PM     all      0.00      0.00      0.07      0.02      0.00     99.92                +
 10:04:01 PM     all      0.00      0.00      0.07      0.00      0.00     99.93                +
 10:05:01 PM     all      0.02      0.00      0.08      0.00      0.00     99.90                +
 10:06:01 PM     all      0.00      0.00      0.10      0.02      0.00     99.88                +
 10:07:02 PM     all      0.02      0.00      0.07      0.00      0.00     99.92                +
 10:08:01 PM     all      0.00      0.00      0.07      0.02      0.00     99.91                +
 10:09:01 PM     all      0.02      0.00      0.07      0.02      0.00     99.90                +
 10:10:01 PM     all      0.00      0.00      0.07      0.00      0.00     99.93                +
 10:11:01 PM     all      0.02      0.00      0.07      0.00      0.00     99.92                +
 10:12:01 PM     all      0.00      0.00      0.07      0.02      0.00     99.92                +

由于我们只对iowait这一列感兴趣,我们可以使用awk进行过滤。

postgres=# \set var_iostat `sar -f /var/log/sa/sa22 | egrep -v "^$|Average|Linux|LINUX|CPU" | awk -F " " '{print $7}'`
postgres=# select :'var_iostat';
 ?column? 
----------
 0.03    +
 0.02    +
 0.02    +
 0.00    +
 0.00    +
 0.02    +
 0.00    +
 0.02    +
 0.02    +
 0.00    +
 0.00    +
 0.02    +
 0.00    +
 0.02    +

如果我们把这个插入到表中,结果会是一个varchar列,而不是多列。这对于我们分析数据不是很好,接下来我们需要做一下行列转换,先把这么多行转换成一行数据,并用,分割开。

postgres=# \set var_iostat `sar -f /var/log/sa/sa22 | egrep -v "^$|Average|Linux|LINUX|CPU" | awk -F " " '{print $7}' | sed ':a;N;$!ba;s/\n/,/g'`
postgres=# select :'var_iostat';
                                                                                                                                                            ?column?                                                                                          
                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------
 0.03,0.02,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.00,0.02,0.00,0.02,0.00,0.00,0.00,0.00,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.02,0.00,0.02,0.02,0.00,0.00,0.02,0.00,0.00,0.00,0.00,0.00,0.02,0.00,0.00,0.00,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.0
0,0.00,0.02,0.00,0.02,0.00,0.07,0.00,0.02,0.00,0.00,0.02,0.00,0.02
(1 row)

现在我们把上述这个变量用逗号分割,然后转换成数组。

postgres=# select string_to_array(:'var_iostat',',')::numeric[];
                                                                                                                                                          string_to_array                                                                                     
                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------
 {0.03,0.02,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.00,0.02,0.00,0.02,0.00,0.00,0.00,0.00,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.02,0.00,0.02,0.02,0.00,0.00,0.02,0.00,0.00,0.00,0.00,0.00,0.02,0.00,0.00,0.00,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.
00,0.00,0.02,0.00,0.02,0.00,0.07,0.00,0.02,0.00,0.00,0.02,0.00,0.02}
(1 row)

接下来存储到表里面,我们就可以查询出当前使用率最大值和最小值做一些统计.

postgres=# create table tab_iowaits (varday date, iowaits numeric[]);
postgres=# insert into tab_iowaits values (current_date, string_to_array(:'var_iostat',',')::numeric[]);
postgres=# with tab as ( select unnest(iowaits) vals, varday from tab_iowaits where varday = current_date ) select max(vals) from tab;
 max  
------
 0.07
(1 row)

postgres=# with tab as ( select unnest(iowaits) vals, varday from tab_iowaits where varday = current_date ) select min(vals) from tab;
 min  
------
 0.00
(1 row)
分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *