使用 PostgreSQL 9.1.2
我发现 CPU 使用率过高，并且 postmaster 任务向磁盘写入了大量数据。即使我的应用程序几乎什么都没做（每分钟插入 10 次），这种情况也会发生。但是，打开的连接数量还是合理的。
我一直在尝试确定我的应用程序中是什么导致了这种情况。我对 postgresql 还很陌生，到目前为止还没有取得任何进展。我在配置文件中打开了一些日志记录选项，并查看了 pg_stat_activity 表中的连接，但它们都处于空闲状态。然而，每个连接消耗了大约 50% 的 CPU，并且以大约 15M/s 的速度向磁盘写入数据（什么都没有读取）。
我基本上使用的是原版 postgresql.conf，只做了很少的调整。如果您能提供任何建议或指示，告诉我如何解决此问题，我将不胜感激。
以下是 top/iotop 显示的内容的示例：
Cpu(s): 18.9%us, 14.4%sy, 0.0%ni, 53.4%id, 11.8%wa, 0.0%hi, 1.5%si, 0.0%st
Mem: 32865916k total, 7263720k used, 25602196k free, 575608k buffers
Swap: 16777208k total, 0k used, 16777208k free, 4464212k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17057 postgres 20 0 236m 33m 13m R 45.0 0.1 73:48.78 postmaster
17188 postgres 20 0 219m 15m 11m R 42.3 0.0 61:45.57 postmaster
17963 postgres 20 0 219m 16m 11m R 42.3 0.1 27:15.01 postmaster
17084 postgres 20 0 219m 15m 11m S 41.7 0.0 63:13.64 postmaster
17964 postgres 20 0 219m 17m 12m R 41.7 0.1 27:23.28 postmaster
18688 postgres 20 0 219m 15m 11m R 41.3 0.0 63:46.81 postmaster
17088 postgres 20 0 226m 24m 12m R 41.0 0.1 64:39.63 postmaster
24767 postgres 20 0 219m 17m 12m R 41.0 0.1 24:39.24 postmaster
18660 postgres 20 0 219m 14m 9.9m S 40.7 0.0 60:51.52 postmaster
18664 postgres 20 0 218m 15m 11m S 40.7 0.0 61:39.61 postmaster
17962 postgres 20 0 222m 19m 11m S 40.3 0.1 11:48.79 postmaster
18671 postgres 20 0 219m 14m 9m S 39.4 0.0 60:53.21 postmaster
26168 postgres 20 0 219m 15m 10m S 38.4 0.0 59:04.55 postmaster
Total DISK READ: 0.00 B/s | Total DISK WRITE: 195.97 M/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
17962 be/4 postgres 0.00 B/s 14.83 M/s 0.00 % 0.25 % postgres: aggw aggw [local] idle
17084 be/4 postgres 0.00 B/s 15.53 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle
17963 be/4 postgres 0.00 B/s 15.00 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle
17188 be/4 postgres 0.00 B/s 14.80 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle
17964 be/4 postgres 0.00 B/s 15.50 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle
18664 be/4 postgres 0.00 B/s 15.13 M/s 0.00 % 0.23 % postgres: aggw aggw [local] idle
17088 be/4 postgres 0.00 B/s 14.71 M/s 0.00 % 0.13 % postgres: aggw aggw [local] idle
18688 be/4 postgres 0.00 B/s 14.72 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
24767 be/4 postgres 0.00 B/s 14.93 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
18671 be/4 postgres 0.00 B/s 16.14 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
17057 be/4 postgres 0.00 B/s 13.58 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
26168 be/4 postgres 0.00 B/s 15.50 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
18660 be/4 postgres 0.00 B/s 15.85 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
更新：许多文件写入似乎都是针对$PG_DATA/base/ 目录中的一些临时文件（？）。我的理解这里的文件结构的特点是，每个表基本上都存储为一个文件，其名称是表的 OID。但是，有大量名为 的文件tnn_nnnnnnn，这些文件似乎被不断写入（甚至可能被覆盖）。这些文件是做什么用的？有 ~4700 个文件，大小均为 8K：
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t12_1430975
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t16_1432736
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t28_1439066
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t24_1436243
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t24_1436210
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t19_1393372
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t28_1439051
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t8_1430334
更新：在 postmaster 进程上运行 strace 基本上会显示很多文件 I/O 内容：
open("base/16388/t24_1435947_fsm", O_RDWR) = -1 ENOENT (No such file or directory)
open("base/16388/t24_1435947_vm", O_RDWR) = -1 ENOENT (No such file or directory)
open("base/16388/t24_1435947", O_RDWR) = 9
lseek(9, 0, SEEK_END) = 8192
ftruncate(9, 0) = 0
lseek(9, 0, SEEK_END) = 0
open("base/16388/t24_1435941", O_RDWR) = 18
lseek(18, 0, SEEK_END) = 0
write(9, "\0\0\0\0\0\0\0\0\1\0\0\0000\0\360\37\360\37\4 \0\0\0\0b1\5\0\2\0\0\0"..., 8192) = 8192
lseek(18, 0, SEEK_END) = 0
close(9) = 0
open("base/16388/t24_1435947", O_RDWR) = 9
lseek(9, 0, SEEK_END) = 8192
close(18) = 0
close(9) = 0
open("base/16388/t24_1435944_fsm", O_RDWR) = -1 ENOENT (No such file or directory)
open("base/16388/t24_1435944_vm", O_RDWR) = -1 ENOENT (No such file or directory)
open("base/16388/t24_1435944", O_RDWR) = 9
lseek(9, 0, SEEK_END) = 0
close(9) = 0
更新：所以这个问题似乎与临时表有关。我们更改了设置，使临时表成为“常规”表，所有磁盘活动都消失了，性能也回到了我预期的水平。现在，这个改变只是一个快速而粗糙的测试：如果我们真的要改为使用常规表，我们会遇到并发性和清理问题。临时表真的那么糟糕吗，还是我们在滥用它们？
更新：更多背景信息。我正在使用内部开发的基于语句的复制中间件。它相当成熟，多年来已在多个项目中使用，但使用的是 MySQL。我们只在过去一两年内使用过 PostgreSQL。我们基本上将临时表用作复制机制的一部分。每当建立新连接时，我们都会为数据库中的每个表创建一个临时表。如果有 10-20 个（长寿命）连接和 ~50 个表，则可能产生大量临时表。所有临时表均使用以下命令创建：
CREATE TEMPORARY TABLE... ON COMMIT DELETE ROWS;
临时表的语义与我们的复制方案非常契合，并且简化了我们必须为 MySQL 使用的许多代码，但看起来实现效果并不理想。根据我所做的研究，我认为临时表并不适合我们使用它们来实现的功能。
我不是这个主题的内部专家（甚至差得很远），只是一个用户，所以我的解释可能不是 100% 准确，但我认为它非常接近。
答案1
你的 PostgreSQL 配置完全不对。从你最初的帖子来看，这很可疑，
Cpu(s): 18.9%us, 14.4%sy, 0.0%ni, 53.4%id, 11.8%wa, 0.0%hi, 1.5%si, 0.0%st
Mem: 32865916k total, 7263720k used, 25602196k free, 575608k buffers
Swap: 16777208k total, 0k used, 16777208k free, 4464212k cached
在您的服务器的 32GB 中，不包括约 575MB 的缓冲区，约 25GB 是可用的。
从你的 postgresql.conf 文件中，
shared_buffers = 32MB # min 128kB
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
...
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB
我假设这是一个专用数据库。如果是这样，请将其更改为以下参数并重新加载/重新启动，
shared_buffers = 16GB # min 128kB
temp_buffers = 128MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
...
work_mem = 8MB # min 64kB
maintenance_work_mem = 64MB # min 1MB
max_stack_depth = 4MB
请让我知道这会如何改变您的表现，并且可以根据需要进一步调整。
对于未记录的表，如果您的临时表包含短暂的临时数据，并且如您所提到的那样是在会话中创建的，那么最好使用未记录的表。
如果可以接受的话，您可以在会话后截断表格。
更多信息请点击这里——http://michael.otacoo.com/postgresql-2/unlogged-table-performance-in-postgresql-9-1/
我不确定为什么需要临时表进行复制。您不能使用 PostgreSQL 流式复制吗？
答案2
如果您的服务器尚未做好准备，使用临时表和长期连接（可能涉及连接池）可能会成为负担。您可以尝试使用的一个 PostgreSQL 参数是temp_buffers它控制分配给临时表的 RAM。这些临时缓冲区是按连接分配的，默认值 (8MB) 可能对您的网站来说太低了。
也许您还需要更改客户端应用程序的一些行为，具体取决于您如何使用临时表。有一个类似的问题Stack Overflow 上的好答案。
答案3
你能发布你的 postgresql.conf 文件吗？你的 postgresql 似乎明显优化不足。
您还可以发布：
您是否使用未记录的表作为临时表？
有多少个磁盘以及采用哪种 RAID 配置？