在高流量期间阻塞 ClientRead 上的空闲连接以进行参数化查询(绑定) [英] Blocking on idle connections on ClientRead for parametrized queries (bindings) during high traffic

问题描述

我正在为我在高流量高峰期间出现的问题寻找好的解决方案.我在 AWS 上使用 postgres 和 nodejs(用于构建查询的 knex) - 详情如下.

I am looking for good solution for my problem which occurs during high traffic peaks. I use postgres on AWS with nodejs (knex for queries buliding) - details below.

当我在 RDS 控制台中查看 Performance Insights 时，我看到一些查询停留在ClientRead"上.我的 RDS 实例相当大，我的 CPU 利用率非常低(1%-10%).所以我通过连接到 db 并运行 pg_stats 的查询来确认它，结果我看到很多查询在 ClientRead 事件上是空闲的.

When I am looking on Performance Insights in my RDS console I see that some of queries stuck on "ClientRead". My RDS instances are rather huge and my CPU utilization is on very low level (1%-10%). So I confirmed it by connect to db and run query for pg_stats and in result I saw that a lot of queries is idle on ClientRead event.

什么连接这些查询?绑定.我假设这些参数化查询等待从我的 EC2 实例获取值.我觉得我的服务太慢了，所以我扩展到更多的实例，但在RDS上的结果更糟，更多的连接被阻塞.

What connect these queries? Bindings. I assume that these parametrized queries wait to get values from my EC2 instances. I thought that my services are too slow, so I scaled up to more instances, but the results on RDS were worse and more connections were blocked.

为了测试解决方案，我将几个查询从参数化转换为没有绑定的原始 sql 查询(直接在查询中使用值).而这些查询正是立即运行，没有任何问题.但即使出于安全考虑，这似乎也不是完美的解决方案.

For testing solution I converted few of queries from parametrized into raw sql queries without bindings (with values directly in query). And these queries exactly were run immediately without any problems. But it seems that not perfect solution even if security reasons.

目前我不知道问题出在哪里?我应该通过在 api gw 上添加节流来减少流量吗?在我的服务中创建内部队列?是我的RDS/postgre的通讯问题还是设置的问题?

At the moment I have no idea where the problem is? Should I reduce traffic by add throttling on api gw? Create inner queues in my service? Is it a communication problem or settings of my RDS/postgre?

如果有人对类似案例有更多经验或可以指出可能的解决方案，请链接到可以帮助我或检测问题所在的文档.

If anyone has more experience with similar cases or is possible to point at probable solution, link to documents which could help me or detect where the problem is it would be great.

AWS RDS (Aurora) Postgres 9.6.9节点 10.12.0膝关节 0.17.3节点 postgres 7.4.1

AWS RDS (Aurora) Postgres 9.6.9 nodejs 10.12.0 knex 0.17.3 node-postgres 7.4.1

推荐答案

如果你的数据库后端被阻塞等待 ClientRead，那意味着数据库正在等待来自客户端的请求.

If your database backends are blocked waiting for ClientRead, that means that the database is waiting for requests from the client.

您看到的查询没有运行查询.如果 state 不是 active，query 包含在此数据库连接上运行的最后 SQL 语句.

The queries you are seeing are not running queries. If the state is not active, query contains the last SQL statement that was run on this database connection.

如果您遇到性能问题，原因似乎在数据库之外.

If you are experiencing performance problems, the cause seems to be outside the database.

这篇关于在高流量期间阻塞 ClientRead 上的空闲连接以进行参数化查询(绑定)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！