Skip site navigation (1) Skip section navigation (2)

Re: Odd out of memory problem.

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Greg Stark <stark(at)mit(dot)edu>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Odd out of memory problem.
Date: 2012-03-27 13:46:07
Message-ID: 4F71C49F.2020403@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-hackers

On 03/26/2012 01:54 PM, Andrew Dunstan wrote:
>
>
> On 03/26/2012 01:34 PM, Tom Lane wrote:
>> Andrew Dunstan<andrew(at)dunslane(dot)net>  writes:
>>> On 03/26/2012 01:06 PM, Heikki Linnakangas wrote:
>>>>> Is it possible this job is inserting and then updating (or deleteing)
>>>>> the row it just inserted and doing a large number of such
>>>>> insert/update operations all within the same transaction? Or perhaps
>>>>> it's updating the same row over and over again?
>>> It's all in a single transaction. In fact the solution I'm currently
>>> testing and seems to be working involves breaking it up into batches of
>>> a few thousand LOs restored per batch.
>> Hm.  The test case is just a straight pg_restore of lots and lots of 
>> LOs?
>> What pg_dump version was the dump made with?
>>
>>
>
>
> 8.4.8, same as the target. We get the same issue whether we restore 
> direct to the database from pg_restore or via a text dump.



Following this up, the workaround of making small batches of LOs did 
solve that memory issue. Here's what I did:

    pg_restore --list full_export.dmp | grep BLOB > bloblist
    pg_restore --use-list=bloblist full_export.dmp | \
        perl -n e  ' $n++  if /lo_open/; ' \
             -e ' print " end; begin;\n" if (/lo_open/ && ($n % 1000 ==
    0)); ' \
             -e ' print ;' |  \
        psql -t -q -v ON_ERROR_STOP "dbname=adtest" > /dev/null


That's a fairly ugly hack to have to use.


cheers

andrew

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-03-27 13:49:25
Subject: Re: Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)
Previous:From: Andres FreundDate: 2012-03-27 13:37:18
Subject: Re: Command Triggers patch v18

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group