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

问题描述

我正在为交通高峰期间出现的问题寻找好的解决方案.我在带有Node.js的AWS上使用Postgres(用于查询的knex)-下面详细说明.

当我在RDS控制台中查看Performance Insights时，我发现某些查询停留在"ClientRead"上.我的RDS实例非常庞大，我的CPU使用率处于非常低的水平(1％-10％).因此，我通过连接到数据库并运行 pg_stats 的查询来确认它，结果我看到很多查询在ClientRead事件上处于空闲状态.

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

对于测试解决方案，我将很少的查询从参数化转换为无绑定的原始sql查询(直接在查询中包含值).这些查询完全可以立即运行而没有任何问题.但是，即使出于安全原因，这似乎也不是完美的解决方案.

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

如果任何人在类似案件上有更多经验，或者有可能指出可能的解决方案，请链接到可能对我有帮助或发现问题出在哪里的文档.

AWS RDS(Aurora)Postgres 9.6.9的NodeJS 10.12.0纳克斯0.17.3节点postgres 7.4.1

解决方案

如果您的数据库后端被阻止以等待 ClientRead ，则意味着数据库正在等待来自客户端的请求.

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

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

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.

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.

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.

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.

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 nodejs 10.12.0 knex 0.17.3 node-postgres 7.4.1

解决方案

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

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屋！