3年部署3000套PG实例的架构设计与踩坑经验

https://mp.weixin.qq.com/s/fvAd5PdeDHCyC1mUXNKXhA

陈华军老师的博文，欢迎点击上面的原文地址。

作者介绍

陈华军 十年以上数据库相关工作经验。PostgreSQL中文社区核心组成员，主要负责PostgreSQL中文手册翻译项目的维护。

PostgreSQL作为一款许可开放，功能齐备的开源关系数据库，在当前提倡自主可控的大背景下，正受到越来越多企业的重视。苏宁从2017年开始引入PostgreSQL，到2019年双11前3年间已上线3000多PostgreSQL实例，运行在我司各个不同的业务线。我们用PostgreSQL替换掉大量的商业数据库，不仅为公司节省了很多成本，而且通过灵活运用PostgreSQL的特色功能，甚至大大提升了业务的使用体验。

本次分享主要介绍苏宁引入PostgreSQL的背景和历程，以及我们在实际使用PostgreSQL中积累的一些经验。

一、背景
早期苏宁的数据库全部采用商业数据库。2013年，我们引入了MySQL。到了2016年，MySQL已经被大量使用，但是，核心业务仍然依靠商业数据库承载。当时我们意识到继续依赖国外商业数据库，除了每年需要支付高昂的许可和维保成本，对我们提升自身的数据库运维水平更好地支撑业务发展也存在诸多的弊端。因此我们在2016年启动了去商业数据库的调研工作，并于2017年开始逐步推进。

通过摸底现有业务数据库的使用情况，我们发现当时已大量使用的MySQL在功能和性能上离成熟商业数据库都有一定的差距，在某些场景下难以替代商业数据库，特别是需要执行一些复杂SQL的场景。因此我们在现有MySQL的基础上又考察对比了另一款非常流行的开源数据库PostgreSQL。

二、数据库选型

PostgreSQL和MySQL最大的差异可以概况为两点。第一，在使用场景上，MySQL支持的SQL特性比较少，在大数据量和复杂查询下性能也容易出问题，适合比较简单的OLTP业务；第二，PostgreSQL的功能更加全面，可同时支持OLTP和OLAP类的业务，和Oracle、DB2这样的商业数据库更加接近。

特性对比

详细的一些特性对比可参考下面的表格。


注1：参考 http://www.sql-workbench.net/dbms_comparison.html

以上对比基于MySQL 5.7和PostgreSQL 10。MySQL 8.0在SQL特性上有非常明显的提升，比如DDL事务一致性保证，支持窗口函数和CTE。

性能对比

通过实际的测试对比，我们发现PostgreSQL在某些场景下比MySQL有明显的优势：

1、sysbench高并发主键查询测试，PostgreSQL和MySQL性能相当；

2、sysbench高并发纯写入测试，PostgreSQL的性能是MySQL的4倍。测试MySQL时发现系统过早触及IO瓶颈，可能和MySQL的SQL加存储的两层架构导致IO放大有关；

3、TPCH测试，PostgreSQL性能比MySQL高一个数量级。这个测试做的比较早，使用的MySQL和PostgreSQL数据库的版本都比较低，MySQL使用的是5.6，PostgreSQL使用的是9.5。当时MySQL 5.6中有一个SQL跑了2天都没执行完，还有一个SQL执行的结果是错误的。相信新版的MySQL和PostgreSQL上的性能都会有所提升，而且新版的MySQL上不应该还存这个Bug。

OLAP测试，如下图：

可靠性对比

在可靠性方面，根据我们的测试和实际的生产运行，发现PostgreSQL也优于MySQL。

1）复制延迟

MySQL在高并发写入场景很容易产生复制延迟，相同测试条件下PostgreSQL不仅写入的TPS更高而且没有观察到复制延迟。根据我们的测试，MySQL从库应用事务的速度在1w/s左右，PostgreSQL备库应用事务的速度则在10w/s以上。

产生这么大差异的原因在于MySQL是逻辑复制，主库的每条SQL在从库上都要完整的执行一遍，MySQL的从库即使启用并行复制，也难以达到主库的并行度。PostgreSQL的物理复制，备库直接修改被变更的数据块即可，所以应用日志的速度很快。为确保MySQL的稳定运行，我们要求业务在使用MySQL时，单库的写入TPS不超过5000。

