性能监控脚本#

脚本说明#

性能监控脚本用于监控数据库性能，包括查询性能、连接数、缓存命中率等指标。

脚本代码#

#!/bin/bash

# 性能监控脚本
# 功能：监控数据库性能
# 作者：System Admin
# 日期：2024-01-01

set -euo pipefail

# 配置变量
LOG_FILE="/var/log/db_performance.log"
REPORT_FILE="/tmp/db_performance_report.txt"
DB_TYPE="mysql"
DB_HOST="localhost"
DB_PORT=3306
DB_USER=""
DB_PASS=""
DB_NAME=""
INTERVAL=60
DURATION=3600

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[0;33m'
BLUE='\033[0;34m'
NC='\033[0m'

# 日志函数
log() {
    local level=$1
    shift
    local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
    echo "[$timestamp] [$level] $@" | tee -a "$LOG_FILE"
}

log_info() {
    log "INFO" "$@"
}

log_error() {
    log "ERROR" "$@"
}

# 监控MySQL性能
monitor_mysql() {
    log_info "监控MySQL性能"
    
    echo "MySQL性能监控"
    echo "=========="
    
    # 获取连接数
    local connections=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2 {print $2}')
    echo "当前连接数: $connections"
    
    # 获取最大连接数
    local max_connections=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2 {print $2}')
    echo "最大连接数: $max_connections"
    
    # 获取查询数
    local queries=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW STATUS LIKE 'Questions';" | awk 'NR==2 {print $2}')
    echo "总查询数: $queries"
    
    # 获取慢查询数
    local slow_queries=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW STATUS LIKE 'Slow_queries';" | awk 'NR==2 {print $2}')
    echo "慢查询数: $slow_queries"
    
    # 获取缓存命中率
    local qcache_hits=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW STATUS LIKE 'Qcache_hits';" | awk 'NR==2 {print $2}')
    local qcache_inserts=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW STATUS LIKE 'Qcache_inserts';" | awk 'NR==2 {print $2}')
    
    if [ $qcache_inserts -gt 0 ]; then
        local cache_hit_rate=$(echo "scale=2; $qcache_hits * 100 / ($qcache_hits + $qcache_inserts)" | bc -l)
        echo "查询缓存命中率: ${cache_hit_rate}%"
    fi
    
    # 获取InnoDB缓冲池命中率
    local innodb_buffer_pool_reads=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW STATUS LIKE 'Innodb_buffer_pool_reads';" | awk 'NR==2 {print $2}')
    local innodb_buffer_pool_read_requests=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';" | awk 'NR==2 {print $2}')
    
    if [ $innodb_buffer_pool_read_requests -gt 0 ]; then
        local buffer_pool_hit_rate=$(echo "scale=2; (1 - $innodb_buffer_pool_reads / $innodb_buffer_pool_read_requests) * 100" | bc -l)
        echo "InnoDB缓冲池命中率: ${buffer_pool_hit_rate}%"
    fi
    
    # 获取表锁等待
    local table_locks_waited=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW STATUS LIKE 'Table_locks_waited';" | awk 'NR==2 {print $2}')
    echo "表锁等待: $table_locks_waited"
    
    # 获取临时表使用
    local tmp_table=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW STATUS LIKE 'Created_tmp_tables';" | awk 'NR==2 {print $2}')
    local tmp_disk_table=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -e "SHOW STATUS LIKE 'Created_tmp_disk_tables';" | awk 'NR==2 {print $2}')
    echo "临时表: $tmp_table (磁盘: $tmp_disk_table)"
}

# 监控PostgreSQL性能
monitor_postgresql() {
    log_info "监控PostgreSQL性能"
    
    echo ""
    echo "PostgreSQL性能监控"
    echo "=========="
    
    # 获取连接数
    local connections=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" \
        -t -c "SELECT count(*) FROM pg_stat_activity;")
    echo "当前连接数: $connections"
    
    # 获取最大连接数
    local max_connections=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" \
        -t -c "SHOW max_connections;")
    echo "最大连接数: $max_connections"
    
    # 获取缓存命中率
    local cache_hit=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" \
        -t -c "SELECT sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) * 100 FROM pg_stat_database;")
    echo "缓存命中率: ${cache_hit}%"
    
    # 获取慢查询数
    local slow_queries=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" \
        -t -c "SELECT count(*) FROM pg_stat_statements WHERE mean_exec_time > 1000;")
    echo "慢查询数: $slow_queries"
    
    # 获取表大小
    echo ""
    echo "表大小:"
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" \
        -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;"
    
    # 获取索引使用情况
    echo ""
    echo "索引使用情况:"
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" \
        -c "SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 10;"
}

# 监控MongoDB性能
monitor_mongodb() {
    log_info "监控MongoDB性能"
    
    echo ""
    echo "MongoDB性能监控"
    echo "=========="
    
    # 获取连接数
    local connections=$(mongo --host "$DB_HOST" --port "$DB_PORT" -u "$DB_USER" -p "$DB_PASS" \
        --eval "db.serverStatus().connections" --quiet | grep "current" | awk '{print $2}')
    echo "当前连接数: $connections"
    
    # 获取操作数
    local operations=$(mongo --host "$DB_HOST" --port "$DB_PORT" -u "$DB_USER" -p "$DB_PASS" \
        --eval "db.serverStatus().opcounters" --quiet)
    echo "操作数: $operations"
    
    # 获取内存使用
    local memory=$(mongo --host "$DB_HOST" --port "$DB_PORT" -u "$DB_USER" -p "$DB_PASS" \
        --eval "db.serverStatus().mem" --quiet)
    echo "内存使用: $memory"
    
    # 获取锁等待
    local locks=$(mongo --host "$DB_HOST" --port "$DB_PORT" -u "$DB_USER" -p "$DB_PASS" \
        --eval "db.serverStatus().locks" --quiet)
    echo "锁等待: $locks"
    
    # 获取集合大小
    echo ""
    echo "集合大小:"
    mongo --host "$DB_HOST" --port "$DB_PORT" -u "$DB_USER" -p "$DB_PASS" \
        "$DB_NAME" --eval "db.getCollectionNames().forEach(function(name) { var stats = db.getCollection(name).stats(); print(name + ': ' + stats.size + ' bytes'); })" --quiet
}

# 监控查询性能
monitor_query_performance() {
    log_info "监控查询性能"
    
    echo ""
    echo "查询性能监控"
    echo "=========="
    
    case $DB_TYPE in
        mysql)
            # 获取慢查询
            echo "慢查询:"
            mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
                -e "SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;" 2>/dev/null || \
            mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
                -e "SHOW FULL PROCESSLIST;" | awk '$6 > 1'
            ;;
        postgresql)
            # 获取慢查询
            echo "慢查询:"
            psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" \
                -c "SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;"
            ;;
        mongodb)
            # 获取慢查询
            echo "慢查询:"
            mongo --host "$DB_HOST" --port "$DB_PORT" -u "$DB_USER" -p "$DB_PASS" \
                "$DB_NAME" --eval "db.getCollection('system.profile').find().sort({millis: -1}).limit(10)" --quiet
            ;;
    esac
}

# 监控索引性能
monitor_index_performance() {
    log_info "监控索引性能"
    
    echo ""
    echo "索引性能监控"
    echo "=========="
    
    case $DB_TYPE in
        mysql)
            # 获取索引使用情况
            echo "索引使用情况:"
            mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
                "$DB_NAME" -e "SELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = '$DB_NAME' ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;"
            ;;
        postgresql)
            # 获取索引使用情况
            echo "索引使用情况:"
            psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" \
                -c "SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 10;"
            ;;
        mongodb)
            # 获取索引使用情况
            echo "索引使用情况:"
            mongo --host "$DB_HOST" --port "$DB_PORT" -u "$DB_USER" -p "$DB_PASS" \
                "$DB_NAME" --eval "db.getCollectionNames().forEach(function(name) { var indexes = db.getCollection(name).getIndexes(); print(name + ': ' + indexes.length + ' indexes'); })" --quiet
            ;;
    esac
}

# 生成性能报告
generate_performance_report() {
    log_info "生成性能报告"
    
    {
        echo "数据库性能报告"
        echo "=========="
        echo "报告时间: $(date)"
        echo "数据库类型: $DB_TYPE"
        echo "数据库主机: $DB_HOST:$DB_PORT"
        echo ""
        
        monitor_mysql
        monitor_postgresql
        monitor_mongodb
        monitor_query_performance
        monitor_index_performance
        
        echo ""
        echo "报告生成时间: $(date)"
    } > "$REPORT_FILE"
    
    log_info "性能报告已生成: $REPORT_FILE"
}

