From: Dzmitry <dzmitry.nikitsin@gmail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: ERROR: out of memory
Date: Sat, 23 Nov 2013 19:20:54 +0300
Message-ID: <CEB6B095.1BFB3%dzmitry.nikitsin@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: dzmitry.nikitsin@gmail.com
Subject: Re: ERROR: out of memory
In-Reply-To: <CEB6B095.1BFB3%dzmitry.nikitsin@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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