部署|3年部署3000套PG实例的架构设计与踩坑经验( 五 ) mysql|pg|postgresql|经验|架构

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

文章插图
>>>>
参考资料

>>>>
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原生的高可用，比如流复制。