ERROR: out of memory

From: Rohit Arora <arora(dot)leo9(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: ERROR: out of memory
Date: 2019-02-09 11:08:55
Message-ID: CAMUAjH_fugtS-W5Tm6AKr+vDXDTJ9obDhDVRChQsGiSTRcn1gQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Rohit Arora 2019-02-10 13:35:10 Re: ERROR: out of memory
Previous Message jose ramon costa gomez 2019-02-09 09:28:33 Quit Channel