Re: ERROR: out of memory

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(at)nordeus(dot)com>
Date: Saturday, November 23, 2013 8:04 PM
To: Dzmitry <dzmitry(dot)nikitsin(at)gmail(dot)com>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: [ADMIN] 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(dot)nikitsin(at)gmail(dot)com> 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(at)gmail(dot)com') 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
>