Обсуждение: linux, memory (mis)accounting/reporting, and the planner/optimizer

Apologies if this is a FAQ, but...

Given linux's (mis)accounting/reporting of per-process memory, including
shared memory (see for example this page:
http://lwn.net/Articles/230975/) how does postgresql interpret and use
the information that's provided?  Does it use the information as-is?
Does it just accept the configuration parameters provided (e.g. --
shared_buffers, effective_cache_size, etc.)?  Or does it combine its
internal knowledge of what it's sharing and adjust what linux reports
accordingly?

I'm aware that there are lots of userspace tools for accessing what the
kernel reports, but I think its reporting is still problematic,
especially for apps that use shared memory.  (User space tools like sar,
ps, top, pmap, free, vmstat, iostat, slabinfo, et al., as well as just
looking at /proc fds -- /proc/meminfo, etc.)

On Wed, 21 Jan 2009, Dave Youatt wrote:

> Does it just accept the configuration parameters provided (e.g. --
> shared_buffers, effective_cache_size, etc.)?

That's it.  The only time PostgreSQL gets a report from the OS related to
memory is if it makes an allocation attempt that fails.  Couldn't care
less what Linux thinks the rest of the time--unless the OOM killer goes on
a rampage, counts shared memory badly, and decides to kill a database
process that is.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith wrote:
> On Wed, 21 Jan 2009, Dave Youatt wrote:
>
>> Does it just accept the configuration parameters provided (e.g. --
>> shared_buffers, effective_cache_size, etc.)?
>
> That's it.  The only time PostgreSQL gets a report from the OS related
> to memory is if it makes an allocation attempt that fails.  Couldn't
> care less what Linux thinks the rest of the time--unless the OOM killer
> goes on a rampage, counts shared memory badly, and decides to kill a
> database process that is.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>

The shared memory accounting in Linux got better in the 2.6.25 kernel,
although I'm not sure the user space tools are fully deployed even today
to track it. And of course, lots of servers still use kernels older than
2.6.25.

Re the OOM killer -- maybe a patch to the kernel could make things
"better"??

--
M. Edward (Ed) Borasky

I've never met a happy clam. In fact, most of them were pretty steamed.

On Wed, 21 Jan 2009, M. Edward (Ed) Borasky wrote:

> Re the OOM killer -- maybe a patch to the kernel could make things
> "better"??

People have tried to raise awareness of it; sample:

http://lkml.org/lkml/2007/2/9/275

without much success.  The Linux kernel hackers dislike the whole approach
PostgreSQL uses to allocate shared memory anyway--witness the backlash
against any attempt to raise SHMMAX.

I found the long thread that beats this issue to death in the archives
again:

http://archives.postgresql.org/pgsql-hackers/2008-02/msg00026.php

That discussion should get raised to a higher profile eventually, maybe a
summary on the wiki.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith wrote:
> On Wed, 21 Jan 2009, M. Edward (Ed) Borasky wrote:
>
>> Re the OOM killer -- maybe a patch to the kernel could make things
>> "better"??
>
> People have tried to raise awareness of it; sample:
>
> http://lkml.org/lkml/2007/2/9/275
>
> without much success.  The Linux kernel hackers dislike the whole
> approach PostgreSQL uses to allocate shared memory anyway--witness the
> backlash against any attempt to raise SHMMAX.
>
> I found the long thread that beats this issue to death in the archives
> again:
>
> http://archives.postgresql.org/pgsql-hackers/2008-02/msg00026.php
>
> That discussion should get raised to a higher profile eventually, maybe
> a summary on the wiki.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
Yes, please collect as much detail as you can in some centralized place.
For recent kernels (2.6.25+) the memory accounting is much better, and
if nothing else, there might be some things PostgreSQL could do to
minimize the probability of getting hit, at the cost of some
platform-dependent (/proc reading) code. The problem is that
"enterprise" Linux distros aren't running 2.6.25+ yet. :(

--
M. Edward (Ed) Borasky

I've never met a happy clam. In fact, most of them were pretty steamed.