#!/bin/bash
# env_prepare.sh - 测试环境准备

echo "=== 系统信息收集 ==="
echo "操作系统: $(cat /etc/os-release | grep PRETTY_NAME | cut -d= -f2)"
echo "内核版本: $(uname -r)"
echo "CPU 型号: $(lscpu | grep 'Model name' | cut -d: -f2 | xargs)"
echo "CPU 核心: $(nproc)"
echo "内存大小: $(free -h | grep Mem | awk '{print $2}')"
echo "磁盘类型: $(lsblk -d -o NAME,ROTA,SIZE,MODEL | head -5)"
echo ""

echo "=== 关闭不必要的服务 ==="
# 避免测试期间干扰
sudo systemctl stop cron 2>/dev/null || true
sudo systemctl stop unattended-upgrades 2>/dev/null || true
sudo systemctl stop snapd 2>/dev/null || true
echo "Done"
echo ""

echo "=== 清空文件缓存 ==="
sudo sh -c 'echo 3 > /proc/sys/vm/drop_caches'
echo "Done"
echo ""

echo "=== 设置 CPU 调度器为 performance ==="
sudo cpupower frequency-set -g performance 2>/dev/null || echo "cpupower not available"
echo ""

echo "=== 禁用 Transparent HugePages（推荐数据库测试时禁用） ==="
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled > /dev/null 2>&1 || echo "THP not available"
echo ""

echo "=== 检查并设置文件描述符限制 ==="
echo "当前限制: $(ulimit -n)"
ulimit -n 65535
echo "新限制: $(ulimit -n)"
echo ""

echo "=== 检查 Swap ==="
echo "Swap: $(free -h | grep Swap | awk '{print $2}')"
echo "Swappiness: $(cat /proc/sys/vm/swappiness)"
echo ""

echo "=== 网络信息 ==="
ip -4 addr show | grep inet | head -3
echo ""

echo "=== 环境准备完成 ==="

#!/bin/bash
# mysql_env_prepare.sh - MySQL 测试环境准备

MYSQL_HOST="127.0.0.1"
MYSQL_USER="root"
MYSQL_PASS="secret"

echo "=== MySQL 版本 ==="
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS -e "SELECT VERSION();"

echo ""
echo "=== 关键参数 ==="
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS -e "
  SHOW VARIABLES WHERE Variable_name IN (
    'innodb_buffer_pool_size',
    'innodb_buffer_pool_instances',
    'innodb_flush_log_at_trx_commit',
    'innodb_io_capacity',
    'innodb_io_capacity_max',
    'innodb_log_buffer_size',
    'innodb_redo_log_capacity',
    'max_connections',
    'table_open_cache',
    'table_open_cache_instances',
    'thread_cache_size',
    'sort_buffer_size',
    'join_buffer_size',
    'read_buffer_size',
    'innodb_flush_method',
    'sync_binlog'
  );
"

echo ""
echo "=== Buffer Pool 命中率 ==="
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS -e "
  SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
"

echo ""
echo "=== 当前连接数 ==="
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS -e "
  SHOW STATUS LIKE 'Threads_connected';
  SHOW STATUS LIKE 'Max_used_connections';
"

#!/bin/bash
# pgsql_env_prepare.sh

PG_HOST="127.0.0.1"
PG_USER="postgres"

echo "=== PostgreSQL 版本 ==="
psql -h $PG_HOST -U $PG_USER -c "SELECT version();"

echo ""
echo "=== 关键参数 ==="
psql -h $PG_HOST -U $PG_USER -c "
  SELECT name, setting, unit 
  FROM pg_settings 
  WHERE name IN (
    'shared_buffers', 'effective_cache_size', 'work_mem',
    'maintenance_work_mem', 'max_connections', 'wal_buffers',
    'checkpoint_completion_target', 'max_wal_size', 'random_page_cost',
    'effective_io_concurrency', 'max_parallel_workers_per_gather'
  ) ORDER BY name;
"

echo ""
echo "=== Buffer Cache 命中率 ==="
psql -h $PG_HOST -U $PG_USER -c "
  SELECT 
    sum(blks_hit) * 100.0 / NULLIF(sum(blks_hit) + sum(blks_read), 0) AS hit_ratio
  FROM pg_stat_database 
  WHERE datname = 'sbtest';
