Skip site navigation (1) Skip section navigation (2)

Re: pg_basebackup blocking all queries with horrible performance

From: Jerry Sievers <gsievers19(at)comcast(dot)net>
To: Lonni J Friedman <netllama(at)gmail(dot)com>
Cc: Magnus Hagander <magnus(at)hagander(dot)net>, pgsql-admin(at)postgresql(dot)org
Subject: Re: pg_basebackup blocking all queries with horrible performance
Date: 2012-06-08 00:07:24
Message-ID: 874nqmy8cj.fsf@comcast.net (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-hackers
Lonni J Friedman <netllama(at)gmail(dot)com> writes:

> On Thu, Jun 7, 2012 at 12:40 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>
>> On Thu, Jun 7, 2012 at 8:04 PM, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:
>>> On Thu, Jun 7, 2012 at 10:41 AM, Lonni J Friedman <netllama(at)gmail(dot)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.
>>>>
>>>> http://wiki.postgresql.org/wiki/Lock_Monitoring
>>>
>>> err, i included that URL but neglected to explain why.  On a different
>>> list someone suggested that I verify that there were no locks that
>>> were blocking things, and I did so, and found no locks.
>>>
>>> So I'm still at a loss why pg_basebackup is killing perf, and would
>>> appreciate pointers on how to debug it or at least reduce its impact
>>> on performance if that is possible.
>>>
>>
>> My guess would be that you are overloading your I/O system. You should
>> look at values from iostat and vmstat from when the system works fine
>> and when you run pg_basebackup, that should give you a hint in the
>> right direction.
>
> ok, thanks.  i'll take a look at that.  If this turns out to be the
> issue, is there some way to get pg_basebackup to run more slowly, so
> that it has less impact?  Or could I do this with ionice on the
> pg_basebackup process?

You might try stopping pg_basebackup in place with SIGSTOP and check
if problem goes away.  SIGCONT and you should  start having
sluggishness again.

If verified, then any sort of throttling mechanism should work.

> -- 
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 732.216.7255

In response to

Responses

pgsql-hackers by date

Next:From: Sergey KoposovDate: 2012-06-08 00:28:15
Subject: Re: slow dropping of tables, DropRelFileNodeBuffers, tas
Previous:From: Tom LaneDate: 2012-06-08 00:06:44
Subject: Re: log_newpage header comment

pgsql-admin by date

Next:From: Lonni J FriedmanDate: 2012-06-08 01:01:46
Subject: Re: pg_basebackup blocking all queries with horrible performance
Previous:From: Lonni J FriedmanDate: 2012-06-07 20:08:27
Subject: Re: pg_basebackup blocking all queries with horrible performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group