Subject: Re: Server goes to Recovery Mode when run a SQL
To: PegoraroF10 <marcos@f10.com.br>, pgsql-general@postgresql.org
References: <1549209182255-0.post@n3.nabble.com>
 <1549209942200-0.post@n3.nabble.com> <1549660293178-0.post@n3.nabble.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <4f05eb46-78d3-7262-1ebd-c12d4e696daf@aklaver.com>
Date: Fri, 8 Feb 2019 13:23:54 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.3.0
MIME-Version: 1.0
In-Reply-To: <1549660293178-0.post@n3.nabble.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 2/8/19 1:11 PM, PegoraroF10 wrote:
> *Well, now we have two queries which stops completelly our postgres server.
> That problem occurs on 10.6 and 11.1 versions.
> On both server the problem is the same. *

> *Linux Log of new crash, which takes several minutes to stop:*
> 
> Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643121] postgres invoked oom-killer: gfp_mask=0x24280ca, order=0, oom_score_adj=0

So the oom-killer kicked in.

See below for some ways to deal with this:

https://www.postgresql.org/docs/10/kernel-resources.html


> *And this new query which stops the server too but differently because 
> the old one stops immediatelly instead of this, as you can see images.*
> 
> *This query runs for aproximately 5 minutes. See link above with images 
> and logs and you´ll see how memory will grow. Memory use starts with 8gb 
> e grows until use them all. When all memory is in use then it starts to 
> swap. When all swap is allocated then it gets the "out of memory" and 
> stops completelly. You'll see that this query has generate_series, with, 
> recursive and lateral statements. It was bad written because that 
> generate_series generates a enormous quantity of records, probably 
> millions of them. But a wrong query couldn´t stop the entire server, 
> could ?*

You run anything(computer related or otherwise) out of resources and bad 
things are going to happen.


-- 
Adrian Klaver
adrian.klaver@aklaver.com