Re: [PERFORM] DELETE takes too much memory
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 (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] DELETE takes too much memory
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: > On Mon, Jul 4, 2016 at 11:35 AM, 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? > > > > 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 >
Re: [PERFORM] DELETE takes too much memory
On Mon, Jul 4, 2016 at 11:35 AM, Kouber Saparevwrote: > 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 (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] DELETE takes too much memory
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: > 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) >
Re: [PERFORM] DELETE takes too much memory
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 (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] DELETE takes too much memory
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: > 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 Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: [PERFORM] DELETE takes too much memory
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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