- 在应用负载不变的情况下可以通过如下方法进行WAL优化
1) 延长checkpoint时间间隔
FPI产生于checkpoint之后第一次变脏的page，在下次checkpoint到来之前，已经输出过FPI的page是不在需要再次输出FPI。因此checkpoint时间间隔越长，FPI产生的频度会越低。增大checkpoint_timeout和max_wal_size可以延长checkpoint时间间隔。
2) 增加hot_update比例
普通的update经常需要更新2个数据块，并且可能还需要更新索引page，这些又都有可能产生FPI。而hot_update只修改1个数据块，需要写的WAL量也大大减少，通过调整fillfactor可以达到目标
3) 压缩
PG9.5新增了一个wal_compression 参数，设置为on可以对FPI进行压缩，消减WAL的大小，另外还可以在外部通过ssl/ssh的压缩功能减少主备间的通信流量，已经自定义归档脚本对归档的wal进行压缩- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 验证
1) 初始化pgbench
-bash-4.2$ pgbench -i
Password:
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.13 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.42 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 0.21 s, vacuum 0.09 s, primary keys 0.09 s).
2) 验证--延长checkpoint
a. 第一次
#配置参数
max_connections = 3000
shared_buffers = 2048MB
max_wal_size = 1GB
min_wal_size = 100MB
wal_keep_size=5000
checkpoint_timeout = 5min
#生成数据
-bash-4.2$ psql -c "checkpoint;select pg_current_wal_lsn()"; pgbench -n -c 100 -j 100 -t 100; psql -c "select pg_current_wal_lsn()"
Password for user postgres:
CHECKPOINT
pg_current_wal_lsn
--------------------
0/A0001C0
(1 row)
Password:
pgbench (15.8)
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 347.779 ms
initial connection time = 273.044 ms
tps = 287.538702 (without initial connection time)
Password for user postgres:
pg_current_wal_lsn
--------------------
0/B3EC678
(1 row)
#解析WAL日志
-bash-4.2$ pg_waldump -t 1 --stats=record -s 0/A0001C0 -e 0/B3EC678
WAL statistics between 0/A0001C0 and 0/B3EC678:
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
Transaction/COMMIT 10003 ( 12.62) 340502 ( 6.44) 0 ( 0.00) 340502 ( 1.65)
Standby/RUNNING_XACTS 3 ( 0.00) 956 ( 0.02) 0 ( 0.00) 956 ( 0.00)
Standby/INVALIDATIONS 3 ( 0.00) 366 ( 0.01) 0 ( 0.00) 366 ( 0.00)
Heap2/PRUNE 3901 ( 4.92) 271240 ( 5.13) 0 ( 0.00) 271240 ( 1.31)
Heap2/VACUUM 2 ( 0.00) 118 ( 0.00) 0 ( 0.00) 118 ( 0.00)
Heap2/VISIBLE 3 ( 0.00) 192 ( 0.00) 98304 ( 0.64) 98496 ( 0.48)
Heap/INSERT 9905 ( 12.50) 786061 ( 14.86) 0 ( 0.00) 786061 ( 3.80)
Heap/UPDATE 3140 ( 3.96) 530419 ( 10.03) 7548 ( 0.05) 537967 ( 2.60)
Heap/HOT_UPDATE 26752 ( 33.76) 1932113 ( 36.53) 25988 ( 0.17) 1958101 ( 9.48)
Heap/LOCK 22033 ( 27.81) 1191797 ( 22.53) 13202280 ( 85.88) 14394077 ( 69.66)
Heap/INPLACE 8 ( 0.01) 1631 ( 0.03) 24716 ( 0.16) 26347 ( 0.13)
Heap/INSERT+INIT 101 ( 0.13) 7979 ( 0.15) 0 ( 0.00) 7979 ( 0.04)
Heap/UPDATE+INIT 115 ( 0.15) 18374 ( 0.35) 0 ( 0.00) 18374 ( 0.09)
Btree/INSERT_LEAF 3261 ( 4.12) 207923 ( 3.93) 2014420 ( 13.10) 2222343 ( 10.76)
Btree/VACUUM 2 ( 0.00) 122 ( 0.00) 0 ( 0.00) 122 ( 0.00)
-------- -------- -------- --------
Total 79232 5289793 [25.60%] 15373256 [74.40%] 20663049 [100%]
b. 第二次(调整参数后)
#配置参数
max_connections = 3000
shared_buffers = 2048MB
max_wal_size = 2GB
min_wal_size = 100MB
wal_keep_size=5000
checkpoint_timeout=30min
#生产数据
-bash-4.2$ psql -c "checkpoint;select pg_current_wal_lsn()"; pgbench -n -c 100 -j 100 -t 100; psql -c "select pg_current_wal_lsn()"
Password for user postgres:
CHECKPOINT
pg_current_wal_lsn
--------------------
0/C000188
(1 row)
Password:
pgbench (15.8)
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 341.077 ms
initial connection time = 165.900 ms
tps = 293.189074 (without initial connection time)
Password for user postgres:
pg_current_wal_lsn
--------------------
0/D3AA588
(1 row)
#解析WAL数据
-bash-4.2$ pg_waldump -t 1 --stats=record -s 0/C000188 -e 0/D3AA588
WAL statistics between 0/C000188 and 0/D3AA588:
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
Transaction/COMMIT 10003 ( 13.52) 340374 ( 7.26) 0 ( 0.00) 340374 ( 1.67)
Standby/RUNNING_XACTS 4 ( 0.01) 1269 ( 0.03) 0 ( 0.00) 1269 ( 0.01)
Standby/INVALIDATIONS 2 ( 0.00) 180 ( 0.00) 0 ( 0.00) 180 ( 0.00)
Heap2/PRUNE 4808 ( 6.50) 332597 ( 7.09) 12696384 ( 80.80) 13028981 ( 63.86)
Heap/INSERT 10000 ( 13.52) 787500 ( 16.79) 494892 ( 3.15) 1282392 ( 6.29)
Heap/UPDATE 267 ( 0.36) 41002 ( 0.87) 3720 ( 0.02) 44722 ( 0.22)
Heap/HOT_UPDATE 29733 ( 40.19) 2148707 ( 45.82) 607136 ( 3.86) 2755843 ( 13.51)
Heap/LOCK 18869 ( 25.51) 1018926 ( 21.73) 0 ( 0.00) 1018926 ( 4.99)
Heap/INPLACE 3 ( 0.00) 601 ( 0.01) 24716 ( 0.16) 25317 ( 0.12)
Heap/UPDATE+INIT 11 ( 0.01) 934 ( 0.02) 0 ( 0.00) 934 ( 0.00)
Btree/INSERT_LEAF 278 ( 0.38) 17088 ( 0.36) 1886160 ( 12.00) 1903248 ( 9.33)
-------- -------- -------- --------
Total 73978 4689178 [22.98%] 15713008 [77.02%] 20402186 [100%] #比20663049，WAL减少了
c. 第三次(不做checkpoint)
#配置参数
max_connections = 3000
shared_buffers = 2048MB
max_wal_size = 2GB
min_wal_size = 100MB
wal_keep_size=5000
checkpoint_timeout=30min
#生产数据
-bash-4.2$ psql -c "select pg_current_wal_lsn()"; pgbench -n -c 100 -j 100 -t 100; psql -c "select pg_current_wal_lsn()"
Password for user postgres:
pg_current_wal_lsn
--------------------
0/D3CB198
(1 row)
Password:
pgbench (15.8)
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 218.119 ms
initial connection time = 145.239 ms
tps = 458.464513 (without initial connection time)
Password for user postgres:
pg_current_wal_lsn
--------------------
0/D8AA9E0
(1 row)
#解析WAL日志
-bash-4.2$ pg_waldump -t 1 --stats=record -s 0/D3CB198 -e 0/D8AA9E0
WAL statistics between 0/D3CB198 and 0/D8AA9E0:
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
Transaction/COMMIT 10005 ( 13.46) 340714 ( 7.23) 0 ( 0.00) 340714 ( 6.90)
Standby/RUNNING_XACTS 2 ( 0.00) 655 ( 0.01) 0 ( 0.00) 655 ( 0.01)
Standby/INVALIDATIONS 2 ( 0.00) 228 ( 0.00) 0 ( 0.00) 228 ( 0.00)
Heap2/PRUNE 4639 ( 6.24) 319782 ( 6.78) 0 ( 0.00) 319782 ( 6.47)
Heap2/VACUUM 8 ( 0.01) 420 ( 0.01) 0 ( 0.00) 420 ( 0.01)
Heap2/VISIBLE 5 ( 0.01) 295 ( 0.01) 0 ( 0.00) 295 ( 0.01)
Heap/INSERT 10000 ( 13.46) 790000 ( 16.76) 0 ( 0.00) 790000 ( 15.99)
Heap/UPDATE 266 ( 0.36) 44061 ( 0.93) 21252 ( 9.44) 65313 ( 1.32)
Heap/HOT_UPDATE 29735 ( 40.01) 2152030 ( 45.65) 21000 ( 9.33) 2173030 ( 44.00)
Heap/LOCK 19341 ( 26.03) 1044414 ( 22.16) 0 ( 0.00) 1044414 ( 21.15)
Heap/INPLACE 7 ( 0.01) 1664 ( 0.04) 0 ( 0.00) 1664 ( 0.03)
Heap/UPDATE+INIT 16 ( 0.02) 1530 ( 0.03) 0 ( 0.00) 1530 ( 0.03)
Btree/INSERT_LEAF 282 ( 0.38) 17982 ( 0.38) 182900 ( 81.23) 200882 ( 4.07)
Btree/VACUUM 2 ( 0.00) 136 ( 0.00) 0 ( 0.00) 136 ( 0.00)
-------- -------- -------- --------
Total 74310 4713911 [95.44%] 225152 [4.56%] 4939063 [100%]
3) 验证--增加hot_update比例
a. 第一次(fillfactor为100)
#配置参数
max_connections = 3000
shared_buffers = 2048MB
max_wal_size = 2GB
min_wal_size = 100MB
wal_keep_size=5000
checkpoint_timeout=30min
#生产数据
-bash-4.2$ psql -c "checkpoint;select pg_current_wal_lsn()"; pgbench -n -c 100 -j 100 -t 100; psql -c "select pg_current_wal_lsn()"
Password for user postgres:
CHECKPOINT
pg_current_wal_lsn
--------------------
0/D8AFBB8
(1 row)
Password:
pgbench (15.8)
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 400.769 ms
initial connection time = 188.981 ms
tps = 249.520098 (without initial connection time)
Password for user postgres:
pg_current_wal_lsn
--------------------
0/EDBD258
(1 row)
#解析WAL数据
-bash-4.2$ pg_waldump -t 1 --stats=record -s 0/D8AFBB8 -e 0/EDBD258
WAL statistics between 0/D8AFBB8 and 0/EDBD258:
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
Transaction/COMMIT 10005 ( 13.47) 340714 ( 7.21) 0 ( 0.00) 340714 ( 1.56)
Storage/TRUNCATE 1 ( 0.00) 46 ( 0.00) 0 ( 0.00) 46 ( 0.00)
Standby/LOCK 1 ( 0.00) 42 ( 0.00) 0 ( 0.00) 42 ( 0.00)
Standby/RUNNING_XACTS 3 ( 0.00) 1263 ( 0.03) 0 ( 0.00) 1263 ( 0.01)
Standby/INVALIDATIONS 2 ( 0.00) 180 ( 0.00) 0 ( 0.00) 180 ( 0.00)
Heap2/PRUNE 4408 ( 5.94) 319323 ( 6.75) 12892876 ( 75.27) 13212199 ( 60.45)
Heap2/VACUUM 8 ( 0.01) 430 ( 0.01) 0 ( 0.00) 430 ( 0.00)
Heap2/VISIBLE 113 ( 0.15) 6682 ( 0.14) 98304 ( 0.57) 104986 ( 0.48)
Heap/INSERT 10000 ( 13.47) 787500 ( 16.66) 1534892 ( 8.96) 2322392 ( 10.63)
Heap/UPDATE 345 ( 0.46) 57061 ( 1.21) 0 ( 0.00) 57061 ( 0.26)
Heap/HOT_UPDATE 29644 ( 39.92) 2142423 ( 45.31) 591736 ( 3.45) 2734159 ( 12.51)
Heap/LOCK 19322 ( 26.02) 1043388 ( 22.07) 0 ( 0.00) 1043388 ( 4.77)
Heap/INPLACE 6 ( 0.01) 1210 ( 0.03) 24716 ( 0.14) 25926 ( 0.12)
Heap/UPDATE+INIT 28 ( 0.04) 4440 ( 0.09) 0 ( 0.00) 4440 ( 0.02)
Btree/INSERT_LEAF 373 ( 0.50) 23135 ( 0.49) 1986480 ( 11.60) 2009615 ( 9.19)
Btree/VACUUM 2 ( 0.00) 146 ( 0.00) 0 ( 0.00) 146 ( 0.00)
-------- -------- -------- --------
Total 74261 4727983 [21.63%] 17129004 [78.37%] 21856987 [100%]
b. 第二次(fillfactor调整为)
#配置参数
max_connections = 3000
shared_buffers = 2048MB
max_wal_size = 2GB
min_wal_size = 100MB
wal_keep_size=5000
checkpoint_timeout=30min
#调整表的fillfactor
alter table pgbench_accounts set (fillfactor=60);
alter table pgbench_branches set (fillfactor=60);
alter table pgbench_history set (fillfactor=60);
alter table pgbench_tellers set (fillfactor=60);
vacuum full pgbench_accounts;
vacuum full pgbench_branches;
vacuum full pgbench_history ;
vacuum full pgbench_tellers ;
#生成数据
-bash-4.2$ psql -c "checkpoint;select pg_current_wal_lsn()"; pgbench -n -c 100 -j 100 -t 100; psql -c "select pg_current_wal_lsn()"
Password for user postgres:
CHECKPOINT
pg_current_wal_lsn
--------------------
0/FE98040
(1 row)
Password:
pgbench (15.8)
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 413.325 ms
initial connection time = 234.007 ms
tps = 241.940584 (without initial connection time)
Password for user postgres:
pg_current_wal_lsn
--------------------
0/11001FE0
(1 row)
#解析WAL日志
-bash-4.2$ pg_waldump -t 1 --stats=record -s 0/FE98040 -e 0/11001FE0
WAL statistics between 0/FE98040 and 0/11001FE0:
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
Transaction/COMMIT 10001 ( 13.69) 340309 ( 7.38) 0 ( 0.00) 340309 ( 1.88)
Standby/RUNNING_XACTS 3 ( 0.00) 924 ( 0.02) 0 ( 0.00) 924 ( 0.01)
Heap2/PRUNE 4064 ( 5.56) 286153 ( 6.21) 30892 ( 0.23) 317045 ( 1.76)
Heap/INSERT 9900 ( 13.56) 782075 ( 16.97) 2052 ( 0.02) 784127 ( 4.34)
Heap/UPDATE 54 ( 0.07) 6566 ( 0.14) 19576 ( 0.15) 26142 ( 0.14)
Heap/HOT_UPDATE 29934 ( 40.99) 2158766 ( 46.84) 13306060 ( 98.96) 15464826 ( 85.65)
Heap/LOCK 18883 ( 25.86) 1019687 ( 22.12) 32624 ( 0.24) 1052311 ( 5.83)
Heap/INPLACE 3 ( 0.00) 294 ( 0.01) 46204 ( 0.34) 46498 ( 0.26)
Heap/INSERT+INIT 100 ( 0.14) 7900 ( 0.17) 0 ( 0.00) 7900 ( 0.04)
Heap/UPDATE+INIT 19 ( 0.03) 1598 ( 0.03) 0 ( 0.00) 1598 ( 0.01)
Btree/INSERT_LEAF 73 ( 0.10) 4639 ( 0.10) 9160 ( 0.07) 13799 ( 0.08)
-------- -------- -------- --------
Total 73034 4608911 [25.53%] 13446568 [74.47%] 18055479 [100%] #比21856987减少了
4) 验证--压缩
a. 第一次
#配置参数
max_connections = 3000
shared_buffers = 2048MB
max_wal_size = 2GB
min_wal_size = 100MB
wal_keep_size=5000
checkpoint_timeout=30min
#生成数据
同上，省略。。。
#解析WAL日志
同上，省略。。。
b. 第二次(调整压缩参数)
#配置参数
max_connections = 3000
shared_buffers = 2048MB
max_wal_size = 2GB
min_wal_size = 100MB
wal_keep_size=5000
checkpoint_timeout=30min
wal_compression=on
#生成数据
同上，省略。。。
#解析WAL日志
同上，省略。。。- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
- 118.
- 119.
- 120.
- 121.
- 122.
- 123.
- 124.
- 125.
- 126.
- 127.
- 128.
- 129.
- 130.
- 131.
- 132.
- 133.
- 134.
- 135.
- 136.
- 137.
- 138.
- 139.
- 140.
- 141.
- 142.
- 143.
- 144.
- 145.
- 146.
- 147.
- 148.
- 149.
- 150.
- 151.
- 152.
- 153.
- 154.
- 155.
- 156.
- 157.
- 158.
- 159.
- 160.
- 161.
- 162.
- 163.
- 164.
- 165.
- 166.
- 167.
- 168.
- 169.
- 170.
- 171.
- 172.
- 173.
- 174.
- 175.
- 176.
- 177.
- 178.
- 179.
- 180.
- 181.
- 182.
- 183.
- 184.
- 185.
- 186.
- 187.
- 188.
- 189.
- 190.
- 191.
- 192.
- 193.
- 194.
- 195.
- 196.
- 197.
- 198.
- 199.
- 200.
- 201.
- 202.
- 203.
- 204.
- 205.
- 206.
- 207.
- 208.
- 209.
- 210.
- 211.
- 212.
- 213.
- 214.
- 215.
- 216.
- 217.
- 218.
- 219.
- 220.
- 221.
- 222.
- 223.
- 224.
- 225.
- 226.
- 227.
- 228.
- 229.
- 230.
- 231.
- 232.
- 233.
- 234.
- 235.
- 236.
- 237.
- 238.
- 239.
- 240.
- 241.
- 242.
- 243.
- 244.
- 245.
- 246.
- 247.
- 248.
- 249.
- 250.
- 251.
- 252.
- 253.
- 254.
- 255.
- 256.
- 257.
- 258.
- 259.
- 260.
- 261.
- 262.
- 263.
- 264.
- 265.
- 266.
- 267.
- 268.
- 269.
- 270.
- 271.
- 272.
- 273.
- 274.
- 275.
- 276.
- 277.
- 278.
- 279.
- 280.
- 281.
- 282.
- 283.
- 284.
- 285.
- 286.
- 287.
- 288.
- 289.
- 290.
- 291.
- 292.
- 293.
- 294.
- 295.
- 296.
- 297.
- 298.
- 299.
- 300.
- 301.
- 302.
- 303.
- 304.
- 305.
- 306.
- 307.
- 308.
- 309.
- 310.
- 311.
- 312.
- 313.
- 314.
- 315.
- 316.
- 317.
- 318.
- 319.
- 320.
- 321.
- 322.
- 323.
- 324.
- 325.
- 326.
- 327.
- 328.
- 329.
- 330.
- 331.
- 332.
- 333.
- 334.
- 335.
- 336.
- 337.
- 338.
- 339.
- 340.
- 341.
- 342.
- 343.
- 344.
- 345.
- 346.
- 347.
- 348.
- 349.
- 350.
- 351.
- 352.
- 353.
- 优化的副作用
1) 延长checkpoint时间间隔的副作用
导致crash恢复时间边长，crash恢复时需要回放的WAL日志量一般小于max_wal_size的一半，WAL的回放速度(wal_compression=on时)一般是50MB/s~150MB/s之间，可以根据可容忍的最大crash恢复时间，估算出允许的max_wal_size的最大值
2) 调整fillfactor的副作用
过小的设置会浪费存储空间，另外对于频繁更新的表，即使把fillfactor设成100%，每个page里还是要一部分空间被dead tuple占据，不会比设置成一个合适的稍小的fillfactor更节省空间。
3) 设置wal_compression=on的副作用
需要额外占用CPU的资源进行叶索，但影响不大- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
扫一扫
1488
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
