承接上一篇xfs性能優化。
http://blog.163.com/digoal@126/blog/static/16387704020160695427218/
本文是對比xfs優化前後的postgresql tpc-b性能。
配置postgresql
資料庫參數
listen_addresses='0.0.0.0'
port=1921
max_connections=1000
unix_socket_directories='.'
shared_buffers=32gb
maintenance_work_mem=512mb
autovacuum_work_mem=512mb
dynamic_shared_memory_type=posix
bgwriter_delay=10ms
wal_level=hot_standby
synchronous_commit=off
full_page_writes=off
wal_buffers=16mb
wal_writer_delay=10ms
max_wal_size=32gb
max_wal_senders=10
max_replication_slots=10
hot_standby=on
wal_receiver_status_interval=1s
hot_standby_feedback=on
random_page_cost=1.0
effective_cache_size=256gb
log_destination='csvlog'
logging_collector=on
log_checkpoints=on
log_connections=on
log_disconnections=on
log_error_verbosity=verbose
log_timezone='prc'
datestyle='iso,
timezone='prc'
lc_messages='c'
lc_monetary='c'
lc_numeric='c'
lc_time='c'
default_text_search_config='pg_catalog.english'
$pgdata和pg_xlog都放在xfs檔案系統中。
生成測試資料
生成5億測試資料
pgbench -i -s 5000
初始化性能對比
xfs未優化時的統計資訊如下
pgbench初始化測試資料時的磁盤iostat
io落在一塊盤上
平均io響應0.36毫秒，平均io等待0.87毫秒
avg-cpu: %user %nice %system %iowait %steal %idle
3.60 0.00 1.35 0.19 0.00 94.87
device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 1.00 0.00 4.00 0.00 40.00 10.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dfa 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dfb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dfc 0.00 0.00 0.00 250.00 0.00 206752.00 827.01 0.22 0.86 0.36 9.10
dm-0 0.00 0.00 0.00 250.00 0.00 206752.00 827.01 0.22 0.87 0.36 9.10
vacuum 時的iostat
平均io響應0.28毫秒，平均io等待26.56毫秒
1.88 0.00 4.77 0.00 0.00 93.35
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dfb 0.00 0.00 0.00 3211.00 0.00 3280888.00 1021.77 86.20 26.53 0.28 90.20
dfc 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 3210.00 0.00 3279864.00 1021.76 86.21 26.56 0.28 90.20
xfs未優化後的統計資訊如下
io分攤到多快盤上
平均io響應0.00毫秒，平均io等待0.01毫秒
3.57 0.00 2.50 0.03 0.00 93.90
dfa 0.00 0.00 0.00 9262.00 0.00 148096.00 15.99 0.11 0.01 0.01 8.60
dfb 0.00 0.00 0.00 9243.00 0.00 147888.00 16.00 0.11 0.01 0.01 8.80
dfc 0.00 0.00 0.00 9256.00 0.00 148096.00 16.00 0.17 0.02 0.01 9.00
dm-0 0.00 0.00 0.00 27761.00 0.00 444080.00 16.00 0.42 0.01 0.00 9.30
平均io響應0.01毫秒，平均io等待0.02毫秒
2.09 0.00 13.57 0.10 0.00 84.25
dfa 0.00 0.00 0.00 52311.00 0.00 836976.00 16.00 0.70 0.01 0.01 63.50
dfb 0.00 0.00 0.00 52313.00 0.00 837008.00 16.00 0.74 0.01 0.01 66.90
dfc 0.00 0.00 0.00 52312.00 0.00 836984.00 16.00 1.11 0.02 0.02 94.30
dm-0 0.00 0.00 0.00 156936.00 0.00 2510952.00 16.00 2.90 0.02 0.01 97.40
壓測性能對比
壓測指令
nohup pgbench -m prepared -n -r -p 1 -c 96 -j 96 -t 3600 >./bench.log 2>&1 &
xfs優化前性能名額
xfs優化前的iostat
未發生檢查點時
io落到一個塊裝置
平均io響應0.04毫秒，平均io等待0.1毫秒
73.16 0.00 22.17 0.06 0.00 4.61
dfb 0.00 0.00 0.00 922.00 0.00 12456.00 13.51 0.00 0.00 0.00 0.30
dfc 0.00 0.00 0.00 3955.00 0.00 131776.00 33.32 0.44 0.11 0.05 18.20
dm-0 0.00 0.00 0.00 4877.00 0.00 144232.00 29.57 0.47 0.10 0.04 21.00
發生檢查點時
平均io響應0.03毫秒，平均io等待0.6毫秒
72.67 0.00 25.48 0.00 0.00 1.85
dfb 0.00 0.00 0.00 32539.00 0.00 3184272.00 97.86 20.65 0.63 0.03 100.20
dfc 0.00 0.00 0.00 2566.00 0.00 84464.00 32.92 0.35 0.14 0.09 22.90
dm-0 0.00 0.00 0.00 35104.00 0.00 3268672.00 93.11 21.06 0.60 0.03 100.10
tps
transaction type: tpc-b (sort of)
scaling factor: 5000
query mode: prepared
number of clients: 96
number of threads: 96
duration: 3600 s
number of transactions actually processed: 34796111
latency average: 9.929 ms
latency stddev: 14.829 ms
tps = 9664.902474 (including connections establishing)
tps = 9665.028796 (excluding connections establishing)
statement latencies in milliseconds:
0.006275 \set nbranches 1 * :scale
0.001541 \set ntellers 10 * :scale
0.001213 \set naccounts 100000 * :scale
0.002230 \setrandom aid 1 :naccounts
0.001708 \setrandom bid 1 :nbranches
0.001497 \setrandom tid 1 :ntellers
0.001460 \setrandom delta -5000 5000
1.284175 begin;
1.139272 update pgbench_accounts set abalance = abalance + :delta where aid = :aid;
1.012388 select abalance from pgbench_accounts where aid = :aid;
1.682350 update pgbench_tellers set tbalance = tbalance + :delta where tid = :tid;
2.705569 update pgbench_branches set bbalance = bbalance + :delta where bid = :bid;
1.058834 insert into pgbench_history (tid, bid, aid, delta, mtime) values (:tid, :bid, :aid, :delta, current_timestamp);
1.015118 end;
xfs優化後性能名額
平均io響應0.02毫秒，平均io等待0.02毫秒
65.34 0.00 26.94 0.00 0.00 7.72
dfa 0.00 0.00 0.00 1428.00 0.00 22832.00 15.99 0.02 0.02 0.02 2.40
dfb 0.00 0.00 0.00 1417.00 0.00 22672.00 16.00 0.03 0.02 0.02 2.50
dfc 0.00 0.00 0.00 1417.00 0.00 22672.00 16.00 0.04 0.02 0.02 3.50
dm-0 0.00 0.00 0.00 4262.00 0.00 68176.00 16.00 0.10 0.02 0.02 9.50
檢查點發生時
58.82 0.00 35.25 0.00 0.00 5.93
dfa 0.00 0.00 0.00 52709.00 0.00 843272.00 16.00 1.12 0.02 0.01 72.30
dfb 0.00 0.00 0.00 52632.00 0.00 842048.00 16.00 0.95 0.02 0.01 74.10
dfc 0.00 0.00 0.00 52805.00 0.00 844832.00 16.00 1.32 0.02 0.02 89.90
dm-0 0.00 0.00 0.00 158143.00 0.00 2530120.00 16.00 3.72 0.02 0.01 99.50
number of transactions actually processed: 144901132
latency average: 2.381 ms
latency stddev: 1.571 ms
tps = 40248.632845 (including connections establishing)
tps = 40249.214064 (excluding connections establishing)
0.006825 \set nbranches 1 * :scale
0.002457 \set ntellers 10 * :scale
0.001856 \set naccounts 100000 * :scale
0.003474 \setrandom aid 1 :naccounts
0.002448 \setrandom bid 1 :nbranches
0.002864 \setrandom tid 1 :ntellers
0.003144 \setrandom delta -5000 5000
0.220207 begin;
0.372330 update pgbench_accounts set abalance = abalance + :delta where aid = :aid;
0.247961 select abalance from pgbench_accounts where aid = :aid;
0.301182 update pgbench_tellers set tbalance = tbalance + :delta where tid = :tid;
0.305694 update pgbench_branches set bbalance = bbalance + :delta where bid = :bid;
0.247623 insert into pgbench_history (tid, bid, aid, delta, mtime) values (:tid, :bid, :aid, :delta, current_timestamp);
0.645872 end;
tps曲線
小結
經過xfs優化，單次io響應延遲始終保持在0.01毫秒左右，性能表現平穩。
使用了9000個allocation group, fsync時，并行度更高，是以對tps影響更低了。