Thread: pg_basebackup blocking all queries

pg_basebackup blocking all queries

From
Lonni J Friedman
Date:
Greetings,
I have a 4 server postgresql-9.1.3 cluster (one master doing streaming
replication to 3 hot standby servers).  All of them are running
Fedora-16-x86_64.  Last Friday I upgraded the entire cluster from
Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3.

I'm finding that I cannot runpg_basebackup at all, or it blocks all
SQL queries from running until pg_basebackup has completed (and the
load on the box just takes off to over 75.00).  By "blocks" I mean
that any query that is submitted just hangs and does not return at all
until pg_basebackup has stopped.   I'm assuming that this isn't
expected behavior, so I'm rather confused on what is going on.  The
command that I'm issuing is:
pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres

Can someone provide some guidance on how to debug this?

thanks!

Re: pg_basebackup blocking all queries

From
Scott Marlowe
Date:
Do the queries here help?

http://wiki.postgresql.org/wiki/Lock_Monitoring

On Tue, May 22, 2012 at 12:42 PM, Lonni J Friedman <netllama@gmail.com> wrote:
> Greetings,
> I have a 4 server postgresql-9.1.3 cluster (one master doing streaming
> replication to 3 hot standby servers).  All of them are running
> Fedora-16-x86_64.  Last Friday I upgraded the entire cluster from
> Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3.
>
> I'm finding that I cannot runpg_basebackup at all, or it blocks all
> SQL queries from running until pg_basebackup has completed (and the
> load on the box just takes off to over 75.00).  By "blocks" I mean
> that any query that is submitted just hangs and does not return at all
> until pg_basebackup has stopped.   I'm assuming that this isn't
> expected behavior, so I'm rather confused on what is going on.  The
> command that I'm issuing is:
> pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres
>
> Can someone provide some guidance on how to debug this?
>
> thanks!
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



--
To understand recursion, one must first understand recursion.

Re: pg_basebackup blocking all queries

From
Lonni J Friedman
Date:
Thanks for your reply.  Unfortunately, those queries don't shed any
light no the problem.  The first two return 0 rows, and the third just
returns 12 rows all associated with the query itself, rather than
anything else.

Any other suggestions?


On Tue, May 22, 2012 at 2:56 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Do the queries here help?
>
> http://wiki.postgresql.org/wiki/Lock_Monitoring
>
> On Tue, May 22, 2012 at 12:42 PM, Lonni J Friedman <netllama@gmail.com> wrote:
>> Greetings,
>> I have a 4 server postgresql-9.1.3 cluster (one master doing streaming
>> replication to 3 hot standby servers).  All of them are running
>> Fedora-16-x86_64.  Last Friday I upgraded the entire cluster from
>> Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3.
>>
>> I'm finding that I cannot runpg_basebackup at all, or it blocks all
>> SQL queries from running until pg_basebackup has completed (and the
>> load on the box just takes off to over 75.00).  By "blocks" I mean
>> that any query that is submitted just hangs and does not return at all
>> until pg_basebackup has stopped.   I'm assuming that this isn't
>> expected behavior, so I'm rather confused on what is going on.  The
>> command that I'm issuing is:
>> pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres
>>
>> Can someone provide some guidance on how to debug this?
>>
>> thanks!

Re: pg_basebackup blocking all queries

From
Lonni J Friedman
Date:
I'm still plagued by this.  Immediately before starting a basebackup
the load on my server is 1.00 or less.  Within a few minutes of
starting the basebackup, the load climbs steadily to 30+ and anything
trying to write to the database just sits for minutes at a time, with
overall performance on any query (read or write) being horrible
(seconds to minutes).  As soon as the basebackup completes, perf
returns to normal (and the load drops back down to 1.00 or less).

How can I debug what's wrong?

On Tue, May 22, 2012 at 3:20 PM, Lonni J Friedman <netllama@gmail.com> wrote:
> Thanks for your reply.  Unfortunately, those queries don't shed any
> light no the problem.  The first two return 0 rows, and the third just
> returns 12 rows all associated with the query itself, rather than
> anything else.
>
> Any other suggestions?
>
>
> On Tue, May 22, 2012 at 2:56 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> Do the queries here help?
>>
>> http://wiki.postgresql.org/wiki/Lock_Monitoring
>>
>> On Tue, May 22, 2012 at 12:42 PM, Lonni J Friedman <netllama@gmail.com> wrote:
>>> Greetings,
>>> I have a 4 server postgresql-9.1.3 cluster (one master doing streaming
>>> replication to 3 hot standby servers).  All of them are running
>>> Fedora-16-x86_64.  Last Friday I upgraded the entire cluster from
>>> Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3.
>>>
>>> I'm finding that I cannot runpg_basebackup at all, or it blocks all
>>> SQL queries from running until pg_basebackup has completed (and the
>>> load on the box just takes off to over 75.00).  By "blocks" I mean
>>> that any query that is submitted just hangs and does not return at all
>>> until pg_basebackup has stopped.   I'm assuming that this isn't
>>> expected behavior, so I'm rather confused on what is going on.  The
>>> command that I'm issuing is:
>>> pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres
>>>
>>> Can someone provide some guidance on how to debug this?
>>>
>>> thanks!