2）故障恢复

在故障模拟测试和实际的生产运行环境中，我们发现MySQL在遇到宕机，RAID卡重置等硬件故障后容易出现主备数据不一致或者数据损坏无法启动的问题；同等条件下PostgreSQL出问题的概率小得多，通常重启就可以自动恢复。

对于普通的宕机我们可以通过切备机快速恢复生产，但是如果是断电导致的大面积宕机，就需要数据库在供电恢复后能快速启动快速恢复。宕机恢复对数据库来说是一项基本能力，但是在一些极端条件下，数据库也可能无法自动恢复。

下面这个表是在数据库的存储设备缺少掉电保护情况下发生断电故障后的测试数据。

从上面可以看出PostgreSQL表现出了很强的健壮性，我们分析其主要原因有下面几点：

PostgreSQL的checkpoint周期一般比较长(我们配置的是半小时)，可以修复更长时间内的数据错误；

PostgreSQL的WAL中保留有被更新页面的完整数据，可以整体替换数据文件中错误的的页面；

PostgreSQL的数据文件是heap结构，页面之间各自独立，容易恢复。

作为对比，MySQL使用了fuzzy checkpoint，每隔7秒甚至更短的时间就要进行一次checkpoint。在flush不能确保持久化的情况下，很近时间内产生的数据不一致就会导致数据库无法恢复，即使用了强制恢复模式(innodb_force_recovery=6)。

另外，极端情况下数据文件出现坏页又不能通过备份恢复时，PostgreSQL支持设置一个参数再重启就可将坏掉的页面清零快速恢复生产；MySQL没有类似的功能，而且MySQL的索引组织表的页面之间有逻辑关系，技术上要做到这一点也比较困难。

选型结论
通过技术选型，我们决定引入PostgreSQL，通过MySQL和PostgreSQL的组合来替代线上的商业数据库。在具体系统的数据库选型上，还需要考虑业务的使用习惯，周边工具配套等实际情况。

总体上遵守以下的规范进行数据库选型：

OLTP

MySQL + MyCAT

PostgreSQL + Citus

OLAP

PostgreSQL + Citus

GreenPlum

三、PosgreSQL的落地

业务场景

我们上线的第一个PostgreSQL业务系统是一个实时大数据处理系统。这个系统的主要业务流程是从各个其它业务系统里面抽取相关数据放到它的数据库明细表里，然后再定时通过存储过程汇总明细表生成报表提供给分析平台进行展示。

这个系统一个很大的特点就是对数据库的性能要求特别苛刻，当时使用的是单库的商业数据库，平时数据库的CPU利用率都在45%以上，大促期间更是超过80%，可以说不堪重负。而且为了支撑今后的业务发展，这个系统必须在2017年双11前扩容十倍的容量，很显然单机的数据库已经没有任何性能扩容的空间，无法满足这一需求。

这个系统对数据库的使用主要包含下面3个不同的场景，其中每一个场景都对数据库有很高的性能要求。

1）明细表更新

实时更新包含400多个字段的宽表。数据加载速度要求达到5w/s以上，其中90%是UPDATE。

2）报表计算

支持200+/min的实时报表计算

3）报表和明细查询

支持高并发的报表和明细查询

技术选型
考虑到这个系统当前对数据库的业务需求和未来的发展规划，我们希望扩容方案是基于SQL的开源分布式数据库。我们比较了几个候选方案，考虑到和业务场景的匹配度和今后的运维的便利性，最终选择了Citus，一个能把多个单机的PostgreSQL变成分布式数据库集群的插件。

部署架构

下面是这个系统使用Citus的部署架构，为了优化性能，我们做了一些架构上的优化。明细表更新不经过Coordinator节点，而是先到Coordinator节点批量查询待更新记录的位置信息，再直接到对应位置的Worker上以批量INSERT ON CONFLICT的方式更新明细数据。

上线效果

根据POC压测的结果，把原单库的商业数据库替换成1CN + 8个Worker的Citus集群后，性能提升了10多倍，圆满达成扩容目标。

