Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 477861337B56 for ; Tue, 9 Nov 2010 12:02:54 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 73593-08 for ; Tue, 9 Nov 2010 16:02:46 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web39707.mail.mud.yahoo.com (web39707.mail.mud.yahoo.com [209.191.106.53]) by mail.postgresql.org (Postfix) with SMTP id E26351336B42 for ; Tue, 9 Nov 2010 12:02:45 -0400 (AST) Received: (qmail 18709 invoked by uid 60001); 9 Nov 2010 16:02:42 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1289318562; bh=l/JOxxPy+dXrJdioj+KeALgLNEg/EdG8h4SET9oKF/E=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=ZEMeRbbFc1NIasxpaMLyKqn3/Xok9p92GOAE1IwWVqh4TiIINhOEU6ATaX6Dm7UILhQFIiVy16OBuzt4PR3v0KR8Dq+BXmqMz9p/HMVwv40srRryrrOvMPXSR7/oHLJm1n0dGHKlVt+s5Ixm43PVlQjQaoFTc4MVzUNg9ELti7g= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=VPq0Ntdf4/EVz1c8RI3jkPETQubwEKbmDiS+meaLkSKt+cO2BHYRga9/gYZqndw9GdKNVECImJemu3N0J80wJbOiD8IxRKT3I6CgJDXJe8kD7MLA7aqflP6WP1DdQyV8u0/C12Ux8jHO6wtD9wMyrQoii3irKvxg8NyItH6SfzY=; Message-ID: <876368.18227.qm@web39707.mail.mud.yahoo.com> X-YMail-OSG: 6UzV9f4VM1ng1OZTMlHqtdpybKZ8or3UQFa6mQsSyJBUoeI ENiQWpUzIaRvZe9NL7Y0nzzz8nF41acevQSUVcAnsnh1_dRaKDbhHKyFqjoI J2YCSFoqj3dgHvy_fTveAjVqwIER90zqIbUEzHFnD4920pNY97wZK6WanTNQ 1Z3kV0u4DG260RvUG6vyBb19zLuqDEyjXdbWpfFfHy6sVUYlvOhJIh3OvYf2 jZX5dS5FPpUCvV7CJ2Nyl55TZ1SPUU1xKizEXuHtNVi11ZXLQJPCEDjtpqvo rxdPLWGkoBDD7h5ysXGyZpfhbgzkSBhWy7sgyYN9Mc_nuEq5IZisuL8g4jiS WdHBbftvQ9HZqUvuQ46mqku_yZANP9Gc- Received: from [64.126.29.122] by web39707.mail.mud.yahoo.com via HTTP; Tue, 09 Nov 2010 08:02:42 PST X-Mailer: YahooMailClassic/11.4.9 YahooMailWebService/0.8.107.284920 Date: Tue, 9 Nov 2010 08:02:42 -0800 (PST) From: Bob Lunney Subject: Re: out of memory problem To: pgsql-performance@postgresql.org, Till Kirchner In-Reply-To: <4CD924F4.6000607@vti.bund.de> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/117 X-Sequence-Number: 41141 Be sure that you are starting PostgreSQL using an account with sufficient m= emory limits:=0A=0A ulimit -m=0A=0AIf the account has memory limit below t= he server's configuration you may get the out of memory error.=0A=0ABob Lun= ney=0A=0A--- On Tue, 11/9/10, Till Kirchner wro= te:=0A=0A> From: Till Kirchner =0A> Subject: [PE= RFORM] out of memory problem=0A> To: pgsql-performance@postgresql.org=0A> D= ate: Tuesday, November 9, 2010, 5:39 AM=0A> Hello together,=0A> =0A> I get = an out of memory problem I don't understand.=0A> The installed Postgres-Ver= sion is:=0A> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC=0A> gcc= -4.3.real (Debian 4.3.3-5) 4.3.3=0A> It is running on a 32bit Debian machin= e with 4GB RAM.=0A> =0A> Thanks for any help in advance=0A> =0A> Till=0A> = =0A> --=0A> ---------------------------------------------------------------= --------------------------------------------------------------=0A> =0A> Mai= n settings are as follows:=0A> checkpoint_segments 16=0A> checkpoint_timeou= t 120s=0A> effective_cache_size 128MB=0A> maintenance_work_mem 128MB=0A> ma= x_fsm_pages 153600=0A> shared_buffers 1GB=0A> wal_buffers 256MB=0A> work_me= m 256MB=0A> =0A> --=0A> ---------------------------------------------------= --------------------------------------------------------------------------= =0A> =0A> Used query is:=0A> =A0 =A0 CREATE TABLE temp.bwi_atkis0809_forest= land=0A> AS=0A> =A0 =A0 SELECT=0A> =A0 =A0 b.gid AS bwi_gid,=0A> =A0 =A0 a.= dlm0809id,=0A> =A0 =A0 a.objart_08,=0A> =A0 =A0 a.objart_09=0A> =A0 =A0 FRO= M=0A> =A0 =A0 bwi.bwi_pkt AS b,=0A> =A0 =A0 atkis.atkis0809_forestland AS a= =0A> =A0 =A0 WHERE=0A> =A0 =A0 b.the_geom && a.the_geom AND=0A> ST_Within(b= .the_geom, a.the_geom)=0A> =A0 =A0 ;=0A> =A0 =A0 COMMIT;=0A> =0A> (The JOIN= is a Spatial one using PostGIS-Functions)=0A> =0A> --=0A> ----------------= ---------------------------------------------------------------------------= ----------------------------------=0A> =0A> Full Table Sizes:=0A> atkis0809= _forestland 2835mb=0A> bwi_pkt 47mb=0A> =0A> --=0A> -----------------------= ---------------------------------------------------------------------------= ---------------------------=0A> =0A> Error Message is:=0A> FEHLER:=A0 Speic= her aufgebraucht=0A> DETAIL:=A0 Fehler bei Anfrage mit Gr=F6=DFe 32.=0A> = =0A> ********** Fehler **********=0A> =0A> FEHLER: Speicher aufgebraucht=0A= > SQL Status:53200=0A> Detail:Fehler bei Anfrage mit Gr=F6=DFe 32.=0A> =0A>= in english:=0A> ERROR: out of memory=0A> detail: error for request with si= ze 32=0A> =0A> --=0A> -----------------------------------------------------= ------------------------------------------------------------------------=0A= > =0A> The LOG looks as follows:=0A> =0A> TopMemoryContext: 42800 total in = 5 blocks; 4816 free (5=0A> chunks); 37984 used=0A> =A0 CFuncHash: 8192 tota= l in 1 blocks; 4936 free (0=0A> chunks); 3256 used=0A> =A0 TopTransactionCo= ntext: 8192 total in 1 blocks; 5520=0A> free (0 chunks); 2672 used=0A> =A0 = Operator class cache: 8192 total in 1 blocks; 3848=0A> free (0 chunks); 434= 4 used=0A> =A0 Operator lookup cache: 24576 total in 2 blocks;=0A> 14072 fr= ee (6 chunks); 10504 used=0A> =A0 MessageContext: 65536 total in 4 blocks; = 35960 free=0A> (10 chunks); 29576 used=0A> =A0 smgr relation table: 8192 to= tal in 1 blocks; 2808=0A> free (0 chunks); 5384 used=0A> =A0 TransactionAbo= rtContext: 32768 total in 1 blocks;=0A> 32752 free (0 chunks); 16 used=0A> = =A0 Portal hash: 8192 total in 1 blocks; 3912 free (0=0A> chunks); 4280 use= d=0A> =A0 PortalMemory: 8192 total in 1 blocks; 8040 free (0=0A> chunks); 1= 52 used=0A> =A0 =A0 PortalHeapMemory: 1024 total in 1 blocks; 896=0A> free = (0 chunks); 128 used=0A> =A0 =A0 =A0 ExecutorState: 1833967692 total in 230= =0A> blocks; 9008 free (3 chunks); 1833958684 used=0A> =A0 =A0 =A0 =A0 GiST= temporary context: 8192=0A> total in 1 blocks; 8176 free (0 chunks); 16 us= ed=0A> =A0 =A0 =A0 =A0 ExprContext: 0 total in 0=0A> blocks; 0 free (0 chun= ks); 0 used=0A> =A0 =A0 =A0 =A0 ExprContext: 8192 total in 1=0A> blocks; 81= 76 free (9 chunks); 16 used=0A> =A0 =A0 =A0 =A0 ExprContext: 0 total in 0= =0A> blocks; 0 free (0 chunks); 0 used=0A> =A0 =A0 =A0 =A0 ExprContext: 819= 2 total in 1=0A> blocks; 3880 free (4 chunks); 4312 used=0A> =A0 Relcache b= y OID: 8192 total in 1 blocks; 2856 free=0A> (0 chunks); 5336 used=0A> =A0 = CacheMemoryContext: 667472 total in 20 blocks;=0A> 195408 free (3 chunks); = 472064 used=0A> =A0 =A0 pg_toast_12241534_index: 1024 total in 1=0A> blocks= ; 240 free (0 chunks); 784 used=0A> =A0 =A0 pg_shdepend_depender_index: 102= 4 total in 1=0A> blocks; 152 free (0 chunks); 872 used=0A> =A0 =A0 pg_shdep= end_reference_index: 1024 total in 1=0A> blocks; 240 free (0 chunks); 784 u= sed=0A> =A0 =A0 pg_depend_depender_index: 1024 total in 1=0A> blocks; 152 f= ree (0 chunks); 872 used=0A> =A0 =A0 pg_depend_reference_index: 1024 total = in 1=0A> blocks; 152 free (0 chunks); 872 used=0A> =A0 =A0 idx_atkis0809_fo= restland_the_geom_gist: 1024=0A> total in 1 blocks; 136 free (0 chunks); 88= 8 used=0A> =A0 =A0 atkis0809_forestland_pkey: 1024 total in 1=0A> blocks; 3= 44 free (0 chunks); 680 used=0A> =A0 =A0 btree_bwi_pkt_enr: 1024 total in 1= blocks;=0A> 344 free (0 chunks); 680 used=0A> =A0 =A0 btree_bwi_pkt_tnr: 1= 024 total in 1 blocks;=0A> 344 free (0 chunks); 680 used=0A> =A0 =A0 rtree_= bwi_pkt: 1024 total in 1 blocks; 136=0A> free (0 chunks); 888 used=0A> =A0 = =A0 bwi_pkt_pkey: 1024 total in 1 blocks; 344=0A> free (0 chunks); 680 used= =0A> =A0 =A0 pg_attrdef_adrelid_adnum_index: 1024 total in=0A> 1 blocks; 24= 0 free (0 chunks); 784 used=0A> =A0 =A0 pg_constraint_conrelid_index: 1024 = total in 1=0A> blocks; 304 free (0 chunks); 720 used=0A> =A0 =A0 pg_databas= e_datname_index: 1024 total in 1=0A> blocks; 344 free (0 chunks); 680 used= =0A> =A0 =A0 pg_index_indrelid_index: 1024 total in 1=0A> blocks; 304 free = (0 chunks); 720 used=0A> =A0 =A0 pg_ts_dict_oid_index: 1024 total in 1 bloc= ks;=0A> 344 free (0 chunks); 680 used=0A> =A0 =A0 pg_aggregate_fnoid_index:= 1024 total in 1=0A> blocks; 344 free (0 chunks); 680 used=0A> =A0 =A0 pg_l= anguage_name_index: 1024 total in 1=0A> blocks; 344 free (0 chunks); 680 us= ed=0A> =A0 =A0 pg_statistic_relid_att_index: 1024 total in 1=0A> blocks; 24= 0 free (0 chunks); 784 used=0A> =A0 =A0 pg_ts_dict_dictname_index: 1024 tot= al in 1=0A> blocks; 280 free (0 chunks); 744 used=0A> =A0 =A0 pg_namespace_= nspname_index: 1024 total in 1=0A> blocks; 304 free (0 chunks); 720 used=0A= > =A0 =A0 pg_opfamily_oid_index: 1024 total in 1=0A> blocks; 344 free (0 ch= unks); 680 used=0A> =A0 =A0 pg_opclass_oid_index: 1024 total in 1 blocks;= =0A> 304 free (0 chunks); 720 used=0A> =A0 =A0 pg_ts_parser_prsname_index: = 1024 total in 1=0A> blocks; 280 free (0 chunks); 744 used=0A> =A0 =A0 pg_am= op_fam_strat_index: 1024 total in 1=0A> blocks; 88 free (0 chunks); 936 use= d=0A> =A0 =A0 pg_opclass_am_name_nsp_index: 1024 total in 1=0A> blocks; 192= free (0 chunks); 832 used=0A> =A0 =A0 pg_trigger_tgrelid_tgname_index: 102= 4 total=0A> in 1 blocks; 240 free (0 chunks); 784 used=0A> =A0 =A0 pg_cast_= source_target_index: 1024 total in 1=0A> blocks; 240 free (0 chunks); 784 u= sed=0A> =A0 =A0 pg_auth_members_role_member_index: 1024 total=0A> in 1 bloc= ks; 280 free (0 chunks); 744 used=0A> =A0 =A0 pg_attribute_relid_attnum_ind= ex: 1024 total=0A> in 1 blocks; 240 free (0 chunks); 784 used=0A> =A0 =A0 p= g_ts_config_cfgname_index: 1024 total in 1=0A> blocks; 280 free (0 chunks);= 744 used=0A> =A0 =A0 pg_authid_oid_index: 1024 total in 1 blocks;=0A> 304 = free (0 chunks); 720 used=0A> =A0 =A0 pg_ts_config_oid_index: 1024 total in= 1=0A> blocks; 344 free (0 chunks); 680 used=0A> =A0 =A0 pg_conversion_defa= ult_index: 1024 total in 1=0A> blocks; 128 free (0 chunks); 896 used=0A> = =A0 =A0 pg_language_oid_index: 1024 total in 1=0A> blocks; 344 free (0 chun= ks); 680 used=0A> =A0 =A0 pg_enum_oid_index: 1024 total in 1 blocks;=0A> 34= 4 free (0 chunks); 680 used=0A> =A0 =A0 pg_proc_proname_args_nsp_index: 102= 4 total in=0A> 1 blocks; 152 free (0 chunks); 872 used=0A> =A0 =A0 pg_ts_pa= rser_oid_index: 1024 total in 1=0A> blocks; 344 free (0 chunks); 680 used= =0A> =A0 =A0 pg_database_oid_index: 1024 total in 1=0A> blocks; 304 free (0= chunks); 720 used=0A> =A0 =A0 pg_conversion_name_nsp_index: 1024 total in = 1=0A> blocks; 280 free (0 chunks); 744 used=0A> =A0 =A0 pg_class_relname_ns= p_index: 1024 total in 1=0A> blocks; 240 free (0 chunks); 784 used=0A> =A0 = =A0 pg_attribute_relid_attnam_index: 1024 total=0A> in 1 blocks; 240 free (= 0 chunks); 784 used=0A> =A0 =A0 pg_class_oid_index: 1024 total in 1 blocks;= =0A> 304 free (0 chunks); 720 used=0A> =A0 =A0 pg_amproc_fam_proc_index: 10= 24 total in 1=0A> blocks; 88 free (0 chunks); 936 used=0A> =A0 =A0 pg_opera= tor_oprname_l_r_n_index: 1024 total=0A> in 1 blocks; 88 free (0 chunks); 93= 6 used=0A> =A0 =A0 pg_index_indexrelid_index: 1024 total in 1=0A> blocks; 3= 04 free (0 chunks); 720 used=0A> =A0 =A0 pg_type_oid_index: 1024 total in 1= blocks;=0A> 304 free (0 chunks); 720 used=0A> =A0 =A0 pg_rewrite_rel_rulen= ame_index: 1024 total in=0A> 1 blocks; 280 free (0 chunks); 744 used=0A> = =A0 =A0 pg_authid_rolname_index: 1024 total in 1=0A> blocks; 304 free (0 ch= unks); 720 used=0A> =A0 =A0 pg_auth_members_member_role_index: 1024 total= =0A> in 1 blocks; 280 free (0 chunks); 744 used=0A> =A0 =A0 pg_enum_typid_l= abel_index: 1024 total in 1=0A> blocks; 280 free (0 chunks); 744 used=0A> = =A0 =A0 pg_constraint_oid_index: 1024 total in 1=0A> blocks; 344 free (0 ch= unks); 680 used=0A> =A0 =A0 pg_conversion_oid_index: 1024 total in 1=0A> bl= ocks; 344 free (0 chunks); 680 used=0A> =A0 =A0 pg_ts_template_tmplname_ind= ex: 1024 total in=0A> 1 blocks; 280 free (0 chunks); 744 used=0A> =A0 =A0 p= g_ts_config_map_index: 1024 total in 1=0A> blocks; 192 free (0 chunks); 832= used=0A> =A0 =A0 pg_namespace_oid_index: 1024 total in 1=0A> blocks; 344 f= ree (0 chunks); 680 used=0A> =A0 =A0 pg_type_typname_nsp_index: 1024 total = in 1=0A> blocks; 240 free (0 chunks); 784 used=0A> =A0 =A0 pg_operator_oid_= index: 1024 total in 1=0A> blocks; 304 free (0 chunks); 720 used=0A> =A0 = =A0 pg_amop_opr_fam_index: 1024 total in 1=0A> blocks; 240 free (0 chunks);= 784 used=0A> =A0 =A0 pg_proc_oid_index: 1024 total in 1 blocks;=0A> 304 fr= ee (0 chunks); 720 used=0A> =A0 =A0 pg_opfamily_am_name_nsp_index: 1024 tot= al in=0A> 1 blocks; 192 free (0 chunks); 832 used=0A> =A0 =A0 pg_ts_templat= e_oid_index: 1024 total in 1=0A> blocks; 344 free (0 chunks); 680 used=0A> = =A0 MdSmgr: 8192 total in 1 blocks; 7312 free (0=0A> chunks); 880 used=0A> = =A0 LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0=0A> chunks); 4280 = used=0A> =A0 Timezones: 48616 total in 2 blocks; 5968 free (0=0A> chunks); = 42648 used=0A> =A0 ErrorContext: 8192 total in 1 blocks; 8176 free (4=0A> c= hunks); 16 used=0A> 2010-11-09 11:36:10 CET FEHLER:=A0 Speicher=0A> aufgebr= aucht=0A> 2010-11-09 11:36:10 CET DETAIL:=A0 Fehler bei Anfrage=0A> mit Gr= =F6=DFe 32.=0A> 2010-11-09 11:36:10 CET ANWEISUNG:=A0 BEGIN;=0A> =A0 =A0 CR= EATE TABLE temp.bwi_atkis0809_forestland=0A> AS=0A> =A0 =A0 SELECT=0A> =A0 = =A0 b.gid AS bwi_gid,=0A> =A0 =A0 a.dlm0809id,=0A> =A0 =A0 a.objart_08,=0A>= =A0 =A0 a.objart_09=0A> =A0 =A0 FROM=0A> =A0 =A0 bwi.bwi_pkt AS b,=0A> =A0= =A0 atkis.atkis0809_forestland AS a=0A> =A0 =A0 WHERE=0A> =A0 =A0 b.the_ge= om && a.the_geom AND=0A> ST_Within(b.the_geom, a.the_geom)=0A> =A0 =A0 ;=0A= > =A0 =A0 COMMIT;=0A> =0A> =0A> -- Sent via pgsql-performance mailing list = (pgsql-performance@postgresql.org)=0A> To make changes to your subscription= :=0A> http://www.postgresql.org/mailpref/pgsql-performance=0A> =0A=0A=0A =