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.