# 内存管理
vm.swfgappiness = 1
vm.overcommit_memory = 2
vm.overcommit_ratio = 80

# 2. 文件系统
$ vi /etc/fstab

# 添加noatime和nodiratime选项
/dev/sdb1 /postgresql xfs defaults,noatime,nodiratime 0 0

# 3. 网络优化
$ vi /etc/sysctl.conf

# 网络
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 15

# 4. 系统限制
$ vi /etc/security/limits.conf

# 增加文件描述符限制
postgres soft nofile 65536
postgres hard nofile 65536

# 5. 应用sysctl配置
$ sysctl -p

# 2. 优化存储参数
$ vi /postgresql/fgdata/postgresql.conf

# WAL存储
wal_buffers = 16MB

# 检查点
checkpoint_completion_target = 0.9
max_wal_size = 1GB
min_wal_size = 80MB

# 3. 表空间配置
$ psql -U postgres

postgres=# CREATE TABLESPACE fgedutbs LOCATION ‘/postgresql/fgdata/tbs’;
CREATE TABLESPACE

# 4. 数据文件布局
– 将数据文件和WAL文件放在不同的存储设备上
– 将索引和数据放在不同的表空间

# 共享内存
shared_buffers = 4GB # 物理内存的25%

# 工作内存
work_mem = 32MB # 根据并发数和查询复杂度调整

# 维护工作内存
maintenance_work_mem = 1GB # 用于VACUUM等操作

# 有效缓存大小
effective_cache_size = 12GB # 物理内存的75%

# 2. 查询优化
random_page_cost = 1.1 # SSD存储时设置为1.1
seq_page_cost = 1.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025

# 3. 并发参数
max_connections = 100 # 根据服务器资源和应用需求调整

# 4. WAL参数
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

# 5. 自动清理
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 10min
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

fgedudb=> SELECT
schemaname,
relname,
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_index ON pg_index.indexrelid = pg_stat_user_indexes.indexrelid
WHERE NOT indisunique
ORDER BY idx_scan DESC;

# 2. 创建合适的索引
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=> CREATE INDEX idx_fgedu_orders_customer_status ON fgedu_orders (customer_id, status);
CREATE INDEX

# 4. 部分索引
fgedudb=> CREATE INDEX idx_fgedu_orders_pending ON fgedu_orders (customer_id) WHERE status = ‘pending’;
CREATE INDEX

# 5. 表达式索引
fgedudb=> CREATE INDEX idx_fgedu_users_email_lower ON fgedu_users (lower(email));
CREATE INDEX

fgedudb=> SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

# 2. 分析执行计划
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE customer_id = 1000;

# 3. 优化SQL语句
# 原查询
fgedudb=> SELECT * FROM fgedu_orders WHERE customer_id = 1000;

# 优化后查询（只选择需要的列）
fgedudb=> SELECT id, order_date, amount FROM fgedu_orders WHERE customer_id = 1000;

# 4. 避免全表扫描
# 确保WHERE条件中的列有索引
fgedudb=> CREATE INDEX idx_fgedu_orders_customer_id ON fgedu_orders (customer_id);

# 5. 避免排序操作
# 使用索引避免排序
fgedudb=> CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders (order_date);

# 6. 优化JOIN操作
# 使用合适的JOIN类型
fgedudb=> EXPLAIN ANALYZE SELECT
u.name,
o.order_date,
o.amount
FROM fgedu_users u
JOIN fgedu_orders o ON u.id = o.customer_id
WHERE u.name = ‘User 1’;

# 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 = 20
reserve_pool_size = 10
reserve_pool_timeout = 5.0

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

“fgedu” “md5hash”

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

# 5. 连接测试
$ psql -h fgedu.localhost -p 6432 -U fgedu -d fgedudb