我有一台运行Postgres 9.1.15的服务器。该服务器有2GB的RAM和没有交换空间。间歇性地,Postgres会在某些SELECT上开始出现“内存不足”错误,并将继续这样做,直到我重新启动Postgres或连接到它的一些客户端。奇怪的是,当发生这种情况时,free
仍然报告超过500MB的可用内存。
select version();
:
PostgreSQL 9.1.15 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
uname -a
:
Linux db 3.2.0-23-virtual #36-Ubuntu SMP Tue Apr 10 22:29:03 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
Postgresql.conf(其他所有内容都已注释/默认):
max_connections = 100
shared_buffers = 500MB
work_mem = 2000kB
maintenance_work_mem = 128MB
wal_buffers = 16MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
random_page_cost = 2.0
effective_cache_size = 1000MB
default_statistics_target = 100
log_temp_files = 0
我从pgtune获得了这些值(我选择了“混合类型的应用程序”),并根据我的阅读进行了一些调整,但没有取得很多实际进展。目前有68个连接,这是一个典型的数字(我还没有使用pgbouncer或任何其他连接池器)。
/etc/sysctl.conf
:
kernel.shmmax=1050451968
kernel.shmall=256458
vm.overcommit_ratio=100
vm.overcommit_memory=2
两周前,由于OOM killer杀死了Postgres服务器,我首先将overcommit_memory
更改为2。在那之前,该服务器长时间运行良好。现在出现的错误虽然不那么严重,但非常烦人,因为它们更加频繁。
我还没有找到导致Postgres运行“内存不足”的第一个事件 - 每次似乎都不同。最近一次崩溃时,记录的前三行是:
2015-04-07 05:32:39 UTC ERROR: out of memory
2015-04-07 05:32:39 UTC DETAIL: Failed on request of size 125.
2015-04-07 05:32:39 UTC CONTEXT: automatic analyze of table "xxx.public.delayed_jobs"
TopMemoryContext: 68688 total in 10 blocks; 4560 free (4 chunks); 64128 used
[... snipped heaps of lines which I can provide if they are useful ...]
---
2015-04-07 05:33:58 UTC ERROR: out of memory
2015-04-07 05:33:58 UTC DETAIL: Failed on request of size 16.
2015-04-07 05:33:58 UTC STATEMENT: SELECT oid, typname, typelem, typdelim, typinput FROM pg_type
2015-04-07 05:33:59 UTC LOG: could not fork new process for connection: Cannot allocate memory
2015-04-07 05:33:59 UTC LOG: could not fork new process for connection: Cannot allocate memory
2015-04-07 05:33:59 UTC LOG: could not fork new process for connection: Cannot allocate memory
TopMemoryContext: 396368 total in 50 blocks; 10160 free (28 chunks); 386208 used
[... snipped heaps of lines which I can provide if they are useful ...]
---
2015-04-07 05:33:59 UTC ERROR: out of memory
2015-04-07 05:33:59 UTC DETAIL: Failed on request of size 1840.
2015-04-07 05:33:59 UTC STATEMENT: SELECT... [nested select with 4 joins, 19 ands, and 2 order bys]
TopMemoryContext: 388176 total in 49 blocks; 17264 free (55 chunks); 370912 used
在那之前的崩溃中,几个小时之前,只有最后一个查询的三个实例作为崩溃的前三行。由于该查询经常运行,因此我不确定问题是否是由于该查询引起的,或者仅仅因为它是一个相当复杂的SELECT一直在运行而出现在错误日志中。话虽如此,这是它的EXPLAIN ANALYZE: http://explain.depesz.com/s/r00 这是postgres用户的
ulimit -a
的样子:core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 15956
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 15956
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
下次出现崩溃时,我会尝试从free
获取精确的数字,同时这里是我所拥有的所有信息的脑力倾泻。
你有任何想法可以继续探讨吗?
max_connections
。降低shared_buffers
(:-信任于OS-cache)也是一个选项(在Linux中,共享内存被锁定在核心中且无法交换,如果我没记错的话)。还有:添加一些交换空间。 - joopmaintenance_work_mem
已经有了很大的改进。然而,由free
报告的内存使用仍会随着时间的推移逐渐增加(只是慢得多)。我还需要注意或者知道其他什么吗? - Alex Ghiculescu