Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j48GR-000641-Fa for pgsql-performance@arkaria.postgresql.org; Tue, 18 Feb 2020 19:10:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j48GQ-0008KM-7a for pgsql-performance@arkaria.postgresql.org; Tue, 18 Feb 2020 19:10:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j48GP-0008KE-JT for pgsql-performance@lists.postgresql.org; Tue, 18 Feb 2020 19:10:33 +0000 Received: from sonic309-21.consmr.mail.ne1.yahoo.com ([66.163.184.147]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j48GI-0005CP-E8 for pgsql-performance@postgresql.org; Tue, 18 Feb 2020 19:10:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1582053023; bh=rVuGQxcVH60V4Tw8L3Xr2wudFrhpJ2bL4M2T+I7oBxg=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=M1VRXkpDPTOInwFG8xaXWqUWGDHsnQEe10lILLo3LYiw5LooOYh1vcapImBpR/zmLBZnvlsvSY5/eDUlrEZngtH9HqVA4qArW+o3JQCu6S0kisZK/ErWn6mVfNFnI+j6Pe0J/ZGIU+BdQwM47yPcw/qtVZXzhpAEGu/0a+/dr5ZC5fSsGLiAqpJOy1nVwIn5kIJtB6giE3Sd3XuhSZkmDa7vKtuYkXur8Ty8y+JTQLommBI7n+sQtu8S24q7AYRIwgEqj8hi3S1ImZVny4KWVxl1l2K2DffCH05Cf0bpaEzvLQu8aJaZELzdiIS9515eaMDwFiSEhFXi5mGCkFPnVg== X-YMail-OSG: EwOqRsUVM1mmzL7LO2HJLN3aeztA5KMS39bfjS50CHAyMSM0oR6g_H1vuT0KSis kJD7kkWSNmDDUdQEtINVzVft4rlHqtrEiThkJ.d_GXrASZx0ajDFTxqEng6yd9i9kxD0mF8fZDNE b.ONfNAcus5TTjpihPDWXTInvfHBMnKKH.FX4OXxwT3MXdK7_kfrrqunlIwQmCGRTegSUWuBc9nf aUEYJUPvo_04KWWgKlOJJow3kGk4rlDYD8IINNPnvLVQYy3uryfp8rU0e3E04USPWwV0cXCb8mDc v42zl5BcZJBzVP5qniavBL97pBeLydhKukHrGNidtBSzUS5NjxvEQRDID3OIdrlteh.rbhoQmAZ4 sAfRpN5j2ZczdKpURSVLSn.Sju1yeBBW.xhdlpnC.ETVtGMiCgFZPhfk43T.QFlh9twO5St5sXXS TQu_XEAK2qY1gcVg7tu_pTSuKKq0rGmhRtb7MMdqVNax._QaLX9osGvsYEgD4QTMLv_imeBflEvk Oy6wpOzqleBz.xFMQ.mwVw.k62qshWeOmAL.JfV3LhGk5aI77M5kMgg3x4dpv4nsU1xpP0elMA5I g3tKwQuohoklWCUtR1gUcZj.vW4Bn7nx6rLfVLfkh4ZicGX9dIuqg7CY3ijs1zz9fseKzTjM.8gb ZmUHZ7oszwMDRTUKT4qnGKFL_Buv5i87f5WUH.bWLnvtVtv0ElRSd.dgR_Vlb8atvxA.0pEmGn80 adFrxJDbwQ.VxW1X.JaFynm4wFDP6VJJzCVTTKmPJuftI8F9C9LJLRxEoNjPTEkPD.2Z2DrKqwsZ 05KL9PmWVP8HhY4GKHIrIQW34U4oWIVzfiNXRD9JqecvcIDxJCSdd8VS.wz6PFPt4Ln2lXM59MI3 zFrx6ETcVKOwxJxcyuztGsf9MlwKgV7Wxx7pi.2O9h16eHt1BHd5flp6KjG7UzVRLe6oNSZC6vYk f8_I8IrL8_1x8DbXbtWui47tS6d0mRToLcWu4V8mslZhfUyudWMCjnf9WN9Ka4WMrUWJANvIZ2qk NgHirgHHcdjH6IJfVELzV.4HSoazqreA.C5CvG9lCG3XWxItO2rxiPfBpqSxwPnxx8WqxRGos24N Vnn5DNeP03d.BbP.rYdoS4J53vEbN_xVD8pYBk_OdRvrTo2PgJzJ2x7m7rMhZXYVQUwUu_WzGfWX gk6U2EPzF2OjYwFETAu4jYs8zNkPoSCfNSsNxX7sV_KoTMWR1OvogRrrSYZZ2EtlGOiy8RRFCqx2 taYoDIrukiP3N8XUp9m8ek_OwzqXrJasOaRSuolzSSv8doevUQN.nIPev7dVCfGpFVXIm_piJI.a cHEU8Bkh0e1tafojonSuF5dqLFVk- Received: from sonic.gate.mail.ne1.yahoo.com by sonic309.consmr.mail.ne1.yahoo.com with HTTP; Tue, 18 Feb 2020 19:10:23 +0000 Date: Tue, 18 Feb 2020 19:10:20 +0000 (UTC) From: Nagaraj Raj To: Justin Pryzby , Merlin Moncure Cc: postgres performance list Message-ID: <1013528313.4707645.1582053020884@mail.yahoo.com> In-Reply-To: References: <1425420656.4660607.1582047988379.ref@mail.yahoo.com> <1425420656.4660607.1582047988379@mail.yahoo.com> <20200218184037.GZ31889@telsasoft.com> Subject: Re: DB running out of memory issues after upgrade MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4707644_327837111.1582053020883" X-Mailer: WebService/1.1.15199 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36 Content-Length: 4908 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_4707644_327837111.1582053020883 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Merlin, Its configured high value for max_conn, but active and idle session have ne= ver crossed the count 50. DB Size: 20 GBTable size: 30MBRAM: 16GBvC: 4 yes, its view earlier I posted and here is there query planner for new actu= al view, "Append=C2=A0 (cost=3D0.00..47979735.57 rows=3D3194327000 width=3D288)""=C2= =A0 ->=C2=A0 Seq Scan on msghist=C2=A0 (cost=3D0.00..15847101.30 rows=3D316= 2700000 width=3D288)""=C2=A0 ->=C2=A0 Seq Scan on msghist msghist_1=C2=A0 (= cost=3D0.00..189364.27 rows=3D31627000 width=3D288)" Thanks,Rj On Tuesday, February 18, 2020, 10:51:02 AM PST, Merlin Moncure= wrote: =20 =20 On Tue, Feb 18, 2020 at 12:40 PM Justin Pryzby wrot= e: > This is almost certainly unrelated.=C2=A0 It looks like that query did a = seq scan > and accessed a large number of tuples (and pages from "shared_buffers"), = which > the OS then shows as part of that processes memory, even though *shared* > buffers are not specific to that one process. Yeah.=C2=A0 This server looks highly overprovisioned, I'm in particularly suspicious of the high max_connections setting.=C2=A0 To fetch this out I'd be tracking connections in the database, both idle and not idle, continuously.=C2=A0 The solution is most likely to install a connection pooler such as pgbouncer. merlin =20 ------=_Part_4707644_327837111.1582053020883 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Merlin,

Its configured high value for max_conn, but active and idle session have n= ever crossed the count 50.

=
DB Size: 20 GB
Table size: 30MB
RAM: 16GB
vC: = 4


yes, i= ts view earlier I posted and here is there query planner for new actual vie= w,

"Append  (cost=3D0.00..47979735.57 rows= =3D3194327000 width=3D288)"
"  ->  Seq Scan on msghi= st  (cost=3D0.00..15847101.30 rows=3D3162700000 width=3D288)"
"  ->  Seq Scan on msghist msghist_1  (cost=3D0.00..189= 364.27 rows=3D31627000 width=3D288)"


Than= ks,
Rj
=20
=20
On Tuesday, February 18, 2020, 10:51:02 AM PST, Merlin = Moncure <mmoncure@gmail.com> wrote:


On Tue, Feb 18, 2020 at 12:40 PM Just= in Pryzby <pryzby@telsasoft.com> wrote:

> This is al= most certainly unrelated.  It looks like that query did a seq scan
> and accessed a large number of tuples (and pages from "= shared_buffers"), which
> the OS then shows as part of= that processes memory, even though *shared*
> buffers= are not specific to that one process.


Yeah.  This server looks highly overprovisioned, I'm in particu= larly
suspicious of the high max_connections setting.&nbs= p; To fetch this out
I'd be tracking connections in the = database, both idle and not idle,
continuously.  Th= e solution is most likely to install a connection
pooler = such as pgbouncer.

merlin

------=_Part_4707644_327837111.1582053020883--