Postgresql大表更新变慢

Question

我在一个大表（例如8GB）上运行了一个更新操作，只是简单地更新了表中的3个字段。在postgresql 9.1下运行没有问题，虽然需要40-60分钟，但它可以正常工作。我在9.4数据库中运行相同的查询（全新创建，未升级），开始更新操作时速度也很快，但后来变慢了。它只使用了约2%的CPU，IO的水平为4-5MB/s，就这样卡在那里。没有锁定、没有其他查询或连接，只有这个单独的更新SQL在服务器上。

以下是SQL语句。"lookup"表有12条记录。查找只能返回一行，它将离散的范围（SMALLINT，-32768..+32767）分成不重叠的区域。"src"和"dest"表约有6000万条记录。

UPDATE dest SET
    field1 = src.field1,
    field2 = src.field2,
    field3_id = (SELECT lookup.id FROM lookup WHERE src.value BETWEEN lookup.min AND lookup.max)
FROM src
WHERE dest.id = src.id;

我觉得我的硬盘变慢了，但我可以同时复制1GB的文件和查询执行，并且它以大于40MB/s的速度运行，而我只有一个磁盘（它是带有ISCSI媒体的虚拟机）。所有其他磁盘操作都不受影响，还有大量IO带宽。与此同时，PostgreSQL只是坐在那里，几乎没有做任何事情，非常缓慢地运行。

我有两个虚拟化的Linux服务器，一个运行PostgreSQL 9.1，另一个运行9.4。这两台服务器的PostgreSQL配置几乎相同。

有其他人有类似的经历吗？我想不出更多的办法了。救命啊。

编辑：查询“运行”了20小时，我不得不杀掉连接并重新启动服务器。令人惊讶的是，它并没有通过查询杀死连接。

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND datname = current_database();

服务器生成了以下日志：

2015-05-21 12:41:53.412 EDT FATAL:  terminating connection due to administrator command
2015-05-21 12:41:53.438 EDT FATAL:  terminating connection due to administrator command
2015-05-21 12:41:53.438 EDT STATEMENT:  UPDATE <... this is 60,000,000 record table update statement>

此外，服务器重启时间很长，生成了以下日志：

2015-05-21 12:43:36.730 EDT LOG:  received fast shutdown request
2015-05-21 12:43:36.730 EDT LOG:  aborting any active transactions
2015-05-21 12:43:36.730 EDT FATAL:  terminating connection due to administrator command
2015-05-21 12:43:36.734 EDT FATAL:  terminating connection due to administrator command
2015-05-21 12:43:36.747 EDT LOG:  autovacuum launcher shutting down
2015-05-21 12:44:36.801 EDT LOG:  received immediate shutdown request
2015-05-21 12:44:36.815 EDT WARNING:  terminating connection because of crash of another server process
2015-05-21 12:44:36.815 EDT DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

“邮政管理员命令此服务器进程回滚当前事务并退出，因为另一个服务器进程异常退出并可能损坏了共享内存” - 这是否表明PostgreSQL中存在漏洞？

编辑： 我测试了9.1、9.3和9.4。9.1和9.3都没有经历过减速。9.4在大型事务上始终会出现减速。我注意到当事务开始时，htop监视器显示高CPU使用率和进程状态为“R”（运行）。然后它逐渐变成低CPU使用率和状态“D”-磁盘（见截图）。我最大的问题是为什么9.4与9.1和9.3不同？我有十几台服务器，这种效果在整个计算机系统中都会观察到。

- oᴉɹǝɥɔ

---

你最近从9.1升级到9.4了吗？导入后你运行了vacuum analyze吗？ - joop

这是全新的安装，不是升级。我在并行运行2个独立的Linux服务器。数据库是从CSV文件导入的，并且在书籍加载完成后明确地进行了清理。这在9.1上顺利运行了多年。但在9.4上感觉进程会悄悄失败并停滞/挂起。 - oᴉɹǝɥɔ

复制或归档是否启用？此外，更新语句如何？ - Tasos Vogiatzoglou

查找表中有多少行满足条件？此外：0）表描述，包括键和索引1）调整常量，2）查询计划。 - wildplasser

3个想法：1-您是否尝试在两个服务器上运行解释计划，以查看是否有任何差异？2-您是否检查了快速数据库中是否有一些索引不在慢速数据库中？3-您是否同时运行两个虚拟机？也许第一个虚拟机已经消耗了所有主机内存，而另一个虚拟机必须使用更多的交换空间。 - Nelson Teixeira

Answer 1

感谢大家的帮助。无论我如何强调相同配置下9.4版本和之前版本性能差异的重要性，似乎没有人注意到。

通过禁用透明大页功能，问题得以解决：

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

以下是我在研究这个问题时发现有用的一些资源：
* https://dba.stackexchange.com/questions/32890/postgresql-pg-stat-activity-shows-commit/34169#34169
* https://lwn.net/Articles/591723/
* https://blogs.oracle.com/linux/entry/performance_issues_with_transparent_huge

- oᴉɹǝɥɔ

---

是的，以前的版本默认不使用大页。顺便问一下，你有没有注意到在运行9.4时top命令中的% sy（系统时间）很高？这通常表示存在这样的问题。 - alexius

Answer 2

我猜测磁盘寻址很多 - 5MB/s 对于普通（旋转）硬盘上的非常随机的IO来说是刚刚好的。

由于您不断地替换基本上所有行，我建议将dest表的填充因子设置为约45%（alter table dest set (fillfactor=45);），然后使用cluster test using test_pkey;。这将允许更新的行版本放置在同一磁盘扇区中。

此外，使用cluster src using src_pkey;，使得两个表在磁盘上具有相同的物理顺序也可以帮助提高性能。

还要记得在每次大型更新之后运行vacuum table dest;，以便旧的行版本可以在后续更新中再次使用。

您的旧服务器可能在多次更新过程中自然地演变了其填充因子。在新服务器上，它被打包到100％，因此更新的行必须放置在末尾。

- Tometzky

Answer 3

如果只有少数目标行实际上被更新，您可以使用DISTICNT FROM来避免生成新的行版本。这可以防止大量无用的磁盘流量。

UPDATE dest SET
    field1 = src.field1,
    field2 = src.field2,
    field3_id = lu.id
FROM src
JOIN lookup lu ON src.value BETWEEN lu.min AND lu.max
WHERE dest.id = src.id
        -- avoid unnecessary row versions to be generated
AND     (dest.field1 IS DISTINCT FROM src.field1
        OR dest.field1 IS DISTINCT FROM src.field1
        OR dest.field3_id IS DISTINCT FROM lu.id
        )
        ;

- joop