Horton
Menu

Thread

  1. DELETE takes too much memory

    Kouber Saparev <kouber@gmail.com> — 2016-07-04T16:35:47Z

    I tried to DELETE about 7 million rows at once, and the query went up to
15% of the RAM (120 GB in total), which pushed some indexes out and the
server load went up to 250, so I had to kill the query.

The involved table does not have neither foreign keys referring to other
tables, nor other tables refer to it. The size of the table itself is 19 GB
(15% of 120 GB). So why the DELETE tried to put the entire table in memory,
or what did it do to take so much memory?

I am using 9.4.5.

Regards,
--
Kouber Saparev

  2. Re: DELETE takes too much memory

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2016-07-04T17:04:58Z

    Kouber Saparev wrote:
> I tried to DELETE about 7 million rows at once, and the query went up to
> 15% of the RAM (120 GB in total), which pushed some indexes out and the
> server load went up to 250, so I had to kill the query.
> 
> The involved table does not have neither foreign keys referring to other
> tables, nor other tables refer to it. The size of the table itself is 19 GB
> (15% of 120 GB). So why the DELETE tried to put the entire table in memory,
> or what did it do to take so much memory?

Are there triggers in the table?  Deferred triggers in particular can
use memory.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  3. Re: DELETE takes too much memory

    Kouber Saparev <kouber@gmail.com> — 2016-07-04T17:10:09Z

    No. There are AFTER triggers on other tables that write to this one though.
It is an audits table, so I omitted all the foreign keys on purpose.

2016-07-04 20:04 GMT+03:00 Alvaro Herrera <alvherre@2ndquadrant.com>:

> Kouber Saparev wrote:
> > I tried to DELETE about 7 million rows at once, and the query went up to
> > 15% of the RAM (120 GB in total), which pushed some indexes out and the
> > server load went up to 250, so I had to kill the query.
> >
> > The involved table does not have neither foreign keys referring to other
> > tables, nor other tables refer to it. The size of the table itself is 19
> GB
> > (15% of 120 GB). So why the DELETE tried to put the entire table in
> memory,
> > or what did it do to take so much memory?
>
> Are there triggers in the table?  Deferred triggers in particular can
> use memory.
>
> --
> Álvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

  4. Re: DELETE takes too much memory

    Josh Berkus <josh@agliodbs.com> — 2016-07-05T18:51:54Z

    On 07/04/2016 10:10 AM, Kouber Saparev wrote:
> No. There are AFTER triggers on other tables that write to this one
> though. It is an audits table, so I omitted all the foreign keys on purpose.

Is it possible that the DELETE blocked many of those triggers due to
locking the same rows?

Incidentally, any time I get into deleting large numbers of rows, I
generally find it faster to rebuild the table instead ...

-- 
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)

  5. Re: DELETE takes too much memory

    Kouber Saparev <kouber@gmail.com> — 2016-07-05T21:03:30Z

    Well, basically there are only INSERTs going on there (it is a table
holding audit records for each DML statement). I do not see how a DELETE
statement could block an INSERT?

You are correct that rebuilding the table will be faster, but then, there
is a chance that some INSERT's will be blocked and eventually will fail
(depending on the duration of the rebuilding, the exact moment I run it,
and the involved operations on the other tables).

Could such a memory consumption be related to a GET DIAGNOSTICS plpgsql
block? The delete itself is within a stored procedure, and then I return
the amount of the deleted rows from the function:

DELETE FROM
  audits.audits
WHERE
  id <= last_synced_audits_id;

GET DIAGNOSTICS counter = ROW_COUNT;

RETURN counter;


2016-07-05 21:51 GMT+03:00 Josh Berkus <josh@agliodbs.com>:

> On 07/04/2016 10:10 AM, Kouber Saparev wrote:
> > No. There are AFTER triggers on other tables that write to this one
> > though. It is an audits table, so I omitted all the foreign keys on
> purpose.
>
> Is it possible that the DELETE blocked many of those triggers due to
> locking the same rows?
>
> Incidentally, any time I get into deleting large numbers of rows, I
> generally find it faster to rebuild the table instead ...
>
> --
> --
> Josh Berkus
> Red Hat OSAS
> (any opinions are my own)
>

  6. Re: DELETE takes too much memory

    Merlin Moncure <mmoncure@gmail.com> — 2016-07-06T16:12:02Z

    On Mon, Jul 4, 2016 at 11:35 AM, Kouber Saparev <kouber@gmail.com> wrote:
