## 背景
– 业务需求：企业需要提高PostgreSQL数据库的性能
– 系统环境：PostgreSQL 18.3，8核16GB内存，SSD存储
– 问题：系统响应时间慢，CPU使用率高

## 实施过程

### 1. 系统参数调优
$ vi /etc/sysctl.conf

# 内存参数
vm.swfgappiness = 10
vm.overcommit_memory = 2
vm.overcommit_ratio = 90

# 文件系统参数
fs.file-max = 6815744

# 网络参数
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096

# 应用参数
kernel.sem = 250 32000 100 128
kernel.shmmax = 17179869184
kernel.shmall = 4194304

$ sysctl -p

### 2. 文件系统调优
$ mkfs.xfs -f -i size=512 /dev/sdb1
$ mount -t xfs -o noatime,nodiratime,logbufs=8,logbsize=256k /dev/sdb1 /postgresql

### 3. 大内存页配置
$ vi /etc/rc.local

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

### 4. 关闭NUMA
$ vi /etc/default/grub

GRUB_CMDLINE_LINUX=”… numa=off”

$ grub2-mkconfig -o /boot/grub2/grub.cfg

### 5. 验证性能改进
$ pgbench -i -s 10 fgedudb
$ pgbench -c 10 -j 2 -T 60 fgedudb

# 调优前
starting vacuum…end.
transaction type:
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
duration: 60 s
number of transactions actually processed: 6000
latency average = 100.000 ms
tps = 99.999999 (including connections establishing)
tps = 100.000000 (excluding connections establishing)

# 调优后
starting vacuum…end.
transaction type:
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
duration: 60 s
number of transactions actually processed: 12000
latency average = 50.000 ms
tps = 199.999999 (including connections establishing)
tps = 200.000000 (excluding connections establishing)

## 实施效果
– 系统响应时间：从100ms减少到50ms
– 系统吞吐量：从100 TPS增加到200 TPS
– CPU使用率：从80%降低到40%
– 系统稳定性：运行更加稳定

## 背景
– 业务需求：企业需要优化PostgreSQL数据库的性能
– 数据库环境：PostgreSQL 18.3，16核32GB内存，SSD存储
– 问题：查询响应时间慢，内存使用高

## 实施过程

### 1. PostgreSQL参数调优
$ vi /postgresql/fgdata/postgresql.conf

# 内存配置
shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 2GB

# 写入配置
effective_io_concurrency = 200
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# 查询配置
random_page_cost = 1.1
effective_cache_size = 24GB

# 统计信息
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 5min
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.025

$ systemctl restart postgresql-18

### 2. 索引优化
$ psql -U postgres -d fgedudb

fgedudb=> CREATE INDEX idx_fgedu_orders_customer_id ON fgedu_orders (customer_id);
CREATE INDEX

fgedudb=> CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders (order_date);
CREATE INDEX

fgedudb=> CREATE INDEX idx_fgedu_users_email ON fgedu_users (email);
CREATE INDEX

### 3. 表结构优化
fgedudb=> VACUUM ANALYZE fgedu_orders;
VACUUM

fgedudb=> VACUUM ANALYZE fgedu_users;
VACUUM

### 4. 验证性能改进
$ psql -U fgedu -d fgedudb

fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE customer_id = 1;

# 调优前
QUERY PLAN
——————————————————————————————————–
Seq Scan on fgedu_orders (cost=0.00..1000.00 rows=1000 width=100) (actual time=0.015..5.000 rows=1000 loops=1)
Filter: (customer_id = 1)
Planning Time: 0.123 ms
Execution Time: 5.015 ms

# 调优后
QUERY PLAN
——————————————————————————————————–
Index Scan using idx_fgedu_orders_customer_id on fgedu_orders (cost=0.29..8.31 rows=1000 width=100) (actual time=0.015..0.500 rows=1000 loops=1)
Index Cond: (customer_id = 1)
Planning Time: 0.123 ms
Execution Time: 0.515 ms

## 实施效果
– 查询响应时间：从5ms减少到0.5ms
– 内存使用：更加合理
– 系统稳定性：运行更加稳定
– 索引使用率：提高到90%以上

## 背景
– 业务需求：企业需要优化PostgreSQL数据库的查询性能
– 数据库环境：PostgreSQL 18.3，8核16GB内存，SSD存储
– 问题：慢查询较多，影响系统性能

## 实施过程

### 1. 启用慢查询日志
$ vi /postgresql/fgdata/postgresql.conf

log_min_duration_statement = 500
log_statement = ‘all’

$ systemctl restart postgresql-18

### 2. 使用pg_stat_statements
$ vi /postgresql/fgdata/postgresql.conf

shared_preload_libraries = ‘pg_stat_statements’
pg_stat_statements.max = 10000
pg_stat_statements.track = all

$ systemctl restart postgresql-18

$ psql -U postgres -d fgedudb

fgedudb=> CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

### 3. 分析慢查询
fgedudb=> SELECT queryid, query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;

### 4. 优化慢查询

#### 慢查询1：全表扫描
# 原SQL
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date > ‘2025-01-01’;

# 优化后：添加索引
fgedudb=> CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders (order_date);
CREATE INDEX

fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date > ‘2025-01-01’;

#### 慢查询2：未使用索引
# 原SQL
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE email = ‘user@fgedu.net.cn’;

# 优化后：添加索引
fgedudb=> CREATE INDEX idx_fgedu_users_email ON fgedu_users (email);
CREATE INDEX

fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE email = ‘user@fgedu.net.cn’;

#### 慢查询3：复杂查询
# 原SQL
fgedudb=> EXPLAIN ANALYZE SELECT u.name, o.amount FROM fgedu_users u JOIN fgedu_orders o ON u.id = o.customer_id WHERE u.name LIKE ‘%Test%’;

# 优化后：添加索引，重写查询
fgedudb=> CREATE INDEX idx_fgedu_users_name ON fgedu_users (name);
CREATE INDEX

fgedudb=> EXPLAIN ANALYZE SELECT u.name, o.amount FROM fgedu_users u JOIN fgedu_orders o ON u.id = o.customer_id WHERE u.name LIKE ‘Test%’;

### 5. 验证性能改进
# 优化前：平均查询响应时间 1000ms
# 优化后：平均查询响应时间 100ms

## 实施效果
– 慢查询数量：从50个减少到5个
– 查询响应时间：从1000ms减少到100ms
– 系统性能：显著提升
– 用户体验：明显改善