Re: nice'ing the postgres COPY backend process to make pg_dumps run more "softly"

On Mon, Mar 19, 2012 at 3:51 PM, Guillaume Lelarge
<guillaume(at)lelarge(dot)info> wrote:
> On Sun, 2012-03-18 at 21:06 -0700, Aleksey Tsalolikhin wrote:
>> Hi.  When pg_dump runs, our application becomes inoperative (too
>> slow).  I was going to ask if nice'ing the postgres backend process
>> that handles the COPY would help but I just realized probably the
>> pg_dump takes out locks when it runs and nice'ing it would just make
>> it run longer...
>>
>> However the man page says "pg_dump does not block other users
>> accessing the database  (readers  or writers)."  But if we run a
>> pg_dump, the phone starts ringing, users are complaining that the web
>> app is not working.
>>
>> Would appreciate some pointer to help me reconcile these two
>> apparently contradictory facts.
>>
>
> Depends on what your app is doing. It doesn't block any usual use of the
> database: DML are all accepted. But you cannot drop a table that pg_dump
> must save, you cannot change its definition. So there are some DDL
> commands you cannot use during a dump.
>
> Other than this, your users shouldn't be blocked. If it happens again,
> you should look at pg_locks and pg_stat_activity to understand what's
> going on.

This. pg_dump essentially runs a read only database wide transaction
that touches all objects. This will allow all update, select,
delete, etc, but will block a say, CREATE INDEX or ALTER TABLE. Let's
just say maybe some other transaction is firing off one of these guys
and is in turn already touched a customer record... bam. Classic
priority inversion -- slowing down pg_dump will only make the problem
worse.

So the very first thing to determine is if you are seeing generalized
load issues (we don't have the info for that) or locking issues
because the solution will be entirely different depending on what
you're seeing. Most likely case is you're having iowait issues and
moving the dump to another machine will fix the problem. Next most
likely case is cpu (courtesy zlib) -- also easily fixed. But you have
to know before changing things. So:

What's general load (from top) during pg_dump "bad" times?
pg_locks, etc as Guillaume asked
iowait

etc.

merlin