版权声明：本文为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