"

#!/bin/bash
# warmup.sh - 预热脚本

DURATION=${1:-60}  # 默认 60 秒

echo "=== 开始预热（${DURATION}秒） ==="

# CPU 预热
echo ">>> CPU 预热..."
sysbench cpu --threads=$(nproc) --time=10 run > /dev/null 2>&1

# 内存预热
echo ">>> 内存预热..."
sysbench memory --memory-block-size=1M --threads=4 --time=10 run > /dev/null 2>&1

# 数据库预热（OLTP 测试）
if [ -n "$MYSQL_HOST" ]; then
  echo ">>> MySQL 预热..."
  sysbench oltp_read_write \
    --mysql-host=$MYSQL_HOST --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASS \
    --tables=${TABLES:-16} --table-size=${TABLE_SIZE:-1000000} \
    --threads=16 --time=$DURATION \
    run > /dev/null 2>&1
fi

if [ -n "$PG_HOST" ]; then
  echo ">>> PostgreSQL 预热..."
  sysbench oltp_read_write \
    --db-driver=pgsql --pgsql-host=$PG_HOST --pgsql-user=$PG_USER --pgsql-password=$PG_PASS \
    --tables=${TABLES:-16} --table-size=${TABLE_SIZE:-1000000} \
    --threads=16 --time=$DURATION \
    run > /dev/null 2>&1
fi

echo "=== 预热完成 ==="

-- MySQL: 检查 Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
-- 理想值 > 99%

-- PostgreSQL: 检查 Buffer Cache 命中率
SELECT 
  sum(blks_hit) * 100.0 / NULLIF(sum(blks_hit) + sum(blks_read), 0) AS hit_ratio
FROM pg_stat_database WHERE datname = 'sbtest';
-- 理想值 > 99%

#!/bin/bash
# collect_results.sh - 自动化结果收集

RESULT_DIR="./benchmark_$(date +%Y%m%d_%H%M%S)"
mkdir -p "$RESULT_DIR"

echo "=== 结果目录: $RESULT_DIR ==="

# 记录系统信息
cat > "$RESULT_DIR/system_info.txt" << EOF
日期: $(date)
主机名: $(hostname)
内核: $(uname -r)
CPU: $(lscpu | grep 'Model name' | cut -d: -f2 | xargs)
核心数: $(nproc)
内存: $(free -h | grep Mem | awk '{print $2}')
磁盘: $(lsblk -d -o NAME,SIZE,ROTA,MODEL | grep -v NAME)
EOF

# 记录数据库信息
if command -v mysql &> /dev/null; then
  mysql -h 127.0.0.1 -u root -psecret -e "SELECT VERSION();" > "$RESULT_DIR/mysql_info.txt" 2>/dev/null
fi

# 运行测试并收集 JSON 结果
run_test() {
  local test_name=$1
  shift
  
  echo ">>> 运行: $test_name"
  sysbench "$@" --json="$RESULT_DIR/${test_name}.json" run
  
  # 提取关键指标
  local tps=$(jq '.transactions.per_second' "$RESULT_DIR/${test_name}.json")
  local avg_latency=$(jq '.latency.avg' "$RESULT_DIR/${test_name}.json")
  local p95=$(jq '.latency.percentile_95' "$RESULT_DIR/${test_name}.json")
  
  echo "  TPS: $tps, Avg Latency: ${avg_latency}ms, P95: ${p95}ms"
}

# 运行各种测试
run_test "cpu_t1" cpu --threads=1 --time=60
run_test "cpu_t8" cpu --threads=8 --time=60
run_test "memory_t4" memory --memory-block-size=1M --threads=4 --time=60

# 数据库测试
if [ -n "$MYSQL_HOST" ]; then
  run_test "oltp_rw_t8" oltp_read_write \
    --mysql-host=$MYSQL_HOST --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASS \
    --tables=16 --table-size=1000000 --threads=8 --time=300
  
  run_test "oltp_rw_t16" oltp_read_write \
    --mysql-host=$MYSQL_HOST --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASS \
    --tables=16 --table-size=1000000 --threads=16 --time=300
