Обсуждение: BUG #6365: Memory leak in insert and update

The following bug has been logged on the website:

Bug reference:      6365
Logged by:          Otto Havasv=C3=B6lgyi
Email address:      havasvolgyi.otto@gmail.com
PostgreSQL version: 9.1.2
Operating system:   Win XP SP2 x86; Linux Debian 2.6.32 kernel x64
Description:=20=20=20=20=20=20=20=20

The bug can be reproduced with pgbench:

Insert script:

\set nbranches 1*:scale
\set ntellers 10*:scale
\set naccounts 100000*:scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
insert into pgbench_history (tid, bid, aid, delta, mtime) values (:tid,
:bid, :aid, :delta, current_timestamp);

Update script:

\set nbranches 1*:scale
\set ntellers 10*:scale
\set naccounts 100000*:scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
update pgbench_accounts set abalance =3D abalance + :delta where aid =3D :a=
id;

Steps:

./pgbench -i -Uotto test

./pgbench -c1 -j1 -T200 -Msimple -N -r -v -f insert.sql -Uotto testdb

./pgbench -c1 -j1 -T200 -Msimple -N -r -v -f update.sql -Uotto testdb

During this test a continuous increase of the backend memory comsumption can
be observed. During the insert test the increase is quite bigger  than
during update.

havasvolgyi.otto@gmail.com writes:
> The following bug has been logged on the website:
> Bug reference:      6365
> Logged by:          Otto Havasvölgyi
> Email address:      havasvolgyi.otto@gmail.com
> PostgreSQL version: 9.1.2
> Operating system:   Win XP SP2 x86; Linux Debian 2.6.32 kernel x64
> Description:

> The bug can be reproduced with pgbench:

I see no memory leak with this example.

I suspect you are being fooled by tools that report shared memory as
being used by a process only after it first touches a given page of
shared memory ("top" on Linux does that, for example).  This will cause
the apparent memory consumption of any long-lived backend to increase
until it has touched every available shared buffer.  But that's not a
leak, just an artifact of the reporting tool.  You can confirm for
yourself that that's what's happening by reducing shared_buffers to
a few megabytes and observing that reported memory usage increases up
to that much and then stops growing.

On Linux, I find that watching the "VIRT" column of top output is a
far more reliable guide to whether a memory leak is actually occuring.
Can't offer any suggestions as to what to use on Windows.

            regards, tom lane

On Thu, Dec 29, 2011 at 2:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> havasvolgyi.otto@gmail.com writes:
>> The following bug has been logged on the website:
>> Bug reference: =A0 =A0 =A06365
>> Logged by: =A0 =A0 =A0 =A0 =A0Otto Havasv=F6lgyi
>> Email address: =A0 =A0 =A0havasvolgyi.otto@gmail.com
>> PostgreSQL version: 9.1.2
>> Operating system: =A0 Win XP SP2 x86; Linux Debian 2.6.32 kernel x64
>> Description:
>
>> The bug can be reproduced with pgbench:
>
> I see no memory leak with this example.
>
> I suspect you are being fooled by tools that report shared memory as
> being used by a process only after it first touches a given page of
> shared memory ("top" on Linux does that, for example). =A0This will cause
> the apparent memory consumption of any long-lived backend to increase
> until it has touched every available shared buffer. =A0But that's not a
> leak, just an artifact of the reporting tool. =A0You can confirm for
> yourself that that's what's happening by reducing shared_buffers to
> a few megabytes and observing that reported memory usage increases up
> to that much and then stops growing.
>
> On Linux, I find that watching the "VIRT" column of top output is a
> far more reliable guide to whether a memory leak is actually occuring.
> Can't offer any suggestions as to what to use on Windows.

This is by the way a FAQ:
http://wiki.postgresql.org/wiki/FAQ#Why_does_PostgreSQL_use_so_much_memory.=
3F

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> On Thu, Dec 29, 2011 at 2:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I see no memory leak with this example.

> This is by the way a FAQ:
> http://wiki.postgresql.org/wiki/FAQ#Why_does_PostgreSQL_use_so_much_memory.3F

Well, to be fair, the FAQ entry didn't mention this behavior of reported
usage increasing over time.  But it seems like a good place to document
that, so I just added a paragraph about it.

            regards, tom lane