Date: Sun, 23 Feb 2020 11:19:28 +0100
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: DB running out of memory issues after upgrade
Message-ID: <20200223101928.GE12464@hjp.at>
References: <1425420656.4660607.1582047988379.ref@mail.yahoo.com>
 <1425420656.4660607.1582047988379@mail.yahoo.com>
 <20200218175857.5rsxovq5zxmr4wxi@development>
 <1368247121.4687599.1582049408995@mail.yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="6Vw0j8UKbyX0bfpA"
Content-Disposition: inline
In-Reply-To: <1368247121.4687599.1582049408995@mail.yahoo.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--6Vw0j8UKbyX0bfpA
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2020-02-18 18:10:08 +0000, Nagaraj Raj wrote:
> Below are the same configurations ins .conf file before and after updagra=
de
>=20
> show max_connections; =3D 1743
[...]
> show work_mem =3D "4MB"

This is an interesting combination: So you expect a large number of
connections but each one should use very little RAM?

[...]

> here is some sys logs,
>=20
> 2020-02-16 21:01:17 UTC         [-]The database process was killed by the=
 OS
> due to excessive memory consumption.=20
> 2020-02-16 13:41:16 UTC         [-]The database process was killed by the=
 OS
> due to excessive memory consumption.=20

The oom-killer produces a huge block of messages which you can find with
dmesg or in your syslog. It looks something like this:

Feb 19 19:06:53 akran kernel: [3026711.344817] platzangst invoked oom-kille=
r: gfp_mask=3D0x15080c0(GFP_KERNEL_ACCOUNT|__GFP_ZERO), nodemask=3D(null), =
order=3D1, oom_score_adj=3D0
Feb 19 19:06:53 akran kernel: [3026711.344819] platzangst cpuset=3D/ mems_a=
llowed=3D0-1
Feb 19 19:06:53 akran kernel: [3026711.344825] CPU: 7 PID: 2012 Comm: platz=
angst Tainted: G           OE    4.15.0-74-generic #84-Ubuntu
Feb 19 19:06:53 akran kernel: [3026711.344826] Hardware name: Dell Inc. Pow=
erEdge R630/02C2CP, BIOS 2.1.7 06/16/2016
Feb 19 19:06:53 akran kernel: [3026711.344827] Call Trace:
Feb 19 19:06:53 akran kernel: [3026711.344835]  dump_stack+0x6d/0x8e
Feb 19 19:06:53 akran kernel: [3026711.344839]  dump_header+0x71/0x285
=2E..
Feb 19 19:06:53 akran kernel: [3026711.344893] RIP: 0033:0x7f292d076b1c
Feb 19 19:06:53 akran kernel: [3026711.344894] RSP: 002b:00007fff187ef240 E=
FLAGS: 00000246 ORIG_RAX: 0000000000000038
Feb 19 19:06:53 akran kernel: [3026711.344895] RAX: ffffffffffffffda RBX: 0=
0007fff187ef240 RCX: 00007f292d076b1c
Feb 19 19:06:53 akran kernel: [3026711.344896] RDX: 0000000000000000 RSI: 0=
000000000000000 RDI: 0000000001200011
Feb 19 19:06:53 akran kernel: [3026711.344897] RBP: 00007fff187ef2b0 R08: 0=
0007f292d596740 R09: 00000000009d43a0
Feb 19 19:06:53 akran kernel: [3026711.344897] R10: 00007f292d596a10 R11: 0=
000000000000246 R12: 0000000000000000
Feb 19 19:06:53 akran kernel: [3026711.344898] R13: 0000000000000020 R14: 0=
000000000000000 R15: 0000000000000000
Feb 19 19:06:53 akran kernel: [3026711.344899] Mem-Info:
Feb 19 19:06:53 akran kernel: [3026711.344905] active_anon:14862589 inactiv=
e_anon:1133875 isolated_anon:0
Feb 19 19:06:53 akran kernel: [3026711.344905]  active_file:467 inactive_fi=
le:371 isolated_file:0
Feb 19 19:06:53 akran kernel: [3026711.344905]  unevictable:0 dirty:3 write=
back:0 unstable:0
=2E..
Feb 19 19:06:53 akran kernel: [3026711.344985] [ pid ]   uid  tgid total_vm=
      rss pgtables_bytes swapents oom_score_adj name
