Hi,
I've facing an out of memory condition after running SLONY several hours to
get a 1TB database with about 23,000 tables replicated. The error occurs
after about 50% of the tables were replicated.
Most of the 48GB memory is being used for file system cache but for some
reason the initial copy of one table performed by SLONY abended due to an
out of memory condition. The table that was being transferred at the moment
of the abend has two text columns.
After the OOM condition is raised, "select *" of that specific table also
returns out of memory condition.
I guess postgresql is trying to perform an atomic allocation (those which
cannot wait for reclaim) to get a continues memory area and is failing due
to memory fragmentation.
My idea to prevent this issue is to reserve 500MB of free storage for
atomic allocations using vm.min_free_kbytes = 500000000 in the
/etc/sysctl.conf.
Is this a good approach to solve it?
Another question: is it safe to flush file system cache using these steps:
1) Shutdown postgresql
2) sync
3) echo 1 > /proc/sys/vm/drop_caches;
4) Startup postgresql
Some data about the issue:
SLONY error:
2014-12-01 12:14:56 BRST ERROR remoteWorkerThread_1: copy to stdout on
provider - PGRES_FATAL_ERROR ERROR: out of memory
DETAIL: Failed on request of size 123410655.
# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.3 (Santiago)
# uname -m
x86_64
# free
total used free shared buffers cached
Mem: 49422076 49038348 383728 0 268488 47520476
-/+ buffers/cache: 1249384 48172692
Swap: 16777208 0 16777208
# cat /proc/meminfo | grep Commit
CommitLimit: 41488244 kB
Committed_AS: 689312 kB
# /sbin/sysctl vm.min_free_kbytes
vm.min_free_kbytes = 135168
After SLONY gets the out of memory condition, select * of the table also
does not work:
FiscalWeb=# select * from "8147_spunico"."sincdc";
ERROR: out of memory
DETAIL: Failed on request of size 268435456.
Backup of the table using pg_dump also gives out of memory condition.
Buddyinfo indicates memory fragmentation after getting out of memory
condition:
# cat /proc/buddyinfo
Node 0, zone DMA 3 2 2 3 2 1 1
0 1 0 3
Node 0, zone DMA32 94091 69426 30367 7531 996 126 8
0 0 1 0
Node 0, zone Normal 6840 23 0 0 0 0 0
0 0 0 1
Node 1, zone Normal 730 338 159 93 44 26 11
9 3 1 3
Node 2, zone Normal 68 535 309 144 60 18 13
12 32 29 7
Node 3, zone Normal 319246 341233 173115 52602 5989 646 232
63 8 3 1
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.21 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.4.6 20120305 (Red Hat 4.4.6-4)
(1 row)
Thank you!
Carlos Reimer
