Обсуждение: PL/pgSQL memory consumption?

Hi there,

I have recently found a nice utility called memstat (which shows how
much of private
(not shared)) memory each process uses.  What kind of surprised me was
the amount
of memory that is used by PL/pgSQL (one more reason not to provide it
by default? ;-)).

For example:
# sysctl -w|grep plpgsql.so|head -6
  39048k: PID 23814 (/usr/lib/postgresql/8.2/lib/plpgsql.so)
  38972k: PID 23815 (/usr/lib/postgresql/8.2/lib/plpgsql.so)
  39048k: PID 23817 (/usr/lib/postgresql/8.2/lib/plpgsql.so)
  39048k: PID 23822 (/usr/lib/postgresql/8.2/lib/plpgsql.so)
  39048k: PID 23825 (/usr/lib/postgresql/8.2/lib/plpgsql.so)
  39048k: PID 23835 (/usr/lib/postgresql/8.2/lib/plpgsql.so)

Its an x86_64 platform, memory settings are:
 temp_buffers = 8MB
 work_mem = 8MB

...this does not include the plpgsql.so library, which is shared by
all backends (which
use PL/pgSQL).  I know that system will eventually swap out unused parts, but
then again, 40MB * number_of_connections is a bit wasteful.  It seems to me that
both temp_buffers and work_mem don't affect plpgsql.so's allocations.

I wonder if this is something specific for my setup, or if not, can we
reduce memory
used a bit.

   Regards,
       Dawid

PS: I am just starting research now, I thought other people's view is valuable.

"Dawid Kuroczko" <qnex42@gmail.com> writes:
> I have recently found a nice utility called memstat (which shows how
> much of private
> (not shared)) memory each process uses.  What kind of surprised me was
> the amount
> of memory that is used by PL/pgSQL (one more reason not to provide it
> by default? ;-)).

Why should we put any credence whatsoever in these numbers?
I rather doubt that "memstat" knows anything about our palloc
mechanism, so I don't see how it could possibly give reliable
answers about how much memory one portion or another of Postgres
is using.

Having said that, it would be interesting to know exactly what it
*is* measuring.

            regards, tom lane

On Fri, Feb 22, 2008 at 8:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Dawid Kuroczko" <qnex42@gmail.com> writes:
>  > I have recently found a nice utility called memstat (which shows how
>  > much of private
>  > (not shared)) memory each process uses.  What kind of surprised me was
>  > the amount
>  > of memory that is used by PL/pgSQL (one more reason not to provide it
>  > by default? ;-)).
>
>  Why should we put any credence whatsoever in these numbers?
>  I rather doubt that "memstat" knows anything about our palloc
>  mechanism, so I don't see how it could possibly give reliable
>  answers about how much memory one portion or another of Postgres
>  is using.
>
>  Having said that, it would be interesting to know exactly what it
>  *is* measuring.

Correct me if I'm wrong, as well, but I believe that Linux (and
probably other modern Unices) does code-sharing, meaning that separate
processes referring to the same code/libraries will refer to the same
copy in physical memory.

So, even though each process is seeing 40 MB of libpgsql, there's
likely only 1 copy in physical memory. So, the total memory
consumption in the system from this library is 40 MB, not
40*num_processes MB.

Peter

"Peter Koczan" <pjkoczan@gmail.com> writes:
> Correct me if I'm wrong, as well, but I believe that Linux (and
> probably other modern Unices) does code-sharing, meaning that separate
> processes referring to the same code/libraries will refer to the same
> copy in physical memory.

> So, even though each process is seeing 40 MB of libpgsql, there's
> likely only 1 copy in physical memory. So, the total memory
> consumption in the system from this library is 40 MB, not
> 40*num_processes MB.

plpgsql.so is not 40MB ... on my machines it's well under 1MB.  Anyway
the OP specifically stated he was not counting shared text segments.
So there's something pretty weird about these numbers.

            regards, tom lane

On Fri, Feb 22, 2008 at 3:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrot>
"Dawid Kuroczko" <qnex42@gmail.com> writes:
>  > I have recently found a nice utility called memstat (which shows how
>  > much of private
>  > (not shared)) memory each process uses.  What kind of surprised me was
>  > the amount
>  > of memory that is used by PL/pgSQL (one more reason not to provide it
>  > by default? ;-)).

I am sorry it took me so much time to respond, but was out-of-reach-of-PC
on the weekend.

>  Why should we put any credence whatsoever in these numbers?
>  I rather doubt that "memstat" knows anything about our palloc
>  mechanism, so I don't see how it could possibly give reliable
>  answers about how much memory one portion or another of Postgres
>  is using.
>
>  Having said that, it would be interesting to know exactly what it
>  *is* measuring.

What memstat [1] is doing, is that it is loading /proc/<PID>/maps
files to determine
shared objects and private memory.

What I have nailed down was that the 40MBs worth of RAM were actually
an effect of
  PERFORM set_curdict('pl_ispell');
...from within PL/pgSQL function.

...if I wrap set_curdict(...) within SQL (CREATE FUNCTION ... LANGUAGE SQL), the
allocation is rightly attributed to tsearch2.so.

Regards,
    Dawid

[1]: http://packages.debian.org/etch/memstat
PS: I wonder what can I do to lower the memory used by tsearch2.  I
see a few options
like transaction-level pooling by pgBouncer or writing Polish snowball stemmer.
PPS: I like this memstat utility more and more -- appears to be a
helpful little utility.