Re: ERROR: out of memory

[Dzmitry <dzmitry.nikitsin@xxxxxxxxx>]

Ok, thank you, made lower shared buffers down to 6GB, and work_mem – 25MB, will see result then will let you know if it helped. 

Thanks,

  Dzmitry

From: Strahinja Kustudić <strahinjak@xxxxxxxxxxx>
Date: Saturday, November 23, 2013 8:04 PM
To: Dzmitry <dzmitry.nikitsin@xxxxxxxxx>
Cc: "pgsql-admin@xxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxx>
Subject: Re: ERROR: out of memory

I can't help you why exactly you are getting out of memory errors, but they can happen with high work_mem setting. work_mem is too high for 29GB of memory, try lowering it to something like 10MB. You can also lower shared_buffers to half of what you set.

Regards,

Strahinja

On Sat, Nov 23, 2013 at 5:20 PM, Dzmitry <dzmitry.nikitsin@xxxxxxxxx> wrote:

Hi guys, 

  After update postgres 9.2 to 9.3.1 I see issues Out of memory ERROR, there a lot of data, but I can't say it's useful for me :( I have postgres 9.3 configured on ubuntu 12.04.2 with 29 GB memory(I am monitoring my server with newrelic and it does not show that my memory exceed 10GB at any time). My postgres configuration:

max_connections = 350

shared_buffers = 8GB

temp_buffers = 8MB  

work_mem = 50MB

maintenance_work_mem = 1GB 

effective_cache_size = 22GB

constraint_exclusion = partition

 Error message below, any help ?

ERROR:  out of memory

DETAIL:  Failed on request of size 6520.

STATEMENT:  SELECT  "users".* FROM "users"  WHERE (lower(email) = 'bburgess828@xxxxxxxxx') LIMIT 1

FATAL:  out of memory

LOG:  duration: 1222.511 ms  statement: SELECT  "users_for_emails".* FROM "users_for_emails"  WHERE "users_for_emails"."send_job_planner" = 't' AND "users_for_emails"."activity" = 0 AND (("users_for_emails"."type_user" = 'Customer' AND "users_for_emails"."completed_at" IS NOT NULL)) AND (("users_for_emails"."specialization_id" IS NOT NULL OR ("users_for_emails"."function_id" IS NOT NULL AND "users_for_emails"."function_id" IN (105, 110, 112, 114, 119, 121, 122, 129, 131, 148)))) ORDER BY "users_for_emails"."id" LIMIT 100 OFFSET 27400

TopMemoryContext: 454296 total in 53 blocks; 26656 free (127 chunks); 427640 used

  TopTransactionContext: 8192 total in 1 blocks; 7392 free (0 chunks); 800 used

  RI compare cache: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used

  RI query cache: 8192 total in 1 blocks; 3744 free (0 chunks); 4448 used

  RI constraint cache: 40688 total in 2 blocks; 3744 free (0 chunks); 36944 used

  Btree proof lookup cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used

  CFuncHash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used

  Tsearch dictionary cache: 8192 total in 1 blocks; 2704 free (0 chunks); 5488 used

  Tsearch parser cache: 8192 total in 1 blocks; 2704 free (0 chunks); 5488 used

  Tsearch configuration cache: 8192 total in 1 blocks; 2704 free (0 chunks); 5488 used

  Prepared Queries: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used

  TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used

  Type information cache: 24240 total in 2 blocks; 3744 free (0 chunks); 20496 used

  Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used

  MessageContext: 2097152 total in 9 blocks; 33608 free (483 chunks); 2063544 used

  Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used

  smgr relation table: 122880 total in 4 blocks; 36080 free (14 chunks); 86800 used

  TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used

  Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used

  PortalMemory: 8192 total in 1 blocks; 8160 free (1 chunks); 32 used

  Relcache by OID: 57344 total in 3 blocks; 19600 free (6 chunks); 37744 used

  CacheMemoryContext: 9441072 total in 54 blocks; 2829536 free (1 chunks); 6611536 used

    pg_toast_145286835_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used

    CachedPlan: 7168 total in 3 blocks; 2352 free (0 chunks); 4816 used

    pg_toast_48499925_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used

    pg_toast_44510621_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used

    pg_toast_42924797_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used

    pg_toast_996693_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used

    pg_toast_41896098_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used

    pg_toast_54021990_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used

    pg_toast_57326774_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used

    index_blobs_persistence_token: 1024 total in 1 blocks; 152 free (0 chunks); 872 used

    blobs_pkey: 1024 total in 1 blocks; 152 free (0 chunks); 872 used

    index_partners_on_owner_id: 1024 total in 1 blocks; 152 free (0 chunks); 872 used

A lot of other data ….

  MdSmgr: 57344 total in 3 blocks; 32288 free (0 chunks); 25056 used

  LOCALLOCK hash: 122880 total in 4 blocks; 58688 free (13 chunks); 64192 used

  ident parser context: 0 total in 0 blocks; 0 free (0 chunks); 0 used

  hba parser context: 31744 total in 5 blocks; 13296 free (4 chunks); 18448 used

  Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used

  ErrorContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used

Thanks,

  Dzmitry