DB_IP=”192.168.1.10″
DB_PORT=”5432″
DB_NAME=”fgedudb”
DB_USER=”fgedu”
LOG_DIR=”/opengauss/log/performance”
mkdir -p $LOG_DIR

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

monitor_system() {
LOG_FILE=”$LOG_DIR/system_$(date ‘+%Y%m%d’).log”
log “=== 系统资源监控 ===”

# 系统负载
log “系统负载: $(uptime | awk ‘{print $10 $11 $12}’)”

# CPU使用率
cpu_usage=$(top -bn1 | grep “Cpu(s)” | sed “s/.*, *\([0-9.]*\)%* id.*/\1/” | awk ‘{print 100 – $1}’)
log “CPU使用率: ${cpu_usage}%”

# 内存使用
mem_usage=$(free -m | awk ‘/Mem:/ {print $3/$2 * 100.0}’)
log “内存使用率: ${mem_usage}%”

# 磁盘使用
disk_usage=$(df -h /opengauss | awk ‘/\// {print $5}’)
log “磁盘使用率: ${disk_usage}”

# IO状态
log “IO状态:”
iostat -x | grep -v “^Linux” | grep -v “^$” | tee -a $LOG_FILE
}

monitor_database() {
LOG_FILE=”$LOG_DIR/database_$(date ‘+%Y%m%d’).log”
log “=== 数据库性能监控 ===”

# 连接数
connections=$(gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c ”
SELECT count(*) FROM pg_stat_activity;
” | tr -d ‘ ‘)
log “数据库连接数: $connections”

# 活动事务
active_transactions=$(gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c ”
SELECT count(*) FROM pg_stat_activity WHERE state = ‘active’;
” | tr -d ‘ ‘)
log “活动事务数: $active_transactions”

# 慢SQL
log “慢SQL:
$(gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c ”
SELECT
pid,
usename,
now() – query_start AS duration,
query
FROM pg_stat_activity
WHERE state = ‘active’
AND now() – query_start > interval ‘1 second’
ORDER BY duration DESC
LIMIT 5;
“)”

# 表统计
log “表统计:
$(gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c ”
SELECT
relname AS table_name,
n_live_tup AS live_rows,
n_dead_tup AS dead_rows
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 5;
“)”
}

main() {
monitor_system
monitor_database
}

main