Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 213A69066D0 for ; Thu, 7 Jun 2012 21:07:39 -0300 (ADT) Received: from qmta09.westchester.pa.mail.comcast.net ([76.96.62.96]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Scmjp-0001R5-Gk for pgsql-admin@postgresql.org; Fri, 08 Jun 2012 00:07:39 +0000 Received: from omta09.westchester.pa.mail.comcast.net ([76.96.62.20]) by qmta09.westchester.pa.mail.comcast.net with comcast id Kc1h1j00G0SCNGk59c7Q8C; Fri, 08 Jun 2012 00:07:24 +0000 Received: from homie ([68.45.149.190]) by omta09.westchester.pa.mail.comcast.net with comcast id Kc7Q1j00L46izrY3Vc7QXL; Fri, 08 Jun 2012 00:07:24 +0000 Received: by homie (Postfix, from userid 1000) id 4C164181CBF; Thu, 7 Jun 2012 20:07:24 -0400 (EDT) To: Lonni J Friedman Cc: Magnus Hagander , pgsql-admin@postgresql.org Subject: Re: pg_basebackup blocking all queries with horrible performance References: From: Jerry Sievers Date: Thu, 07 Jun 2012 20:07:24 -0400 In-Reply-To: (Lonni J. Friedman's message of "Thu\, 7 Jun 2012 13\:08\:27 -0700") Message-ID: <874nqmy8cj.fsf@comcast.net> User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Pg-Spam-Score: -0.7 (/) X-Archive-Number: 201206/27 X-Sequence-Number: 39618 Lonni J Friedman writes: > On Thu, Jun 7, 2012 at 12:40 PM, Magnus Hagander wrote: > >> On Thu, Jun 7, 2012 at 8:04 PM, Lonni J Friedman wrote: >>> On Thu, Jun 7, 2012 at 10:41 AM, Lonni J Friedman 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 732.216.7255