新的系统从2017年上线后至今已平稳运行多年，生产集群规模也从最初上线的4个Worker逐步扩容到16个Worker，而且CN和Worker平时的CPU利用率都保持在10%左右，很好的支撑了业务的发展。

四、PostgreSQL和Citus的推广
从2017上线第一套PostgreSQL以来，截止2019双11，我们已经部署了3000+PostgreSQL实例，其中80%以Citus集群的形式部署，其余是普通的PostgreSQL。

使用PostgreSQL的应用，既有OLTP类的业务也有OLAP类的业务。下面介绍其中的3个业务案例。

案例一：计费结算
我们在很多计费结算类业务中使用了PostgreSQL + Citus的数据库架构，其中规模最大的系统是物流的计费平台。它有几百个数据库节点，多个大表的数据量超过百亿。这个系统原来使用的数据库是商业数据库，应用在业务层做分库分表。在业务层维护大量数据库节点的方式，大大增加了日常开发和管理上的成本。

举一个简单的例子，如果要在某个表上添加一个字段，需要先在几十个库的几百张分表里依次把字段加上，然后再修改上百个应用的配置，中间不能有任何错误和遗漏。而一次发布不会只改一个表，所有实际上每次发布光编辑发布脚本就是一项很大的工作。

后来我们把这个系统的数据库从一堆单机商业数据库迁移到了分布式的Citus。迁移后不仅省掉了商业数据库的许可和维保成本，而且应用层去掉复杂的分库分表逻辑，使用体验得到了极大的提升。

迁移到Citus后，以前一些不容易做甚至没法做的事情变得可以做而且简单了，比如执行一些跨库的查询，包括跨库事务。这个系统中大量的业务请求是涉及跨库事务的。Citus实现了基于2PC的分布式事务，支持分布式死锁检测和故障时的自动事务恢复，透明地支撑了业务的跨库访问。Citus上的日常DDL发布也很简单，只要在Citus的Coordinator节点对逻辑表执行DDL就可以了，使用体验上和在普通单库上的DDL发布没什么区别。

案例二：精准营销

对电商来说，实时把握客户留存实施精准营销是一项非常重要的功课。但是对于有数亿会员和大量商品的大型电商平台，常规的处理方式是很低效的。通过探索和演进，现在我们使用citus + pg_roaringbitmap插件的技术方案。

roaringbitmap是一种高效的bitmap压缩存储格式，在标签类的应用中，已被业界广泛使用。pg_roaringbitmap插件则把roaringbitmap作为一种新的数据类型引入到了PostgreSQL里。这体现了PostgreSQL非常容易扩展的特点，不仅数据类型，索引类型，FDW甚至存储过程语言等等都可以扩展。再结合Citus的水平扩展能力，我们实现了百亿级标签的实时存储和查询。

案例三：基于地理位置的搜索和推荐

苏宁有大量线下门店，为支撑线下业务的运营，需要基于地理位置为用户提供个性化的搜索和推荐服务。并且在大促期间，需要支撑非常高的访问量。我们使用PostgreSQL + PostGiS插件支持位置数据的存储和高效查询，通过一主多从和基于JDBC多主机URL的读写分离水平扩展数据库处理能力。大促期间会临时增加从节点提升数据库的吞吐能力，最多时扩容到11个节点。

五、PostgreSQL使用经验

在使用PostgreSQL的过程中，我们踩过一些坑也积累了一些经验。下面我介绍三个PostgreSQL使用过程中需要注意的地方。

问题一：Gin索引

Gin索引是PostgreSQL的一个特色功能，很多研发小伙伴反映，数据库从商业数据库迁到PG后，他们最惊喜的PG特性就是GIN索引。很多系统的用户都有灵活的数据查询需求，查询条件涉及的字段不固定，之前应用开发者不得不为每种查询组合都创建一个Btree索引。这样做导致需要创建的索引非常多，不仅占用空间，影响更新性能，而且仍然难以覆盖所有查询场景。

