本文主要介绍并测试一下PostgreSQL 在中高端x86服务器上的数据插入速度，帮助企业用户了解PostgreSQL在这种纯插入场景的性能。
（例如运营商网关数据，金融行业数据，产生量大，并且要求快速插入大数据库中持久化保存。）
另外, 用户如果需要流式实时处理，可以参考基于PostgreSQL的流式处理方案，一天处理1万亿的实时流式处理是如何实现的？
https://yq.aliyun.com/articles/166
测试结果写在前面：
.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的方法请参考：
https://yq.aliyun.com/articles/8482
参数
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万亿的实时流式处理是如何实现的？
https://yq.aliyun.com/articles/166
其他
此次测试发现， 这种硬件环境，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数据。
