Postgres用交换文件填满硬盘 [英] Postgres filling up hard drive with swap files

问题描述

此线程已重命名。它曾经是：shared_buffers问题。

旧线程已经消失了。我希望通过

重新解释这个问题来获得更多方向，以及我最近做出的一些额外观察。

问题的核心是Postgres用每周大约3到7 GB的交换文件来填充我的硬盘

（这就是

千兆字节而不是兆字节）。按此速率，大约需要两个月才能用交换文件填充我的40 GB硬盘。当硬盘驱动器满
时，Postgres崩溃并且所有200个连接的客户端都挂起。这不是很好的b $ b。我可以看到交换文件，并观察它们随着时间的推移而增长。它们

位于我的Mac OS 10.3.2 eMac / var / vm /上。我知道交换文件

来自Postgres进程，因为如果我停止Postgres服务器，那么交换文件就会消失，硬盘空间就会被释放。 />

当我在

活动监视器中查看每个Postgres后端进程时，每个进程似乎占用了大量内存。进程

通常会跳转到60.04 MB的虚拟内存。他们第一次创建后很快就会产生
。然而，真实记忆是指真实记忆。每个过程使用1 MB到10 MB的范围。


我正在寻找有关设置的建议以便解决

的问题我的硬盘充满了交换文件。我的猜测是

，它一定是我正在做的事情，因为我觉得很难

想象Postgres有一个*天生*内存泄漏很大OS X.

以下是我为Postgres定制的唯一设置

安装，如果它有帮助：


In / etc / profile：

ulimit -u 512＃增加每个用户的最大进程数（从100到512）

在shell级别。

ulimit -n 40000＃增加

shell级别允许的最大打开文件数（以前为8000）。


在postgresql.conf中：

max_connections = 200

shared_buffers = 2000

/ etc / rc：

sysctl - w kern.sysv.shmmax = 167772160

sysctl -w kern.sysv.shmall = 65536

在/etc/sysctl.conf中：

kern.maxproc = 2048

kern.maxprocperuid = 512＃每个用户调高maxproc

kern.maxfiles = 40000＃调高最大文件数
kern.maxfiles perproc = 30000＃每个进程调高最大文件数

感谢您提供的任何见解！


Joe

This thread was renamed. It used to be: "shared_buffers Question". The
old thread kind of died out. I''m hoping to get some more direction by
rephrasing the problem, along with some extra observations I''ve
recently made.

The core of the problem is that Postgres is filling up my hard drive
with swap files at the rate of around 3 to 7 GB per week (that''s
Gigabytes not Megabytes) . At this rate it takes roughly two months to
fill up my 40 GB hard drive with swap files. When the hard drive is
full, Postgres crashes and all the 200 connected clients hang. It''s not
pretty. I can see the swap files and watch them grow over time. They
are located on my Mac OS 10.3.2 eMac at /var/vm/. I know the swap files
come from Postgres processes because, if I stop the Postgres server,
the swap files go away and the hard drive space is freed up.

When I look at each individual Postgres back-end process in the
Activity Monitor, each one seems to take up a lot of memory. Processes
usually jump to 60.04 MB of "virtual memory" soon after they are first
created. However, "real memory" use ranges 1 MB to 10 MB per process.

I''m looking for advice on what settings to look at in order to solve
the problem of my hard drive filling up with swap files. My guess is
that it must be something I''m doing, since I would find it hard to
imagine that Postgres has an *innate* memory leak that big on OS X.
Here are the only settings I have customized for my Postgres
installation, in case it helps:

In /etc/profile:
ulimit -u 512 # increases the max processes per user (from 100 to 512)
at the shell level.
ulimit -n 40000 # increases the max number of open files allowed by the
shell level (used to be 8000).

In postgresql.conf:
max_connections = 200
shared_buffers = 2000

In /etc/rc:
sysctl -w kern.sysv.shmmax=167772160
sysctl -w kern.sysv.shmall=65536

In /etc/sysctl.conf:
kern.maxproc=2048
kern.maxprocperuid=512 # Turn up the maxproc per user
kern.maxfiles=40000 # Turn up max files
kern.maxfilesperproc=30000 # Turn up max files per process
Thanks for any insight you can provide!

Joe

推荐答案

2004年8月20日星期五上午10:25:20 -0500，Joe Lester写道：

On Fri, Aug 20, 2004 at 10:25:20AM -0500, Joe Lester wrote:

此线程已重命名。它曾经是：shared_buffers问题。
旧线程已经消失了。我希望通过改写这个问题来获得更多的方向，以及我最近做出的一些额外的观察。

问题的核心是Postgres使用交换文件填充我的硬盘
，每周大约3到7 GB（这是GB / GB不是兆字节）。

This thread was renamed. It used to be: "shared_buffers Question".
The old thread kind of died out. I''m hoping to get some more direction
by rephrasing the problem, along with some extra observations I''ve
recently made.

The core of the problem is that Postgres is filling up my hard drive
with swap files at the rate of around 3 to 7 GB per week (that''s
Gigabytes not Megabytes).

也许你以前说过这个，但是我没跟上前一个帖子。

交换文件是什么意思？这些文件位于何处？你能否b / b
显示它们的列表？

我当然认为你的数据不会线性增长

那些交换文件，是吗？