对于这种搜索类的场景，有些业务会将数据导入到Elasticsearch进行查询。但是这种方案增加了系统的复杂度，需要保证ES中的数据和原始数据库的数据的保持一致，而且ES的索引更新相对与原始数据还存在一定延迟。而使用PostgreSQL的Gin索引，不存在这些问题。

Gin的基本原理和ES的倒排索引一样。它把每个（列号，Key值）作为倒排索引的键，将匹配这些键的元组的物理位置，即TID，作为值存在Entry Tree中。其中TID值存储在Entry Tree的叶子节点。匹配同一个Key的元组比较少时，TID集合以列表的形式存储；匹配同一个Key的元组比较多时，TID集合以Btree的形式存储，即Posting Tree。新插入的元组先写入Fast update list，达到一定数量时在批量合并到Entry Tree，这一优化可以大大提升插入数据到Gin索引的性能。另外Gin索引存储TID集合时做了压缩处理，因此对于低基数的索引字段，Gin索引能显著地节省存储空间。

和常规的Btree索引相比，Gin索引主要有如下优势：

支持任意字段组合查询

低基数字段的索引占用空间小

下面的测试数据可以更直观的反映Gin索引的优点。

对于低基数的索引字段，Gin占用空间甚至不到Btree的十分之一。

在性能上，多字段and组合查询时Gin的性能可达到Btree的3倍。

但是Gin也不是万能的，它也有一些短板，有些场景下使用Gin索引会适得其反。概况起来主要有以下几点，需要注意：

两边界的范围查询效率很低；

按上下边界分别扫描，效率很低。但优化器的估算cost却严重偏低，容易误走gin索引

高基数字段的索引的效率不如btree；

空间占用

查询速度

Fast update list过大的会影响点查询的QPS(可通过gin_pending_list_limit控制)

对固定的多字段AND组合查询(比如 a=1 and b=2)，效率不如btree；

不支持唯一索引；

不支持使用索引优化like前缀匹配，比如like ‘abc%‘（pg_trgm支持）；

不支持使用索引优化组合字段的oredr by，group by(单字段支持)；

对更新的性能影响比btree更大；

比btree更容易膨胀。

问题二：后端内存占用

PostgreSQL是多进程架构，每一个连接对于一个进程。每个进程的私有内存空间中会缓存一些元数据，比如系统表数据，表定义，执行计划等等。如果使用不当，可能会由于后端进程私有内存占用过大导致系统内存不足，导致内存SWAP等问题。

我们可以用下面的命令查看某个后端进程的私有内存分配情况：

gdb --batch-silent -ex ‘call MemoryContextStatsDetail(TopMemoryContext,100)’ -p ${后端进程号}

执行上面的命令后，相关输出反应在PostgreSQL的日志文件中。

上面这个图里，私有内存的大部分被元数据缓存占用了，即CacheMemoryContext。

通常，元数据缓存中占用内存最多的是下面两个系统表

pg_statistic

pg_attribute

当数据库里表，分区以及表字段很多时，它们的元数据会占用的内存也会比较多。下表的例子可以反映这一点。

从上面的数据我们可以知道，表相关的元数据缓存占用内存的大小主要和下面几个因素有关：

查询实际访问的表或分区的数量

查询实际访问的字段的数量

如果在使用PostgreSQL的过程中出现内存不足的问题，我们可以采取以下回避措施：

调小应用端连接池大小(max pool size和min pool size)；

部署pgbouncer连接池；

减少分区数；

访问不同表或分区的连接尽量隔离，比如需要访问全部分区的连接使用单独的连接池；

通过ALTER TABLE SET STATISTICS对不需要通过柱状图评估选择性的字段，减少收集的统计信息；

拆分负载。

读写分离

sharding

问题三：垃圾回收

PostgreSQL的MVCC实现机制和其它传统的关系数据库不太一样。更新记录时不是在原地更新并且把修正的前镜像记录到UNDO日志，而是在数据文件中把原来的记录标记为“被删除”再插入一条新的记录，以后再通过VACUUM把这些”被删除“记录占用的空间回收掉。也就是说PostgreSQL中只有REDO日志，没有UNDO日志。在这种MVCC设计下，PostgreSQL回滚事务可以立即完成，和事务大小无关，回滚事务时只需要在CLOG事务状态文件中标记这个事务的状态为ABORTED即可。

