Thread: Very high memory usage on restoring dump (with plain pqsl) on pg 9.1.2

Hey!

On a host that I'm currently in the process of migrating, I'm
experiencing massive memory usage when importing the dump (generated
using a plain pg_dump without options) using psql. The massive memory
usage happens when the CREATE INDEX commands are executed, and for a
table with about 4G of data (traffic rows in pmacct format) in it, I'm
seeing the respective (single!) PostgreSQL-process jump to around 40GB
(VIRT, RES stays at 24GB, which is the systems actual memory), before
the process is duly killed by the OOM-killer of the system (due to
running out of swap...).

I've checked the corresponding parameters (maintenance_work_mem) which
I guess influences the process growth in this case, and they are
"default" (i.e., 16MB).

Is this expected/known behaviour? Does this have anything to do with
the fact that the dump comes from a PostgreSQL 8.3.x? Thanks for any
hint!

--
--- Heiko.

Heiko Wundram <modelnine@modelnine.org> writes:
> On a host that I'm currently in the process of migrating, I'm
> experiencing massive memory usage when importing the dump (generated
> using a plain pg_dump without options) using psql. The massive memory
> usage happens when the CREATE INDEX commands are executed,

What PG version are we talking about, and what exactly is the
problematic index?

There was a memory leak in the last-but-one releases for index
operations on inet and cidr datatypes, so I'm wondering if that
explains your problem ...

            regards, tom lane

Am 22.03.2012 15:48, schrieb Tom Lane:
> What PG version are we talking about, and what exactly is the
> problematic index?

Index is on (inet, integer, smallint, timestamp w/o timezone), btree
and a primary key.

> There was a memory leak in the last-but-one releases for index
> operations on inet and cidr datatypes, so I'm wondering if that
> explains your problem ...

As written in the title, I'm experiencing this when migrating a
dump-file (created on PostgreSQL 8.3.x) to an installation of PostgreSQL
9.1.2 (plain, comes from Gentoo). But it sure sounds as though this
memory leak might be the culprit (and I'll have to do the update anyway,
from what I gather, simply because I know have empty tables which do
have the inet key). I'll be updating pgsql now and then recheck the
import.

Thanks for the hint!

--
--- Heiko.

Am 22.03.2012 18:21, schrieb Heiko Wundram:
> Am 22.03.2012 15:48, schrieb Tom Lane:
>> There was a memory leak in the last-but-one releases for index
>> operations on inet and cidr datatypes, so I'm wondering if that
>> explains your problem ...
>
> I'll be updating pgsql now and
> then recheck the import.

Yes, that worked - imported the old pmacct data flawlessly after
upgrading to 9.1.3. Thank you again for the hint at the inet problems;
searching through Google initially didn't turn up any relevant hints,
but after I rechecked with the specific reference to inet, I did find
the corresponding bug reports against PostgreSQL 9.1.2.

--
--- Heiko.