AI 建议用pgbouncer 解决PostgreSQL 链接多的问题，唉信AI，你的死多少次！

最近继续搞降本的活动，经济不行做数据库要体现价值不被裁员，最大的出路就是给公司创造利润，俩字省钱，四个字节省成本。其他的没戏，技术在高，领导一句话，现在都嘛时候了，来点实际的，只要 听说你能继续给公司省钱，两眼就放光。

这次主角又是PostgreSQL,上次省了100万，这次又发现一个问题，其实也是一个老问题，打算要给众多的数据库降配，但内存的使用率的达到一个标准，才能发起降配的工作，要想怎么让内存降低，达到可以降配后让数据库持续稳定运行的目标。

现在手里一部分PG的数据库最大的问题就卡在了，内存上，内存的占比太大，如果现在降配那就得OOM，可怎么能卡上稳定降配的点。那就的自己找办法。

Code_Generated_Image

Code_Generated_Image

首先咱们分析根因，PG占用内存主要的有几个部分，回答两个部分，一个是shared buffer pool 一个是我们的work_mem，除此以外还有一个不归数据库管理的file cache 在Linux中为我们提供一个二级缓存，主因是PG不和操作系统的磁盘进行直接的交互，而是和系统的文件缓存进行交互。

从图中也可以看出内存的上涨的百分比和连接数上涨的百分比是一个强相关的关系。

我们从DBA的角度，如果要降低内存的占用率和波动，从连接数下手，削平波峰即可，常出的方案使用链接复用的方案，最常见的是通过pgbouncer来解决问题，通过事务模式的链接复用来解决，我们的一个DBA这样说，咱们装pgbouncer解决，我当时想回怼，但我忍住了，回了一句，咱们开会研究。

为什么一个明显的解决方案，我没同意，因为这样干是一个治标不治本的方案，也不是一个稳妥的方案，如果打分只能给50分，甚至更低，因为你治标不治本，用肢体的勤奋掩盖实际的思考的懒惰。

这个问题直接用pgbouner解决问题，我只能说你基本没有实际数据库的工作经验，没有从实际企业数据库运营的角度提出的一个，切实可行方案，而是提出一个无法执行的“学术方案”。

举例我手里有40多个实例，每个实例里面有少则7个多则30多个逻辑库，而且时不时还要常添加逻辑库，就问一句如果我使用了pgbouncer 有谁把这堆复杂的账号和逻辑库与数据库之间的关系的配置文件给我弄了，我的干多长时间，谁给我干这个活，人非圣贤，干错了产生生产事故，是不是要扣我钱。

方案有了，工作复杂度，可执行度，考虑了吗？

如果你问AI ，AI100%给你出这个馊主意，然后你拿着这个方案，公司架构马上就来找你茬，你擅自改变公司数据库架构，你这个pgbouncer有高可用吗，怎么进行验证你双机pgbouncer的方案的可靠性，如果我一个数据库配主备pgbouncer，我的要80个pgbouner的服务，我的架构复杂性呢？成本呢，本来我要降本，这下有的多花几万块，领导不抽死你！

所以以后谁在给你实际的业务系统，出这个注意，你就问他一句，先把成本算一算，出了问题扣我钱，咋办？

实际上遇到这样的问题，哪里是装个pgbouncer这么简单，要真这么简单，那AI 还真能把大家替了。

这个问题，显然你要找问题的源头，而不能头疼，砸头，脚疼卸脚丫子。

1 你的分析出数据库中，链接的那些应用，那些应用开了过多的idle的链接，通过应用的连接池配置的链接来找到业务，开发，架构，告知他们配置的链接数过多，导致占用了大量的内存，主因不是数据库产生的，是业务和开发对于链接数量的使用导致的问题，这是第一步，这叫分清责任，谁制造的问题，就应该解决谁。

SELECT 
    application_name,
    client_addr,
    status,
    count(*) as connection_count,
    -- 甚至可以把这个应用占用的估算本地内存算出来
    sum(case when status = 'idle' then 1 else 1 end) * 4 || ' MB' as estimated_idle_mem
FROM pg_stat_activity
GROUP BY application_name, client_addr, status
ORDER BY connection_count DESC;

2 在分清责任的情况下，我们谈如何进行问题的解决，

1 开发和架构要适当降低链接的发起的速度，也就是你们要进行链接的复用，同时我们要谈idle_in_transaction_session_timeout 和 statement_timeout的超时时间，我们要设置一个合理的时间，来清理你的idle链接。

2 针对我们分析出长期不回收的idle的链接，我们进行手动的定时清理，具体的方案一定要和开发和架构达成意见，形成文档避免后续扯皮，责任不清。

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE status = 'idle in transaction'
  -- 过滤掉属于你自己的 DBA 运维窗口或高优先级进程
  AND backend_type = 'client backend'
  -- 限制死掉的时间，比如在事务中空闲超过 10 分钟
  AND state_change < now() - INTERVAL '10 minutes';

3 针对当前的数据库应用分析work_mem的最大值给的是不是大了，可以适当的缩小，当然这里会产生新的问题，一些特别大的SQL不适用work_mem的调整，导致一部分数据落到了磁盘上。这个就的细致的在沟通想办法了。下面的参数设置一下，我们关注修改参数后，临时文件的增加数量。

log_temp_files = 0

（其实通过这个你就能发现那些SQL是消耗大户，然后你SQL优化的工作也来了）

最后咱们反向攻击，一个不懂开发的DBA，注定是一个失败者，因为开发说啥是啥，你不就是一个人肉工具，DBA 必须懂点开发。

这里咱们反向攻击的方式

1 在JAVA spring boot 里面hikaiCP配置中开发一定设置了maximun-pool-size 和 minimum-idle 看看是不是minimum-idle设置的太大了，导致JAVA应用一直在维持一个数据库最小链接，比如他设置的是20，但是他有个30个应用，那么就算数据库一个SQL都不跑，他们也会建立600个链接到数据库里面，这是最大的不合理。

2 max-lifetime 这个也要管，开发图省事给你设置一天，你要知道如果一个链接跑了需要8MB的work_mem的SQL，就算后面你在跑小的SQL这8MB只要链接建立他是不会退给系统的，然后就是一天，定时让链接释放掉，把内存退给操作系统才是正路。

这顿操作后，你看看你内存省没省！！ 你责任背了多少。出了问题，你最大也就是一个从犯，你要要用pgbouncer呢，出了问题几个脑袋够枪毙的

写到这里，很多人说AI替代这个 AI 替代那个，我就问问，问AI 他能给你出一堆用pgbouncer 的方案，他能承担责任吗？

初级的DBA，不动脑子的，没经验的，当然可以被AI 替代，但AI 替代不了有真实工作经验的，实干家，因为咱们花花肠子多，套路深，不按常理出牌。