开发者社区> 问答> 正文

Postgresql 9.6 太多的standard_conforming_strings 占了太多连接。 怎么解决?

已解决

我用
SELECT pid, datname, usename, query , state FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active');
结果
screenshot

21574,niubility,postgres,show standard_conforming_strings,idle in transaction
21563,niubility,postgres,show standard_conforming_strings,idle in transaction
21559,niubility,postgres,show standard_conforming_strings,idle in transaction
21575,niubility,postgres,show standard_conforming_strings,idle in transaction
21799,niubility,postgres,show standard_conforming_strings,idle in transaction
21614,niubility,postgres,"SELECT pid, datname, usename, query , state FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active')",active
21584,niubility,postgres,show standard_conforming_strings,idle in transaction
21585,niubility,postgres,show standard_conforming_strings,idle in transaction
21845,niubility,postgres,show standard_conforming_strings,idle in transaction
22195,niubility,postgres,show standard_conforming_strings,idle in transaction
22253,niubility,postgres,show standard_conforming_strings,idle in transaction
21680,niubility,postgres,show standard_conforming_strings,idle in transaction
21681,niubility,postgres,show standard_conforming_strings,idle in transaction
21801,niubility,postgres,show standard_conforming_strings,idle in transaction
21685,niubility,postgres,show standard_conforming_strings,idle in transaction
21686,niubility,postgres,show standard_conforming_strings,idle in transaction
21803,niubility,postgres,show standard_conforming_strings,idle in transaction
21846,niubility,postgres,show standard_conforming_strings,idle in transaction
21804,niubility,postgres,show standard_conforming_strings,idle in transaction
21907,niubility,postgres,show standard_conforming_strings,idle in transaction
21842,niubility,postgres,show standard_conforming_strings,idle in transaction
21843,niubility,postgres,show standard_conforming_strings,idle in transaction
9025,niubility,postgres,show standard_conforming_strings,idle in transaction
21908,niubility,postgres,show standard_conforming_strings,idle in transaction
21920,niubility,postgres,show standard_conforming_strings,idle in transaction
21921,niubility,postgres,show standard_conforming_strings,idle in transaction
22172,niubility,postgres,show standard_conforming_strings,idle in transaction
22173,niubility,postgres,show standard_conforming_strings,idle in transaction
21998,niubility,postgres,show standard_conforming_strings,idle in transaction
22072,niubility,postgres,show standard_conforming_strings,idle in transaction
22000,niubility,postgres,show standard_conforming_strings,idle in transaction
22075,niubility,postgres,show standard_conforming_strings,idle in transaction
22002,niubility,postgres,show standard_conforming_strings,idle in transaction
11329,niubility,postgres,show standard_conforming_strings,idle in transaction
22003,niubility,postgres,show standard_conforming_strings,idle in transaction
22254,niubility,postgres,show standard_conforming_strings,idle in transaction
22085,niubility,postgres,show standard_conforming_strings,idle in transaction
22086,niubility,postgres,show standard_conforming_strings,idle in transaction
22340,niubility,postgres,show standard_conforming_strings,idle in transaction
22342,niubility,postgres,show standard_conforming_strings,idle in transaction
9033,niubility,postgres,show standard_conforming_strings,idle in transaction
22177,niubility,postgres,show standard_conforming_strings,idle in transaction
22178,niubility,postgres,show standard_conforming_strings,idle in transaction
22425,niubility,postgres,show standard_conforming_strings,idle in transaction
22424,niubility,postgres,show standard_conforming_strings,idle in transaction
22426,niubility,postgres,show standard_conforming_strings,idle in transaction
22262,niubility,postgres,show standard_conforming_strings,idle in transaction
22263,niubility,postgres,show standard_conforming_strings,idle in transaction
22427,niubility,postgres,show standard_conforming_strings,idle in transaction
22346,niubility,postgres,show standard_conforming_strings,idle in transaction
22347,niubility,postgres,show standard_conforming_strings,idle in transaction
11559,niubility,postgres,show standard_conforming_strings,idle in transaction
11437,niubility,postgres,show standard_conforming_strings,idle in transaction
11331,niubility,postgres,show standard_conforming_strings,idle in transaction
11552,niubility,postgres,show standard_conforming_strings,idle in transaction
13570,niubility,postgres,show standard_conforming_strings,idle in transaction
11433,niubility,postgres,show standard_conforming_strings,idle in transaction
11434,niubility,postgres,show standard_conforming_strings,idle in transaction
11438,niubility,postgres,show standard_conforming_strings,idle in transaction
11780,niubility,postgres,show standard_conforming_strings,idle in transaction
12097,niubility,postgres,show standard_conforming_strings,idle in transaction
12313,niubility,postgres,show standard_conforming_strings,idle in transaction
12311,niubility,postgres,show standard_conforming_strings,idle in transaction
11777,niubility,postgres,show standard_conforming_strings,idle in transaction
11668,niubility,postgres,show standard_conforming_strings,idle in transaction
11667,niubility,postgres,show standard_conforming_strings,idle in transaction
12317,niubility,postgres,show standard_conforming_strings,idle in transaction
12098,niubility,postgres,show standard_conforming_strings,idle in transaction
12420,niubility,postgres,show standard_conforming_strings,idle in transaction
12310,niubility,postgres,show standard_conforming_strings,idle in transaction
12419,niubility,postgres,show standard_conforming_strings,idle in transaction
12971,niubility,postgres,show standard_conforming_strings,idle in transaction
12972,niubility,postgres,show standard_conforming_strings,idle in transaction
12973,niubility,postgres,show standard_conforming_strings,idle in transaction
12587,niubility,postgres,show standard_conforming_strings,idle in transaction
12588,niubility,postgres,show standard_conforming_strings,idle in transaction
12685,niubility,postgres,show standard_conforming_strings,idle in transaction
12582,niubility,postgres,show standard_conforming_strings,idle in transaction
12688,niubility,postgres,show standard_conforming_strings,idle in transaction
12974,niubility,postgres,show standard_conforming_strings,idle in transaction
14274,niubility,postgres,show standard_conforming_strings,idle in transaction
13285,niubility,postgres,show standard_conforming_strings,idle in transaction
13078,niubility,postgres,show standard_conforming_strings,idle in transaction
13381,niubility,postgres,show standard_conforming_strings,idle in transaction
13176,niubility,postgres,show standard_conforming_strings,idle in transaction
13079,niubility,postgres,show standard_conforming_strings,idle in transaction
13286,niubility,postgres,show standard_conforming_strings,idle in transaction
13081,niubility,postgres,show standard_conforming_strings,idle in transaction
13382,niubility,postgres,show standard_conforming_strings,idle in transaction
13574,niubility,postgres,show standard_conforming_strings,idle in transaction
13083,niubility,postgres,show standard_conforming_strings,idle in transaction
13179,niubility,postgres,show standard_conforming_strings,idle in transaction
13283,niubility,postgres,show standard_conforming_strings,idle in transaction
13282,niubility,postgres,show standard_conforming_strings,idle in transaction
14572,niubility,postgres,show standard_conforming_strings,idle in transaction
14967,niubility,postgres,show standard_conforming_strings,idle in transaction
14964,niubility,postgres,show standard_conforming_strings,idle in transaction
14067,niubility,postgres,show standard_conforming_strings,idle in transaction
14071,niubility,postgres,show standard_conforming_strings,idle in transaction
14573,niubility,postgres,show standard_conforming_strings,idle in transaction
14375,niubility,postgres,show standard_conforming_strings,idle in transaction
14270,niubility,postgres,show standard_conforming_strings,idle in transaction
14271,niubility,postgres,show standard_conforming_strings,idle in transaction
14275,niubility,postgres,show standard_conforming_strings,idle in transaction
14376,niubility,postgres,show standard_conforming_strings,idle in transaction
14574,niubility,postgres,show standard_conforming_strings,idle in transaction
14370,niubility,postgres,show standard_conforming_strings,idle in transaction
14371,niubility,postgres,show standard_conforming_strings,idle in transaction
14787,niubility,postgres,show standard_conforming_strings,idle in transaction
14575,niubility,postgres,show standard_conforming_strings,idle in transaction
14688,niubility,postgres,show standard_conforming_strings,idle in transaction
16765,niubility,postgres,show standard_conforming_strings,idle in transaction
14683,niubility,postgres,show standard_conforming_strings,idle in transaction
14684,niubility,postgres,show standard_conforming_strings,idle in transaction
14788,niubility,postgres,show standard_conforming_strings,idle in transaction
14792,niubility,postgres,show standard_conforming_strings,idle in transaction
16060,niubility,postgres,show standard_conforming_strings,idle in transaction
14794,niubility,postgres,show standard_conforming_strings,idle in transaction
15054,niubility,postgres,show standard_conforming_strings,idle in transaction
22432,niubility,postgres,show standard_conforming_strings,idle in transaction
15056,niubility,postgres,show standard_conforming_strings,idle in transaction
18212,niubility,postgres,show standard_conforming_strings,idle in transaction
16122,niubility,postgres,show standard_conforming_strings,idle in transaction
22433,niubility,postgres,show standard_conforming_strings,idle in transaction
18316,niubility,postgres,show standard_conforming_strings,idle in transaction
18224,niubility,postgres,show standard_conforming_strings,idle in transaction
18515,niubility,postgres,show standard_conforming_strings,idle in transaction
18813,niubility,postgres,show standard_conforming_strings,idle in transaction
18520,niubility,postgres,show standard_conforming_strings,idle in transaction
18892,niubility,postgres,show standard_conforming_strings,idle in transaction
12583,niubility,postgres,show standard_conforming_strings,idle in transaction
15139,niubility,postgres,show standard_conforming_strings,idle in transaction
15142,niubility,postgres,show standard_conforming_strings,idle in transaction
14690,niubility,postgres,show standard_conforming_strings,idle in transaction
15522,niubility,postgres,show standard_conforming_strings,idle in transaction
15523,niubility,postgres,show standard_conforming_strings,idle in transaction
16123,niubility,postgres,show standard_conforming_strings,idle in transaction
15901,niubility,postgres,show standard_conforming_strings,idle in transaction
15817,niubility,postgres,show standard_conforming_strings,idle in transaction
15815,niubility,postgres,show standard_conforming_strings,idle in transaction
15899,niubility,postgres,show standard_conforming_strings,idle in transaction
16062,niubility,postgres,show standard_conforming_strings,idle in transaction
16767,niubility,postgres,show standard_conforming_strings,idle in transaction
17066,niubility,postgres,show standard_conforming_strings,idle in transaction
17222,niubility,postgres,show standard_conforming_strings,idle in transaction
17221,niubility,postgres,show standard_conforming_strings,idle in transaction
17136,niubility,postgres,show standard_conforming_strings,idle in transaction
17065,niubility,postgres,show standard_conforming_strings,idle in transaction
17135,niubility,postgres,show standard_conforming_strings,idle in transaction
18820,niubility,postgres,show standard_conforming_strings,idle in transaction
18905,niubility,postgres,show standard_conforming_strings,idle in transaction
18318,niubility,postgres,show standard_conforming_strings,idle in transaction