Feb 19 19:06:53 akran kernel: [3026711.344997] [  823]     0   823    44909=
        0   106496      121             0 lvmetad
Feb 19 19:06:53 akran kernel: [3026711.344999] [ 1354]     0  1354    11901=
        3   135168      112             0 rpcbind
Feb 19 19:06:53 akran kernel: [3026711.345000] [ 1485]     0  1485    69911=
       99   180224      159             0 accounts-daemon
=2E..
Feb 19 19:06:53 akran kernel: [3026711.345345] Out of memory: Kill process =
25591 (postgres) score 697 or sacrifice child
Feb 19 19:06:53 akran kernel: [3026711.346563] Killed process 25591 (postgr=
es) total-vm:71116948kB, anon-rss:52727552kB, file-rss:0kB, shmem-rss:30231=
96kB

The most interesting lines are usually the last two: In this case they
tell us that the process killed was a postgres process and it occupied
about 71 GB of virtual memory at that time. That was clearly the right
choice since the machine has only 64 GB of RAM. Sometimes it is less
clear and then you might want to scroll through the (usually long) list
of processes to see if there are other processes which need suspicious
amounts of RAM or maybe if there are just more of them than you would
expect.


> I identified one simple select which consuming more memory and here is the
> query plan,
>=20
>=20
>=20
> "Result  (cost=3D0.00..94891854.11 rows=3D3160784900 width=3D288)"
> "  ->  Append  (cost=3D0.00..47480080.61 rows=3D3160784900 width=3D288)"
> "        ->  Seq Scan on msghist  (cost=3D0.00..15682777.12 rows=3D312949=
0000 width
> =3D288)"
> "              Filter: (((data -> 'info'::text) ->> 'status'::text) =3D
> 'CLOSE'::text)"
> "        ->  Seq Scan on msghist msghist_1  (cost=3D0.00..189454.50 rows=
=3D31294900
> width=3D288)"
> "              Filter: (((data -> 'info'::text) ->> 'status'::text) =3D
> 'CLOSE'::text)"

So: How much memory does that use? It produces a huge number of rows
(more than 3 billion) but it doesn't do much with them, so I wouldn't
expect the postgres process itself to use much memory. Are you sure its
the postgres process and not the application which uses a lot of memory?

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--6Vw0j8UKbyX0bfpA
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAl5SUasACgkQ8g5IURL+
KF2bwA//XXH2fofoRyaYGj/wnnxnl2b5Dc4h5C5bRJwQT6tf6Yko11MTbwYdoxxC
azpaxN/s7af/UD/UK9KZUEKqfsLKhNmcGcB3O20JfZqFKrkKdW/JWaGVOdeAkHhf
TNeN/G8H2GxdC1VOGEX6IPGkWOlCwdPaK/cFRHbYunMLosa3QlA/xQun7hceMZX+
NzliN0DFnsf/SUsmVsoDwmoi1q0eWBWO8gSYAI+/oSZuZySVFAmOBflrofbugc/z
Y4TpuGU6Z9lCIRqqDtKYeDlg94aP6aOILZe25+V0d+CLiVjZxzmWUF4t3y1sMLwS
Bm/NxLIN3djKj8NQGr9Gc2yIfDYrHe+FDXBfo960Kh0Am/kWwnwoYL04bVdJcSNB
9nJND5zxFMusLlI1X89Ru5TZOej1LqcIsUJOjfZ0Q1CBTIHFn1VhVdojfvJq6VHo
kHSKI0Xs137K3CguSELMd3d4oYxL4LL/SYh9oSGuvRd0uDRd6Kwc4DvkVAuEIwXc
3C83Q9A8N0ubrDM82mpZU9xW/h3gxcZEJAzur8KapptVOXqq+KDrdy4UWlmRF2Zw
vibPov2FKwS9HUky4bxXWwArYJNQFkhnnh3D3aQC20euV0gyin1Qhf3vPYWOkQDm
LyQY0P3Zg3UrsvhQ6cHAVXGANDDLX3GEft260RuL35wYqu5NKH0=
=jrhT
-----END PGP SIGNATURE-----

--6Vw0j8UKbyX0bfpA--