Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VkVh6-0003mK-5a for pgsql-admin@arkaria.postgresql.org; Sun, 24 Nov 2013 09:09:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VkVh5-0003BF-A8 for pgsql-admin@arkaria.postgresql.org; Sun, 24 Nov 2013 09:09:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VkVh3-0003B6-0S for pgsql-admin@postgresql.org; Sun, 24 Nov 2013 09:09:29 +0000 Received: from mail-qa0-x231.google.com ([2607:f8b0:400d:c00::231]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VkVgz-0001Zv-EH for pgsql-admin@postgresql.org; Sun, 24 Nov 2013 09:09:28 +0000 Received: by mail-qa0-f49.google.com with SMTP id ii20so5146980qab.8 for ; Sun, 24 Nov 2013 01:09:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=user-agent:date:subject:from:to:cc:message-id:thread-topic :in-reply-to:mime-version:content-type; bh=vlcJhySuH8lDfi7wkeKvbZH/rsrL7rH3mwIVoJDkQH8=; b=uPkLBJnKNODHSH3SwATAIstPvfa7uP34p4T73dsMOD4u+szi2i6Rgzg9F+AlogE0x0 WWOt3aeZGdCl0WW9xeMACNrpEiOLRcgmpN6vK5Jc5poSvjYOqh4jf5VbRuDy2v4IotsJ R7gDMCAeBRdgrGWCLwU/+6ZFiyY+VewnApqE0wldu/6GZySkZALNZJMvmZkIpQdmtBIK gV4QSElIPScwr9FxlkY5MPNXZH8mBXoNY2XPpU1n2soVOXvO6nEVuKDkpL3ESiArWu/+ A2ziIHlBCuTInfFyUtpgqs27q/BYo7KAGt1YGsBRWmp2EnSnANlQoagUdx/WG1kaBhYk ZWoQ== X-Received: by 10.224.37.198 with SMTP id y6mr250937qad.104.1385284163100; Sun, 24 Nov 2013 01:09:23 -0800 (PST) Received: from [10.8.1.62] (40.166.255.173.client.static.strong-in91.as13926.net. [173.255.166.40]) by mx.google.com with ESMTPSA id b4sm23921052qai.7.2013.11.24.01.09.11 for (version=TLSv1 cipher=RC4-SHA bits=128/128); Sun, 24 Nov 2013 01:09:22 -0800 (PST) User-Agent: Microsoft-MacOutlook/14.2.3.120616 Date: Sun, 24 Nov 2013 12:08:55 +0300 Subject: Re: ERROR: out of memory From: Dzmitry To: Strahinja =?ISO-8859-2?B?S3VzdHVkaeY=?= CC: "pgsql-admin@postgresql.org" Message-ID: Thread-Topic: [ADMIN] ERROR: out of memory In-Reply-To: Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3468139759_2020457" X-Pg-Spam-Score: -1.0 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3468139759_2020457 Content-type: text/plain; charset="ISO-8859-2" Content-transfer-encoding: quoted-printable 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=E6 Date: Saturday, November 23, 2013 8:04 PM To: Dzmitry Cc: "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 wrote= : > Hi guys,=20 > After update postgres 9.2 to 9.3.1 I see issues Out of memory ERROR, th= ere 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 =3D 350 > shared_buffers =3D 8GB > temp_buffers =3D 8MB > work_mem =3D 50MB > maintenance_work_mem =3D 1GB > effective_cache_size =3D 22GB > constraint_exclusion =3D partition >=20 > Error message below, any help ? >=20 > ERROR: out of memory > DETAIL: Failed on request of size 6520. > STATEMENT: SELECT "users".* FROM "users" WHERE (lower(email) =3D > '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" =3D 't' AND > "users_for_emails"."activity" =3D 0 AND (("users_for_emails"."type_user" =3D > '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, 1= 29, > 131, 148)))) ORDER BY "users_for_emails"."id" LIMIT 100 OFFSET 27400 > TopMemoryContext: 454296 total in 53 blocks; 26656 free (127 chunks); 427= 640 > used > TopTransactionContext: 8192 total in 1 blocks; 7392 free (0 chunks); 80= 0 > 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); 369= 44 > 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); 548= 8 > used > Tsearch configuration cache: 8192 total in 1 blocks; 2704 free (0 chunk= s); > 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 us= ed > 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); 206= 3544 > used > Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 651= 2 > 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); 10= 08 > 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 chunk= s); > 872 used >=20 > A lot of other data .... >=20 > 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); 184= 48 > 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 >=20 > Thanks, > Dzmitry >=20 --B_3468139759_2020457 Content-type: text/html; charset="ISO-8859-2" Content-transfer-encoding: quoted-printable
Ok, thank you, mad= e lower shared buffers down to 6GB, and work_mem – 25MB, will see resu= lt then will let you know if it helped. 

