Обсуждение: DELETE takes too much memory
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
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
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
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)
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)
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
I was using the pg_activity monitoring tool, which I find quite awesome.
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
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