Re: ERROR: out of memory

[Dzmitry Nikitsin <dzmitry.nikitsin@xxxxxxxxx>]

Thank you David. I see some queries running for 10+ seconds, but I do not have transactions there, it’s just select queries. More thoughts ?

Thanks,

 Dzmitry 

From: "David G. Johnston" <david.g.johnston@xxxxxxxxx>
Date: Thursday, April 2, 2015 at 8:57 PM
To: Bob Jones <dzmitry.nikitsin@xxxxxxxxx>
Cc: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Subject: Re: ERROR: out of memory

On Thu, Apr 2, 2015 at 5:24 PM, Dzmitry Nikitsin <dzmitry.nikitsin@xxxxxxxxx> wrote:

Hey folks, 

 I have 4 postgresql servers 9.3.6(on master I use 9.3.5) configured with  streaming replication - with 1 maser(30GB RAM, processor - Intel Xeon E5-2680 v2) and 3 slaves(61 Intel Xeon E5-2670 v2), all on Ubuntu 14.04.1 LTS, 

Master configuration: 

default_statistics_target = 50

maintenance_work_mem = 1GB

constraint_exclusion = on

checkpoint_completion_target = 0.9

effective_cache_size = 22GB

work_mem = 120MB

wal_buffers = 8MB

checkpoint_segments = 16

shared_buffers = 7GB

max_connections = 300

Slave configuration: 

max_connections = 300

shared_buffers = 10GB

effective_cache_size = 45GB

work_mem = 19660kB

maintenance_work_mem = 2GB

checkpoint_segments = 32

checkpoint_completion_target = 0.7

wal_buffers = 16MB

default_statistics_target = 100

I am using XSF file system, size of my database – 168GB. 

For linux kernel I have settings:

vm.swappiness = 0

vm.overcommit_memory = 2

vm.overcommit_ratio = 50

kernel.shmall = 7864320

kernel.shmmax = 32212254720

kernel.shmmni = 4096

Master is primary to write data. Slave – for reporting. In reality I am using not more then 100 connections to slave server at the same time. Writing about 3000 records in a minute. 

I have one table where I writing statistics, that is portioned by month. Below is table size with biggest relations 

public.stats_201408               | 9212 MB

 public.stats_201503               | 8868 MB

 pg_toast.pg_toast_6404464             | 8319 MB

 pg_toast.pg_toast_317921              | 7520 MB

 public.stats_201409               | 7101 MB

 public.stats_201412               | 4458 MB

I see here pg_toast, from doc I read it’s large objects there, but It’s not related to my table stats, which is read/write heavy(type of the biggest column in this table => character varying(3000)). I.e. - it’s related to different table. 

My application create 100 connections & keeping them during whole life cycle(usually until next deploy – that may happen in couple days), with time – connection growing in memory(checking using htop) & free memory going down. As result with time(usually 3-4 hours) my DB start throwing 

ERROR:  out of memory

DETAIL:  Failed on request of size 2048.

After I restart my application(reconnect to DB), it start working fine again. 

It even fail on simple query like: 

 SELECT COUNT(*) FROM “stats" WHERE "stats”."bt_id" = $1 AND (stats.created_at >= '2015-04-02 04:00:00.000000') AND (stats.created_at <= '2015-04-03 03:59:59.999999') AND ("stats"."source" IS NOT NULL) AND "stats"."device" IN (1, 2) AND "stats”."ra" = 0 AND "stats"."paid" = ’t'

Any help appreciated.

​Start here:

http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

Research

Ask more specific questions.

I suspect you not only are keeping your 100 sessions directly and permanently connected to the database but many of them are also keeping open transactions.

​I'd also drop any preconception about which tables you think are problematic - since likely the tables themselves are not the issue.​

David J.​