在非常大的Postgres桌子上创建索引非常慢，而且时间耗尽
我有一张桌子上有一个大约2亿行和800列的AWS RDS群集，我想优化读取速度。不幸的是，查询创建过程太慢了，以至于我的客户连接时间耗尽。我已经尝试了许多事情来解决这个问题，例如：
- 修改
tcp_keepalive群集和超时设置 - 创建另一个表并尝试索引索引，而
- 添加
同时index Creation参数以防锁会导致延误 - 与我的本地防火墙设置混乱，因此在所有这些情况下，在提交索引创建查询之后，任何网络连接都不会关闭
，我在数小时后收到了一个错误消息：ssl syscall错误：操作计时
并检查日志，我看到了
LOG: could not send data to client: Connection timed out
LOG: could not send data to client: Broken pipe
FATAL: connection to client lost
我使用postico和psql cli提交这些查询的消息，以试图排除任何怪异的客户端设置，但是无用。
我有点新手，所以我有可能在故障排除步骤上执行了错误，而且我还阅读了其他相关的帖子以进行故障排除，但并没有真正取得任何进展，并真的很感谢任何建议。提前致谢！
I have a table with about 200 million rows and 800 columns in an AWS RDS cluster that I'd like to optimize read speed on. Unfortunately, the query creation process is so slow that my client connection times out. I've tried a number of things to address this, such as:
- Modifying the
tcp_keepalivecluster and timeout settings - Creating another table and attempting to index that instead
- Adding the
CONCURRENTLYindex creation parameter in case locks were causing delays - Messing with my local firewall settings so any network connections don't get closed out
In all these cases, after submitting index creation queries, I get an error message after many hours saying: SSL SYSCALL error: Operation timed out
and checking the logs, I see messages like
LOG: could not send data to client: Connection timed out
LOG: could not send data to client: Broken pipe
FATAL: connection to client lost
I've submitted these queries using Postico and the psql CLI in attempt to rule out any weird client settings, too, but to no avail.
I'm a bit of a novice so it's possible I've executed on the troubleshooting steps incorrectly, and I've also read other related posts to troubleshoot, but haven't really made any headway and would really appreciate any advice. Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Welp，这是一个集群资源问题。我将RDS实例升级到了更高的RAM/更高的VCPU层，并且这些索引的创建相对较快。在过去的几周中，“可释放的记忆”暴跌，因此可能导致了一些IO瓶颈和随后的超时。
Welp, it turns it was a cluster resource issue. I upgraded the RDS instance to a higher RAM/higher vCPU tier and the indices were created relatively quickly. The "Freeable Memory" had plummeted over the past several weeks, so that probably led to some IO bottlenecks and subsequent timeouts.