DB_IP=”192.168.1.10″
DB_PORT=”5432″
DB_NAME=”fgedudb”
DB_USER=”fgedu”
LOG_FILE=”/opengauss/log/long_transaction_monitor.log”
ALERT_THRESHOLD=300 # 告警阈值(秒)
KILL_THRESHOLD=1800 # 自动终止阈值(秒)

log() {
echo “[$(date ‘+%Y-%m-%d %H:%M:%S’)] $1” | tee -a $LOG_FILE
}

check_long_transactions() {更多视频教程www.fgedu.net.cn
log “=== 检查长事务 ===”

gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c ”
SELECT
pid,
usename,
application_name,
state,
EXTRACT(EPOCH FROM (now() – xact_start))::int AS xact_seconds,
EXTRACT(EPOCH FROM (now() – query_start))::int AS query_seconds,
query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND now() – xact_start > interval ‘5 minutes’
ORDER BY xact_start;
” | while read line; do
if [ -n “$line” ]; then
log “发现长事务: $line”

# 提取事务ID和运行时间
pid=$(echo $line | awk -F’|’ ‘{print $1}’ | tr -d ‘ ‘)
xact_seconds=$(echo $line | awk -F’|’ ‘{print $5}’ | tr -d ‘ ‘)

# 超过告警阈值
if [ “$xact_seconds” -gt “$ALERT_THRESHOLD” ]; then
log “告警: 事务运行时间超过${ALERT_THRESHOLD}秒,会话PID: $pid”
fi

# 超过终止阈值
if [ “$xact_seconds” -gt “$KILL_THRESHOLD” ]; then
log “严重告警: 事务运行时间超过${KILL_THRESHOLD}秒,准备终止会话: $pid”

# 终止会话
gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -c ”
SELECT pg_terminate_backend($pid);
”

log “已终止长事务会话: $pid”
fi更多学习教程公众号风哥教程itpux_com
fi
done
}

check_idle_transactions() {
log “=== 检查空闲事务 ===”

gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c ”
SELECT
pid,
usename,
application_name,
state,
EXTRACT(EPOCH FROM (now() – xact_start))::int AS xact_seconds,
query
FROM pg_stat_activity
WHERE state = ‘idle in transaction’
AND now() – xact_start > interval ’10 minutes’
ORDER BY xact_start;
” | while read line; do
if [ -n “$line” ]; then
log “发现空闲事务: $line”

# 提取事务ID
pid=$(echo $line | awk -F’|’ ‘{print $1}’ | tr -d ‘ ‘)

# 终止空闲事务
gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -c ”
SELECT pg_terminate_backend($pid);
“from DB视频:www.itpux.com

log “已终止空闲事务会话: $pid”
fi
done
}

main() {
log “开始长事务监控检查…”

check_long_transactions
check_idle_transactions

log “长事务监控检查完成”
}

main