摘要：本文主要介绍并测试一下Postgresql 在中高端x86服务器上的数据插入速度，帮助企业用户了解Postgresql在这种纯插入场景的性能。（例如运营商网关数据，金融行业数据，产生量大，并且要求快速插入大数据库中持久化保存。） 测试结果写在前面：每32K的block存储89条记录,每条记录约3
本文主要介绍并测试Postgresql 在中高端x86服务器上的数据插入速度（目标表包含一个时间字段的索引），帮助企业用户了解Postgresql在这类场景下的性能表现。
这类场景常见于 : 运营商网关数据，金融行业数据，产生量大，并且要求快速插入大数据库中持久化保存。
另外,用户如果需要流式实时处理，可以参考基于Postgresql的流式处理方案，一天处理1万亿的实时流式处理是如何实现的？
https://yq.aliyun.com/articles/166
TEST CASE
.1. 平均每条记录长度360字节， (比较常见的长度)
.2. 时间字段创建索引。
.3. 每轮测试插入12TB数据，插入完12T后清除数据继续插入。循环。
.4. 测试满24小时停止测试。
.5. 统计24小时插入的记录数。
TEST 结果
24小时一共完成12轮测试，平均每轮测试耗时7071秒。
506万行/s，1.78 GB/s，全天插入4372亿，154TB数据。
测试的硬件环境
.1. X86服务器
.2. 3?核。
.3. 5??G 内存
.4. 几块SSD，15TB容量
软件环境
@H_404_44@.1. CENTOS 6.x x64 .2 .xfs .3. Postgresql 9.5系统配置参考
https://github.com/digoal/pgsql_admin_script/blob/master/pgsql_perf_tuning.md
数据库配置
@H_404_44@./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
参数
创建测试表 :
每32K的block存储89条记录,每条记录360字节。
分别在3个物理块设备上创建3个表空间目录，同时在数据库中创建表空间。
tbs1,tbs2,tbs3.
创建多个分表，用于减少 block extend 冲突。
@H_404_44@do language plpgsql $$ declare i int; sql text; begin for i in 1..42 loop sql := 'create unlogged table test'||i||' (like test including all) tablespace tbs1'; execute sql; sql := 'create index idx_test'||i||' on testusing brin (crt_time) with (pages_per_range=512) tablespace tbs1'; execute sql; end loop; for i in 43..84 loop sql := 'create unlogged table '||i||' (like test including all) tablespace tbs2'; execute sql; sql := ''; execute sql; end loop; for i in 85..128 loop sql := 'tablespace tbs3'; execute sql; end loop; end; $$;又见黑科技 BRIN 索引方法
这里使用的是brin范围索引，Postgresql 针对物联网流式数据的黑科技。
@H_404_44@postgres=# \di List of relations Schema | Name | Type | Owner | Table --------+-------------+-------+----------+--------- public | idx_test1 | index | postgres | test1 public | idx_test10 | test10 public | idx_test100 | test100 public | idx_test101 | test101 public | idx_test102 | test102 public | idx_test103 | test103 public | idx_test104 | test104 public | idx_test105 | test105 public | idx_test106 | test106 ...... ...... public | idx_test90 | test90 public | idx_test91 | test91 public | idx_test92 | test92 public | idx_test93 | test93 public | idx_test94 | test94 public | idx_test95 | test95 public | idx_test96 | test96 public | idx_test97 | test97 public | idx_test98 | test98 public | idx_test99 | test99 (128 rows)生成测试脚本,一个连接一次插入178条记录，占用2个32KB的block :
@H_404_44@vi test.sql insert into test(crt_time) values (now()),(now()); for ((i=1;i<=128;i++)) do sed "s/test/test$i/" test.sql > ./test$i.sql; done开始测试前清除数据：
language plpgsql $$
.128 'truncate test'||i;
sql;
end loop;
end;
$$;
测试方法：
每轮测试插入12TB数据。通过以下方式控制：
.1. 使用128个并行连接，每个连接执行1572864个事务。
.2. 一共执行201326592个事务（每个事务插入178条记录）。
.3. 一共插入35836133376条记录（358.36 亿记录）（共计12TB 数据，索引空间另算）。
进行下一轮测试前，输出日志，并TRUNCATE所有的数据，然后重复以上测试。
直到测试满24小时，输出统计数据。
测试脚本如下 :
测试
@H_404_44@nohup ./test.sh xxx.xxx.xxx.xxx 1921 postgres postgres postgres >./test.log 2>&1 &测试结果
24小时完成12轮测试，平均每轮测试耗时7071秒。
506万行/s（每行360字节），1.78GB/s，全天插入4372亿，154TB数据。
查询性能
@H_404_44@postgres=# select min(crt_time),max(crt_time) from test1; min | max ----------------------------+---------------------------- 2016-04-08 00:32:26.842728 | 2016-08 02:29:41.583367 (1 row) postgres=# explain select count(*) from test1 where crt_time between '2016-08 00:32:00' and '2016-08 00:33:00'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=1183919.81..1183919.82 rows=1 width=0) -> Bitmap Heap Scan on test1 (cost=14351.45..1180420.19 rows=1399849 width=0) Recheck Cond: ((crt_time >= '2016-08 00:32:00'::timestamp without time zone) AND (crt_time <= '2016-08 00:33:00'::timestamp without time zone)) -> Bitmap Index Scan on idx_test1 (cost=0.00..14001.49 rows=1399849 width=0) Index Cond: ((crt_time >= '2016-08 00:33:00'::timestamp without time zone)) (5 rows) Time: 0.382 ms postgres=# select count(*) from test1 where crt_time between '2016-08 00:33:00'; count --------- 2857968 (1 row) Time: 554.474 ms小结
.1. 这个CASE主要的应用场景是实时的大数据入库，例如 物联网 的应用场景，大量的 传感器 会产生庞大的数据。
又比如传统的 运营商网关 ，也会有非常庞大的流量数据或业务数据需要实时的入库。
索引方面，用到了Postgresql黑科技BRIN。
.2. 除了实时入库，用户如果需要流式实时处理，可以参考基于Postgresql的流式处理方案，
一天处理1万亿的实时流式处理是如何实现的？
https://yq.aliyun.com/articles/166
.3. 瓶颈,还是在IO上面,有几个表现，TOP大量进程处于D(front io)状态 。
@H_404_44@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 所有块设备的使用率均达100% 。
清理数据时 ：
插入数据时 ：
000.00 235936.00 1887488.8.00 2676.34 11.17 99.10 dfb 0.00 237621.00 1900968.66.10 10 dfc 0.00 239830.00 1918632.10.66 04 0.00 101.30
IO层面的性能问题，可以通过优化代码（例如 Postgresql bgwriter 在写出数据时，尽量顺序写出），便于OS层进行IO合并，来缓解IO压力，从这个信息来看，单次写IO的大小还可以再大点。
有几个工具你可能用得上，perf,systemtap,goprof. 如果要较全面的分析，建议把Postgresql --enable-profiling打开用于诊断。
