public inbox for [email protected]
help / color / mirror / Atom feedDELETE takes too much memory
8+ messages / 5 participants
[nested] [flat]
* DELETE takes too much memory
@ 2016-07-04 16:35 Kouber Saparev <[email protected]>
2016-07-04 17:04 ` Re: DELETE takes too much memory Alvaro Herrera <[email protected]>
2016-07-06 16:12 ` Re: DELETE takes too much memory Merlin Moncure <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Kouber Saparev @ 2016-07-04 16:35 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DELETE takes too much memory
2016-07-04 16:35 DELETE takes too much memory Kouber Saparev <[email protected]>
@ 2016-07-04 17:04 ` Alvaro Herrera <[email protected]>
2016-07-04 17:10 ` Re: DELETE takes too much memory Kouber Saparev <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2016-07-04 17:04 UTC (permalink / raw)
To: Kouber Saparev <[email protected]>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DELETE takes too much memory
2016-07-04 16:35 DELETE takes too much memory Kouber Saparev <[email protected]>
2016-07-04 17:04 ` Re: DELETE takes too much memory Alvaro Herrera <[email protected]>
@ 2016-07-04 17:10 ` Kouber Saparev <[email protected]>
2016-07-05 18:51 ` Re: DELETE takes too much memory Josh Berkus <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Kouber Saparev @ 2016-07-04 17:10 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: pgsql-performance
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 <[email protected]>:
> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DELETE takes too much memory
2016-07-04 16:35 DELETE takes too much memory Kouber Saparev <[email protected]>
2016-07-04 17:04 ` Re: DELETE takes too much memory Alvaro Herrera <[email protected]>
2016-07-04 17:10 ` Re: DELETE takes too much memory Kouber Saparev <[email protected]>
@ 2016-07-05 18:51 ` Josh Berkus <[email protected]>
2016-07-05 21:03 ` Re: DELETE takes too much memory Kouber Saparev <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2016-07-05 18:51 UTC (permalink / raw)
To: Kouber Saparev <[email protected]>; Alvaro Herrera <[email protected]>; +Cc: pgsql-performance
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)
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DELETE takes too much memory
2016-07-04 16:35 DELETE takes too much memory Kouber Saparev <[email protected]>
2016-07-04 17:04 ` Re: DELETE takes too much memory Alvaro Herrera <[email protected]>
2016-07-04 17:10 ` Re: DELETE takes too much memory Kouber Saparev <[email protected]>
2016-07-05 18:51 ` Re: DELETE takes too much memory Josh Berkus <[email protected]>
@ 2016-07-05 21:03 ` Kouber Saparev <[email protected]>
2016-07-19 14:28 ` Re: DELETE takes too much memory Jim Nasby <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Kouber Saparev @ 2016-07-05 21:03 UTC (permalink / raw)
To: Josh Berkus <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; pgsql-performance
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 <[email protected]>:
> 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)
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DELETE takes too much memory
2016-07-04 16:35 DELETE takes too much memory Kouber Saparev <[email protected]>
2016-07-04 17:04 ` Re: DELETE takes too much memory Alvaro Herrera <[email protected]>
2016-07-04 17:10 ` Re: DELETE takes too much memory Kouber Saparev <[email protected]>
2016-07-05 18:51 ` Re: DELETE takes too much memory Josh Berkus <[email protected]>
2016-07-05 21:03 ` Re: DELETE takes too much memory Kouber Saparev <[email protected]>
@ 2016-07-19 14:28 ` Jim Nasby <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Jim Nasby @ 2016-07-19 14:28 UTC (permalink / raw)
To: Kouber Saparev <[email protected]>; Josh Berkus <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; pgsql-performance
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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DELETE takes too much memory
2016-07-04 16:35 DELETE takes too much memory Kouber Saparev <[email protected]>
@ 2016-07-06 16:12 ` Merlin Moncure <[email protected]>
2016-07-07 07:39 ` Re: DELETE takes too much memory Kouber Saparev <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Merlin Moncure @ 2016-07-06 16:12 UTC (permalink / raw)
To: Kouber Saparev <[email protected]>; +Cc: pgsql-performance
On Mon, Jul 4, 2016 at 11:35 AM, Kouber Saparev <[email protected]> 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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DELETE takes too much memory
2016-07-04 16:35 DELETE takes too much memory Kouber Saparev <[email protected]>
2016-07-06 16:12 ` Re: DELETE takes too much memory Merlin Moncure <[email protected]>
@ 2016-07-07 07:39 ` Kouber Saparev <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Kouber Saparev @ 2016-07-07 07:39 UTC (permalink / raw)
To: Merlin Moncure <[email protected]>; +Cc: pgsql-performance
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 <[email protected]>:
> On Mon, Jul 4, 2016 at 11:35 AM, Kouber Saparev <[email protected]> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2016-07-19 14:28 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-07-04 16:35 DELETE takes too much memory Kouber Saparev <[email protected]>
2016-07-04 17:04 ` Alvaro Herrera <[email protected]>
2016-07-04 17:10 ` Kouber Saparev <[email protected]>
2016-07-05 18:51 ` Josh Berkus <[email protected]>
2016-07-05 21:03 ` Kouber Saparev <[email protected]>
2016-07-19 14:28 ` Jim Nasby <[email protected]>
2016-07-06 16:12 ` Merlin Moncure <[email protected]>
2016-07-07 07:39 ` Kouber Saparev <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox