Postgres Pro
Downloads
Home   >   mailing lists

Re: ERROR: out of memory - Mailing list pgsql-admin

From Rohit Arora
Subject Re: ERROR: out of memory
Date
Msg-id CAMUAjH80dq0sSbuOLNVCyNzV49h2v-S0GiRj9OQki9O+XhOw2Q@mail.gmail.com
Whole thread Raw
In response to ERROR: out of memory  (Rohit Arora <arora.leo9@gmail.com>)
List pgsql-admin
Tree view
Dear List,

On further investigation it  is observed that a particular connection PID, which is using 32 GB of memory approx as per top command, 

When i kill that  particular  pid with the help of pg_terminate_backend command it  suddenly release lot of memory.

I confirmed  it with the help of free command.

Please note that the mentioned PID was from one of our application which is connected by persistent connection.

Regards








On Sat, Feb 9, 2019 at 4:38 PM Rohit Arora <arora.leo9@gmail.com> wrote:

Dear List,

I am getting frequent errors for "out  of memory" on my one of the postgresql instance.

It occurs on read as well as write operations/queries. Below is one such case 

2019-02-05 02:00:02.736 IST [30452] ERROR:  out of memory
2019-02-05 02:00:02.736 IST [30452] DETAIL:  Failed on request of size 24 in memory context "CachedPlanQuery".
2019-02-05 02:00:02.736 IST [30452] STATEMENT:  insert into tableName (<col1>,<col2>,<col3>,<col4>,<col5>,<col6>,<col7>,<col8>,<col9>,<col10>,<col11>,<col12>,<col13>,<col14>,<col15>,<col16>,<col17>,<col18>,<col19>,<col20>,<col21>,<col22>,<col23>,<col24>,<col25>,<col26>,<col27>,<col28>,<col29>,<col30>,<col31>,<col32>,<col33>,<col34>,<col35>,<col36>,<col37>,<col38>,<col39>,<col40>,<col41>,<col42>, <col43>) values ( $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42, $43 )

More  details here: https://pastebin.com/raw/Np1ExJ63

The Table <tableName> is a large table apprx  70 million rows and data size of approx 40 GB.

Please note that the rows and data size is for the mentioned table but when the "out of memory" error occurs it comes for all the queries irrespective of size of table.

As a matter of fact when such a server state is reached the server starts refusing 
*any* new connections altogether.


Machine OS is Centos :  7.6
Postgresql Version   : 11.1

shared_buffer is 15 GB (which is apprx 25% of max server ram)
effective_cache_size is at 46GB ( apprx 75% of server ram).

As per my observation when used memory (as per free command) reaches upto the 
level of shared_buffer size such errors comes.

Any help will be highly appreciable. 


Regards


pgsql-admin by date:

Previous
From: Rohit Arora
Date:
Subject: ERROR: out of memory
Next
From: Guillaume Lelarge
Date:
Subject: Re: [Junk released by User action] Re: [Junk released by User action]Re: [External] Postgres Database Disk Usage