pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: out of memory
7+ messages / 4 participants
[nested] [flat]
* ERROR: out of memory
@ 2013-11-23 16:20 Dzmitry <dzmitry.nikitsin@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dzmitry @ 2013-11-23 16:20 UTC (permalink / raw)
To: pgsql-admin
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@gmail.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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ERROR: out of memory
@ 2013-11-23 17:04 Strahinja Kustudić <strahinjak@nordeus.com>
parent: Dzmitry <dzmitry.nikitsin@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Strahinja Kustudić @ 2013-11-23 17:04 UTC (permalink / raw)
To: Dzmitry <dzmitry.nikitsin@gmail.com>; +Cc: pgsql-admin
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@gmail.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@gmail.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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ERROR: out of memory
@ 2013-11-23 20:47 Евгений Селявка <evg.selyavka@gmail.com>
parent: Strahinja Kustudić <strahinjak@nordeus.com>
1 sibling, 0 replies; 7+ messages in thread
From: Евгений Селявка @ 2013-11-23 20:47 UTC (permalink / raw)
To: Strahinja Kustudić <strahinjak@nordeus.com>; +Cc: Dzmitry <dzmitry.nikitsin@gmail.com>; pgsql-admin
Did you read this
http://www.postgresql.org/docs/9.2/interactive/kernel-resources.html. What
you kernel settings for vm.overcommit_memory? If you have
vm.overcommit_memory=2 you can try set vm.overcommit_ratio=100. What say
/proc/meminfo?
2013/11/23 Strahinja Kustudić <strahinjak@nordeus.com>
> 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@gmail.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@gmail.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
>>
>>
>
--
С уважением Селявка Евгений
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ERROR: out of memory
@ 2013-11-24 09:08 Dzmitry <dzmitry.nikitsin@gmail.com>
parent: Strahinja Kustudić <strahinjak@nordeus.com>
1 sibling, 1 reply; 7+ messages in thread
From: Dzmitry @ 2013-11-24 09:08 UTC (permalink / raw)
To: Strahinja Kustudić <strahinjak@nordeus.com>; +Cc: pgsql-admin
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@nordeus.com>
Date: Saturday, November 23, 2013 8:04 PM
To: Dzmitry <dzmitry.nikitsin@gmail.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.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.nikitsin@gmail.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@gmail.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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ERROR: out of memory
@ 2013-11-24 10:33 Евгений Селявка <evg.selyavka@gmail.com>
parent: Dzmitry <dzmitry.nikitsin@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Евгений Селявка @ 2013-11-24 10:33 UTC (permalink / raw)
To: Dzmitry <dzmitry.nikitsin@gmail.com>; +Cc: Strahinja Kustudić <strahinjak@nordeus.com>; pgsql-admin
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.nikitsin@gmail.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@nordeus.com>
> Date: Saturday, November 23, 2013 8:04 PM
> To: Dzmitry <dzmitry.nikitsin@gmail.com>
> Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.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.nikitsin@gmail.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@gmail.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
>>
>>
>
--
С уважением Селявка Евгений
^ permalink raw reply [nested|flat] 7+ messages in thread
* ERROR: out of memory
@ 2019-02-09 11:08 Rohit Arora <arora.leo9@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rohit Arora @ 2019-02-09 11:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ERROR: out of memory
@ 2019-02-10 13:35 Rohit Arora <arora.leo9@gmail.com>
parent: Rohit Arora <arora.leo9@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rohit Arora @ 2019-02-10 13:35 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Dear List,
On further investigation it is observed that a particular connection PID,
which is using 32 GB of memory approx as per top command,
When i kill that particular pid with the help of pg_terminate_backend
command it suddenly release lot of memory.
I confirmed it with the help of free command.
Please note that the mentioned PID was from one of our application which is
connected by persistent connection.
Regards
On Sat, Feb 9, 2019 at 4:38 PM Rohit Arora <arora.leo9@gmail.com> wrote:
>
> 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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-02-10 13:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-23 16:20 ERROR: out of memory Dzmitry <dzmitry.nikitsin@gmail.com>
2013-11-23 17:04 ` Re: ERROR: out of memory Strahinja Kustudić <strahinjak@nordeus.com>
2013-11-23 20:47 ` Re: ERROR: out of memory Евгений Селявка <evg.selyavka@gmail.com>
2013-11-24 09:08 ` Re: ERROR: out of memory Dzmitry <dzmitry.nikitsin@gmail.com>
2013-11-24 10:33 ` Re: ERROR: out of memory Евгений Селявка <evg.selyavka@gmail.com>
2019-02-09 11:08 ERROR: out of memory Rohit Arora <arora.leo9@gmail.com>
2019-02-10 13:35 ` Re: ERROR: out of memory Rohit Arora <arora.leo9@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox