本文主要介绍并测试一下PostgreSQL 在中高端x86服务器上的数据插入速度，帮助企业用户了解PostgreSQL在这种纯插入场景的性能。
（例如运营商网关数据，金融行业数据，产生量大，并且要求快速插入大数据库中持久化保存。）
另外, 用户如果需要流式实时处理，可以参考基于PostgreSQL的流式处理方案，一天处理1万亿的实时流式处理是如何实现的？
测试结果写在前面：
.1.平均每条记录长度约360字节。
.2.测试十轮，每轮持续100秒，每轮测试的结果在1200万行每秒以上，约1210万左右。
.3.每秒约入库1200 万条记录，相当于4.2GB数据。
.4.换算成天，入库 10368 亿记录，相当于363TB数据。
测试的硬件环境
.1. X86服务器
.2. 3?核。
.3. 5??G 内存
.4. 几块SSD，15TB容量
软件环境
.1. CENTOS 6.x x64
.2 .xfs
.3. PostgreSQL 9.5
系统配置参考
https://github.com/digoal/pgsql_admin_script/blob/master/pgsql_perf_tuning.md
数据库配置
./configure --prefix=/home/digoal/pgsql9.5.1 --with-blocksize=32 --with-segsize=128 --with-wal-blocksize=32 --with-wal-segsize=64
make && make install
PostgreSQL支持hugepage的方法请参考：
参数
listen_addresses = '0.0.0.0' # what IP address(es) to listen on;
fsync=on
port = 1921 # (change requires restart)
max_connections = 600 # (change requires restart)
superuser_reserved_connections = 13 # (change requires restart)
unix_socket_directories = '.' # comma-separated list of directories
unix_socket_permissions = 0700 # begin with 0 to use octal notation
tcp_keepalives_idle = 60 # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10 # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 10 # TCP_KEEPCNT;
shared_buffers = 256GB # min 128kB
huge_pages = on # on, off, or try
work_mem = 512MB # min 64kB
maintenance_work_mem = 1GB # min 1MB
autovacuum_work_mem = 1GB # min 1MB, or -1 to use maintenance_work_mem
dynamic_shared_memory_type = posix # the default is the first option
bgwriter_delay = 10ms # 10-10000ms between rounds
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round
bgwriter_lru_multiplier = 2.0
synchronous_commit = off # synchronization level;
full_page_writes = on # recover from partial page writes
wal_buffers = 2047MB # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms # 1-10000 milliseconds
checkpoint_timeout = 55min # range 30s-1h
max_wal_size = 512GB
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
effective_cache_size = 40GB
log_destination = 'csvlog' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0600 # creation mode for log files,
log_truncate_on_rotation = on # If on, an existing log file with the
log_checkpoints = off
log_connections = off
log_disconnections = off
log_error_verbosity = verbose # terse, default, or verbose messages
log_timezone = 'PRC'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
autovacuum=off
测试方法, 并发插入数据测试，每条记录约360字节。
postgres=# select string_agg(i,'') from (select md5(random()::text) i from generate_series(1,10) t(i)) t(i);
string_agg
----------------------------------------------------------------------
75feba6d5ca9ff65d09af35a67fe962a4e3fa5ef279f94df6696bee65f4529a4bbb03ae56c3b5b86c22b447fc48da894740ed1a9d518a9646b3a751a57acaca1142ccfc945b1082b40043e3f83f8b7605b5a55fcd7eb8fc1d0475c7fe465477da47d96957849327731ae76322f440d167725d2e2bbb60313150a4f69d9a8c9e86f9d79a742e7a35bf159f670e54413fb89ff81b8e5e8ab215c3ddfd00bb6aeb4
(1 row)
create unlogged table test(crt_time timestamp, info text default '75feba6d5ca9ff65d09af35a67fe962a4e3fa5ef279f94df6696bee65f4529a4bbb03ae56c3b5b86c22b447fc48da894740ed1a9d518a9646b3a751a57acaca1142ccfc945b1082b40043e3f83f8b7605b5a55fcd7eb8fc1d0475c7fe465477da47d96957849327731ae76322f440d167725d2e2bbb60313150a4f69d9a8c9e86f9d79a742e7a35bf159f670e54413fb89ff81b8e5e8ab215c3ddfd00bb6aeb4');
alter table test alter column info set storage plain;
postgres=# insert into test select now() from generate_series(1,1000);
select ctid from test limit 1000;
每32K的block存储89条记录, 每条记录约360字节。
分别在3个物理块设备上创建3个表空间目录，同时在数据库中创建表空间。
tbs1, tbs2, tbs3.
创建多个分表，用于减少测试时block extend 冲突。
do language plpgsql $$
declare
i int;
sql text;
begin
for i in 1..128 loop
sql := 'create unlogged table test'||i||' (like test including all)';
execute sql;
end loop;
end;
$$;
do language plpgsql $$
declare
i int;
sql text;
begin
for i in 1..42 loop
sql := 'alter table test'||i||' set tablespace tbs1';
execute sql;
end loop;
for i in 43..84 loop
sql := 'alter table test'||i||' set tablespace tbs2';
execute sql;
end loop;
for i in 85..128 loop
sql := 'alter table test'||i||' set tablespace tbs3';
execute sql;
end loop;
end;
$$;
生成测试脚本:
vi test.sql
insert into test(crt_time) values (now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now());
for ((i=1;i<=192;i++)) do sed "s/test/test$i/" test.sql > ./test$i.sql; done
每次测试前清除数据：
do language plpgsql $$
declare
i int;
sql text;
begin
for i in 1..128 loop
sql := 'truncate test'||i;
execute sql;
end loop;
end;
$$;
测试，使用128个并行。
for ((i=1;i<=128;i++)) do pgbench -M prepared -n -r -P 1 -f ./test$i.sql -h xxx.xxx.xxx.xxx -p 1921 -U postgres postgres -c 1 -j 1 -T 100 >./$i.log 2>&1 & done
测试了十几轮，每轮100秒，每轮测试的结果在1200万行每秒以上，约1210万左右。
取测试结果的方法，以下的SUM值就是qps，每条QUERY插入100条记录。
for ((i=1;i<=128;i++)) do tail -n 4 $i.log|head -n 1|awk '{print $3}'; done
每秒约插入1200万条记录，相当于4.2GB数据。
换算成天，一天入库10368亿记录，相当于363TB数据。
小结
1. 这个CASE主要的应用场景是实时的大数据入库，例如 物联网 的应用场景，大量的 传感器 会产生庞大的数据。又比如传统的 运营商网关 ，也会有非常庞大的流量数据或业务数据需要实时的入库。
除了实时入库，用户如果需要流式实时处理，可以参考基于PostgreSQL的流式处理方案，一天处理1万亿的实时流式处理是如何实现的？
其他
此次测试发现， 这种硬件环境，1200万每秒远没有达到上限， 还有非常大的性能提升空间：
.1. 测试过程中，通过perf观察到系统调用的spin lock较高，有内核代码优化空间。
115092.00 43.0% mutex_spin_on_owner [kernel.kallsyms]
10658.00 4.0% _spin_lock [kernel.kallsyms]
9161.00 3.4% __mutex_lock_slowpath [kernel.kallsyms]
7304.00 2.7% __memset_sse2 /lib64/libc-2.12.so
4994.00 1.9% put_into_wait_copy_list [xxxxxxxx]
4448.00 1.7% LWLockAcquire /home/digoal/pgsql9.5.1/bin/postgres
3861.00 1.4% clear_page_c_e [kernel.kallsyms]
3759.00 1.4% copy_user_generic_string [kernel.kallsyms]
.2. PG仅仅占用40%左右的CPU ， 系统占用30%左右CPU ， 还有30%左右的CPU空闲。
.3. 数据库刚启动时，shared buffer hash table还没有初始化好， mutex lock较多，如果你用TOP会发现有大量进程处于D状态 。
w: S -- Process Status
The status of the task which can be one of:
’D’ = uninterruptible sleep
’R’ = running
’S’ = sleeping
’T’ = traced or stopped
’Z’ = zombie
代码的性能优化不在此展开，有兴趣的朋友可以自己去玩一下看看。
有几个工具你可能用得上，perf, systemtap, goprof.
如果要较全面的分析，建议把--enable-profiling打开用于诊断。
长时间测试(数据下午补上)：
.1.测试24轮，每轮持续1000秒，每轮测试的结果在760万行每秒以上，约765万左右。
#!/bin/bash
clean() {
sleep 610
psql -h xxx.xxx.xxx.xxx -p 1921 -U postgres postgres <<EOF
do language plpgsql \$\$
declare
i int;
sql text;
begin
for i in 1..128 loop
sql := 'truncate test'||i;
execute sql;
end loop;
end;
\$\$;
checkpoint;
EOF
}
for ((x=1;x<=24;x++))
do
for ((i=1;i<=128;i++)) do pgbench -M prepared -n -r -f ./test$i.sql -h xxx.xxx.xxx.xxx -p 1921 -U postgres postgres -c 1 -j 1 -T 1000 >>./$i.log 2>&1 & done
clean
done
.2.每秒约入库760万条记录，相当于2.7GB数据。
.3.换算成天，入库 6566 亿记录，相当于233TB数据。
时间： 2024-12-21 09:13:22