From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: DB running out of memory issues after upgrade |
Date: | 2020-02-18 18:40:37 |
Message-ID: | 20200218184037.GZ31889@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-hackers pgadmin-support pgsql-admin pgsql-bugs pgsql-general pgsql-hackers pgsql-performance pgsql-pkg-yum |
Please don't cross post to different lists.
Pgsql-general <pgsql-general(at)postgresql(dot)org>,
PgAdmin Support <pgadmin-support(at)postgresql(dot)org>,
PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>,
"pgsql-hackers-owner(at)postgresql(dot)org" <pgsql-hackers-owner(at)postgresql(dot)org>,
Postgres Performance List <pgsql-performance(at)postgresql(dot)org>,
Pg Bugs <pgsql-bugs(at)postgresql(dot)org>,
Pgsql-admin <pgsql-admin(at)postgresql(dot)org>,
Pgadmin-hackers <pgadmin-hackers(at)postgresql(dot)org>,
PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>,
Pgsql-pkg-yum <pgsql-pkg-yum(at)postgresql(dot)org>
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
On Tue, Feb 18, 2020 at 06:10:08PM +0000, Nagaraj Raj wrote:
> Below are the same configurations ins .conf file before and after updagrade
> show max_connections; = 1743
> show shared_buffers = "4057840kB"
> show work_mem = "4MB"
> show maintenance_work_mem = "259MB"
> Any bug reported like this or suggestions on how to fix this issue? I appreciate the response..!!
>
> I could see below error logs and due to this reason database more often going into recovery mode,
What do you mean "more often" ? Did the crash/OOM happen before the upgrade, too ?
> 2020-02-17 22:34:32 UTC::@:[20467]:LOG: server process (PID32731) was terminated by signal 9: Killed
> 2020-02-17 22:34:32 UTC::@:[20467]:DETAIL:Failed process was running: selectinfo_starttime,info_starttimel,info_conversationid,info_status,classification_type,intentname,confidencescore,versions::text,messageidfrom salesdb.liveperson.intents where info_status='CLOSE' AND ( 1=1 ) AND ( 1=1)
That process is the one which was killed (in this case) but maybe not the
process responsible for using lots of *private* RAM. Is
salesdb.liveperson.intents a view ? What is the query plain for that query ?
(Run it with "explain").
https://wiki.postgresql.org/wiki/SlowQueryQuestions#EXPLAIN_.28ANALYZE.2C_BUFFERS.29.2C_not_just_EXPLAIN
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
On Tue, Feb 18, 2020 at 06:10:08PM +0000, Nagaraj Raj wrote:
> 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)"
This is almost 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.
--
Justin
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2020-02-18 18:49:50 | Re: DB running out of memory issues after upgrade |
Previous Message | Merlin Moncure | 2020-02-18 18:38:35 | Re: DB running out of memory issues after upgrade |
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2020-02-18 18:49:50 | Re: DB running out of memory issues after upgrade |
Previous Message | Merlin Moncure | 2020-02-18 18:38:35 | Re: DB running out of memory issues after upgrade |
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2020-02-18 18:49:50 | Re: DB running out of memory issues after upgrade |
Previous Message | Merlin Moncure | 2020-02-18 18:38:35 | Re: DB running out of memory issues after upgrade |
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2020-02-18 18:49:50 | Re: DB running out of memory issues after upgrade |
Previous Message | Merlin Moncure | 2020-02-18 18:38:35 | Re: DB running out of memory issues after upgrade |
From | Date | Subject | |
---|---|---|---|
Next Message | Anthony Hall | 2020-02-18 18:40:58 | How to handle CASE statement with PostgreSQL without need for typecasting |
Previous Message | Merlin Moncure | 2020-02-18 18:38:35 | Re: DB running out of memory issues after upgrade |
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2020-02-18 18:49:50 | Re: DB running out of memory issues after upgrade |
Previous Message | Merlin Moncure | 2020-02-18 18:38:35 | Re: DB running out of memory issues after upgrade |
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2020-02-18 18:49:50 | Re: DB running out of memory issues after upgrade |
Previous Message | Merlin Moncure | 2020-02-18 18:38:35 | Re: DB running out of memory issues after upgrade |
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2020-02-18 18:49:50 | Re: DB running out of memory issues after upgrade |
Previous Message | Merlin Moncure | 2020-02-18 18:38:35 | Re: DB running out of memory issues after upgrade |