没有合适的资源?快使用搜索试试~ 我知道了~
首页PostgreSQL性能优化最佳综合案例实践
PostgreSQL性能优化最佳综合案例实践
5星 · 超过95%的资源 需积分: 50 107 下载量 100 浏览量
更新于2023-03-16
评论 2
收藏 853KB PDF 举报
介绍了建立了一个包含INSERT, UPDATE, SELECT数据库请求的简单的业务模型, 并使用pgbench模拟业务请求, 详细的阐述PostgreSQL数据库的调优全过程。
资源详情
资源评论
资源推荐
美河学习在线 www.eimhe.com
PostgreSQL 性能优化最佳综合案例实践
Digoal.Zhou
【前言】
本文建立了一个包含 INSERT, UPDATE, SELECT 数据库请求的简单的业务模型, 并使用 pgbench 模拟业务请求,
详细的阐述 PostgreSQL 数据库的调优过程.
【正文】
【软件环境】
CentOS 5 x64
PostgreSQL 9.1.3
plproxy 2.3
pgbouncer 1.4.2
【测试模型】
设计一个包含 INSERT, UPDATE, SELECT 语句的业务模型用于本优化案例.
业务逻辑 :
美河学习在线 www.eimhe.com
【测试表】
create table user_info
(userid int,
engname text,
cnname text,
occupation text,
birthday date,
signname text,
email text,
qq numeric,
crt_time timestamp without time zone,
mod_time timestamp without time zone
);
create table user_session
(userid int,
logintime timestamp(0) without time zone,
login_count bigint default 0,
美河学习在线 www.eimhe.com
logouttime timestamp(0) without time zone,
online_interval interval default interval '0'
);
create table user_login_rec
(userid int,
login_time timestamp without time zone,
ip inet
);
create table user_logout_rec
(userid int,
logout_time timestamp without time zone,
ip inet
);
【初始化数据】
insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)
select generate_series(1,20000000),
'digoal.zhou',
'德哥',
'DBA',
'1970-01-01'
,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
'digoal@126.com',
276732431,
clock_timestamp(),
NULL;
insert into user_session (userid) select generate_series(1,20000000);
set work_mem='2048MB';
set maintenance_work_mem='2048MB';
alter table user_info add constraint pk_user_info primary key (userid);
alter table user_session add constraint pk_user_session primary key (userid);
【业务函数】
-- 模拟用户登录的函数
create or replace function f_user_login
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
美河学习在线 www.eimhe.com
OUT o_birthday date,
OUT o_signname text,
OUT o_email text,
OUT o_qq numeric
)
as $BODY$
declare
begin
select userid,engname,cnname,occupation,birthday,signname,email,qq
into o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qq
from user_info where userid=i_userid;
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session set logintime=now(),login_count=login_count+1 where userid=i_userid;
return;
end;
$BODY$
language plpgsql;
-- 模拟用户退出的函数
create or replace function f_user_logout
(i_userid int,
OUT o_result int
)
as $BODY$
declare
begin
insert into user_logout_rec (userid,logout_time,ip) values (i_userid,now(),inet_client_addr());
update user_session set logouttime=now(),online_interval=online_interval+(now()-logintime) where
userid=i_userid;
o_result := 0;
return;
exception
when others then
o_result := 1;
return;
end;
$BODY$
language plpgsql;
【搭建测试模型】
1. 搭建环境, 安装 PostgreSQL9.1.3 数据库软件并初始化数据库(略).
2. 调整数据库 postgresql.conf 参数. 打开日志, SQL 统计, 跟踪, 以及性能参数, 便于优化过程中取证.
监听 IPv4 的所有 IP.
listen_addresses = '0.0.0.0'
美河学习在线 www.eimhe.com
最大允许 1000 个连接.
max_connections = 1000
为超级用户保留 3 个可用连接.
superuser_reserved_connections = 3
默认的 unix socket 文件放在/tmp, 修改为$PGDATA, 以确保安全.
unix_socket_directory = '/pgdata/digoal/1921/data02/pg_root'
默认的访问权限是 0777, 修改为 0700 更安全.
unix_socket_permissions = 0700
空闲多少秒后,给 idle 的连接的客户端发送 tcp 的 keepalives 包, Linux 下面默认是 2 小时.
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 6
大的 shared_buffers 需要大的 checkpoint_segments,同时需要申请更多的 System V 共享内存资源.
这个值不需要设的太大, 因为 PostgreSQL 还依赖操作系统的 cache 来提高读性能, 另外, 写操作频繁的数据库这个设太
大反而会增加 checkpoint 压力.
shared_buffers = 512MB
这个值越大, VACUUM, CREATE INDEX 的操作越快, 当然大到一定程度瓶颈就不在内存了, 可能是 CPU 例如创建索引.
这个值是一个操作的内存使用上限, 而不是一次性分配出去的. 并且需要注意如果开启了 autovacuum, 最大可能有
autovacuum_max_workers*maintenance_work_mem 的内存被系统消耗掉.
maintenance_work_mem = 512MB
一般设置为比系统限制的略少,ulimit -a : stack size (kbytes, -s) 10240
max_stack_depth = 8MB
手动执行 vacuum 操作时, 默认是没有停顿执行到底的, 为了防止 VACUUM 操作消耗太多数据库服务器硬件资源, 这个值
是指 vacuum 在消耗多少资源后停顿多少时间,以便其他的操作可以使用更多的硬件资源.
vacuum_cost_delay = 10ms
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
默认 bgwriter 进程执行一次后会停顿 200ms 再被唤醒执行下一次操作, 当数据库的写操作很频繁的时候, 200ms 可能太
长, 导致其他进程需要花费过多的时间来进行 bgwriter 的操作.
bgwriter_delay = 10ms
如果需要做数据库 WAL 日志备份的话至少需要设置成 archive 级别, 如果需要做 hot_standby 那么需要设置成
hot_standby, 由于这个值修改需要重启数据库, 所以先设置成 hot_standby 比较好. 当然 hot_standby 意味着 WAL 记录
得更详细, 如果没有打算做 hot_standby 设置得越低性能越好.
剩余52页未读,继续阅读
machen_smiling
- 粉丝: 506
- 资源: 1958
上传资源 快速赚钱
- 我的内容管理 收起
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
会员权益专享
最新资源
- c++校园超市商品信息管理系统课程设计说明书(含源代码) (2).pdf
- 建筑供配电系统相关课件.pptx
- 企业管理规章制度及管理模式.doc
- vb打开摄像头.doc
- 云计算-可信计算中认证协议改进方案.pdf
- [详细完整版]单片机编程4.ppt
- c语言常用算法.pdf
- c++经典程序代码大全.pdf
- 单片机数字时钟资料.doc
- 11项目管理前沿1.0.pptx
- 基于ssm的“魅力”繁峙宣传网站的设计与实现论文.doc
- 智慧交通综合解决方案.pptx
- 建筑防潮设计-PowerPointPresentati.pptx
- SPC统计过程控制程序.pptx
- SPC统计方法基础知识.pptx
- MW全能培训汽轮机调节保安系统PPT教学课件.pptx
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
评论1