PostgreSQL的MVCC实现机制带来的问题是如果垃圾回收不及时容易导致数据文件膨胀，这也是很多人吐槽PostgreSQL的地方。不过，不能单纯地把PostgreSQL的MVCC机制视作一个槽点，只是MVCC的不同实现方式而已。不知道大家注意到没有，很多新兴的分布式数据库库，也都采用了类似PostgreSQL标记删除的MVCC机制。

作为PostgreSQL的使用者，我们需要做的事是要确保PostgreSQL的垃圾回收工作能够正常运作。具体有下面几件事情要做。

1）autovacuum参数调优

PostgreSQL中有一个后台的autovacuum进程专门负责回收垃圾，我们可以根据数据库的配置和业务特点对autovacuum进行合理的参数设置，确保autovacuum回收垃圾的速度足够快，对数据库负载冲击又比较平滑。

比如下面几个参数：

其中，最需要修改的是autovacuum_vacuum_cost_limit，其含义是一次回收最多消耗多少cost则暂停一会（autovacuum_vacuum_cost_delay的设定值）。默认值为200，这个值对于现代的设备显得太小，容易导致垃圾回收速度跟不上垃圾产生的速度，使用SSD时可以考虑把它设置为10000。

另外，autovacuum默认在表中被更新的元组比率达到20%的时候启动垃圾回收，对于亿级别的大表，会导致一个问题。就是一次垃圾回收的任务太大，垃圾回收时间过长。

所以建议采用以下优化措施：

对过大的表进行分区控制单表的大小：比如单表控制在1亿记录或10GB以下；

对大表单独调优autovacuum_vacuum_scale_factor参数：autovacuum_vacuum_scale_factor参数默认值是0.2，大表可适当调小，比如设为0.05。

关于autovacuum，详细可以参考一下这篇文章：

http://www.postgres.cn/v2/news/viewone/1/387

2）自动释放过长事务

PostgreSQL在垃圾回收时会保留对当前的存活事务可见的死元组。如果数据库中有一个执行时间特别长的事务，那么在这个事务存活期间数据库中产生的所有垃圾元组都无法被回收。这种长事务有时侯不是来自我们正常的业务访问，而是来自一些异常场景。我们可以在数据库中设置一些超时参数，使得在异常情况下，PostgreSQL可以及时把事务终止。

可以参考下面几类超时参数设置：

释放长时间空闲的事务

idle_in_transaction_session_timeout = 3600s

释放长时间拿不到锁的事务

lock_timeout = 60s

释放已无法继续通讯的空闲连接

tcp_keepalives_idle = 60

tcp_keepalives_count = 10

tcp_keepalives_interval = 5

限制SQL的最长执行时间：如果业务上有一个允许SQL最长执行时间的要求，可以把这个值作为SQL超时，通过statement_timeout参数或者通过客户端驱动的API进行设置。确保SQL执行时间过长时能被时取消。

3）监控垃圾回收相关的数据库状态

除了前面介绍的措施，我们还需要对垃圾回收相关的一些数据库指标进行监控，一旦发生异常可以及时发现和处理。以下是我们在生成环境部署的一些和垃圾回收相关的监控项，大家可以参考一下。

参考资料

http://www.sql-workbench.net/dbms_comparison.html

《PostgreSQL+CITUS在苏宁物流经营结算中的实践》

《苏宁大规模标签场景应用实践》

https://bigdata.51cto.com/art/202006/617771.htm

https://yq.aliyun.com/articles/68244

https://postgrespro.com/blog/pgsql/4261647

http://www.postgres.cn/v2/news/viewone/1/387

Q&A

Q1：目前PosgreSQL的recovery功能与Oracle相比怎么样？

A：PostgreSQL本身的备份恢复功能很完备，支持恢复到指定时间点，使用也很方便。另外还有很多第三方的开源备份管理工具，比如pg_rman，barman等等，可以更加方便的管理备份。所以，我觉得PG的备份恢复不弱于Oracle这样的商业数据库。

Q2：老师你们的高可用方案是怎样的？

