From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Pgsql-general <pgsql-general@postgresql.org>
Cc: PgAdmin Support <pgadmin-support@postgresql.org>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: pgsql-hackers-owner@postgresql.org <pgsql-hackers-owner@postgresql.org>
Cc: Postgres Performance List <pgsql-performance@postgresql.org>
Cc: Pg Bugs <pgsql-bugs@postgresql.org>
Cc: Pgsql-admin <pgsql-admin@postgresql.org>
Cc: Pgadmin-hackers <pgadmin-hackers@postgresql.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Pgsql-pkg-yum <pgsql-pkg-yum@postgresql.org>
Subject: Re: DB running out of memory issues after upgrade
Date: Tue, 18 Feb 2020 18:10:08 +0000 (UTC)
Message-ID: <1368247121.4687599.1582049408995@mail.yahoo.com> (raw)
In-Reply-To: <20200218175857.5rsxovq5zxmr4wxi@development>
References: <1425420656.4660607.1582047988379.ref@mail.yahoo.com>
<1425420656.4660607.1582047988379@mail.yahoo.com>
<20200218175857.5rsxovq5zxmr4wxi@development>
Below are the same configurations ins .conf file before and after updagrade
show max_connections; = 1743show shared_buffers = "4057840kB"show effective_cache_size = "8115688kB"show maintenance_work_mem = "259MB"show checkpoint_completion_target = "0.9"show wal_buffers = "16MB"show default_statistics_target = "100"show random_page_cost = "1.1"show effective_io_concurrency =" 200"show work_mem = "4MB"show min_wal_size = "256MB"show max_wal_size = "2GB"show max_worker_processes = "8"show max_parallel_workers_per_gather = "2"
here is some sys logs,
2020-02-16 21:01:17 UTC [-]The database process was killed by the OS due to excessive memory consumption. 2020-02-16 13:41:16 UTC [-]The database process was killed by the OS due to excessive memory consumption.
I identified one simple select which consuming more memory and here is the query plan,
"Result (cost=0.00..94891854.11 rows=3160784900 width=288)"" -> Append (cost=0.00..47480080.61 rows=3160784900 width=288)"" -> Seq Scan on msghist (cost=0.00..15682777.12 rows=3129490000 width=288)"" Filter: (((data -> 'info'::text) ->> 'status'::text) = 'CLOSE'::text)"" -> Seq Scan on msghist msghist_1 (cost=0.00..189454.50 rows=31294900 width=288)"" Filter: (((data -> 'info'::text) ->> 'status'::text) = 'CLOSE'::text)"
Thanks,
On Tuesday, February 18, 2020, 09:59:37 AM PST, Tomas Vondra <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.
>
>spec: RAM 16gb,4vCore
>Any bug reported like this or suggestions 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, 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
(gdb) p MemoryContextStats(TopMemoryContext)
(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
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nagaraj.sf@yahoo.com, tomas.vondra@2ndquadrant.com, pgsql-general@postgresql.org, pgadmin-support@postgresql.org, pgsql-hackers@postgresql.org, pgsql-hackers-owner@postgresql.org, pgsql-bugs@postgresql.org, pgsql-admin@postgresql.org, pgadmin-hackers@postgresql.org, pgsql-hackers@lists.postgresql.org, pgsql-pkg-yum@postgresql.org
Subject: Re: DB running out of memory issues after upgrade
In-Reply-To: <1368247121.4687599.1582049408995@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox