数据库服务器性能，在高频查询时尤为重要，在物联网应用中，如thingsboard数据库高频的数据上报情况下，用于查询和记录postgresql数据库服务器的主要性能和磁盘IO延迟，在CentOS9，postgresql16下通过。
#!/bin/bash
# 设置数据库连接信息
DB_HOST="localhost"
DB_PORT="5432"
DB_USER="用户名"
DB_NAME="数据库名"
# 通过which psql， 查找psql路径
PSQL="/www/server/pgsql/bin/psql"
# 获取 PostgreSQL 的性能信息并保存到变量
# version=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -c "SELECT version();")
version=$($PSQL -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -c "SELECT substring(version() FROM '([0-9]+\.[0-9]+)') AS version;")
connection_count=$($PSQL -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -c "SELECT count(*) FROM pg_stat_activity;")
database_size=$($PSQL -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -c "SELECT pg_size_pretty(pg_database_size(current_database()));")
active_queries_count=$($PSQL -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';")
buffer_hit_ratio=$($PSQL -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -c "SELECT round(100 * (blks_hit::float / (blks_hit + blks_read))) FROM pg_stat_database WHERE datname = current_database();")
active_transactions_count=$($PSQL -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction';")
shared_buffers_usage=$($PSQL -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -c "SELECT pg_size_pretty(pg_stat_bgwriter.buffers_backend * 8192) FROM pg_stat_bgwriter;")
# 获取磁盘I/O信息
iostat -x 1 1 | while read line; do
# 只处理包含字母和数字的行（跳过标题行）
if [[ "$line" =~ ^[a-zA-Z0-9] ]]; then
# 获取当前时间戳
timestamp=$(date +"%Y-%m-%d %H:%M:%S")
# 提取设备名、r_await、w_await和w/s（假设设备名在第1列，r_await在第6列，w_await在第12列,f_await在第21列）
device=$(echo $line | awk '{print $1}')
r_await=$(echo $line | awk '{print $6}')
w_await=$(echo $line | awk '{print $12}')
f_await=$(echo $line | awk '{print $21}')
w_s=$(echo $line | awk '{print $11}')
# 输出当前时间戳、r_await、w_await、f_await值 ，并输出到文件
if [[ "$device" == "sda" ]]; then
echo "$timestamp ReadIOAwait: $r_await ms, WriteIOAwait: $w_await ms, FlushIOAwait: $f_await ms, PGVer: $version, ConnectionCount: $connection_count, DatabaseSize: $database_size, ActiveQueriesCount: $active_queries_count, BufferHitRatio: $buffer_hit_ratio%, ActiveTransactionsCount: $active_transactions_count, SharedBuffersUsage: $shared_buffers_usage" >> "/www/wwwlogs/pgsql/pgsql_log_$(date '+%Y-%m-%d').txt"
fi
fi
done
以上脚本可以放在计划任务中定时运行，以此来获得数据库的性能。
原创文章，作者：Gary，如若转载，请注明出处：https://www.cpw5.top/1464.html
淘宝小店：陈皮王五工作室