A：我们基于PG原生的流复制搭建HA集群，对外提供VIP由普通的读写业务访问。只读业务在JDBC的URL上同时所有个节点的实际IP，通过pgjdbc的多主机URL功能进行读写分离和读负载均衡。

Q3：用PosgreSQL的时候，什么时候开始表分区？实现方式是怎样？

A：我们内部规范要求单表控制在1亿记录或10GB，超过这个标准建议分区或分片。分区的话如果是PG10或以下版本，建议使用pg_pathman插件。PG10及以前版本的分区在分区数很大时性能会比较差。如果是更高的PG版本，特别是PG12以后就建议直接使用原生的分区了。分片我们使用Citus进行分库分表。

Q4：请问你们的监控、自动化运维方面用了什么工具？

A：监控上我们主要用了Zabbix和Prometheus，其他的日常运维方面主要使用的自研的工具和平台。

Q5：3000+实例如何管理？有运维系统支撑下，需要多少人力？

A：我们主要运维工作都通过内部的运维平台支撑，实现了自动化。如果只是单纯PG的维护的话，我觉得10个人左右甚至更少是可以支撑的。

Q6：MySQL如何迁移到PostgreSQL呢？有什么需要注意的事项吗?

A：我们用mysql_fdw进行迁移，这也是很方便的一种方式。关于迁移的注意事项，除了数据类型是适配，需要注意PG一定要使用UTF8编码。另外PG不支持\0000这个特殊的Unicode字符。当然这个字符也没是什么实际意义，主要是有一些应用系统数据入库不规范，在以前的DB2或MySQL库里不小心写入的这个字符，后面迁移到PG的时候就需要规范化，把这个非法字符删掉。

Q7：PosgreSQL使用内存方面有什么好的监控方式吗？经常会遇到连接爆内存不足，但实际上系统还有内存。

A：可以使用OS级别的内存监控，比如可用内存量和swap，一旦出现swap都需要及时处理。如果系统还有内存但连接爆内存不足就要具体问题具体分析了，需要参考当时出错的信息。建议把问题的复现方法提供出来。

Q8：citus的高可用如何实现的呢？是每个worker节点都搭建一个从库吗，cn节点是否也需要搭建一个从库？

A：Citus支持2种高可用方式，一种是多副本分片，由CN节点在写数据的时候同时写多个副本到不同worker上；另一个是使用PG原生的高可用，比如流复制。

多副本的高可用方式，维护简单，但使用起来有一些限制，比如不支持Citus MX，写入性能下降明显等等，Citus官方手册上也认为多副本高可用只适合append only的业务场景。

因此，Citus更加通用的高可用方式是由底层PG做高可用，即CN节点以及下面每个Worker节点都采用流复制的方式部署一对主备机器。

Q9：有没有一个搭建citus集群的具体实施说明呢？

A：Citus部署实施相关的问题，可以参考一下《Citus生产部署和维护.pptx》(https://pan.baidu.com/s/1eRQsdAa)。

另外，Citus集群里每个节点都是PG，这些PG节点的参数配置以及HA部署和普通的PG是一样的，参考PG的相关资料即可。

Q10：磁盘是怎么规划的？如何规划性能更好？使用raid5、raid10还是单盘raid0？

A：对磁盘的规划需求，PG和其他数据库应该是类似的。raid5、raid10还是单盘raid0这其实是一个可靠性，性能还有成本的取舍，大家可以根据自己的情况判断。我们目前用的是raid5。

Q11：max_locks_per_transaction 这个参数有推荐的值吗？感觉这个参数和内存使用有很大的关系。

A：这个默认值是64，通常默认值应该就可以覆盖绝大多数情况不需要修改。每个锁占用的空间都不是很大，只要不把这个参数调得特别大，锁应该不是最消耗内存的地方。

Q12：Pacemake集群安全吗？

A：Pacemaker+corosync的HA方案中它的集群元数据存在每个集群节点上并实时同步，但是它的元数据的同步协议不是强一致的协议，一旦网络分区，有脑裂风险。

所以，基于Pacemaker的PG HA需要配置物理的fence设备防止脑裂，特别是一主一从的架构。