展开
收起
hifly2016 2016-11-23 09:13:36 3624 0
2 条回答
写回答
取消 提交回答
  • PostgreSQL你值得拥有。
    采纳回答

    根据你的SQL:

    SELECT pid, datname, usename, query , state FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active');

    你要搜查的是所有尚未执行查询的后端事务连接,这种状态下的pg_stat_activity表中的query属性代表上次执行的SQL语句

    从你的结果来看,你目前保持着153个左右的连接,并且有很多个是一个后端事务,每个事务开始自动执行show standard_conforming_strings。你可以检查下你的应用程序使用的基本框架里面是不是在建立连接之后,默认执行show standard_conforming_strings。

    2019-07-17 20:28:22
    赞同 1 展开评论 打赏
  • 公益是一辈子的事, I am digoal, just do it. 阿里云数据库团队, 擅长PolarDB, PostgreSQL, DuckDB, ADB等, 长期致力于推动开源数据库技术、生态在中国的发展与开源产业人才培养. 曾荣获阿里巴巴麒麟布道师称号、2018届OSCAR开源尖峰人物.

    可能你的程序有问题,建议排查一下程序,驱动,框架等。

    2019-07-17 20:28:22
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
2023云栖大会:和客户一起玩转PolarDB新特性 立即下载
2023云栖大会:PolarDB for AI 立即下载
2023云栖大会:AnalyticDB PostgreSQL 立即下载

相关镜像