Greenplum-慢日志监控

原创布衣 2024-02-02

1041

前言

公司自从上了Greenplum数据库作为数据仓库后，随着业务的需求不段增加。从小打小闹到现在直接给公司领导层定期出日周月年报的“总裁驾驶仓”项目，领导层已经开始依赖“总裁驾驶仓”的数据了。
因此监控就应该更加全面，从而保障数据仓库的正常运转，能及时为公司提供数据。经过2周的时间整出了一份监控脚本，供大家参考。

一、慢日志参数设置

log_filename = 'greenplum-%a.log'  #日志名称，以周几为单位
log_truncate_on_rotation = on  #开启覆盖功能
log_rotation_age = 1d  #以1天位单位生成1个文件
log_rotation_size = 0 #切换日志
log_min_duration_statement = 5000 #记录执行5秒及以上的语句，跟踪慢查询语句，单位为毫秒。

日志仅保留7天，循环覆盖

[gpadmin@master pg_log]$ pwd
/u05/data/mirror1/gpseg12/pg_log
[gpadmin@master pg_log]$ ll
greenplum-Mon.csv
greenplum-Tue.csv
greenplum-Wed.csv
greenplum-Thu.csv
greenplum-Fri.csv
greenplum-Sat.csv
greenplum-Sun.csv

1.1、开启覆盖功能

[gpadmin@master ~]$ gpconfig -s log_truncate_on_rotation
Values on all segments are consistent
GUC          : log_truncate_on_rotation
Master  value: on
Segment value: off
[gpadmin@master ~]$ gpconfig -c log_truncate_on_rotation -v on
20240130:11:10:58:013662 gpconfig:master:gpadmin-[INFO]:-completed successfully with parameters '-c log_truncate_on_rotation -v on'

1.2、日志文件一天一份

[gpadmin@master ~]$ gpconfig -s log_rotation_age
Values on all segments are consistent
GUC          : log_rotation_age
Master  value: 1d
Segment value: 1d

1.3、日志文件大小不限制

[gpadmin@master ~]$ gpconfig -s log_rotation_size
Values on all segments are consistent
GUC          : log_rotation_size
Master  value: 30MB
Segment value: 1GB
[gpadmin@master ~]$ gpconfig -c log_rotation_size -v 0
20240130:11:13:11:014128 gpconfig:master:gpadmin-[INFO]:-completed successfully with parameters '-c log_rotation_size -v 0'

1.4、开启慢SQL监控，单位为毫秒。

[gpadmin@master ~]$ gpconfig -s log_min_duration_statement
Values on all segments are consistent
GUC          : log_min_duration_statement
Master  value: 5s
Segment value: 5s
[gpadmin@master ~]$ gpconfig -c log_min_duration_statement -v 5000
20240130:11:14:09:014525 gpconfig:master:gpadmin-[INFO]:-completed successfully with parameters '-c log_min_duration_statement -v 5000'

1.5、日志名称，以周几为单位

删除log_filename 参数

[gpadmin@master gpconfigs]$ gpssh -f hostlist 
=> sed -i '/log_filename/d' /u05/data/*/*/postgresql.conf
[segment2]
[segment3]
[segment4]
[  master]
[segment1]
[ standby]

设置log_filename：

[gpadmin@master gpconfigs]$ gpssh -f hostlist 
=>  sed  -i "\$a\log_filename = \'greenplum-%a.log\'" /u05/data/*/*/postgresql.conf
[segment1]
[segment3]
[ standby]
[segment2]
[  master]
[segment4]

1.6、重启集群

gpstop -i
gpstart -a

二、日志切割

[gpadmin@master pg_log]$ awk -F',' '{print $1,$17,$2,$3,$19}' /u05/data/master/gpseg-1/pg_log/greenplum-Mon.csv | grep duration
2024-01-29 16:27:25.719137 CST "LOG" "two" "twodb" "duration: 8844.762 ms  statement: select count(*) from t_order  t1 join t_order t2 on t1.order_no =t2.order_no "

三、慢日志监控脚本