= Thanks,
  Dzmitry

=
From: Strahinja Kustudi=E6 <<= a href=3D"mailto:strahinjak@nordeus.com">strahinjak@nordeus.com>
Date: Saturday, November 23, 2013 8:04 P= M
To: Dzmitry <dzmitry.nikitsin@gmail.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
S= ubject: 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 lo= wer s= hared_buffers to half of what you set.
=
Regards,
St= rahinja

On Sat, Nov 23, 2013 at 5:20 PM, Dzm= itry <dzmitry.nikitsin@gmail.com> wrote:
Hi guys, 
  After update postgr= es 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&nb= sp;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 configura= tion:
max_connections =3D 350
shared_buffers =3D 8GB
temp_buffers =3D 8MB  
work_mem =3D 50MB
maintenance= _work_mem =3D 1GB 
effective_cache_size =3D 22GB
constr= aint_exclusion =3D partition

 Error message belo= w, any help ?

ERROR:  out of memory
= DETAIL:  Failed on request of size 6520.
STATEMENT:  SEL= ECT  "users".* FROM "users"  WHERE (lower(email) =3D 'bburgess828@gmail.com') LIMIT 1=
FATAL:  out of memory
LOG:  duration: 1222.51= 1 ms  statement: SELECT  "users_for_emails".* FROM "users_for_emai= ls"  WHERE "users_for_emails"."send_job_planner" =3D 't' AND "users_for_e= mails"."activity" =3D 0 AND (("users_for_emails"."type_user" =3D 'Customer' AND = "users_for_emails"."completed_at" IS NOT NULL)) AND (("users_for_emails"."sp= ecialization_id" IS NOT NULL OR ("users_for_emails"."function_id" IS NOT NUL= L 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<= /div>
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: 2= 4576 total in 2 blocks; 15984 free (5 chunks); 8592 used
  RI= query cache: 8192 total in 1 blocks; 3744 free (0 chunks); 4448 used
<= div>  RI constraint cache: 40688 total in 2 blocks; 3744 free (0 chunks= ); 36944 used
  Btree proof lookup cache: 8192 total in 1 blo= cks; 1680 free (0 chunks); 6512 used
  CFuncHash: 8192 total = in 1 blocks; 1680 free (0 chunks); 6512 used
  Tsearch dictio= nary 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 blo= cks; 2704 free (0 chunks); 5488 used
  Prepared Queries: 2457= 6 total in 2 blocks; 15984 free (5 chunks); 8592 used
  Table= Space cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used
  Type information cache: 24240 total in 2 blocks; 3744 free (0 chun= ks); 20496 used
  Operator lookup cache: 24576 total in 2 blo= cks; 11888 free (5 chunks); 12688 used
  MessageContext: 2097= 152 total in 9 blocks; 33608 free (483 chunks); 2063544 used
 = ; Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 u= sed
  smgr relation table: 122880 total in 4 blocks; 36080 fr= ee (14 chunks); 86800 used
  TransactionAbortContext: 32768 t= otal in 1 blocks; 32736 free (0 chunks); 32 used
  Portal has= h: 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); 37= 744 used
  CacheMemoryContext: 9441072 total in 54 blocks; 28= 29536 free (1 chunks); 6611536 used
    pg_toast_1452868= 35_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used
&n= bsp;   CachedPlan: 7168 total in 3 blocks; 2352 free (0 chunks); 4816 u= sed
    pg_toast_48499925_index: 1024 total in 1 blocks;= 16 free (0 chunks); 1008 used
    pg_toast_44510621_ind= ex: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used
  &= nbsp; pg_toast_42924797_index: 1024 total in 1 blocks; 16 free (0 chunks); 1= 008 used
    pg_toast_996693_index: 1024 total in 1 bloc= ks; 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_p= ersistence_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: 5= 7344 total in 3 blocks; 32288 free (0 chunks); 25056 used
  L= OCALLOCK hash: 122880 total in 4 blocks; 58688 free (13 chunks); 64192 used<= /div>
  ident parser context: 0 total in 0 blocks; 0 free (0 chunks= ); 0 used
  hba parser context: 31744 total in 5 blocks; 1329= 6 free (4 chunks); 18448 used
  Timezones: 83472 total in 2 b= locks; 3744 free (0 chunks); 79728 used
  ErrorContext: 8192 = total in 1 blocks; 8160 free (0 chunks); 32 used

<= div>
Thanks,
  Dzmitry


 --B_3468139759_2020457--