如何显示外部PostgreSQL连接的WORK_MEM？ [英] How to show WORK_MEM of external PostgreSQL connections?

问题描述

我知道我可以在psql中使用 SHOW WORK_MEM; 来查看当前使用的连接的值。但是其他连接呢？我有一个可以打开许多连接并根据上下文动态更改 WORK_MEM 的应用程序。我想监视这些 WORK_MEM 更改，以确保已应用它们。

I know I can use SHOW WORK_MEM; in psql to view the value of the connection currently used. But what about other connections? I have an application that opens many connections and dynamically changes the WORK_MEM based on context. I would like to monitor these WORK_MEM changes to ensure that they are being applied.

换句话说，我想来调整 pg_stat_activity ，以同时包含 WORK_MEM 或任何其他会话参数。我检查了 pg_top ，它不包含这样的选项。

In other words I would like to "tweak" pg_stat_activity to also include WORK_MEM or any other session parameter. I checked pg_top, it doesn't include such an option.

此EBD帖子可能是关于使用perl扩展的到显示连接CPU和RAM的使用情况是否可以修改为在每个活动套接字上执行 SHOW WORK_MEM ？

Perhaps this EBD post about using a perl extension to display connection CPU and RAM usage could be modified to execute SHOW WORK_MEM on every active socket? If so could anyone provide an example?

我在Ubuntu 14.04上使用9.5.6，以防万一。

I'm using 9.5.6 on Ubuntu 14.04 in case it matters.

推荐答案

您可以在操作系统级别使用调试器进入该过程。

You can break into the process with a debugger on the operating system level.

我将展示如何做在Linux上：

I'll show how to do it on Linux:

$ psql "dbname=test options='-c work_mem=256MB' application_name=test"
psql (9.6.1)
Type "help" for help.

test=>  SELECT pg_backend_pid();
 pg_backend_pid
----------------
          21089
(1 row)

现在我可以进入服务器计算机上的进程了：

Now I can break into the process on the server machine:

$ gdb /path/to/postgres-9.6.1/bin/postgres 21089
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-90.el6)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
...
(gdb) print work_mem
$1 = 262144
(gdb) detach
Detaching from program: /path/to/postgres-9.6.1/bin/postgres, process 21089
(gdb) quit

您不想做在生产系统上，因为后端在调试时被阻止了。

You wouldn't want to do this on a production system, since the backend is blocked while you are debugging it.

这篇关于如何显示外部PostgreSQL连接的WORK_MEM？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！