Re: Performance problems deleting data

Rafael Martinez wrote:

> CPU 50% idle, rest mainly used in "system".  Virtually no IO.  No
> blocked processes.  An impressive amount of context switches.  No swap.
>
> An strace(1) of the postgres process may give a hint about the "system"
> part; this is what it does over and over and over again.  The filename
> does change to a different file in the same directory every now and
> then, but not often.
>
> semop(4227102, 0xbf8ef23a, 1)           = 0
> semop(4227102, 0xbf8ef67a, 1)           = 0
> open("pg_subtrans/047B", O_RDWR|O_LARGEFILE) = 12
> _llseek(12, 139264, [139264], SEEK_SET) = 0
> read(12, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,
> 8192) = 8192
> close(12)                               = 0

Hmm, severe usage of subtransactions?  Does it ever write to these
pg_subtrans files?  I wonder if it's related to the ON DELETE rule that
updates alerthist.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.