# 持续监控
continuous_monitoring() {
    log_info "开始持续监控"
    
    local end_time=$(($(date +%s) + DURATION))
    
    while [ $(date +%s) -lt $end_time ]; do
        log_info "执行性能监控"
        
        case $DB_TYPE in
            mysql)
                monitor_mysql
                ;;
            postgresql)
                monitor_postgresql
                ;;
            mongodb)
                monitor_mongodb
                ;;
        esac
        
        log_info "等待 $INTERVAL 秒后再次监控"
        sleep $INTERVAL
    done
}

# 显示帮助
show_help() {
    echo "用法: $0 [选项]"
    echo ""
    echo "选项:"
    echo "  -t <类型>        数据库类型（mysql/postgresql/mongodb）"
    echo "  -h <主机>        数据库主机（默认: localhost）"
    echo "  -p <端口>        数据库端口（默认: 3306）"
    echo "  -u <用户>        数据库用户"
    echo "  -P <密码>        数据库密码"
    echo "  -d <数据库>      数据库名"
    echo "  -i <间隔>        监控间隔（秒，默认: 60）"
    echo "  -d <时长>        监控时长（秒，默认: 3600）"
    echo "  -c               持续监控"
    echo "  -h               显示帮助信息"
    echo ""
    echo "示例:"
    echo "  $0 -t mysql -u root -P password"
    echo "  $0 -t postgresql -u postgres -d mydb"
    echo "  $0 -t mongodb -u admin -P password -c"
}

# 主函数
main() {
    # 解析选项
    while getopts "t:h:p:u:P:d:i:cH" opt; do
        case $opt in
            t)
                DB_TYPE="$OPTARG"
                log_info "数据库类型: $DB_TYPE"
                ;;
            h)
                DB_HOST="$OPTARG"
                log_info "数据库主机: $DB_HOST"
                ;;
            p)
                DB_PORT="$OPTARG"
                log_info "数据库端口: $DB_PORT"
                ;;
            u)
                DB_USER="$OPTARG"
                log_info "数据库用户: $DB_USER"
                ;;
            P)
                DB_PASS="$OPTARG"
                log_info "数据库密码: ****"
                ;;
            d)
                DB_NAME="$OPTARG"
                log_info "数据库名: $DB_NAME"
                ;;
            i)
                INTERVAL="$OPTARG"
                log_info "监控间隔: $INTERVAL 秒"
                ;;
            c)
                CONTINUOUS=true
                log_info "持续监控"
                ;;
            H)
                show_help
                exit 0
                ;;
            *)
                log_error "无效选项: $opt"
                show_help
                exit 1
                ;;
        esac
    done
    
    # 执行监控
    if [ "${CONTINUOUS:-false}" = true ]; then
        continuous_monitoring
    else
        generate_performance_report
    fi
    
    log_info "数据库性能监控完成"
}

# 执行主函数
main "$@"

使用说明#

添加执行权限：
```
chmod +x db_performance.sh
```

基本用法：

# 监控MySQL性能
./db_performance.sh -t mysql -u root -P password

# 监控PostgreSQL性能
./db_performance.sh -t postgresql -u postgres -d mydb

# 监控MongoDB性能
./db_performance.sh -t mongodb -u admin -P password

高级用法：

# 持续监控
./db_performance.sh -t mysql -u root -P password -c

# 指定监控间隔
./db_performance.sh -t mysql -u root -P password -i 30 -c

# 指定数据库主机
./db_performance.sh -t mysql -h remotehost -u root -P password

功能特点#

多数据库支持（MySQL、PostgreSQL、MongoDB）
连接数监控
查询性能监控
缓存命中率监控
索引性能监控
持续监控
性能报告生成

依赖项#

mysql: MySQL客户端
psql: PostgreSQL客户端
mongo: MongoDB客户端
bc: 用于数值计算

注意事项#

需要数据库用户有足够权限
某些监控功能需要配置
持续监控建议使用systemd管理
监控间隔不宜过短
性能报告可以用于性能优化分析