PgSQL · 性能优化 · 如何潇洒的处理每天上百TB的数据增量

本文主要介绍postgresql 在中高端x86服务器上的数据插入速度（目标表包含一个时间字段的索引），帮助企业用户了解postgresql在这类场景下的性能表现。

平均每条记录长度360字节（比较常见的长度）；

时间字段创建索引；

每轮测试插入12tb数据，插入完12t后清除数据继续插入。循环；

测试满24小时停止测试；

统计24小时插入的记录数；

24小时一共完成12轮测试，平均每轮测试耗时7071秒。

506万行/s，1.78 gb/s，全天插入4372亿，154tb数据。

<a href="https://github.com/digoal/pgsql_admin_script/blob/master/pgsql_perf_tuning.md" target="_blank">pgsql_perf_tuning</a>

<a href="https://yq.aliyun.com/articles/8482" target="_blank">postgresql支持hugepage的方法</a>

参数

创建测试表 :

每32k的block存储89条记录, 每条记录360字节。

分别在3个物理块设备上创建3个表空间目录，同时在数据库中创建表空间。

tbs1, tbs2, tbs3.

创建多个分表，用于减少 block extend 冲突。

这里使用的是brin范围索引，postgresql 针对物联网流式数据的黑科技。

生成测试脚本, 一个连接一次插入178条记录，占用2个32kb的block :

开始测试前清除数据：

测试方法：

每轮测试插入12tb数据。通过以下方式控制：

使用128个并行连接，每个连接执行1572864个事务；

一共执行201326592个事务（每个事务插入178条记录）；

一共插入35836133376条记录（358.36 亿记录）（共计12tb 数据，索引空间另算）。

进行下一轮测试前，输出日志，并truncate所有的数据，然后重复以上测试。直到测试满24小时，输出统计数据。

测试脚本如下 :

测试

这个case主要的应用场景是实时的大数据入库，例如物联网的应用场景，大量的传感器会产生庞大的数据。

又比如传统的运营商网关，也会有非常庞大的流量数据或业务数据需要实时的入库。索引方面，用到了postgresql黑科技brin。

瓶颈, 还是在io上面 , 有几个表现，top大量进程处于d(front io)状态 。

所有块设备的使用率均达100% 。

清理数据时 ：

插入数据时：

io层面的性能问题，可以通过优化代码（例如 postgresql bgwriter 在写出数据时，尽量顺序写出），便于os层进行io合并，来缓解io压力，从这个信息来看，单次写io的大小还可以再大点。

有几个工具你可能用得上，perf、systemtap和goprof。如果要较全面的分析，建议把 postgresql –enable-profiling 打开用于诊断。