fi

# 生成摘要
echo ""
echo "=== 测试摘要 ==="
echo "结果保存在: $RESULT_DIR"
ls -la "$RESULT_DIR"/*.json

#!/bin/bash
# parse_results.sh - 解析 JSON 结果

RESULT_DIR=$1

if [ -z "$RESULT_DIR" ]; then
  echo "Usage: $0 <result_dir>"
  exit 1
fi

echo "========================================="
echo "  Sysbench 测试结果摘要"
echo "========================================="
echo ""
printf "%-20s %10s %10s %10s %10s\n" "测试项" "TPS" "QPS" "Avg(ms)" "P95(ms)"
printf "%-20s %10s %10s %10s %10s\n" "-----" "---" "---" "-------" "-------"

for json_file in "$RESULT_DIR"/*.json; do
  [ -f "$json_file" ] || continue
  
  test_name=$(basename "$json_file" .json)
  tps=$(jq '.transactions.per_second // 0' "$json_file" 2>/dev/null || echo "N/A")
  qps=$(jq '.queries.per_second // 0' "$json_file" 2>/dev/null || echo "N/A")
  avg=$(jq '.latency.avg // 0' "$json_file" 2>/dev/null || echo "N/A")
  p95=$(jq '.latency.percentile_95 // 0' "$json_file" 2>/dev/null || echo "N/A")
  
  printf "%-20s %10.2f %10.2f %10.2f %10.2f\n" "$test_name" "$tps" "$qps" "$avg" "$p95"
done

echo ""
echo "========================================="

-- 查看当前运行的查询
SELECT * FROM information_schema.processlist 
WHERE command != 'Sleep' AND time > 1 
ORDER BY time DESC;

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS\G

-- 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits;

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

-- 查看表统计
SELECT table_name, table_rows, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'sbtest'
ORDER BY data_length DESC;

#!/bin/bash
# generate_report.sh - 生成 Markdown 测试报告

RESULT_DIR=$1
REPORT_FILE="$RESULT_DIR/report.md"

cat > "$REPORT_FILE" << 'HEADER'
# Sysbench 性能测试报告

## 测试环境

| 项目 | 详情 |
|------|------|
HEADER

echo "| 测试日期 | $(date '+%Y-%m-%d %H:%M:%S') |" >> "$REPORT_FILE"
echo "| 操作系统 | $(cat /etc/os-release | grep PRETTY_NAME | cut -d= -f2 | tr -d '"') |" >> "$REPORT_FILE"
echo "| 内核版本 | $(uname -r) |" >> "$REPORT_FILE"
echo "| CPU | $(lscpu | grep 'Model name' | cut -d: -f2 | xargs) |" >> "$REPORT_FILE"
echo "| CPU 核心 | $(nproc) |" >> "$REPORT_FILE"
echo "| 内存 | $(free -h | grep Mem | awk '{print $2}') |" >> "$REPORT_FILE"
echo "| Sysbench 版本 | $(sysbench --version 2>&1 | head -1) |" >> "$REPORT_FILE"

cat >> "$REPORT_FILE" << 'TABLE_HEADER'

## 测试结果

| 测试项 | TPS | QPS | 平均延迟(ms) | P95延迟(ms) | P99延迟(ms) |
|--------|-----|-----|-------------|------------|------------|
TABLE_HEADER

for json_file in "$RESULT_DIR"/*.json; do
  [ -f "$json_file" ] || continue
  
  test_name=$(basename "$json_file" .json)
  tps=$(jq '.transactions.per_second // 0' "$json_file")
  qps=$(jq '.queries.per_second // 0' "$json_file")
  avg=$(jq '.latency.avg // 0' "$json_file")
  p95=$(jq '.latency.percentile_95 // 0' "$json_file")
  p99=$(jq '.latency.percentile_99 // 0' "$json_file")
  
  echo "| $test_name | $tps | $qps | $avg | $p95 | $p99 |" >> "$REPORT_FILE"
done

cat >> "$REPORT_FILE" << 'FOOTER'

## 结论

1. （在此填写测试结论）
2. （在此填写性能建议）
3. （在此填写后续优化方向）

## 附录

### 测试命令

```bash
# CPU 测试
sysbench cpu --threads=8 --time=60 run

# OLTP 测试
sysbench oltp_read_write --tables=16 --table-size=1000000 --threads=16 --time=300 run

#!/bin/bash
# establish_baseline.sh - 建立性能基线

BASELINE_DIR="./baseline/$(date +%Y%m%d)"
mkdir -p "$BASELINE_DIR"

echo "=== 建立性能基线 ==="

# 记录环境信息
cat > "$BASELINE_DIR/env.txt" << EOF
日期: $(date)
主机: $(hostname)
内核: $(uname -r)
CPU: $(lscpu | grep 'Model name' | cut -d: -f2 | xargs)
内存: $(free -h | grep Mem | awk '{print $2}')
EOF

# 运行基线测试
for i in 1 2 3; do
  echo "--- 第 $i 次运行 ---"
  sysbench cpu --threads=$(nproc) --time=60 \
    --json="$BASELINE_DIR/cpu_run${i}.json" run
  
  sysbench memory --memory-block-size=1M --threads=$(nproc) --time=60 \
    --json="$BASELINE_DIR/mem_run${i}.json" run
done

# 计算平均值
echo ""
echo "=== 基线结果（3 次平均） ==="
cpu_avg=$(jq -s 'map(.cpu_speed.events_per_second) | add / length' "$BASELINE_DIR"/cpu_run*.json)
echo "CPU events/sec: $cpu_avg"

mem_avg=$(jq -s 'map(.total_number_of_events) | add / length' "$BASELINE_DIR"/mem_run*.json)
echo "Memory operations: $mem_avg"

# 保存基线
echo "$cpu_avg" > "$BASELINE_DIR/cpu_baseline.txt"
echo "$mem_avg" > "$BASELINE_DIR/mem_baseline.txt"

#!/bin/bash
# compare_with_baseline.sh - 与基线对比

BASELINE_DIR=$1
CURRENT_RESULT=$2
THRESHOLD=${3:-5}  # 默认阈值 5%

# 对比 CPU
cpu_baseline=$(cat "$BASELINE_DIR/cpu_baseline.txt")
cpu_current=$(jq '.cpu_speed.events_per_second' "$CURRENT_RESULT/cpu_run1.json")
cpu_diff=$(echo "scale=2; ($cpu_current - $cpu_baseline) / $cpu_baseline * 100" | bc)

echo "=== CPU 性能对比 ==="
echo "基线: $cpu_baseline"
echo "当前: $cpu_current"
echo "变化: ${cpu_diff}%"

if (( $(echo "$cpu_diff < -$THRESHOLD" | bc -l) )); then
  echo "⚠️  警告: CPU 性能下降超过 ${THRESHOLD}%！"
fi

#!/bin/bash
# periodic_benchmark.sh - 定期性能测试

LOG_DIR="./benchmark_logs"
mkdir -p "$LOG_DIR"

LOG_FILE="$LOG_DIR/benchmark_$(date +%Y%m%d_%H%M%S).log"

{
  echo "=== 性能测试开始: $(date) ==="
  
  sysbench cpu --threads=$(nproc) --time=60 run 2>&1
  echo ""
  
  sysbench memory --memory-block-size=1M --threads=$(nproc) --time=60 run 2>&1
  echo ""
  
  if [ -n "$MYSQL_HOST" ]; then
    sysbench oltp_read_write \
      --mysql-host=$MYSQL_HOST --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASS \
      --tables=16 --table-size=1000000 --threads=16 --time=300 run 2>&1
  fi
  
  echo "=== 性能测试结束: $(date) ==="
} | tee "$LOG_FILE"

# 编辑 crontab
# crontab -e

# 每天凌晨 3 点运行性能测试
0 3 * * * /opt/scripts/periodic_benchmark.sh >> /var/log/benchmark.log 2>&1

# 每周一生成报告
0 4 * * 1 /opt/scripts/generate_report.sh /opt/results >> /var/log/benchmark_report.log 2>&1