## 背景
– 业务需求：高并发Web应用，需要处理大量数据库连接
– 并发连接数：峰值1000个连接
– 数据库服务器：8核16GB内存

## 实施过程

### 1. 安装PgBouncer
$ yum install pgbouncer

### 2. 配置PgBouncer
$ vi /etc/pgbouncer/pgbouncer.ini

[databases]
fgedudb = host=fgedu.localhost port=5432 dbname=fgedudb

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
reserve_pool_size = 20
reserve_pool_timeout = 5.0
server_idle_timeout = 60
server_connect_timeout = 15
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

### 3. 创建用户列表
$ vi /etc/pgbouncer/userlist.txt

“fgedu” “md5hash”

### 4. 启动PgBouncer
$ systemctl start pgbouncer
$ systemctl enable pgbouncer

### 5. 应用配置
– 修改应用连接字符串，连接到PgBouncer端口6432
– 保持应用连接池配置，与PgBouncer配合使用

### 6. 性能测试
– 使用ab工具测试并发连接
– 监控系统资源使用情况
– 对比使用PgBouncer前后的性能差异

## 实施效果
– 连接数：从1000个实际连接减少到70个
– 响应时间：从500ms减少到100ms
– 系统负载：CPU使用率从80%降低到40%
– 稳定性：系统运行更加稳定，无连接超时现象

## 背景
– 业务需求：电商系统，需要高可用和负载均衡
– 数据库集群：1主2从
– 读请求比例：80%读，20%写

## 实施过程

### 1. 安装PgPool-II
$ yum install pgpool-II-18

### 2. 配置PgPool-II
$ vi /etc/pgpool-II/pgpool.conf

# 基本配置
listen_addresses = ‘*’
port = 9999

# 后端服务器配置
backend_hostname0 = ‘192.168.1.10’ # 主库
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ‘/postgresql/fgdata’
backend_flag0 = ‘ALLOW_TO_FAILOVER’

backend_hostname1 = ‘192.168.1.11’ # 从库1
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/postgresql/fgdata’
backend_flag1 = ‘ALLOW_TO_FAILOVER’

backend_hostname2 = ‘192.168.1.12’ # 从库2
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = ‘/postgresql/fgdata’
backend_flag2 = ‘ALLOW_TO_FAILOVER’

# 负载均衡配置
load_balance_mode = on

# 健康检查配置
health_check_period = 10
health_check_timeout = 2
health_check_user = ‘postgres’
health_check_password = ‘postgres_pass’

# 故障切换配置
failover_command = ‘/etc/pgpool-II/failover.sh %d %h %p %D %m %M %H %P %r %R’

### 3. 配置故障切换脚本
$ vi /etc/pgpool-II/failover.sh

#!/bin/bash
# Failover script for PgPool-II

# 获取参数
failed_node_id=$1
failed_host=$2
failed_port=$3
failed_db=$4
master_host=$5
master_port=$6
new_master_host=$7
old_master_port=$8
recovery_node=$9
recovery_type=${10}

# 执行故障切换逻辑
echo “Failover triggered for node $failed_node_id ($failed_host:$failed_port)” >> /var/log/pgpool/failover.log

# 4. 启动PgPool-II
$ systemctl start pgpool-II
$ systemctl enable pgpool-II

# 5. 应用配置
– 修改应用连接字符串，连接到PgPool-II端口9999
– 无需修改应用代码，透明使用负载均衡

# 6. 性能测试
– 模拟高并发读请求
– 监控各节点负载情况
– 测试故障切换功能

## 实施效果
– 负载均衡：读请求均匀分布到从库
– 高可用：主库故障时自动切换到从库
– 性能提升：读性能提高200%
– 稳定性：系统运行更加稳定

## 背景
– 业务需求：金融系统，需要高可用和负载均衡
– 数据库集群：2主2从（双主架构）
– 可用性要求：99.99%

## 实施过程

### 1. 安装HAProxy
$ yum install haproxy

### 2. 配置HAProxy
$ vi /etc/haproxy/haproxy.cfg

global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon

defaults
mode tcp
log global
option tcplog
option dontlognull
option redispatch
retries 3
timeout connect 10s
timeout client 1m
timeout server 1m
maxconn 3000

frontend postgresql
bind *:5000
default_backend postgresql_servers

backend postgresql_servers
balance roundrobin
server postgres1 192.168.1.10:5432 check
server postgres2 192.168.1.11:5432 check
server postgres3 192.168.1.12:5432 check backup
server postgres4 192.168.1.13:5432 check backup

# 3. 启动HAProxy
$ systemctl start haproxy
$ systemctl enable haproxy

# 4. 配置Keepalived（可选，实现HAProxy高可用）
$ yum install keepalived
$ vi /etc/keepalived/keepalived.conf

vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.100
}
}

# 5. 应用配置
– 修改应用连接字符串，连接到HAProxy虚拟IP和端口5000
– 无需修改应用代码，透明使用高可用服务

# 6. 故障测试
– 模拟主库故障，验证HAProxy自动切换
– 模拟HAProxy故障，验证Keepalived自动切换
– 测试系统恢复能力

## 实施效果
– 高可用：系统可用性达到99.99%
– 负载均衡：请求均匀分布到各节点
– 故障切换：自动切换时间小于10秒
– 稳定性：系统运行更加稳定，无单点故障