Re: out of memory problem

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@vti.bund.de> wrote:

> From: Till Kirchner <till.kirchner@vti.bund.de>
> Subject: [PERFORM] out of memory problem
> To: pgsql-performance@postgresql.org
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>