--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
checkpoint性能测试
最新推荐文章于 2023-05-04 19:36:16 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)