Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXUck-0000Vb-RO for pgsql-performance@arkaria.postgresql.org; Mon, 16 Mar 2015 13:00:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YXUcj-0004w8-Qi for pgsql-performance@arkaria.postgresql.org; Mon, 16 Mar 2015 13:00:01 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YXUci-0004vz-Bo for pgsql-performance@postgresql.org; Mon, 16 Mar 2015 13:00:00 +0000 Received: from mail-wi0-x232.google.com ([2a00:1450:400c:c05::232]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YXUce-00056c-1h for pgsql-performance@postgresql.org; Mon, 16 Mar 2015 12:59:58 +0000 Received: by wixw10 with SMTP id w10so29803675wix.0 for ; Mon, 16 Mar 2015 05:59:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=sender:date:from:to:message-id:in-reply-to:references:subject :mime-version:content-type; bh=I3VRiMokc4j0ZVVGTB+7PNxDvxoXTr43uv29aR9niJg=; b=fmr5PPR8AeoPXHysLeteAH4otBZ2p4EpFgHFadGk0QAn0gmhzLQ0CdZv5P8cejIFdv /E1byxMeSEVje9EvmDrYdEZ0Qo+bogHJ35/v9kzaJeflcWsdAEL1HeHPHuBEdq2blgJn o3Ik7wfwXoBEMLptv8B+N7P+4pX3nOzL1264FRL6erFUiUzGSbAbtDz3dkI2LKbMmNy8 0T5zOGsoVDCU9+NKKEqpYGY4JbV9a1L5Pc4cVLt9PC4BCICW3dESPLPgPdlyBkVEbkAO eWsPqEUJJUGP/1nV0zM8FX9UAPoUKo8lUbouPGaFT5intDIBBh2pWTCo8ccDiJNWSYYx dzFQ== X-Received: by 10.194.83.66 with SMTP id o2mr125034236wjy.55.1426510794280; Mon, 16 Mar 2015 05:59:54 -0700 (PDT) Received: from poot (158.Red-79-157-19.dynamicIP.rima-tde.net. [79.157.19.158]) by mx.google.com with ESMTPSA id e2sm15308060wjy.46.2015.03.16.05.59.53 for (version=TLSv1.2 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Mon, 16 Mar 2015 05:59:53 -0700 (PDT) Date: Mon, 16 Mar 2015 13:59:52 +0100 From: Robert Kaye To: pgsql-performance@postgresql.org Message-ID: In-Reply-To: <55063EBC.5020405@tara-lu.com> References: <55063EBC.5020405@tara-lu.com> Subject: Re: MusicBrainz postgres performance issues X-Mailer: Airmail (249) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="5506d3c8_625558ec_15b19" X-Pg-Spam-Score: -2.3 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --5506d3c8_625558ec_15b19 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On March 16, 2015 at 3:24:34 AM, Roxanne Reid-Bennett (rox=40tara-lu.com)= wrote: Robert, Wow - You've engaged the wizards indeed. I haven't heard or seen anything that would answer my *second* question i= f faced with this (my first would have been =22what changed=22).... Yes, indeed =E2=80=94 I feel honored to have so many people chime into th= is issue. The problem was that nothing abnormal was happening =E2=80=94 just the no= rmal queries were running that hadn=E2=80=99t given us any problems for m= onths. We undid everything that had been recently changed in an effort to= address =E2=80=9Cwhat changed=E2=80=9D. Nothing helped, which is what ha= d us so perplexed. However, I am glad to report that our problems are fixed and that our ser= ver is back to humming along nicely.=C2=A0 What we changed: 1. As it was pointed out here, max=5Fconnections of 500 was in fact insan= ely high, especially in light of using PGbouncer. Before we used PGbounce= r we needed a lot more connections and when we started using PGbouncer, w= e never reduced this number. 2. Our=C2=A0server=5Flifetime was set far too high (1 hour). Josh Berkus = suggested lowering that to 5 minutes. 3. We reduced the number of PGbouncer active connections to the DB. What we learned: 1. We had too many backends 2. The backends were being kept around for too long by PGbouncer. 3. This caused too many idle backends to kick around. Once we exhausted p= hysical ram, we started swapping. 4. Linux 3.2 apparently has some less than desirable swap behaviours. Onc= e we started swapping, everything went nuts.=C2=A0 Going forward we=E2=80=99re going to upgrade our kernel the next time we = have down time for our site and the rest should be sorted now. I wanted to thank everyone who contributed their thoughts to this thread = =E2=80=94 THANK YOU. And as I said to Josh earlier: =22Postgres rocks our world. I=E2=80=99m i= mmensely pleased that once again the problems were our own stupidity and = not PG=E2=80=99s fault. In over 10 years of us using PG, it has never bee= n PG=E2=80=99s fault.=C2=A0Not once.=E2=80=9D And thus we=E2=80=99re one tiny bit smarter today. Thank you everyone=21 P.S. If anyone would still like to get some more information about this p= roblem for their own edification, please let me know. Given that we=E2=80= =99ve fixed the issue, I don=E2=80=99t want to spam this list by respondi= ng to all the questions that were posed. -- --ruaok =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Robert Kaye =C2=A0=C2=A0=C2=A0=C2=A0-- =C2=A0=C2=A0=C2=A0=C2=A0rob=40musi= cbrainz.org=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-- =C2=A0=C2=A0=C2=A0http://musi= cbrainz.org --5506d3c8_625558ec_15b19 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline