Re: pg_dump & performance degradation

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>> "nice" doesn't help at all when you try it?

> Only marginally; and what I really need to do is 'nice' the backend, and
> when I do that it still only helps only a little - even if I drop it to the
> lowest priority. I think a process with only a little CPU can still do a
> lot of I/O requests, and perhaps it is getting swapped back in to service
> the requests. This is just guesswork.

I think that's true --- on most Unixes, 'nice' level only affects CPU
scheduling not I/O scheduling.

It would be a bad idea to nice down a backend anyway, if the intent is
to speed up other backends. The Unix scheduler has no idea about
application-level locking, so you'll get priority-inversion problems:
once the nice'd backend has acquired any sort of lock, other backends
that may be waiting for that lock are at the mercy of the low priority
setting. In effect, your entire database setup may be running at the
nice'd priority relative to anything else on the system.

I think Philip's idea of adding some delays into pg_dump is a reasonable
answer. I'm just recommending a KISS approach to implementing the
delay, in the absence of evidence that a more complex mechanism will
actually buy anything...

regards, tom lane