Postgres 9.6:重启后插入速度很慢 [英] Postgres 9.6: Inserts very slow after reboot

问题描述

我在将数据导入 postgres 时遇到一个奇怪的问题.我有一个导入脚本，每天晚上通过 Linux 操作系统上的 cronjob 运行一次(Linux 4.9.0-5-amd64 #1 SMP Debian 4.9.65-3+deb9u2 (2018-01-04) x86_64 GNU/Linux).该进程读取一个包含大约 200 万行的文件，并将它们分批插入到一个表中(批量大小为 25.000).这大约需要 2 到 2.5 分钟，这是绝对合理的.该表有 3 个索引，同时包含超过 1.3 亿行.为了提高性能，我将表声明为 UNLOGGED 并禁用 WAL.有时我需要升级需要重启服务器的系统.这会以某种方式影响数据库，因为数据导入突然需要大约 1 到 1.5 小时而不是几分钟.我找不到任何原因.这里出了什么问题，有什么想法吗?

I'm experiencing a strange issue when importing data into postgres. I have an import script that runs once daily at night via cronjob on a Linux OS (Linux 4.9.0-5-amd64 #1 SMP Debian 4.9.65-3+deb9u2 (2018-01-04) x86_64 GNU/Linux). The process reads a file containing about 2 million rows and inserts these into a table in batches (batch size 25.000). This takes roughly 2 to 2.5 minutes, which is absolutely reasonable. The table has 3 indexes and meanwhile contains more than 130M rows. To increase the performance I declared the table as UNLOGGED and disabled WAL. From time to time I need to upgrade the system which requires a server restart. This somehow affects the database as the data import suddenly takes instead of minutes about 1 to 1.5 hours. I couldn't find out any reason for this. What's going wrong here, any ideas?

推荐答案

这发生在我身上一次.真的不知道为什么，但对我来说，它在重新启动 postgresql 服务后修复了.我的想法是，如果您不重新启动服务(即使在重新启动后)，postgres 似乎会保留其 WAL(预写日志).通过重新启动服务，一切都会清理干净，所以一切都运行得像新的一样.但是，也许还有另一个原因.无论如何，尝试重新启动服务而不是重新启动或重新启动后重新启动服务.

This happened to me once. Don't really know why, but for me it was fixed after restarting the postgresql service. My thinking says that if you do not restart the service(even after a reboot), it seems postgres keeps its WAL (Write-Ahead Logging). By restarting the service everything cleans up, so everything runs as good as new again. But, perhaps there's another reason. Anyway, try restarting the service instead of rebooting or restart the service after reboot.

阅读问题评论以获取更多信息或此链接 @user35934 发布的.

Read the question comments for more info or this link that @user35934 posted.

这篇关于Postgres 9.6:重启后插入速度很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！