Be sure that you are starting PostgreSQL using an account with sufficient memory limits: ulimit -m If the account has memory limit below the server's configuration you may get the out of memory error. Bob Lunney --- On Tue, 11/9/10, Till Kirchner <till.kirchner@xxxxxxxxxxx> wrote: > From: Till Kirchner <till.kirchner@xxxxxxxxxxx> > Subject: out of memory problem > To: pgsql-performance@xxxxxxxxxxxxxx > Date: Tuesday, November 9, 2010, 5:39 AM > Hello together, > > I get an out of memory problem I don't understand. > The installed Postgres-Version is: > PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC > gcc-4.3.real (Debian 4.3.3-5) 4.3.3 > It is running on a 32bit Debian machine with 4GB RAM. > > Thanks for any help in advance > > Till > > -- > ----------------------------------------------------------------------------------------------------------------------------- > > Main settings are as follows: > checkpoint_segments 16 > checkpoint_timeout 120s > effective_cache_size 128MB > maintenance_work_mem 128MB > max_fsm_pages 153600 > shared_buffers 1GB > wal_buffers 256MB > work_mem 256MB > > -- > ----------------------------------------------------------------------------------------------------------------------------- > > Used query is: > CREATE TABLE temp.bwi_atkis0809_forestland > AS > SELECT > b.gid AS bwi_gid, > a.dlm0809id, > a.objart_08, > a.objart_09 > FROM > bwi.bwi_pkt AS b, > atkis.atkis0809_forestland AS a > WHERE > b.the_geom && a.the_geom AND > ST_Within(b.the_geom, a.the_geom) > ; > COMMIT; > > (The JOIN is a Spatial one using PostGIS-Functions) > > -- > ----------------------------------------------------------------------------------------------------------------------------- > > Full Table Sizes: > atkis0809_forestland 2835mb > bwi_pkt 47mb > > -- > ----------------------------------------------------------------------------------------------------------------------------- > > Error Message is: > FEHLER: Speicher aufgebraucht > DETAIL: Fehler bei Anfrage mit Größe 32. > > ********** Fehler ********** > > FEHLER: Speicher aufgebraucht > SQL Status:53200 > Detail:Fehler bei Anfrage mit Größe 32. > > in english: > ERROR: out of memory > detail: error for request with size 32 > > -- > ----------------------------------------------------------------------------------------------------------------------------- > > The LOG looks as follows: > > TopMemoryContext: 42800 total in 5 blocks; 4816 free (5 > chunks); 37984 used > CFuncHash: 8192 total in 1 blocks; 4936 free (0 > chunks); 3256 used > TopTransactionContext: 8192 total in 1 blocks; 5520 > free (0 chunks); 2672 used > Operator class cache: 8192 total in 1 blocks; 3848 > free (0 chunks); 4344 used > Operator lookup cache: 24576 total in 2 blocks; > 14072 free (6 chunks); 10504 used > MessageContext: 65536 total in 4 blocks; 35960 free > (10 chunks); 29576 used > smgr relation table: 8192 total in 1 blocks; 2808 > free (0 chunks); 5384 used > TransactionAbortContext: 32768 total in 1 blocks; > 32752 free (0 chunks); 16 used > Portal hash: 8192 total in 1 blocks; 3912 free (0 > chunks); 4280 used > PortalMemory: 8192 total in 1 blocks; 8040 free (0 > chunks); 152 used > PortalHeapMemory: 1024 total in 1 blocks; 896 > free (0 chunks); 128 used > ExecutorState: 1833967692 total in 230 > blocks; 9008 free (3 chunks); 1833958684 used > GiST temporary context: 8192 > total in 1 blocks; 8176 free (0 chunks); 16 used > ExprContext: 0 total in 0 > blocks; 0 free (0 chunks); 0 used > ExprContext: 8192 total in 1 > blocks; 8176 free (9 chunks); 16 used > ExprContext: 0 total in 0 > blocks; 0 free (0 chunks); 0 used > ExprContext: 8192 total in 1 > blocks; 3880 free (4 chunks); 4312 used > Relcache by OID: 8192 total in 1 blocks; 2856 free > (0 chunks); 5336 used > CacheMemoryContext: 667472 total in 20 blocks; > 195408 free (3 chunks); 472064 used > pg_toast_12241534_index: 1024 total in 1 > blocks; 240 free (0 chunks); 784 used > pg_shdepend_depender_index: 1024 total in 1 > blocks; 152 free (0 chunks); 872 used > pg_shdepend_reference_index: 1024 total in 1 > blocks; 240 free (0 chunks); 784 used > pg_depend_depender_index: 1024 total in 1 > blocks; 152 free (0 chunks); 872 used > pg_depend_reference_index: 1024 total in 1 > blocks; 152 free (0 chunks); 872 used > idx_atkis0809_forestland_the_geom_gist: 1024 > total in 1 blocks; 136 free (0 chunks); 888 used > atkis0809_forestland_pkey: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > btree_bwi_pkt_enr: 1024 total in 1 blocks; > 344 free (0 chunks); 680 used > btree_bwi_pkt_tnr: 1024 total in 1 blocks; > 344 free (0 chunks); 680 used > rtree_bwi_pkt: 1024 total in 1 blocks; 136 > free (0 chunks); 888 used > bwi_pkt_pkey: 1024 total in 1 blocks; 344 > free (0 chunks); 680 used > pg_attrdef_adrelid_adnum_index: 1024 total in > 1 blocks; 240 free (0 chunks); 784 used > pg_constraint_conrelid_index: 1024 total in 1 > blocks; 304 free (0 chunks); 720 used > pg_database_datname_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > pg_index_indrelid_index: 1024 total in 1 > blocks; 304 free (0 chunks); 720 used > pg_ts_dict_oid_index: 1024 total in 1 blocks; > 344 free (0 chunks); 680 used > pg_aggregate_fnoid_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > pg_language_name_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > pg_statistic_relid_att_index: 1024 total in 1 > blocks; 240 free (0 chunks); 784 used > pg_ts_dict_dictname_index: 1024 total in 1 > blocks; 280 free (0 chunks); 744 used > pg_namespace_nspname_index: 1024 total in 1 > blocks; 304 free (0 chunks); 720 used > pg_opfamily_oid_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > pg_opclass_oid_index: 1024 total in 1 blocks; > 304 free (0 chunks); 720 used > pg_ts_parser_prsname_index: 1024 total in 1 > blocks; 280 free (0 chunks); 744 used > pg_amop_fam_strat_index: 1024 total in 1 > blocks; 88 free (0 chunks); 936 used > pg_opclass_am_name_nsp_index: 1024 total in 1 > blocks; 192 free (0 chunks); 832 used > pg_trigger_tgrelid_tgname_index: 1024 total > in 1 blocks; 240 free (0 chunks); 784 used > pg_cast_source_target_index: 1024 total in 1 > blocks; 240 free (0 chunks); 784 used > pg_auth_members_role_member_index: 1024 total > in 1 blocks; 280 free (0 chunks); 744 used > pg_attribute_relid_attnum_index: 1024 total > in 1 blocks; 240 free (0 chunks); 784 used > pg_ts_config_cfgname_index: 1024 total in 1 > blocks; 280 free (0 chunks); 744 used > pg_authid_oid_index: 1024 total in 1 blocks; > 304 free (0 chunks); 720 used > pg_ts_config_oid_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > pg_conversion_default_index: 1024 total in 1 > blocks; 128 free (0 chunks); 896 used > pg_language_oid_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > pg_enum_oid_index: 1024 total in 1 blocks; > 344 free (0 chunks); 680 used > pg_proc_proname_args_nsp_index: 1024 total in > 1 blocks; 152 free (0 chunks); 872 used > pg_ts_parser_oid_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > pg_database_oid_index: 1024 total in 1 > blocks; 304 free (0 chunks); 720 used > pg_conversion_name_nsp_index: 1024 total in 1 > blocks; 280 free (0 chunks); 744 used > pg_class_relname_nsp_index: 1024 total in 1 > blocks; 240 free (0 chunks); 784 used > pg_attribute_relid_attnam_index: 1024 total > in 1 blocks; 240 free (0 chunks); 784 used > pg_class_oid_index: 1024 total in 1 blocks; > 304 free (0 chunks); 720 used > pg_amproc_fam_proc_index: 1024 total in 1 > blocks; 88 free (0 chunks); 936 used > pg_operator_oprname_l_r_n_index: 1024 total > in 1 blocks; 88 free (0 chunks); 936 used > pg_index_indexrelid_index: 1024 total in 1 > blocks; 304 free (0 chunks); 720 used > pg_type_oid_index: 1024 total in 1 blocks; > 304 free (0 chunks); 720 used > pg_rewrite_rel_rulename_index: 1024 total in > 1 blocks; 280 free (0 chunks); 744 used > pg_authid_rolname_index: 1024 total in 1 > blocks; 304 free (0 chunks); 720 used > pg_auth_members_member_role_index: 1024 total > in 1 blocks; 280 free (0 chunks); 744 used > pg_enum_typid_label_index: 1024 total in 1 > blocks; 280 free (0 chunks); 744 used > pg_constraint_oid_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > pg_conversion_oid_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > pg_ts_template_tmplname_index: 1024 total in > 1 blocks; 280 free (0 chunks); 744 used > pg_ts_config_map_index: 1024 total in 1 > blocks; 192 free (0 chunks); 832 used > pg_namespace_oid_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > pg_type_typname_nsp_index: 1024 total in 1 > blocks; 240 free (0 chunks); 784 used > pg_operator_oid_index: 1024 total in 1 > blocks; 304 free (0 chunks); 720 used > pg_amop_opr_fam_index: 1024 total in 1 > blocks; 240 free (0 chunks); 784 used > pg_proc_oid_index: 1024 total in 1 blocks; > 304 free (0 chunks); 720 used > pg_opfamily_am_name_nsp_index: 1024 total in > 1 blocks; 192 free (0 chunks); 832 used > pg_ts_template_oid_index: 1024 total in 1 > blocks; 344 free (0 chunks); 680 used > MdSmgr: 8192 total in 1 blocks; 7312 free (0 > chunks); 880 used > LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 > chunks); 4280 used > Timezones: 48616 total in 2 blocks; 5968 free (0 > chunks); 42648 used > ErrorContext: 8192 total in 1 blocks; 8176 free (4 > chunks); 16 used > 2010-11-09 11:36:10 CET FEHLER: Speicher > aufgebraucht > 2010-11-09 11:36:10 CET DETAIL: Fehler bei Anfrage > mit Größe 32. > 2010-11-09 11:36:10 CET ANWEISUNG: BEGIN; > CREATE TABLE temp.bwi_atkis0809_forestland > AS > SELECT > b.gid AS bwi_gid, > a.dlm0809id, > a.objart_08, > a.objart_09 > FROM > bwi.bwi_pkt AS b, > atkis.atkis0809_forestland AS a > WHERE > b.the_geom && a.the_geom AND > ST_Within(b.the_geom, a.the_geom) > ; > COMMIT; > > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
