[postgis-users] Controlling PostGIS memory usage

[Dan Gast]

On Wed, Mar 26, 2014 at 10:47 PM, Nyall Dawson <nyall.dawson at gmail.com> wrote:
> Dan - it's not quite clear from your original post whether you have indexes
> on your geography columns? That would also have a huge impact on the
> performance of this query.

There are GIST indexes in the actual program code, but there are not
in this test case. Once I found the crash I focused on understanding
that so we don't hit it in production.


On Thu, Mar 27, 2014 at 7:25 AM, Newcomb, Doug <doug_newcomb at fws.gov> wrote:
> FYI in Postgresql 9.3 :
>
> https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3#Switch_to_Posix_shared_memory_and_mmap.28.29
>
> "except in unusual cases, system parameters such as SHMMAX and SHMALL no
> longer need to be adjusted. We need users to rigorously test and ensure that
> no memory management issues have been introduced by the change."

I hadn't thought to try this... I just threw together another VM with
9.2/2.1 and see the same behavior, so I suppose it's not related to
the 9.3 SHM changes. :/


On Thu, Mar 27, 2014 at 7:00 AM, Sandro Santilli <strk at keybit.net> wrote:
>
> I've faced this recently, and found out it was the Linux kernel's fault.
> See http://www.etalabs.net/overcommit.html
> And "Linux Memory Overcommit" here:
> http://www.postgresql.org/docs/9.1/static/kernel-resources.html

What's interesting is that I have a dev environment Ubuntu 12.04
machine (the machine where I found this initially) that has overcommit
set to 0 (overcommit allowed), and it will OOM kill the daemon
quickly, with little other impact to the running host. Running a small
VM (also 12.04) with overcommit set to 0, Linux doesn't OOM the
process but the machine becomes generally unresponsive. It will accept
^C in the client and say "Cancel request sent" but it never comes back
around. Setting overcommit to 2 on that small host causes the process
to end itself based on a failed malloc.

overcommit = 2 is definitely the best choice in this situation (i.e.
give the daemon a chance to give up gracefully), but I still don't
understand why memory balloons in the first place.

Thanks,

Dan