Re: DELETE takes too much memory

Поиск
Список
Период
Сортировка
От Kouber Saparev
Тема Re: DELETE takes too much memory
Дата
Msg-id CAN4RuQtDxv0tAs=bY-6RyhSfUxPRc=XJV-6iLrP+yyE9snJDQQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: DELETE takes too much memory  (Josh Berkus <josh@agliodbs.com>)
Ответы Re: DELETE takes too much memory  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Список 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 <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)

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Mkrtchyan, Tigran"
Дата:
Сообщение: Re: Tuning guidelines for server with 256GB of RAM and SSDs?
Следующее
От: Torsten Zuehlsdorff
Дата:
Сообщение: Re: less than 2 sec for response - possible?