266
技術社區[雲棲]
PostgreSQL 性能優化方法 - 2
【調優階段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能力到底有多強.
(2015-12-21 更新，當下的SSD硬件1台頂當時的8台性能 (同一個測試CASE)。)
那麼這些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》
https://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》
https://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】
圖1
【調優性能圖表2】
圖2
【其他可優化點補充】
1. 批量提交,降低IO請求量, 並發請求很高的場景. 但是當並發場景這麼高的時候已經可以考慮增加服務器分庫了.
相關參數
#commit_delay = 0
#commit_siblings = 5
參考《Test PostgreSQL 9.1's group commit》
https://blog.163.com/digoal@126/blog/static/1638770402011102214142132/
2. 連接池,如pgbouncer(適用於短連接, 大量空閑連接的情況.)
3. 綁定變量, 性能提升參考
《how many performance decreased use dynamic SQL》
https://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》
https://blog.163.com/digoal@126/blog/static/163877040201212432441676/
《Compare Oracle's & PostgreSQL's Partition Table write performance》
https://blog.163.com/digoal@126/blog/static/163877040201123084853271/
《PostgreSQL partition table's arithmetic tuning example》
https://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, 分表, 分庫
【參考】
1.《PostgreSQL HOT STANDBY using Stream》
https://blog.163.com/digoal@126/blog/static/16387704020110442050808/
2.《A Smart PostgreSQL extension plproxy 2.2 practices》
https://blog.163.com/digoal@126/blog/static/163877040201192535630895/
3.《Test PostgreSQL 9.1's group commit》
https://blog.163.com/digoal@126/blog/static/1638770402011102214142132/
4.《how many performance decreased use dynamic SQL》
https://blog.163.com/digoal@126/blog/static/1638770402011109103953350/
5.《execute plan difference between Oracle and PostgreSQL's partition table》
https://blog.163.com/digoal@126/blog/static/163877040201212432441676/
6.《Compare Oracle's & PostgreSQL's Partition Table write performance》
https://blog.163.com/digoal@126/blog/static/163877040201123084853271/
7.《PostgreSQL partition table's arithmetic tuning example》
https://blog.163.com/digoal@126/blog/static/1638770402011210114036419/
8.《Use pgbench test Your PostgreSQL DBSystem performace》
https://blog.163.com/digoal@126/blog/static/163877040201151534631313/
9.《Use pg_test_fsync test which wal_sync_method is fastest in your filesystem》
https://blog.163.com/digoal@126/blog/static/163877040201141795025354/
10.《a powerful upgrade from pgfincore 1.0》
https://blog.163.com/digoal@126/blog/static/1638770402011630102117658/
11.《use posix_fadvise pre-cache frequency data》
https://blog.163.com/digoal@126/blog/static/163877040201062944945126/
12.《TOAST table with pgfincore》
https://blog.163.com/digoal@126/blog/static/16387704020120524144140/
13.《PostgreSQL and Oracle's async commit》
https://blog.163.com/digoal@126/blog/static/16387704020121229223072/
圖1
圖2
最後更新：2017-04-01 13:44:35