#!/bin/bash
source /usr/local/greenplum-db/greenplum_path.sh
source /home/gpadmin/gpcc/greenplum-cc-web-6.1.0/gpcc_path.sh
GPHOME=/usr/local/greenplum-db-6.20.5
MASTER_DATA_DIRECTORY=/u05/data/master/gpseg-1
LANG=C
log_name=greenplum-`date +%a`.csv
#慢SQL阀值
v_Exe_time=$1
#当天日期
v_day=`date +%Y%m%d`
#日志输出目录
Slow_dir='/home/gpadmin'
#当天日志输出文件
Slow_dayfile=$Slow_dir'/'$v_day'/Slow_day.sql'
#当时日志输出文件
Slow_nowfile=$Slow_dir'/'$v_day'/Slow_now.sql'
#生成日志输出目录
if [ ! -d $Slow_dir'/'$v_day ];then
mkdir -p $Slow_dir'/'$v_day
fi
# 删除1个月前日志
v_month_ago=`date -d " -30 day" +%Y%m%d`
if [ -d $Slow_dir'/'$v_month_ago ];then
rm -rf $Slow_dir'/'$v_month_ago
fi
awk -F','  '{print $1,$2,$3,$19}' $MASTER_DATA_DIRECTORY/pg_log/${log_name} | grep duration | awk -v Exe_time=$v_Exe_time  -F'[:ms]'
 '$4/1000>Exe_time {print $0}' > $Slow_nowfile
v_len=`cat  $Slow_nowfile | wc -l`
if [ $v_len -gt 0 ];then
    if [ ! -d $Slow_dayfile ];then
    cat $Slow_nowfile > $Slow_dayfile
    fi
    v_day_len=`cat  $Slow_dayfile | wc -l`
    if [ $v_len -eq $v_day_len ];then
        echo 'Successed'
    else
        echo 'Error:Slow SQL:'$v_len
        #慢SQL日志输出至历史文件
        cat $Slow_nowfile > $Slow_dayfile
        #当时慢SQL日志清空
        > $Slow_nowfile
    fi
else
    echo 'Successed'
fi

3.1、执行及输出

[gpadmin@master ~]$ sh Check_Greenplum.sh 8 <= 阀值：8s
Error:Slow SQL:19                           <= 有慢SQL输出Error并告警条数
[gpadmin@master ~]$ sh Check_Greenplum.sh 8
Successed                                   <= 第二次执行，无增加慢SQL条数报:Successed
[gpadmin@master ~]$ sh Check_Greenplum.sh 8
Error:Slow SQL:22                           <= SQL条数增加再次报警
[gpadmin@master ~]$ sh Check_Greenplum.sh 8
Successed

3.2、输出日志

[gpadmin@master 20240201]$ pwd
/home/gpadmin/20240201                                      <= 每天生成一个目录
[gpadmin@master 20240201]$ ll
总用量 16
-rw-rw-r-- 1 gpadmin gpadmin 5852 2月   1 10:36 Slow_day.sql <= 当天慢SQL日志
-rw-rw-r-- 1 gpadmin gpadmin 5852 2月   1 10:36 Slow_now.sql <= 当时慢SQL日志
[gpadmin@master 20240201]$ vim Slow_day.sql <= 输出：日期|用户|数据库|执行时间：SQL语句
2024-02-01 10:34:56.663795 CST "two" "twodb" "duration: 8002.242 ms  statement: select count(*) from t_order  t1 join t_order t2 on t1.order_no =t2.order_no 
2024-02-01 10:34:56.915131 CST "two" "twodb" "duration: 8251.265 ms  statement: select count(*) from t_order  t1 join t_order t2 on  t1.order_no =t2.order_no
2024-02-01 10:34:57.098807 CST "two" "twodb" "duration: 8439.178 ms  statement: select count(*) from t_order  t1 join t_order t2 on t1.order_no =t2.order_no 
2024-02-01 10:35:05.235942 CST "two" "twodb" "duration: 8066.328 ms  statement: select count(*) from t_order  t1 join t_order t2 ont1.order_no =t2.order_no

四、监控脚本下载

4.1、监控脚本下载

监控脚本下载：Check_Greenplum.sh
慢SQL日志监控下载：Greenplum-慢日志监控

4.2、监控脚本运行

# 监控twodb 数据库的session数
[gpadmin@master ~]$ sh Check_Greenplum.sh status session twodb
0
# 监控所有数据库的session数
[gpadmin@master ~]$ sh Check_Greenplum.sh status session
1
# 监控segment状态
[gpadmin@master ~]$ sh Check_Greenplum.sh status segment
Pri_Mir_Seg_status is ok
# 监控超8s的慢SQL
[gpadmin@master ~]$ sh Check_Greenplum.sh slowsql 8
Error:Slow SQL:60
# 慢SQL条目不增加，第二次执行返回正常
[gpadmin@master ~]$ sh Check_Greenplum.sh slowsql 8
Successed
# 监控数据库异常日志
[gpadmin@master ~]$ sh Check_Greenplum.sh log
LOG:FATAL: 60
# 异常日志条目不增加，第二次执行返回正常
[gpadmin@master ~]$  sh Check_Greenplum.sh log
Successed