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 1j47K6-00045t-OL for pgsql-pkg-yum@arkaria.postgresql.org; Tue, 18 Feb 2020 18:10:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j47K5-0005Uh-KQ for pgsql-pkg-yum@arkaria.postgresql.org; Tue, 18 Feb 2020 18:10:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j47K5-0005UO-5N for pgsql-pkg-yum@lists.postgresql.org; Tue, 18 Feb 2020 18:10:17 +0000 Received: from sonic311-24.consmr.mail.ne1.yahoo.com ([66.163.188.205]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j47K1-0002d6-Eh for pgsql-pkg-yum@postgresql.org; Tue, 18 Feb 2020 18:10:15 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1582049412; bh=uYivneMmORXQ6ND5WRTdWqYH1vWpcl+R2iL5my6i6uY=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=OMsizeProTh0hOHjKK4jendNaml/IlUl6O4pXnZKPrSDwL92H8mGqANqldTBz19G3/2ZlQZjsb6Y2C78bTj3z01J5qLzkTRvCgzUA0uEYw1A+HpSSMcFARJI2e87/LYgp0udrOtog+ODIBMLBG30sZmC7UDAdCphhPEJ874a4YuWXYLrLCsB9uF/3xpxsX8hQ78Ul//KLrvBs/18CNYY08v3bsCIJh11ZZMVaPAgHD1qEStFB1/PX1v0nDAOk7j1TPNeIvPhXrznjYhv1AegItLbY9JiJXWcnJ76OPHyJh5jY9dOi1hogtkdgxxvuR6vzSuC+48QqxIIwA/xvsujCA== X-YMail-OSG: LEIDJaQVM1nNPMbzvOYuzZiAL1uCyhnLNKbEbDdv1pDbopQaudnf8kE7FJ58RzL hW6Rw4_U2kpLC_NQIM8visoe10aGZm78yr9Cl5Ui0Yg5k63tm.kuGfGkdals2O2EinMiLB9HHrPc C1idMkkYYXo7dKY3w2gSLpXVfXa3I1SY8lqfj932KckZvKQSVpMPHH2SYrvibFa5kEthA2Ge8hu8 vdAv6MyiJ903Cfi7cmCU7oeJHxUNMZLt1HViraDn70tAYn167ZFeUdaY5sbSs4GqUer.PkdpgYlA erWYTLDD47nxtpg1Vc81U9snz9azAT.Db45XOzhRc1lWx_Rd0GLC.r7Z10P.bL5RneVvPjbasML4 IqXL0Kyr6Kku44YFXCu5V.c9WEoVfoThhR5jaeCbijyXllgTVB2A_OR2_PUkKi0zl98J8XytMjX3 H.oFvrED.lxUCj6L1MDdm5TsTblVSK_9QGcd.S_kvqqPxT79Hsp5S78ZAfLNMNDoiTCqCYYRsGT2 .1r_dLOaba.VNq4Rgr3mGt91_RG7YKOlnPy3Ni9mThZkOxmvlQ0rG5vYM41K3i_PFflck0TBm5gN Tq7N45iPDVtVofY3SDRlGPVznnpzMdKUSylURSliXQWwgJzZ2YR8wqGPs_ygg13yG23v6C4W790f eyPrwzHE.yhZsychVka35Sl_T7FICu0vjvikOeRGZ7sljgMQ5jcEy6XFTstiUiYP9dBh0Lm2BSwu Zmowc0JQWg.QU723CDCBUJLXrILIdGjWEP3LTL.N2AHaaky1gqXyF_9rqbBvKBgqk6DCDb3AwLfC vW0S3PntM4Cy2YfCmvT9ZK9Ui4YVRzdcVH4QTlnP7cFxOsVMyjtGo8wto0MOarrkNBkCMvfV45FQ HPSDMo60aNxXgcP6MO7E2WiPibqzKtOEfkagafn1hwmO6b_RLvVEt39fMpP67qYR2..i2FbtbAuS 1NL4wvDuq004YqSsVcDuX6H0.l_S_d1O7rbUqRUCWl8VNhMsV0BXP2clV0fmvhufqmbke_D728Cs MwnuAuHF0BdfWoI_s.3Jbp8srkK2TDWdTAlVymurfgOr9UMHFqqqfj7bg6urnGU8a5CRXOskerqa SZDjliMyc0VbRAq113_gBMyX1f9_bOyzZlPAi1mwifSxA7BVOyM2OHRDi0L0A6sAmqHAwcMBqX0o bNYVH4vzR5eiVz035GRlerj_hHZ0pUIxqqLozGy60Fhivx5vRI8piSGKlz.tm6oPZPM..rgFwfKe aW_371uW6eA7WGA3G1r_ChIPjW_k72oMDnJb475gSsuYuqAwUwz0kEs5ueXWDz1fJdYfoRmqR.XR G_2XpYrQS2DOzl8QYlRbXw5wERplp Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.ne1.yahoo.com with HTTP; Tue, 18 Feb 2020 18:10:12 +0000 Date: Tue, 18 Feb 2020 18:10:08 +0000 (UTC) From: Nagaraj Raj To: Tomas Vondra Cc: Pgsql-general , PgAdmin Support , PostgreSQL Hackers , "pgsql-hackers-owner@postgresql.org" , Postgres Performance List , Pg Bugs , Pgsql-admin , Pgadmin-hackers , PostgreSQL Hackers , Pgsql-pkg-yum Message-ID: <1368247121.4687599.1582049408995@mail.yahoo.com> In-Reply-To: <20200218175857.5rsxovq5zxmr4wxi@development> References: <1425420656.4660607.1582047988379.ref@mail.yahoo.com> <1425420656.4660607.1582047988379@mail.yahoo.com> <20200218175857.5rsxovq5zxmr4wxi@development> Subject: Re: DB running out of memory issues after upgrade MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4687598_1436378711.1582049408993" 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: 9331 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_4687598_1436378711.1582049408993 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Below are the same configurations ins .conf file before and after updagrad= e show max_connections; =3D 1743show shared_buffers =3D "4057840kB"show effec= tive_cache_size =3D=C2=A0 "8115688kB"show maintenance_work_mem =3D "259MB"s= how checkpoint_completion_target =3D "0.9"show wal_buffers =3D "16MB"show d= efault_statistics_target =3D "100"show random_page_cost =3D "1.1"show effec= tive_io_concurrency =3D" 200"show work_mem =3D "4MB"show min_wal_size =3D "= 256MB"show max_wal_size =3D "2GB"show max_worker_processes =3D "8"show max_= parallel_workers_per_gather =3D "2" here is some sys logs, 2020-02-16 21:01:17 UTC=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 [-]= The database process was killed by the OS due to excessive memory consumpti= on.=C2=A02020-02-16 13:41:16 UTC=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 [-]The database process was killed by the OS due to excessive memory= consumption.=C2=A0 I identified one simple select which consuming more memory and here is the = query plan, "Result=C2=A0 (cost=3D0.00..94891854.11 rows=3D3160784900 width=3D288)""=C2= =A0 ->=C2=A0 Append=C2=A0 (cost=3D0.00..47480080.61 rows=3D3160784900 width= =3D288)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Scan on msghist=C2=A0 (co= st=3D0.00..15682777.12 rows=3D3129490000 width=3D288)""=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: (((data -> 'info'::text) ->> 'status':= :text) =3D 'CLOSE'::text)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Scan on= msghist msghist_1=C2=A0 (cost=3D0.00..189454.50 rows=3D31294900 width=3D28= 8)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: (((data -> 'in= fo'::text) ->> 'status'::text) =3D 'CLOSE'::text)" Thanks, On Tuesday, February 18, 2020, 09:59:37 AM PST, Tomas Vondra wrote: =20 =20 On Tue, Feb 18, 2020 at 05:46:28PM +0000, Nagaraj Raj wrote: >after upgrade Postgres to v9.6.11 from v9.6.9 DB running out of memory iss= ues no world load has changed before and after upgrade.=C2=A0 > >spec: RAM 16gb,4vCore >Any bug reported like this or suggestions on how to fix this issue? I appr= eciate the response..!!=C2=A0 > This bug report (in fact, we don't know if it's a bug, but OK) is woefully incomplete :-( The server log is mostly useless, unfortunately - it just says a bunch of processes were killed (by OOM killer, most likely) so the server has to restart. It tells us nothing about why the backends consumed so much memory etc. What would help us is knowing how much memory was the backend (killed by OOM) consuming, which should be in dmesg. And then MemoryContextStats output - you need to connect to a backend consuming a lot of memory using gdb (before it gets killed) and do =C2=A0 (gdb) p MemoryContextStats(TopMemoryContext) =C2=A0 (gdb) q and show us the output printed into server log. If it's a backend running a query, it'd help knowing the execution plan. It would also help knowing the non-default configuration, i.e. stuff tweaked in postgresql.conf. regards --=20 Tomas Vondra=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services=20 =20 ------=_Part_4687598_1436378711.1582049408993 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Below are the same configurations ins .conf file before and= after updagrade

show max_connections; =3D 1743
show shared_buffers =3D "4057840kB"
show effective_cache_= size =3D  "8115688kB"
show maintenance_work_mem =3D "259MB"<= /div>
show checkpoint_completion_target =3D "0.9"
show wal_bu= ffers =3D "16MB"
show default_statistics_target =3D "100"
show random_page_cost =3D "1.1"
show effective_io_concurrency = =3D" 200"
show work_mem =3D "4MB"
show min_wal_size =3D= "256MB"
show max_wal_size =3D "2GB"
show max_worker_pr= ocesses =3D "8"
show max_parallel_workers_per_gather =3D "2"


here is some sys logs,

2020-02-16 21:01:17 UTC  &= nbsp;      [-]The database process was killed by t= he OS due to excessive memory consumption. 
2020-02-16 13:41:16 UTC =         [-]The database process was kill= ed by the OS due to excessive memory consumption. 


I identified one simple select which consuming more m= emory and here is the query plan,

=


"Result  (cost=3D0.00..94891854.11 ro= ws=3D3160784900 width=3D288)"
"  ->  Append  (c= ost=3D0.00..47480080.61 rows=3D3160784900 width=3D288)"
"  &= nbsp;     ->  Seq Scan on msghist  (cost=3D0.00..156= 82777.12 rows=3D3129490000 width=3D288)"
"      &n= bsp;       Filter: (((data -> 'info'::text) ->> 'st= atus'::text) =3D 'CLOSE'::text)"
"        -&g= t;  Seq Scan on msghist msghist_1  (cost=3D0.00..189454.50 rows= =3D31294900 width=3D288)"
"          &nb= sp;   Filter: (((data -> 'info'::text) ->> 'status'::text) = =3D 'CLOSE'::text)"



Thanks,


=20
=20
On Tuesday, February 18, 2020, 09:59:37 AM PST, Tomas V= ondra <tomas.vondra@2ndquadrant.com> wrote:


On Tue, Feb 18, 2020 at 05:46:28PM +0000, Nagaraj Raj = wrote:
>after upgrade Postgres to v9.6.11 from v9.6.9 DB running out = of memory issues no world load has changed before and after upgrade. <= br>>
>spec: RAM 16gb,4vCore
>Any bug reported like this or s= uggestions on how to fix this issue? I appreciate the response..!! >

This bug report (in fact, we don't know if it's a bug, but OK)= is
woefully incomplete :-(

The server log is mostly useless, unf= ortunately - it just says a bunch
of processes were killed (by OOM kille= r, most likely) so the server has
to restart. It tells us nothing about = why the backends consumed so much
memory etc.

What would help us = is knowing how much memory was the backend (killed by
OOM) consuming, wh= ich should be in dmesg.

And then MemoryContextStats output - you nee= d to connect to a backend
consuming a lot of memory using gdb (before it= gets killed) and do

  (gdb) p MemoryContextStats(TopMemoryCont= ext)
  (gdb) q

and show us the output printed into server lo= g. If it's a backend
running a query, it'd help knowing the execution pl= an.

It would also help knowing the non-default configuration, i.e. s= tuff
tweaked in postgresql.conf.

regards

--
Tomas Vond= ra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Serv= ices


------=_Part_4687598_1436378711.1582049408993--