覆盖数据库“全栈”！被用于大学实验课
digoal约 66 分钟文中参考文档在github需点击阅读原文打开, 同时推荐2个学习环境:
1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》
2、有web浏览器就能用的云起实验室: 《免费体验PolarDB开源数据库》
3、PolarDB开源数据库内核、最佳实践等学习图谱: https://www.aliyun.com/database/openpolardb/activity
关注公众号, 持续发布PostgreSQL、PolarDB、DuckDB等相关文章.
插播广告1：2024年第13届PostgreSQL中国技术大会来啦！！！
十几年前写的文章, 想不到被用到大学数据库实验课了
最近去大学交流, 老师把我拉到一旁随即掏出了一本秘籍：《数据库实验手册》, 让我给学生们讲讲, 当时有点懵为什么要我来给学生讲, 原来手册借鉴了我十几年前写的《PostgreSQL性能优化综合案例讲解》, 学生在实验课上做了里面的实验, 想让我说明一下为什么要这么设计? 让学生带着思考去进行实验效果更好。
交流点评：
这个优化实验的业务场景很简单, 用户登陆和用户退出, 查询用户信息, 记录用户行为, 更新用户当前的会话状态. 整个优化涉及到十几个步骤, 覆盖到了数据库的大部分模块, 老师挑选这个实验给同学们做真的用心良苦.
这个实验实操性强，很容易完成，并且涉及较多模块/知识点，易于和课程内容结合引发思考:
网络交互
sql parser
sql rewrite/优化
协议/simple query|绑定变量
IO
buffer pool/page cache
锁管理
wal 持久化行为和提交风格
cbo: 索引/全表扫描 等
索引结构和索引深度
- 自定义函数
- 进程模型和连接池
业务或架构侧行为: 读写分离
业务或架构侧行为: 拆表
业务或架构侧行为: 表空间/存储/IO均分/lvm
业务或架构侧行为: 拆库
建议还可以给同学们参考一下PostgreSQL flowchat : https://www.postgresql.org/developer/backend/
以上方法是了解数据库原理, 把数据库当成白盒后的性能优化思路, 那如果是个黑盒呢? 可以参考如下方法:
《MacOS下抓取火焰图和分析程序性能的方法(sample, FlameGraph) - Linux Perf》
《Linux ftrace - 内核性能分析 - 火焰图》
《PostgreSQL explain analyze 火山图火焰图 图形化性能分析软件 pg_flame》
《PostgreSQL 源码性能诊断(perf profiling)指南(含火焰图生成分析FlameGraph) - 珍藏级》
以下是实验原文，有兴趣的同学可以实验一下。
【软件环境】
CentOS 5 x64
PostgreSQL 9.1.3
plproxy 2.3
pgbouncer 1.4.2
【测试模型】
设计一个包含INSERT, UPDATE, SELECT语句的业务模型用于本优化案例.
业务逻辑 :
【测试表】
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,
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!',
'[email protected]',
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,
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' 最大允许1000个连接.
max_connections = 1000
为超级用户保留3个可用连接.
superuser_reserved_connections = 3
默认的unix socket文件放在/tmp, 修改为$PGDATA, 以确保安全.
unix_socket_directory = '.'
默认的访问权限是0777, 修改为0700更安全.
unix_socket_permissions = 0700
Linux下面默认是2小时.tcp的keepalives包发送间隔以及重试次数, 如果客户端没有响应, 将主动释放对应的SOCKET.
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设置得越低性能越好.
wal_level = hot_standby
wal buffers默认是-1 根据shared_buffers的设置自动调整shared_buffers*3% .最大限制是XLOG的segment_size.
wal_buffers = 32MB
多少个xlog file产生后开始checkpoint操作, 这个值越大, 允许shared_buffer中的被频繁访问的脏数据存储得更久. 一定程度上可以提高数据库性能. 但是太大的话会导致在数据库发生checkpoint的时候需要处理更多的脏数据带来长时间的IO开销. 太小的话会导致产生更多的WAL文件(因为full page writes=on,CHECKPOINT后的第一次块的改变要写全块, checkpoint越频繁, 越多的数据更新要写全块导致产生更多WAL).
checkpoint_segments = 64
这个和checkpoint_segments的效果是一样的, 只是触发的条件是时间条件.
checkpoint_timeout = 5min
归档参数的修改也需要重启数据库, 所以就先打开吧.
archive_mode = on
这个是归档调用的命令, 我这里用date代替, 所以归档的时候调用的是输出时间而不是拷贝wal文件.
archive_command = '/bin/date'
如果要做hot standby这个必须大于0, 并且修改之后要重启数据库所以先设置为32.
max_wal_senders = 32
这是个standby 数据库参数, 为了方便角色切换, 我一般是所有的数据库都把他设置为on 的.
hot_standby = on
这个参数是说数据库中随机的PAGE访问的开销占seq_page_cost的多少倍 , seq_page_cost默认是1. 其他的开销都是seq_page_cost的倍数. 这些都用于基于成本的执行计划选择.
random_page_cost = 2.0
和上一个参数一样, 用于基于成本的执行计划选择. 不是说会用多少cache, 它只是个度量值. 表示系统有多少内存可以作为操作系统的cache. 越大的话, 数据库越倾向使用index这种适合random访问的执行计划.
effective_cache_size = 12000MB
下面是日志输出的配置.
log_destination = 'csvlog'
logging_collector = on
log_directory = '/var/applog/pg_log/digoal/1921'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
这个参数调整的是记录执行时间超过1秒的SQL到日志中, 一般用于跟踪哪些SQL执行时间长.
log_min_duration_statement = 1000ms
记录每一次checkpoint到日志中.
log_checkpoints = on
记录锁等待超过1秒的操作, 一般用于排查业务逻辑上的问题.
log_lock_waits = on
deadlock_timeout = 1s
记录DDL语句, 一般用于跟踪数据库中的危险操作.
log_statement = 'ddl'
这个原本是1024表示跟踪的SQL在1024的地方截断, 超过1024将无法显示全SQL. 修改为2048会消耗更多的内存(基本可以忽略), 不过可以显示更长的SQL.
track_activity_query_size = 2048
默认autovacuum就是打开的, log_autovacuum_min_duration = 0记录所有的autovacuum操作.
autovacuum = on
log_autovacuum_min_duration = 0
这个模块用于记录数据库中的最近的1000条SQL以及这些SQL的统计信息, 如执行了多少次, 总共耗时是多少. 一般用于发现业务上最频繁调用的SQL是什么, 有针对性的进行SQL优化.
shared_preload_libraries = 'pg_stat_statements'
custom_variable_classes = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all
其他参数值默认.
这些参数的详细解释如有疑问请参考PostgreSQL官方文档.
3. 新建数据库用户digoal, 库digoal. 并使用前面的测试模型新建表以及函数, 初始化数据.
下面的测试过程中只测登陆部分, 未测试退出部分, 因为登陆过程已经包含了INSERT, UPDATE, SELECT. 基本上可以反映整个调优过程了.
【调优阶段1】
使用pgbench进行压力测试, 发现瓶颈并合理优化.
1. pgbench用到的登陆脚本
cat login.sql
\setrandom userid 1 20000000
select userid,engname,cnname,occupation,birthday,signname,email,qq from user_info where userid=:userid;
insert into user_login_rec (userid,login_time,ip) values (:userid,now(),inet_client_addr());
update user_session set logintime=now(),login_count=login_count+1 where userid=:userid;
2. pgbench用到的退出脚本
cat logout.sql
\setrandom userid 1 20000000
insert into user_logout_rec (userid,logout_time,ip) values (:userid,now(),inet_client_addr());
update user_session set logouttime=now(),online_interval=online_interval+(now()-logintime) where userid=:userid;
3. 压力测试
pgbench -M simple -r -c 8 -f /home/postgres/test/login.sql -j 8 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal
4. 压力测试结果
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
duration: 180 s
number of transactions actually processed: 62675
tps = 348.084647 (including connections establishing)
tps = 348.100337 (excluding connections establishing)
statement latencies in milliseconds:
0.004577 \setrandom userid 1 20000000
12.963789 select userid,engname,cnname,occupation,birthday,signname,email,qq from user_info where userid=:userid;
5.540750 insert into user_login_rec (userid,login_time,ip) values (:userid,now(),inet_client_addr());
4.457834 update user_session set logintime=now(),login_count=login_count+1 where userid=:userid;
5. 瓶颈分析与优化
压力测试中查看数据库服务器的iostat -x
avg-cpu: %user %nice %system %iowait %steal %idle
0.69 0.00 0.25 24.11 0.00 74.95 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 6.00 0.00 1.50 0.00 60.00 40.00 0.01 6.67 6.67 1.00
cciss/c0d0p1 0.00 6.00 0.00 1.50 0.00 60.00 40.00 0.01 6.67 6.67 1.00
cciss/c0d0p2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d2 0.00 638.50 10.00 217.50 160.00 6444.00 29.03 152.58 707.89 4.40 100.10
cciss/c0d3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 10.00 866.50 160.00 6932.00 8.09 446.26 510.49 1.14 100.10
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
操作系统的平均IO请求等待700多毫秒, PostgreSQL数据文件所处的块设备使用率100%. 存在严重的IO性能瓶颈.
使用pgfincore降低读的物理IO请求.
pgfincore的相关文章可参考如下,
《use posix_fadvise pre-cache frequency data》
http://blog.163.com/digoal@126/blog/static/163877040201062944945126/
《a powerful upgrade from pgfincore 1.0》
http://blog.163.com/digoal@126/blog/static/1638770402011630102117658/
《TOAST table with pgfincore》
http://blog.163.com/digoal@126/blog/static/16387704020120524144140/
pgfincore所起的作用类似EnterpriseDB的InfiniteCache或者熟悉Oracle的朋友可能更易于接受的KEEP BUFFER POOL.
载入os cache
digoal=> select reltoastrelid from pg_class where relname='user_info';
reltoastrelid
---------------
16424
(1 row) digoal=> select relname from pg_class where oid=16424;
relname
----------------
pg_toast_16421
(1 row)
digoal=> \c digoal postgres
seYou are now connected to database "digoal" as user "postgres".
digoal=# select * from pgfadvise_willneed('pg_toast.pg_toast_16421');
relpath | os_page_size | rel_os_pages | os_pages_free
----------------------------------------------+--------------+--------------+---------------
pg_tblspc/16385/PG_9.1_201105231/16386/16424 | 4096 | 0 | 243865
(1 row)
digoal=# select * from pgfadvise_willneed('digoal.user_info');
relpath | os_page_size | rel_os_pages | os_pages_free
------------------------------------------------+--------------+--------------+---------------
pg_tblspc/16385/PG_9.1_201105231/16386/16421 | 4096 | 262144 | 243834
pg_tblspc/16385/PG_9.1_201105231/16386/16421.1 | 4096 | 262144 | 243834
pg_tblspc/16385/PG_9.1_201105231/16386/16421.2 | 4096 | 244944 | 243834
(3 rows)
digoal=# select * from pgfadvise_willneed('digoal.user_session');
relpath | os_page_size | rel_os_pages | os_pages_free
------------------------------------------------+--------------+--------------+---------------
pg_tblspc/16385/PG_9.1_201105231/16386/16431 | 4096 | 262144 | 243834
pg_tblspc/16385/PG_9.1_201105231/16386/16431.1 | 4096 | 33640 | 243834
(2 rows)
digoal=# select reltoastrelid from pg_class where relname='user_session';
reltoastrelid
---------------
0
(1 row)
digoal=# select * from pgfadvise_willneed('digoal.pk_user_session');
relpath | os_page_size | rel_os_pages | os_pages_free
----------------------------------------------+--------------+--------------+---------------
pg_tblspc/16385/PG_9.1_201105231/16386/16438 | 4096 | 109680 | 243865
(1 row)
digoal=# select * from pgfadvise_willneed('digoal.pk_user_info');
relpath | os_page_size | rel_os_pages | os_pages_free
----------------------------------------------+--------------+--------------+---------------
pg_tblspc/16385/PG_9.1_201105231/16386/16436 | 4096 | 109680 | 235567
(1 row)
【调优阶段2】
1. 压力测试
pgbench -M simple -r -c 8 -f /home/postgres/test/login.sql -j 8 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal
2. 测试结果
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
duration: 180 s
number of transactions actually processed: 264895
tps = 1471.517096 (including connections establishing)
tps = 1471.585818 (excluding connections establishing)
statement latencies in milliseconds:
0.004226 \setrandom userid 1 20000000
0.459824 select userid,engname,cnname,occupation,birthday,signname,email,qq from user_info where userid=:userid;
2.457797 insert into user_login_rec (userid,login_time,ip) values (:userid,now(),inet_client_addr());
2.501684 update user_session set logintime=now(),login_count=login_count+1 where userid=:userid;
3. 瓶颈分析与优化
SELECT语句的延时已经通过上一个优化阶段下降到了0.45毫秒, INSERT和UPDATE语句的平均耗时也从原来的5.5和4.45下降到了2.5.原因是select的请求在内存中命中了, 因此update和insert不需要和select争抢物理io请求, 处理效率自然有一定的提高.
但是INSERT和UPDATE的语句延时还有2.5毫秒存在很大的可优化空间.
开启PostgreSQL的异步提交日志.
synchronous_commit = off
wal_writer_delay = 10ms
与Oracle的异步日志差别请参考 :
《PostgreSQL and Oracle's async commit》
http://blog.163.com/digoal@126/blog/static/16387704020121229223072/
【调优阶段3】
1. 压力测试
pgbench -M simple -r -c 8 -f /home/postgres/test/login.sql -j 8 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal
2. 测试结果
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
duration: 180 s
number of transactions actually processed: 685344
tps = 3751.377919 (including connections establishing)
tps = 3751.568948 (excluding connections establishing)
statement latencies in milliseconds:
0.003474 \setrandom userid 1 20000000
0.418716 select userid,engname,cnname,occupation,birthday,signname,email,qq from user_info where userid=:userid;
0.511601 insert into user_login_rec (userid,login_time,ip) values (:userid,now(),inet_client_addr());
1.188277 update user_session set logintime=now(),login_count=login_count+1 where userid=:userid;
3. 瓶颈分析与优化
客户端连接使用simple协议, 存在一定的可优化空间.
修改协议为extended, 查看性能提升多少.
【调优阶段4】
1. 压力测试
pgbench -M extended -r -c 8 -f /home/postgres/test/login.sql -j 8 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal
2. 测试结果
transaction type: Custom query
scaling factor: 1
query mode: extended
number of clients: 8
number of threads: 8
duration: 180 s
number of transactions actually processed: 970981
tps = 5394.015368 (including connections establishing)
tps = 5394.215477 (excluding connections establishing)
statement latencies in milliseconds:
0.003345 \setrandom userid 1 20000000
0.381675 select userid,engname,cnname,occupation,birthday,signname,email,qq from user_info where userid=:userid;
0.296300 insert into user_login_rec (userid,login_time,ip) values (:userid,now(),inet_client_addr());
0.792592 update user_session set logintime=now(),login_count=login_count+1 where userid=:userid;
3. 瓶颈分析与优化
客户端连接使用extended协议, 存在一定的可优化空间.
修改协议为prepared, 查看性能提升多少.
参见 :
《PostgreSQL prepared statement: SPI_prepare, prepare|execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》
http://blog.163.com/digoal@126/blog/static/1638770402012112452432251/
【调优阶段5】
1. 压力测试
pgbench -M prepared -r -c 8 -f /home/postgres/test/login.sql -j 8 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal
2. 测试结果
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 180 s
number of transactions actually processed: 1044186
tps = 5800.589330 (including connections establishing)
tps = 5800.902491 (excluding connections establishing)
statement latencies in milliseconds:
0.003465 \setrandom userid 1 20000000
0.319665 select userid,engname,cnname,occupation,birthday,signname,email,qq from user_info where userid=:userid;
0.266931 insert into user_login_rec (userid,login_time,ip) values (:userid,now(),inet_client_addr());
0.777822 update user_session set logintime=now(),login_count=login_count+1 where userid=:userid;
3. 瓶颈分析与优化
压力测试的脚本中使用的是普通的SQL语句, 未使用初始化时用到的登陆函数和退出函数. 使用普通SQL显然比使用函数多了交互的次数以及每次发送的数据包的大小.
使用函数看看性能能提升多少.
【调优阶段6】
1. 登陆脚本
cat login.sql
\setrandom userid 1 20000000
SELECT f_user_login(:userid);
2. 退出脚本
cat logout.sql
\setrandom userid 1 20000000
SELECT f_user_logout(:userid);
3. 压力测试
pgbench -M prepared -r -c 8 -f /home/postgres/test/login.sql -j 8 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal
4. 测试结果
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 180 s
number of transactions actually processed: 1616746
tps = 8981.596290 (including connections establishing)
tps = 8981.995800 (excluding connections establishing)
statement latencies in milliseconds:
0.004012 \setrandom userid 1 20000000
0.881060 SELECT f_user_login(:userid);
5. 瓶颈分析与优化
到这个时候看起来好像没什么好优化的了, 其实不然.
我们知道在整个登陆过程中用到了SELECT, UPDATE, INSERT.
其中UPDATE的表有一个PK索引, 每次更新需要修改数据表的同时还需要更新索引. 所以理论上这个更新操作表越小性能越高.
我们可以通过拆表来提升性能. 如下 :
拆表 :
create table user_info_0 (like user_info including all);
create table user_info_1 (like user_info including all);
create table user_info_2 (like user_info including all);
create table user_info_3 (like user_info including all);
create table user_info_4 (like user_info including all); create table user_session_0 (like user_session including all);
create table user_session_1 (like user_session including all);
create table user_session_2 (like user_session including all);
create table user_session_3 (like user_session including all);
create table user_session_4 (like user_session including all);
插入初始化数据 :
insert into user_info_0 (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)
select generate_series(1,4000000),
'digoal.zhou',
'德哥',
'DBA',
'1970-01-01'
,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
'[email protected]',
276732431,
clock_timestamp(),
NULL; insert into user_info_1 (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)
select generate_series(4000001,8000000),
'digoal.zhou',
'德哥',
'DBA',
'1970-01-01'
,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
'[email protected]',
276732431,
clock_timestamp(),
NULL;
insert into user_info_2 (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)
select generate_series(8000001,12000000),
'digoal.zhou',
'德哥',
'DBA',
'1970-01-01'
,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
'[email protected]',
276732431,
clock_timestamp(),
NULL;
insert into user_info_3 (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)
select generate_series(12000001,16000000),
'digoal.zhou',
'德哥',
'DBA',
'1970-01-01'
,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
'[email protected]',
276732431,
clock_timestamp(),
NULL;
insert into user_info_4 (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)
select generate_series(16000001,20000000),
'digoal.zhou',
'德哥',
'DBA',
'1970-01-01'
,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
'[email protected]',
276732431,
clock_timestamp(),
NULL;
insert into user_session_0 (userid) select generate_series(1,4000000);
insert into user_session_1 (userid) select generate_series(4000001,8000000);
insert into user_session_2 (userid) select generate_series(8000001,12000000);
insert into user_session_3 (userid) select generate_series(12000001,16000000);
insert into user_session_4 (userid) select generate_series(16000001,20000000);
set work_mem='2048MB';
set maintenance_work_mem='2048MB';
alter table user_info_0 add primary key (userid);
alter table user_info_1 add primary key (userid);
alter table user_info_2 add primary key (userid);
alter table user_info_3 add primary key (userid);
alter table user_info_4 add primary key (userid);
alter table user_session_0 add primary key (userid);
alter table user_session_1 add primary key (userid);
alter table user_session_2 add primary key (userid);
alter table user_session_3 add primary key (userid);
alter table user_session_4 add primary key (userid);
同样通过pgfincore把他们加载到内存中, 这里不详细描述.
新建登陆和退出函数
create or replace function f_user_login_0
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
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_0 where userid=i_userid;
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session_0 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return;
end;
$BODY$
language plpgsql; create or replace function f_user_login_1
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
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_1 where userid=i_userid;
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session_1 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_2
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
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_2 where userid=i_userid;
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session_2 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_3
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
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_3 where userid=i_userid;
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session_3 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_4
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
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_4 where userid=i_userid;
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session_4 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return;
end;
$BODY$
language plpgsql;
create or replace function f_user_logout_0
(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_0 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;
create or replace function f_user_logout_1
(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_1 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;
create or replace function f_user_logout_2
(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_2 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;
create or replace function f_user_logout_3
(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_3 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;
create or replace function f_user_logout_4
(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_4 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;
【调优阶段7】
1. 登陆脚本
cat login*.sql
\setrandom userid 1 4000000
SELECT f_user_login_0(:userid);
\setrandom userid 4000001 8000000
SELECT f_user_login_1(:userid);
\setrandom userid 8000001 12000000
SELECT f_user_login_2(:userid);
\setrandom userid 12000001 16000000
SELECT f_user_login_3(:userid);
\setrandom userid 16000001 20000000
SELECT f_user_login_4(:userid);
2. 退出脚本
cat logout*.sql
\setrandom userid 1 4000000
SELECT f_user_logout_0(:userid);
\setrandom userid 4000001 8000000
SELECT f_user_logout_1(:userid);
\setrandom userid 8000001 12000000
SELECT f_user_logout_2(:userid);
\setrandom userid 12000001 16000000
SELECT f_user_logout_3(:userid);
\setrandom userid 16000001 20000000
SELECT f_user_logout_4(:userid);
3. 压力测试
pgbench -M prepared -r -c 1 -f /home/postgres/test/login0.sql -j 1 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login0 &
pgbench -M prepared -r -c 1 -f /home/postgres/test/login1.sql -j 1 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login1 &
pgbench -M prepared -r -c 2 -f /home/postgres/test/login2.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login2 &
pgbench -M prepared -r -c 2 -f /home/postgres/test/login3.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login3 &
pgbench -M prepared -r -c 2 -f /home/postgres/test/login4.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login4 &
4. 测试结果
cat log.log*
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 180 s
number of transactions actually processed: 233348
tps = 1281.818097 (including connections establishing)
tps = 1281.837109 (excluding connections establishing)
statement latencies in milliseconds:
0.003492 \setrandom userid 1 4000000
0.771932 SELECT f_user_login_0(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 180 s
number of transactions actually processed: 233466
tps = 1282.514774 (including connections establishing)
tps = 1282.573500 (excluding connections establishing)
statement latencies in milliseconds:
0.003546 \setrandom userid 4000001 8000000
0.771399 SELECT f_user_login_1(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 180 s
number of transactions actually processed: 475466
tps = 2612.200783 (including connections establishing)
tps = 2612.281526 (excluding connections establishing)
statement latencies in milliseconds:
0.003605 \setrandom userid 8000001 12000000
0.757312 SELECT f_user_login_2(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 180 s
number of transactions actually processed: 468904
tps = 2576.380443 (including connections establishing)
tps = 2576.488485 (excluding connections establishing)
statement latencies in milliseconds:
0.003587 \setrandom userid 12000001 16000000
0.767869 SELECT f_user_login_3(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 180 s
number of transactions actually processed: 439381
tps = 2414.347086 (including connections establishing)
tps = 2414.425600 (excluding connections establishing)
statement latencies in milliseconds:
0.004431 \setrandom userid 16000001 20000000
0.817879 SELECT f_user_login_4(:userid);
总计 :
tps = 10167.261183 (including connections establishing)
tps = 10167.261183 (excluding connections establishing)
5. 瓶颈分析与优化
到这里我们还没有关注过表空间, 其实这些表拆分后它们还在同一个表空间里面. 把它们放在不同的表空间可以扩展它们整体的IO吞吐能力.
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Description
------------+----------+---------------------------------------------+---------------------+-------------
digoal | postgres | /pgdata/digoal/1921/data02/pg_tbs/digoal | postgres=C/postgres+|
| | | digoal=C/postgres |
digoal_01 | postgres | /pgdata/digoal/1921/data03/pg_tbs/digoal_01 | postgres=C/postgres+|
| | | digoal=C/postgres |
digoal_02 | postgres | /pgdata/digoal/1921/data04/pg_tbs/digoal_02 | postgres=C/postgres+|
| | | digoal=C/postgres |
digoal_03 | postgres | /pgdata/digoal/1921/data05/pg_tbs/digoal_03 | postgres=C/postgres+|
| | | digoal=C/postgres |
digoal_04 | postgres | /pgdata/digoal/1921/data06/pg_tbs/digoal_04 | postgres=C/postgres+|
| | | digoal=C/postgres |
digoal=> alter table user_info_0 set tablespace digoal_04;
ALTER TABLE
digoal=> alter table user_info_2 set tablespace digoal_01;
ALTER TABLE
digoal=> alter table user_info_3 set tablespace digoal_02;
ALTER TABLE
digoal=> alter table user_info_4 set tablespace digoal_03; digoal=> alter index user_info_0_pkey set tablespace digoal_04;
ALTER INDEX
digoal=> alter index user_info_2_pkey set tablespace digoal_01;
ALTER INDEX
digoal=> alter index user_info_3_pkey set tablespace digoal_02;
ALTER INDEX
digoal=> alter index user_info_4_pkey set tablespace digoal_03;
digoal=> alter table user_session_0 set tablespace digoal_04;
ALTER TABLE
digoal=> alter table user_session_2 set tablespace digoal_01;
ALTER TABLE
digoal=> alter table user_session_3 set tablespace digoal_02;
ALTER TABLE
digoal=> alter table user_session_4 set tablespace digoal_03;
digoal=> alter index user_session_0_pkey set tablespace digoal_04;
ALTER INDEX
digoal=> alter index user_session_2_pkey set tablespace digoal_01;
ALTER INDEX
digoal=> alter index user_session_3_pkey set tablespace digoal_02;
ALTER INDEX
digoal=> alter index user_session_4_pkey set tablespace digoal_03;
重新把它们加载到内存.
下节 :
http://blog.163.com/digoal@126/blog/static/163877040201221333411196/
【附】
pgbench simple|extended|prepare 部分源码 :
1. pgbench.c
if (commands[st->state]->type == SQL_COMMAND)
{
const Command *command = commands[st->state];
int r; if (querymode == QUERY_SIMPLE)
{
char *sql;
sql = xstrdup(command->argv[0]);
sql = assignVariables(st, sql);
if (debug)
fprintf(stderr, "client %d sending %s\n", st->id, sql);
r = PQsendQuery(st->con, sql);
free(sql);
}
else if (querymode == QUERY_EXTENDED)
{
const char *sql = command->argv[0];
const char *params[MAX_ARGS];
getQueryParams(st, command, params);
if (debug)
fprintf(stderr, "client %d sending %s\n", st->id, sql);
r = PQsendQueryParams(st->con, sql, command->argc - 1,
NULL, params, NULL, NULL, 0);
}
else if (querymode == QUERY_PREPARED)
{
char name[MAX_PREPARE_NAME];
const char *params[MAX_ARGS];
if (!st->prepared[st->use_file])
{
int j;
for (j = 0; commands[j] != NULL; j++)
{
PGresult *res;
char name[MAX_PREPARE_NAME];
if (commands[j]->type != SQL_COMMAND)
continue;
preparedStatementName(name, st->use_file, j);
res = PQprepare(st->con, name,
commands[j]->argv[0], commands[j]->argc - 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
fprintf(stderr, "%s", PQerrorMessage(st->con));
PQclear(res);
}
st->prepared[st->use_file] = true;
}
getQueryParams(st, command, params);
preparedStatementName(name, st->use_file, st->state);
if (debug)
fprintf(stderr, "client %d sending %s\n", st->id, name);
r = PQsendQueryPrepared(st->con, name, command->argc - 1,
params, NULL, NULL, 0);
}
2. src/interfaces/libpq/fe-exec.c
【调优阶段8】
1. 压力测试
pgbench -M prepared -r -c 1 -f /home/postgres/test/login0.sql -j 1 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login0 &
pgbench -M prepared -r -c 1 -f /home/postgres/test/login1.sql -j 1 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login1 &
pgbench -M prepared -r -c 2 -f /home/postgres/test/login2.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login2 &
pgbench -M prepared -r -c 2 -f /home/postgres/test/login3.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login3 &
pgbench -M prepared -r -c 2 -f /home/postgres/test/login4.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login4 &
2. 测试结果
cat log.log*
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 180 s
number of transactions actually processed: 296485
tps = 1647.130827 (including connections establishing)
tps = 1647.153173 (excluding connections establishing)
statement latencies in milliseconds:
0.003394 \setrandom userid 1 4000000
0.599293 SELECT f_user_login_0(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 180 s
number of transactions actually processed: 270077
tps = 1500.414232 (including connections establishing)
tps = 1500.434330 (excluding connections establishing)
statement latencies in milliseconds:
0.004436 \setrandom userid 4000001 8000000
0.656274 SELECT f_user_login_1(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 180 s
number of transactions actually processed: 543390
tps = 3018.814281 (including connections establishing)
tps = 3018.901510 (excluding connections establishing)
statement latencies in milliseconds:
0.004553 \setrandom userid 8000001 12000000
0.652033 SELECT f_user_login_2(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 180 s
number of transactions actually processed: 592774
tps = 3293.147194 (including connections establishing)
tps = 3293.235012 (excluding connections establishing)
statement latencies in milliseconds:
0.003446 \setrandom userid 12000001 16000000
0.599297 SELECT f_user_login_3(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 180 s
number of transactions actually processed: 593614
tps = 3297.831371 (including connections establishing)
tps = 3297.946707 (excluding connections establishing)
statement latencies in milliseconds:
0.003421 \setrandom userid 16000001 20000000
0.598465 SELECT f_user_login_4(:userid);
总计 :
tps = 12757.337905 (including connections establishing)
tps = 12757.670732 (excluding connections establishing)
3. 瓶颈分析与优化
测试中我们使用的数据库服务器cpu是8核的服务器, 根据以往的经验, 当活跃的进程数等于核数的2倍时可以发挥CPU的最大能力.
所以我们通过增加并发连接来看看到底有多少性能提升.
【调优阶段9】
1. 压力测试
pgbench -M prepared -r -c 2 -f /home/postgres/test/login0.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login0 &
pgbench -M prepared -r -c 2 -f /home/postgres/test/login1.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login1 &
pgbench -M prepared -r -c 4 -f /home/postgres/test/login2.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login2 &
pgbench -M prepared -r -c 4 -f /home/postgres/test/login3.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login3 &
pgbench -M prepared -r -c 4 -f /home/postgres/test/login4.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login4 &
2. 测试结果
cat log.log*
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 180 s
number of transactions actually processed: 375743
tps = 2087.443600 (including connections establishing)
tps = 2087.489913 (excluding connections establishing)
statement latencies in milliseconds:
0.003492 \setrandom userid 1 4000000
0.949744 SELECT f_user_login_0(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 180 s
number of transactions actually processed: 367801
tps = 2043.313370 (including connections establishing)
tps = 2043.386454 (excluding connections establishing)
statement latencies in milliseconds:
0.003710 \setrandom userid 4000001 8000000
0.969828 SELECT f_user_login_1(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 4
duration: 180 s
number of transactions actually processed: 730267
tps = 4057.007177 (including connections establishing)
tps = 4057.148280 (excluding connections establishing)
statement latencies in milliseconds:
0.003962 \setrandom userid 8000001 12000000
0.976372 SELECT f_user_login_2(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 4
duration: 180 s
number of transactions actually processed: 738398
tps = 4101.985844 (including connections establishing)
tps = 4102.135039 (excluding connections establishing)
statement latencies in milliseconds:
0.003615 \setrandom userid 12000001 16000000
0.966314 SELECT f_user_login_3(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 4
duration: 180 s
number of transactions actually processed: 732793
tps = 4070.957105 (including connections establishing)
tps = 4071.200533 (excluding connections establishing)
statement latencies in milliseconds:
0.003882 \setrandom userid 16000001 20000000
0.973208 SELECT f_user_login_4(:userid);
总计 :
tps = 16360.707096 (including connections establishing)
tps = 16361.360219 (excluding connections establishing)
3. 瓶颈分析与优化
继续增加连接,tps还可以再提高吗? : 不可以.
8核的机器16个活动的会话基本上就到达它的上限了.
因此要提高tps还可以加CPU.
下面增加连接到30个的测试结果证明了上面的结论.
pgbench -M prepared -r -c 6 -f /home/postgres/test/login0.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login0 &
pgbench -M prepared -r -c 6 -f /home/postgres/test/login1.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login1 &
pgbench -M prepared -r -c 6 -f /home/postgres/test/login2.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login2 &
pgbench -M prepared -r -c 6 -f /home/postgres/test/login3.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login3 &
pgbench -M prepared -r -c 6 -f /home/postgres/test/login4.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login4 &
结果
cat log.log*
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 6
number of threads: 6
duration: 180 s
number of transactions actually processed: 544811
tps = 3026.494301 (including connections establishing)
tps = 3026.608244 (excluding connections establishing)
statement latencies in milliseconds:
0.003768 \setrandom userid 1 4000000
1.973230 SELECT f_user_login_0(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 6
number of threads: 6
duration: 180 s
number of transactions actually processed: 544485
tps = 3024.298399 (including connections establishing)
tps = 3024.468785 (excluding connections establishing)
statement latencies in milliseconds:
0.003735 \setrandom userid 4000001 8000000
1.974466 SELECT f_user_login_1(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 6
number of threads: 6
duration: 180 s
number of transactions actually processed: 544778
tps = 3025.262019 (including connections establishing)
tps = 3025.469901 (excluding connections establishing)
statement latencies in milliseconds:
0.003707 \setrandom userid 8000001 12000000
1.973661 SELECT f_user_login_2(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 6
number of threads: 6
duration: 180 s
number of transactions actually processed: 542008
tps = 3010.921306 (including connections establishing)
tps = 3011.146550 (excluding connections establishing)
statement latencies in milliseconds:
0.003662 \setrandom userid 12000001 16000000
1.983714 SELECT f_user_login_3(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 6
number of threads: 6
duration: 180 s
number of transactions actually processed: 539505
tps = 2996.511493 (including connections establishing)
tps = 2996.874239 (excluding connections establishing)
statement latencies in milliseconds:
0.003768 \setrandom userid 16000001 20000000
1.992923 SELECT f_user_login_4(:userid);
总计 :
tps = 15083.487518 (including connections establishing)
tps = 15084.567719 (excluding connections establishing)
连接数超过2倍核数后根本不会有性能提升了, 这台服务器的潜力基本上挖掘得差不多了.
接下来就需要通过增加服务器来提升数据库的整体性能了.
首先要用到的是PostgreSQL的流复制, 通过hot standby可以进行读写分离, 也就是将SELECT的请求分发到hot standby上.
(需要注意跨库事务的问题, 如standby的延时, 这里不详细阐述)
新建查询函数和插入更新函数 :
create or replace function f_user_login_sel_0
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
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_0 where userid=i_userid;
return;
end;
$BODY$
language plpgsql; create or replace function f_user_login_sel_1
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
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_1 where userid=i_userid;
return;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_sel_2
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
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_2 where userid=i_userid;
return;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_sel_3
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
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_3 where userid=i_userid;
return;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_sel_4
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
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_4 where userid=i_userid;
return;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_insupd_0
(i_userid int
)
returns int as $BODY$
declare
begin
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session_0 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_insupd_1
(i_userid int
)
returns int as $BODY$
declare
begin
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session_1 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_insupd_2
(i_userid int
)
returns int as $BODY$
declare
begin
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session_2 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_insupd_3
(i_userid int
)
returns int as $BODY$
declare
begin
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session_3 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_insupd_4
(i_userid int
)
returns int as $BODY$
declare
begin
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
update user_session_4 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql;
hot standby库也需要将数据加载到内存, 具体操作略.
【调优阶段10】
1. 测试脚本
cat log*
\setrandom userid 1 4000000
SELECT f_user_login_insupd_0(:userid);
\setrandom userid 4000001 8000000
SELECT f_user_login_insupd_1(:userid);
\setrandom userid 8000001 12000000
SELECT f_user_login_insupd_2(:userid);
\setrandom userid 12000001 16000000
SELECT f_user_login_insupd_3(:userid);
\setrandom userid 16000001 20000000
SELECT f_user_login_insupd_4(:userid);
\setrandom userid 1 4000000
SELECT f_user_login_sel_0(:userid);
\setrandom userid 4000001 8000000
SELECT f_user_login_sel_1(:userid);
\setrandom userid 8000001 12000000
SELECT f_user_login_sel_2(:userid);
\setrandom userid 12000001 16000000
SELECT f_user_login_sel_3(:userid);
\setrandom userid 16000001 20000000
SELECT f_user_login_sel_4(:userid);
2. 压力测试
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel0.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel0 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel1.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel1 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel2.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel2 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel3.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel3 &
pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_sel4.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel4 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd0.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd0 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd1.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd1 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd2.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd2 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd3.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd3 &
pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_insupd4.sql -j 4 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd4 &
3. 测试结果
hot standby的测试数据 :
cat log.login_sel*
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 552618
tps = 3012.767914 (including connections establishing)
tps = 3012.877330 (excluding connections establishing)
statement latencies in milliseconds:
0.003166 \setrandom userid 1 4000000
0.988247 SELECT f_user_login_sel_0(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 750314
tps = 4089.671930 (including connections establishing)
tps = 4089.771337 (excluding connections establishing)
statement latencies in milliseconds:
0.003030 \setrandom userid 4000001 8000000
0.726462 SELECT f_user_login_sel_1(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 727839
tps = 3967.242817 (including connections establishing)
tps = 3967.364415 (excluding connections establishing)
statement latencies in milliseconds:
0.003260 \setrandom userid 8000001 12000000
0.748466 SELECT f_user_login_sel_2(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 715952
tps = 3903.028278 (including connections establishing)
tps = 3903.130455 (excluding connections establishing)
statement latencies in milliseconds:
0.003077 \setrandom userid 12000001 16000000
0.761439 SELECT f_user_login_sel_3(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 4
duration: 180 s
number of transactions actually processed: 964366
tps = 5257.974345 (including connections establishing)
tps = 5258.120849 (excluding connections establishing)
statement latencies in milliseconds:
0.003153 \setrandom userid 16000001 20000000
0.753196 SELECT f_user_login_sel_4(:userid);
总计 :
tps = 20230.685284 (including connections establishing)
tps = 20231.264386 (excluding connections establishing)
primary的测试数据 :
cat log.login_insupd*
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 745415
tps = 4141.145602 (including connections establishing)
tps = 4141.250129 (excluding connections establishing)
statement latencies in milliseconds:
0.003236 \setrandom userid 1 4000000
0.716912 SELECT f_user_login_insupd_0(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 737761
tps = 4098.582645 (including connections establishing)
tps = 4098.704693 (excluding connections establishing)
statement latencies in milliseconds:
0.003360 \setrandom userid 4000001 8000000
0.723997 SELECT f_user_login_insupd_1(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 761171
tps = 4228.709500 (including connections establishing)
tps = 4228.817139 (excluding connections establishing)
statement latencies in milliseconds:
0.003333 \setrandom userid 8000001 12000000
0.701648 SELECT f_user_login_insupd_2(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 761960
tps = 4233.031271 (including connections establishing)
tps = 4233.166856 (excluding connections establishing)
statement latencies in milliseconds:
0.003306 \setrandom userid 12000001 16000000
0.700967 SELECT f_user_login_insupd_3(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 4
duration: 180 s
number of transactions actually processed: 999167
tps = 5550.893825 (including connections establishing)
tps = 5551.246720 (excluding connections establishing)
statement latencies in milliseconds:
0.003385 \setrandom userid 16000001 20000000
0.712689 SELECT f_user_login_insupd_4(:userid);
总计 :
tps = 22252.362843 (including connections establishing)
tps = 22253.185537 (excluding connections establishing) QPS :
qps = 20230.685284 + (22252.362843 * 2) (including connections establishing)
qps = 20231.264386 + (22253.185537 * 2) (excluding connections establishing)
4. 瓶颈分析与优化
主节点 :
avg-cpu: %user %nice %system %iowait %steal %idle
56.30 0.00 21.72 4.24 0.00 17.73
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 0.00 2781.50 0.00 93.50 0.00 22876.00 244.66 0.09 0.93 0.90 8.40
sdd 0.00 10656.50 0.00 2302.50 0.00 105300.00 45.73 108.00 27.85 0.43 100.05
dm-0 0.00 0.00 0.00 2875.50 0.00 23004.00 8.00 2.56 0.89 0.03 8.30
dm-1 0.00 0.00 0.00 12943.00 0.00 103544.00 8.00 569.00 34.94 0.08 100.10
dm-2 0.00 0.00 0.00 2832.50 0.00 22660.00 8.00 2.55 0.90 0.03 8.05
dm-3 0.00 0.00 0.00 41.50 0.00 332.00 8.00 0.02 0.54 0.06 0.25
dm-4 0.00 0.00 0.00 1.50 0.00 12.00 8.00 0.00 0.00 0.00 0.00
dm-5 0.00 0.00 0.00 1.00 0.00 8.00 8.00 0.01 0.00 4.00 0.40
dm-6 0.00 0.00 0.00 11545.50 0.00 92364.00 8.00 505.23 33.04 0.08 91.75
dm-7 0.00 0.00 0.00 1396.50 0.00 11172.00 8.00 63.54 50.65 0.15 20.65
standby节点 :
avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.31 12.87 0.00 86.82
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 1222.39 0.00 996.52 0.00 19136.32 19.20 113.22 116.63 1.00 99.55
cciss/c0d0p1 0.00 2.99 0.00 1.00 0.00 31.84 32.00 0.10 101.50 101.50 10.10
cciss/c0d0p2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p3 0.00 1219.40 0.00 995.52 0.00 19104.48 19.19 113.12 116.64 1.00 99.55
cciss/c0d1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d2 0.00 1384.08 0.00 251.74 0.00 13297.51 52.82 142.31 522.75 3.95 99.55
cciss/c0d3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 1638.81 0.00 13110.45 8.00 946.36 538.61 0.61 99.55
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-5 0.00 0.00 0.00 2193.03 0.00 17544.28 8.00 275.53 132.89 0.45 99.55
显然IO到达瓶颈了. 为什么每次IO都顶不住呢? 是的, 机械硬盘的随机IOPS能力就是这么差, 不要有太高的奢望.
要提升IOPS要么就用高端存储要么就选择SSD硬盘. 下次有机会找块ssd硬盘来测试一下它的iops能力到底有多强.
那么这些IO是怎么产生的呢?
1. 主库的IO来自insert和update请求.
2. hot standby的IO来自stream data recovery.
因为我的测试环境没有办法扩存储, 所以这里就不通过扩存储来解决这个瓶颈了, 还是加服务器.
但是这次加2台服务器, 1台用来做hot standby. 另一台我要把insert请求剥离过去.
也就是总共用4台服务器.
具体的操作如下 :
初始化新增的日志库 :
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
);
create or replace function f_user_login_ins
(i_userid int)
returns int as $BODY$
declare
begin
insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql;
主库新增函数 :
create or replace function f_user_login_upd_0
(i_userid int
)
returns int as $BODY$
declare
begin
update user_session_0 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql; create or replace function f_user_login_upd_1
(i_userid int
)
returns int as $BODY$
declare
begin
update user_session_1 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_upd_2
(i_userid int
)
returns int as $BODY$
declare
begin
update user_session_2 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_upd_3
(i_userid int
)
returns int as $BODY$
declare
begin
update user_session_3 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql;
create or replace function f_user_login_upd_4
(i_userid int
)
returns int as $BODY$
declare
begin
update user_session_4 set logintime=now(),login_count=login_count+1 where userid=i_userid;
return 0;
exception
when others then
return 1;
end;
$BODY$
language plpgsql;
再增加一台standby, 流复制过程略, 请参考我写过的流复制环境搭建BLOG.
《PostgreSQL HOT STANDBY using Stream》
http://blog.163.com/digoal@126/blog/static/16387704020110442050808/
优化当前环境如下,
primary : 172.16.3.150
standby1 : 172.16.3.33
standby2 : 172.16.3.39
logdb : 172.16.3.40
【调优阶段11】
1. 测试脚本
postgres@db5-> cat login_ins.sql
\setrandom userid 1 20000000
SELECT f_user_login_ins(:userid);
postgres@db5-> cat login_sel*
\setrandom userid 1 4000000
SELECT f_user_login_sel_0(:userid);
\setrandom userid 4000001 8000000
SELECT f_user_login_sel_1(:userid);
\setrandom userid 8000001 12000000
SELECT f_user_login_sel_2(:userid);
\setrandom userid 12000001 16000000
SELECT f_user_login_sel_3(:userid);
\setrandom userid 16000001 20000000
SELECT f_user_login_sel_4(:userid);
postgres@db5-> cat login_upd*
\setrandom userid 1 4000000
SELECT f_user_login_upd_0(:userid);
\setrandom userid 4000001 8000000
SELECT f_user_login_upd_1(:userid);
\setrandom userid 8000001 12000000
SELECT f_user_login_upd_2(:userid);
\setrandom userid 12000001 16000000
SELECT f_user_login_upd_3(:userid);
\setrandom userid 16000001 20000000
SELECT f_user_login_upd_4(:userid);
2. 压力测试
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel0.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel0 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel1.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel1 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel2.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel2 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel3.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel3 &
pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_sel4.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel4 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel0.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel0 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel1.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel1 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel2.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel2 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel3.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel3 &
pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_sel4.sql -j 4 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel4 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd0.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd0 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd1.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd1 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd2.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd2 &
pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd3.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd3 &
pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_upd4.sql -j 4 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd4 &
pgbench -M prepared -r -c 16 -f /home/postgres/test_zsplit/login_ins.sql -j 16 -n -T 180 -h 172.16.3.40 -p 1921 -U digoal digoal >./log.login_ins &
3. 测试结果
cat log.login33_sel*
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 1534211
tps = 8523.315651 (including connections establishing)
tps = 8523.524318 (excluding connections establishing)
statement latencies in milliseconds:
0.002438 \setrandom userid 1 4000000
0.346514 SELECT f_user_login_sel_0(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 1533785
tps = 8520.894378 (including connections establishing)
tps = 8521.168645 (excluding connections establishing)
statement latencies in milliseconds:
0.002423 \setrandom userid 4000001 8000000
0.346564 SELECT f_user_login_sel_1(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 1544585
tps = 8580.974433 (including connections establishing)
tps = 8581.260902 (excluding connections establishing)
statement latencies in milliseconds:
0.002448 \setrandom userid 8000001 12000000
0.344071 SELECT f_user_login_sel_2(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 1482080
tps = 8233.719776 (including connections establishing)
tps = 8234.138037 (excluding connections establishing)
statement latencies in milliseconds:
0.002435 \setrandom userid 12000001 16000000
0.358877 SELECT f_user_login_sel_3(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 4
duration: 180 s
number of transactions actually processed: 1982503
tps = 11013.842899 (including connections establishing)
tps = 11014.329592 (excluding connections establishing)
statement latencies in milliseconds:
0.002422 \setrandom userid 16000001 20000000
0.357698 SELECT f_user_login_sel_4(:userid); cat log.login39_sel*
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 1534696
tps = 8526.005287 (including connections establishing)
tps = 8526.221472 (excluding connections establishing)
statement latencies in milliseconds:
0.002436 \setrandom userid 1 4000000
0.346352 SELECT f_user_login_sel_0(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 1542513
tps = 8569.192037 (including connections establishing)
tps = 8569.392061 (excluding connections establishing)
statement latencies in milliseconds:
0.002416 \setrandom userid 4000001 8000000
0.344625 SELECT f_user_login_sel_1(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 1508389
tps = 8379.888796 (including connections establishing)
tps = 8380.257897 (excluding connections establishing)
statement latencies in milliseconds:
0.002426 \setrandom userid 8000001 12000000
0.352536 SELECT f_user_login_sel_2(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 1491690
tps = 8287.124725 (including connections establishing)
tps = 8287.453198 (excluding connections establishing)
statement latencies in milliseconds:
0.002464 \setrandom userid 12000001 16000000
0.356436 SELECT f_user_login_sel_3(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 4
duration: 180 s
number of transactions actually processed: 2014650
tps = 11192.426565 (including connections establishing)
tps = 11192.867173 (excluding connections establishing)
statement latencies in milliseconds:
0.002418 \setrandom userid 16000001 20000000
0.351905 SELECT f_user_login_sel_4(:userid);
cat log.login_ins
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 16
duration: 180 s
number of transactions actually processed: 7091331
tps = 39394.952222 (including connections establishing)
tps = 39397.035365 (excluding connections establishing)
statement latencies in milliseconds:
0.002984 \setrandom userid 1 20000000
0.399208 SELECT f_user_login_ins(:userid);
cat log.login_upd*
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 968016
tps = 5377.633815 (including connections establishing)
tps = 5377.769568 (excluding connections establishing)
statement latencies in milliseconds:
0.002434 \setrandom userid 1 4000000
0.552395 SELECT f_user_login_upd_0(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 965529
tps = 5363.841108 (including connections establishing)
tps = 5364.017826 (excluding connections establishing)
statement latencies in milliseconds:
0.002461 \setrandom userid 4000001 8000000
0.553797 SELECT f_user_login_upd_1(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 969904
tps = 5388.302421 (including connections establishing)
tps = 5388.476038 (excluding connections establishing)
statement latencies in milliseconds:
0.002436 \setrandom userid 8000001 12000000
0.551348 SELECT f_user_login_upd_2(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 3
number of threads: 3
duration: 180 s
number of transactions actually processed: 990833
tps = 5504.605729 (including connections establishing)
tps = 5504.844893 (excluding connections establishing)
statement latencies in milliseconds:
0.002448 \setrandom userid 12000001 16000000
0.539510 SELECT f_user_login_upd_3(:userid);
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 4
duration: 180 s
number of transactions actually processed: 1316258
tps = 7312.497604 (including connections establishing)
tps = 7312.837009 (excluding connections establishing)
statement latencies in milliseconds:
0.002405 \setrandom userid 16000001 20000000
0.541622 SELECT f_user_login_upd_4(:userid);
总计 :
QPS :
158169.217446 ( select 89827.384547, insert 39394.952222, update 28946.880677 )
158175.593994 ( select 89830.613295, insert 39397.035365, update 28947.945334 )
4. 瓶颈分析与优化
如果前面的拆库是纵向的拆的话, 那么接下来要提升性能就得横向的来拆了.
例如 :
select能力可以通过数据库流复制扩展, 9.2以后可以级联复制因此基本上可以做到不影响主库性能的情况下无限扩展.
insert能力可以通过增加logdb服务器扩展, 无限扩展.
update能力可以通过将表拆分到多个服务器上, 无限扩展.
横向分库,需要考虑跨库事务的问题, 1.plproxy
参考 :
《A Smart PostgreSQL extension plproxy 2.2 practices》
http://blog.163.com/digoal@126/blog/static/163877040201192535630895/
【调优阶段12】
本文的最后一个阶段, 由于服务器有限, 所以我这里测试的是一个节点的性能, 以前测试过plproxy, 性能是线性扩展的.
因此测试一个节点基本上就可以推算出多节点的性能.
1. 压力测试
pgbench -M prepared -r -c 16 -f /home/postgres/test_zsplit/login_upd0.sql -j 16 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd0 &
2. 测试结果
cat log.login_upd0
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 16
duration: 180 s
number of transactions actually processed: 6015759
tps = 33416.574452 (including connections establishing)
tps = 33419.030898 (excluding connections establishing)
statement latencies in milliseconds:
0.002152 \setrandom userid 1 4000000
0.473792 SELECT f_user_login_upd_0(:userid);
因此5个节点的性能约等于 :
tps = 167082.872260 ( 33416.574452 * 5 ) (including connections establishing)
tps = 167095.154490 ( 33419.030898 * 5 ) (excluding connections establishing)
3. 瓶颈分析与优化
同11阶段 :
select能力可以通过数据库流复制扩展, 9.2以后可以级联复制因此基本上可以做到不影响主库性能的情况下无限扩展.
insert能力可以通过增加logdb服务器扩展, 无限扩展.
update能力可以通过将表拆分到多个服务器上, 无限扩展.
横向分库,需要考虑跨库事务的问题, 1.plproxy
【调优性能图表1】
【调优性能图表2】
【其他可优化点补充】
1. 批量提交,降低IO请求量, 并发请求很高的场景. 但是当并发场景这么高的时候已经可以考虑增加服务器分库了.
相关参数
#commit_delay = 0
#commit_siblings = 5
参考《Test PostgreSQL 9.1's group commit》
http://blog.163.com/digoal@126/blog/static/1638770402011102214142132/
2. 连接池,如pgbouncer(适用于短连接, 大量空闲连接的情况.)
3. 绑定变量, 性能提升参考
《how many performance decreased use dynamic SQL》
http://blog.163.com/digoal@126/blog/static/1638770402011109103953350/
4. user_session中记录了用户的登陆统计信息和退出统计信息, 由于MVCC特性, 每次更新都会新产生一条tuple, 因此如果将登陆和退出的统计拆开, 就能减少新增的tuple的大小. 一定程度上提升性能.
user_session_login (userid, logintime, login_count)
user_session_logout (userid, logouttime, online_interval)
5. OS级别也有可以优化的地方, 比如文件系统的mount参数可以加上noatime.
6. 服务器硬件也有可以优化的地方, 比如numa.
7. PostgreSQL也还有可以微调的参数, 比如bgwriter_lru_maxpages和bgwriter_lru_multiplier它们的值也将影响数据库和文件系统交互的频率以及每次交互产生的io请求数.
8. 在做分表优化的时候, 本例使用的是按userid分段拆分成了5个表. 其实还可以按hash取模拆, 按时间段拆等等. 拆分的关键是尽量按照常用的条件字段进行拆分. 另外需要注意的是, 我这里没有提到PostgreSQL的partition table的实现, 而是直接使用应用端来识别数据在哪个分区. 原因是PostgreSQL的partition table需要通过rule或者触发器来实现, 大量的消耗数据库服务器的CPU, 不推荐使用. 性能下降和Oracle的比较可参考,
《execute plan difference between Oracle and PostgreSQL's partition table》
http://blog.163.com/digoal@126/blog/static/163877040201212432441676/
《Compare Oracle's & PostgreSQL's Partition Table write performance》
http://blog.163.com/digoal@126/blog/static/163877040201123084853271/
《PostgreSQL partition table's arithmetic tuning example》
http://blog.163.com/digoal@126/blog/static/1638770402011210114036419/
【小结】
1. 诊断角度
操作系统层面: 查看CPU, IO.
数据库层面:
查看pg_stat_statements
Column | Type | Modifiers
---------------------+------------------+-----------
userid | oid |
dbid | oid |
query | text |
calls | bigint |
total_time | double precision |
rows | bigint |
shared_blks_hit | bigint |
shared_blks_read | bigint |
shared_blks_written | bigint |
local_blks_hit | bigint |
local_blks_read | bigint |
local_blks_written | bigint |
temp_blks_read | bigint |
temp_blks_written | bigint |
其他pg_stat性能视图
日志中的long SQL,
2. 优化角度
参数, SQL, 架构, 连接池, 表空间拆分, 存储cache, 分表, 分库
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.