> I tried to DELETE about 7 million rows at once, and the query went up to 15%
> of the RAM (120 GB in total), which pushed some indexes out and the server
> load went up to 250, so I had to kill the query.
>
> The involved table does not have neither foreign keys referring to other
> tables, nor other tables refer to it. The size of the table itself is 19 GB
> (15% of 120 GB). So why the DELETE tried to put the entire table in memory,
> or what did it do to take so much memory?
>
> I am using 9.4.5.

How did you measure memory usage exactly?  In particular, memory
consumption from the pid attached to the query or generalized to the
server?  Is this linux and if so what memory metric did you use?  What
kinds of indexes are on this table (in particular, gin/gist?)?

merlin

  7. Re: DELETE takes too much memory

    Kouber Saparev <kouber@gmail.com> — 2016-07-07T07:39:59Z

    I was using the pg_activity monitoring tool, which I find quite awesome.

https://github.com/julmon/pg_activity

There are 3 btree indexes, here's the definition of the table itself:

                                                  Table "audits.audits"
      Column       |            Type             |
      Modifiers
-------------------+-----------------------------+-----------------------------------------------------------------------
 id                | bigint                      | not null default
nextval('audits.audits_id_seq'::regclass)
 auditable_type_id | oid                         | not null
 auditable_id      | integer                     |
 operation         | audits.operation            | not null
 old_data          | jsonb                       |
 new_data          | jsonb                       |
 user_id           | integer                     | default
(NULLIF(session.get_var('user_id'::text), ''::text))::integer
 ip                | inet                        | default
(NULLIF(session.get_var('ip'::text), ''::text))::inet
 service_name      | character varying(100)      | default
NULLIF(session.get_var('service'::text), ''::text)
 service_action    | text                        | default
NULLIF(session.get_var('action'::text), ''::text)
 created_at        | timestamp without time zone | not null default
clock_timestamp()
Indexes:
    "audits_pkey" PRIMARY KEY, btree (id)
    "index_audits_on_auditable_type_id_and_auditable_id" btree
(auditable_type_id, auditable_id)
    "index_audits_on_created_at" btree (created_at)

2016-07-06 19:12 GMT+03:00 Merlin Moncure <mmoncure@gmail.com>:

> On Mon, Jul 4, 2016 at 11:35 AM, Kouber Saparev <kouber@gmail.com> wrote:
> > I tried to DELETE about 7 million rows at once, and the query went up to
> 15%
> > of the RAM (120 GB in total), which pushed some indexes out and the
> server
> > load went up to 250, so I had to kill the query.
> >
> > The involved table does not have neither foreign keys referring to other
> > tables, nor other tables refer to it. The size of the table itself is 19
> GB
> > (15% of 120 GB). So why the DELETE tried to put the entire table in
> memory,
> > or what did it do to take so much memory?
> >
> > I am using 9.4.5.
>
> How did you measure memory usage exactly?  In particular, memory
> consumption from the pid attached to the query or generalized to the
> server?  Is this linux and if so what memory metric did you use?  What
> kinds of indexes are on this table (in particular, gin/gist?)?
>
> merlin
>

  8. Re: DELETE takes too much memory

    Jim Nasby <jim.nasby@bluetreble.com> — 2016-07-19T14:28:20Z

    On 7/5/16 4:03 PM, Kouber Saparev wrote:
> Could such a memory consumption be related to a GET DIAGNOSTICS plpgsql
> block? The delete itself is within a stored procedure, and then I return
> the amount of the deleted rows from the function:

Looking at the code, no, GET DIAG won't change anything; 
exec_stmt_execsql() is simply remembering the count returned by SPI; it 
has no idea whether anything will end up using that count.

The only thing I can think of is that you have triggers that are 
consuming the memory (either the trigger funcs, or because it's an 
after/constraint trigger), or that there's something screwy with finding 
the target rows. I can't see how the latter could be an issue if id is a 
simple int though.

There are ways to get memory debug info, but I'm not sure if they'd 
really be safe to use in production (in particular, they require 
stopping the process by attaching gdb and calling a function. I think 
you also need a special compile.)
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461