Re: ERROR: out of memory

Try to set vm.overcommit_ratio=95, because
CommitLimit: 15379984 kB
Committed_AS: 12222992 kB

You have limit - 15Gb and commited - 12GB it is near the limit, or you cant
return vm.overcommit_memory=0.

2013/11/24 Dzmitry <dzmitry(dot)nikitsin(at)gmail(dot)com>

> 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
>>
>>
>

--
С уважением Селявка Евгений