--checkpoint各参数的值
checkpoint_segments 最多的wal log数量，到达后会激发checkpoint，通常设定在30就好
checkpoint_timeout 一般设置15-20分钟，常的可以设定1天也没关系
checkpoint_completion_target 这个保持不动就好。内建是0.5，意思就是每个checkpoint预计在下个checkpoint完成前的一半时间内完成（听起来有点绕嘴，呵呵）
checkpoint_warning 如果checkpint速度快于这个时间，在log中记录。内建是30秒
select name,setting,context,vartype,source from pg_settings where name like 'checkpoint%';
name | setting | context | vartype | source
------------------------------+---------+---------+---------+--------------------
checkpoint_completion_target | 0.5 | sighup | real | default
checkpoint_segments | 16 | sighup | integer | configuration file
checkpoint_timeout | 300 | sighup | integer | default
checkpoint_warning | 30 | sighup | integer | default
--新增测试表和函数.
create table test (id int primary key, info text, crt_time timestamp);
create or replace function f_id_add(i_id int) returns void as $$
declare
begin
update test set crt_time=clock_timestamp() where id=i_id;
if not found then
insert into test values (i_id,md5(random()::text),clock_timestamp());
return;
else
return;
end if;
return;
exception when others then
return;
end;
$$ language plpgsql strict;
--测试脚本
vi test.sql
\setrandom id 1 5000000
select * from f_id_add(:id);
--测试
pgbench -M prepared -f ./test.sql -n -r -c 6 -j 6 -T 600
checkpoint_segments=2
[postgres@rudy ~]$ pgbench -M prepared -f ./test.sql -n -r -c 6 -j 6 -T 600
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 6
number of threads: 6
duration: 600 s
number of transactions actually processed: 984350
tps = 1637.193339 (including connections establishing)
tps = 1648.603223 (excluding connections establishing)
--可以看到tps为1600左右，且系统中有大量的磁盘IO,每隔几秒
----total-cpu-usage---- -dsk/total- ------memory-usage----- ---paging-- -net/total- ----swap--- ---system-- --io/total- ---file-locks-- --sysv-ipc-
usr sys idl wai hiq siq| read writ| used buff cach free| in out | recv send| used free| int csw | read writ|pos lck rea wri|msg sem shm
1 0 0 99 0 0| 0 2440k| 320M 3236k 596M 70.5M| 0 0 | 618B 824B| 624M 3376M| 399 400 | 0 236 |227 5.0 0 232| 0 69 18
0 1 0 98 0 1| 220k 2824k| 320M 3236k 596M 70.2M| 0 0 | 0 0 | 624M 3376M| 441 426 |12.0 266 |227 5.0 0 232| 0 69 18
1 1 0 98 0 0| 208k 3696k| 319M 3236k 596M 70.2M| 0 0 | 60B 1174B| 624M 3376M| 450 429 |3.00 321 |227 5.0 0 232| 0 69 18
5 6 0 90 0 0| 68k 7928k| 319M 3276k 597M 68.9M| 0 0 | 120B 844B| 624M 3376M| 780 3258 |5.00 584 |227 5.0 0 232| 0 69 18
30 44 0 23 1 1| 0 15M| 319M 3276k 604M 61.8M| 0 0 | 60B 678B| 624M 3376M|1873 22k| 0 1196 |227 5.0 0 232| 0 69 18
25 48 0 25 3 0| 0 13M| 319M 3268k 599M 68.0M| 0 492k| 60B 662B| 625M 3375M|1901 23k| 0 1214 |227 5.0 0 232| 0 69 18
35 39 0 22 3 1| 128k 12M| 318M 3280k 598M 69.8M| 0 788k| 60B 662B| 626M 3374M|1716 19k|1.00 1040 |227 5.0 0 232| 0 69 18
51 29 0 19 0 1| 0 34M| 317M 3280k 599M 69.4M| 0 108k| 60B 678B| 626M 3374M|1649 17k| 0 967 |227 5.0 0 232| 0 69 18
19 17 0 58 2 4|4096B 32M| 318M 3284k 603M 64.7M| 0 1300k| 60B 662B| 627M 3373M|1328 8586 |1.00 898 |227 5.0 0 232| 0 69 18
9 18 0 64 0 9|4096B 17M| 317M 3288k 598M 70.6M| 0 0 | 60B 678B| 627M 3373M| 97 185 |1.00 100 |227 5.0 0 232| 0 69 18
1 1 0 97 0 0| 0 4780k| 317M 3292k 598M 70.6M| 0 0 | 60B 678B| 627M 3373M| 515 423 | 0 443 |227 5.0 0 232| 0 69 18
checkpoint_segments=16
[postgres@rudy ~]$ pgbench -M prepared -f ./test.sql -n -r -c 6 -j 6 -T 600
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 6
number of threads: 6
duration: 600 s
number of transactions actually processed: 1333210
tps = 2221.020258 (including connections establishing)
tps = 2221.203625 (excluding connections establishing)
--可以看到tps为2200左右，且系统中磁盘io没有之前频繁
----total-cpu-usage---- -dsk/total- ------memory-usage----- ---paging-- -net/total- ----swap--- ---system-- --io/total- ---file-locks-- --sysv-ipc-
usr sys idl wai hiq siq| read writ| used buff cach free| in out | recv send| used free| int csw | read writ|pos lck rea wri|msg sem shm
22 52 0 25 1 0| 24k 9024k| 259M 3032k 658M 68.9M| 0 0 | 60B 828B| 682M 3318M|1887 22k|3.00 1180 |227 5.0 0 232| 0 69 18
42 32 0 22 3 1|8192B 9024k| 259M 3032k 659M 67.7M| 0 0 | 120B 1756B| 682M 3318M|1942 23k|1.00 1169 |227 5.0 0 232| 0 69 18
46 29 0 24 1 0| 200k 8600k| 259M 3032k 660M 66.4M| 0 0 | 60B 678B| 682M 3318M|1942 21k|9.00 1122 |227 5.0 0 232| 0 69 18
32 45 0 22 1 0| 0 9224k| 259M 3032k 662M 65.2M| 0 0 | 60B 678B| 682M 3318M|1955 23k| 0 1198 |227 5.0 0 232| 0 69 18
21 51 0 27 0 1| 84k 8768k| 259M 3032k 663M 64.0M| 0 0 | 60B 662B| 682M 3318M|1876 22k|9.00 1153 |227 5.0 0 232| 0 69 18
12 31 0 54 1 1|8192B 28M| 260M 3032k 663M 62.5M| 0 0 | 60B 678B| 682M 3318M|1250 12k|1.00 800 |227 5.0 0 232| 0 69 18
1 2 0 94 1 1| 0 14M| 260M 3032k 663M 62.5M| 0 0 | 60B 678B| 682M 3318M| 754 570 | 0 542 |227 5.0 0 232| 0 69 18
1 1 0 98 0 0| 0 6920k| 260M 3032k 663M 62.5M| 0 0 | 60B 812B| 682M 3318M| 566 518 | 0 373 |227 5.0 0 232| 0 69 18
1 0 0 98 0 1| 0 6088k| 260M 3032k 663M 62.5M| 0 0 | 60B 662B| 682M 3318M| 546 486 | 0 348 |227 5.0 0 232| 0 69 18
1 0 0 99 0 0| 0 8032k| 260M 3032k 663M 62.5M| 0 0 | 60B 662B| 682M 3318M| 664 532 | 0 448 |227 5.0 0 232| 0 69 18
1 1 0 98 0 0| 0 4952k| 260M 3032k 664M 62.4M| 0 0 | 120B 704B| 682M 3318M| 535 540 | 0 359 |227 5.0 0 232| 0 69 18
1 1 0 98 0 0| 0 6596k| 260M 3032k 664M 62.5M| 0 0 | 60B 662B| 682M 3318M| 651 511 | 0 499 |227 5.0 0 232| 0 69 18
1 1 0 97 1 0| 0 6968k| 260M 3032k 664M 62.5M| 0 0 | 60B 662B| 682M 3318M| 677 575 | 0 543 |227 5.0 0 232| 0 69 18
3 6 0 90 0 1| 16k 8852k| 260M 3048k 664M 62.4M| 0 0 | 60B 662B| 682M 3318M| 902 2627 |2.00 697 |227 5.0 0 232| 0 69 18
20 60 0 17 2 2|8192B 9968k| 260M 3048k 665M 61.1M| 0 0 | 60B 678B| 682M 3318M|2019 24k|1.00 1309 |227 5.0 0 232| 0 69 18
22 43 0 31 3 0| 0 8632k| 260M 3048k 666M 60.0M| 0 0 | 60B 678B| 682M 3318M|1774 22k| 0 1117 |227 5.0 0 232| 0 69 18
53 32 0 13 1 1| 0 10M| 259M 3044k 656M 69.9M| 0 0 | 60B 662B| 682M 3318M|2251 25k| 0 1331 |227 5.0 0 232| 0 69 18
42 42 0 15 1 0| 0 9808k| 259M 3044k 658M 69.2M| 0 0 | 60B 662B| 682M 3318M|2137 24k| 0 1292 |227 5.0 0 232| 0 69 18
--checkpint信息查看
SELECT (100 * checkpoints_req) / (checkpoints_timed + checkpoints_req) AS checkpoints_req_pct,
pg_size_pretty(buffers_checkpoint * block_size / (checkpoints_timed + checkpoints_req)) AS avg_checkpoint_write,
pg_size_pretty(block_size * (buffers_checkpoint + buffers_clean + buffers_backend)) AS total_written,
100 * buffers_checkpoint / (buffers_checkpoint + buffers_clean + buffers_backend) AS checkpoint_write_pct,
100 * buffers_backend / (buffers_checkpoint + buffers_clean + buffers_backend) AS backend_write_pct,
*
FROM pg_stat_bgwriter,
(SELECT cast(current_setting('block_size') AS integer) AS block_size) AS bs;