On 8 July 2011 10:44, Vincent de Phily
<vincent.dephily@mobile-devices.fr> wrote:
> On Friday 08 July 2011 10:05:47 Dean Rasheed wrote:
>> > On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote:
>> >> Hi,
>> >>
>> >> I have a delete query taking 7.2G of ram (and counting) but I do not
>> >> understant why so much memory is necessary. The server has 12G, and
>> >> I'm afraid it'll go into swap. Using postgres 8.3.14.
>> >>
>> >> I'm purging some old data from table t1, which should cascade-delete
>> >> referencing rows in t2. Here's an anonymized rundown :
>> >>
>> >> # explain delete from t1 where t1id in (select t1id from t2 where
>> >> foo=0 and bar < '20101101');
>>
>> It looks as though you're hitting one of the known issues with
>> PostgreSQL and FKs. The FK constraint checks and CASCADE actions are
>> implemented using AFTER triggers, which are queued up during the query
>> to be executed at the end. For very large queries, this queue of
>> pending triggers can become very large, using up all available memory.
>>
>> There's a TODO item to try to fix this for a future version of
>> PostgreSQL (maybe I'll have another go at it for 9.2), but at the
>> moment all versions of PostgreSQL suffer from this problem.
>
> That's very interesting, and a more plausible not-optimized-yet item than my
> guesses so far, thanks. Drop me a mail if you work on this, and I'll find some
> time to test your code.
>
> I'm wondering though : this sounds like the behaviour of a "deferrable" fkey,
> which AFAICS is not the default and not my case ? I haven't explored that area
> of constraints yet, so there's certainly some detail that I'm missing.
>
Yes, it's the same issue that affects deferrable PK and FK
constraints, but even non-deferrable FKs use AFTER ROW triggers that
suffer from this problem. These triggers don't show up in a "\d" from
psql, but they are there (try select * from pg_trigger where
tgconstrrelid = 't1'::regclass) and because they fire AFTER rather
than BEFORE, queuing up large numbers of them is a problem.
Regards,
Dean
>
>> The simplest work-around for you might be to break your deletes up
>> into smaller chunks, say 100k or 1M rows at a time, eg:
>>
>> delete from t1 where t1id in (select t1id from t2 where foo=0 and bar
>> < '20101101' limit 100000);
>
> Yes, that's what we ended up doing. We canceled the query after 24h, shortly
> before the OOM killer would have, and started doing things in smaller batches.
>
>
> --
> Vincent de Phily
>