-

Alvaro Herrera（< alvherre [a] dcc.uchile.cl>）

Si没有sabes adonde vas，es muy probable que acabes en otra parte。

---------------------------（广播结束）---------------------------

提示5：您是否检查过我们广泛的常见问题解答？<

http://www.postgresql.org /docs/faqs/FAQ.html

Maybe you said this before, but I didn''t follow the previous thread.
What do you mean by swap files? Where are those files located? Can you
show a listing of them?

I assume, of course, that your data proper does not grow linearly with
those "swap files," does it?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Si no sabes adonde vas, es muy probable que acabes en otra parte.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

感谢您的回复！

Thanks for the response!

也许你之前说过这个，但是我没跟上前面的帖子。
交换文件是什么意思？


通过交换文件，我的意思是包含内存的文件，在物理RAM溢出后，操作系统是在硬盘上维护的.b $ b。 >
这些文件位于哪里？


我可以看到交换文件并观察它们随着时间的推移而增长。它们是位于我的Mac OS 10.3.2 eMac上的/ var / vm /

。

你能显示它们的列表吗？


现在没有那么多，因为我刚刚重新启动了服务器

。但是已经，它们总共3 GB。


officelink：/ var / vm root＃ls -lh

drwx - x - x 14根轮476B 2004年2月17日app_profile

-rw ------ T 1根轮64M 31 Jul 09:10 swapfile0

-rw ------ T 1 root wheel 64M 31 Jul 15:13 swapfile1

-rw ------ T 1根轮128M 31 Jul 16:08 swapfile2

-rw ----- -T 1根轮256M 3 8月13:20 swapfile3

-rw ------ T 1根轮512M 18 8月14:29 swapfile4

-rw- ----- T 1根轮512M 18 8月16:24 swapfile5

-rw ------ T 1根轮512M 18 8月23:30 swapfile6

-rw ------ T 1根轮512M 19 Aug 12:59 swapfile7

-rw ------ T 1根轮512M 20 Aug 09:56 swapfile8

当然，我假设您的数据不会与那些交换文件线性增长。是吗？

Maybe you said this before, but I didn''t follow the previous thread.
What do you mean by swap files?
By swap files I mean the files that contain the memory that the OS is
maintaining on the hard disk, after physical RAM has overflowed.
Where are those files located?
I can see the swap files and watch them grow over time. They are
located on my Mac OS 10.3.2 eMac at /var/vm/.
Can you show a listing of them?
Right now there are not that many since I just restarted the server
recently. But already, they total 3 GB.

officelink:/var/vm root# ls -lh
drwx--x--x 14 root wheel 476B 17 Feb 2004 app_profile
-rw------T 1 root wheel 64M 31 Jul 09:10 swapfile0
-rw------T 1 root wheel 64M 31 Jul 15:13 swapfile1
-rw------T 1 root wheel 128M 31 Jul 16:08 swapfile2
-rw------T 1 root wheel 256M 3 Aug 13:20 swapfile3
-rw------T 1 root wheel 512M 18 Aug 14:29 swapfile4
-rw------T 1 root wheel 512M 18 Aug 16:24 swapfile5
-rw------T 1 root wheel 512M 18 Aug 23:30 swapfile6
-rw------T 1 root wheel 512M 19 Aug 12:59 swapfile7
-rw------T 1 root wheel 512M 20 Aug 09:56 swapfile8
I assume, of course, that your data proper does not grow linearly with
those "swap files," does it?

交换文件消耗的磁盘空间总量大大超过了我的postgres数据目录的大小，如果是这样的话这就是你要求的b
大约300 MB左右。


------------ ---------------（播出结束）---------------------------

提示1：订阅和取消订阅命令转到 ma ******* @ postgresql.org

The total amount of disk space consumed by the swap files vastly
exceeds the size of my postgres data directory, if that''s what you''re
asking, which is about 300 MB or so.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

2004年8月20日星期五上午11:45:18 -0500，Joe Lester写道：


Joe，

On Fri, Aug 20, 2004 at 11:45:18AM -0500, Joe Lester wrote:

Joe,

也许你以前说过这个，但我没跟上前一个帖子。
你是什么意思交换文件？

Maybe you said this before, but I didn''t follow the previous thread.
What do you mean by swap files?

交换文件是指在物理RAM溢出后，包含操作系统在硬盘上维护的内存的文件。

By swap files I mean the files that contain the memory that the OS is
maintaining on the hard disk, after physical RAM has overflowed.

哇，这将是最具规格的我见过的大量内存泄漏。

你怎么知道这些文件与Postgres进程有关？

我们可以看到一个顶级提取，或者一些显示进程的等效文件列表

及其内存大小？


-

Alvaro Herrera（< alvherre [a] dcc.uchile.cl> ;）

Jajaja！ Solo hablaba en serio！

---------------------------（播出结束）----- ----------------------

提示9：如果您的选择指数扫描，计划员将无视您的愿望

加入列的数据类型不匹配

Wow, this would be the most spectacular memory leak I have ever seen.
How do you know these files are related to the Postgres processes?
Could we see a top extract, or some equivalent showing a process list
and their memory sizes?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Jajaja! Solo hablaba en serio!
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match

这篇关于Postgres用交换文件填满硬盘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！