pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOut of Memory Problem.
4+ messages / 4 participants
[nested] [flat]
* Out of Memory Problem.
@ 2006-07-12 07:38 nicky <nicky@valuecare.nl>
0 siblings, 0 replies; 4+ messages in thread
From: nicky @ 2006-07-12 07:38 UTC (permalink / raw)
To: pgsql-performance
Hello Everyone,
I'm trying to find out/understand what causes my 'out of memory' error.
I do not have enough experience with such logs to understand what is
wrong or how to fix it. So i hope someone can point me in the right
direction.
The 'rpt.rpt_verrichting' table contains about 8.5 million records and
the 'rpt.rpt_dbc_traject' table contains 700k records.
It's part of a nightly process, so there is only 1 user active.
The server PostgreSQL 8.1.4 is running on, has 4GB Ram, OS FreeBSD
6.1-Stable.
postgresql.conf
shared_buffers = 8192
work_mem = 524288
maintenance_work_mem = 524288
effective_cache_size = 104858
Resource limits (current):
cputime infinity secs
filesize infinity kB
datasize 1048576 kB <---- could this be a problem?
stacksize 131072 kB <---- could this be a problem?
coredumpsize infinity kB
memoryuse infinity kB
memorylocked infinity kB
maxprocesses 5547
openfiles 11095
sbsize infinity bytes
vmemoryuse infinity kB
Thanks in advance.
_*The Query that is causing the out of memory error.*_
LOG: statement: insert into rpt.rpt_verrichting_dbc
(
verrichting_id
, verrichting_secid
, dbcnr
, vc_dbcnr
)
select
t1.verrichting_id
, t1.verrichting_secid
, t1.dbcnr
, max(t1.vc_dbcnr) as vc_dbcnr
from
rpt.rpt_verrichting t1
, rpt.rpt_dbc_traject t00
where
t1.vc_patientnr = t00.vc_patientnr
and
t1.vc_agb_specialisme_nr_toek = t00.agb_specialisme_nr
and
t1.verrichtingsdatum between t00.begindat_dbc and
COALESCE(t00.einddat_dbc, t00.begindat_dbc + interval '365 days')
group by
t1.verrichting_id
, t1.verrichting_secid
, t1.dbcnr
;
_*An EXPLAIN for the query:*_
Subquery Scan "*SELECT*" (cost=1837154.04..1839811.72 rows=106307
width=74)
-> HashAggregate (cost=1837154.04..1838482.88 rows=106307 width=56)
-> Merge Join (cost=1668759.55..1836090.97 rows=106307 width=56)
Merge Cond: ((("outer".vc_patientnr)::text =
"inner"."?column8?") AND ("outer".agb_specialisme_nr =
"inner".vc_agb_specialisme_nr_toek))
Join Filter: (("inner".verrichtingsdatum >=
"outer".begindat_dbc) AND ("inner".verrichtingsdatum <=
COALESCE("outer".einddat_dbc, ("outer".begindat_dbc + '365
days'::interval))))
-> Index Scan using rpt_dbc_traject_idx1 on
rpt_dbc_traject t00 (cost=0.00..84556.01 rows=578274 width=37)
-> Sort (cost=1668759.55..1689806.46 rows=8418765 width=79)
Sort Key: (t1.vc_patientnr)::text,
t1.vc_agb_specialisme_nr_toek
-> Seq Scan on rpt_verrichting t1
(cost=0.00..302720.65 rows=8418765 width=79)
_*Out of memory log.*_
TopMemoryContext: 16384 total in 2 blocks; 3824 free (4 chunks); 12560 used
Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks);
6328 used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336
used
MessageContext: 122880 total in 4 blocks; 64568 free (4 chunks); 58312 used
smgr relation table: 8192 total in 1 blocks; 2872 free (0 chunks); 5320 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: 8192 total in 1 blocks; 5304 free (1 chunks); 2888 used
ExecutorState: 562316108 total in 94 blocks; 528452720 free (2593154
chunks); 33863388 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
AggContext: 399499264 total in 58 blocks; 5928 free (110 chunks);
399493336 used
TupleHashTable: 109109272 total in 23 blocks; 2468576 free (70 chunks);
106640696 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
CacheMemoryContext: 516096 total in 6 blocks; 83448 free (0 chunks);
432648 used
rpt_dbc_traject_idx1: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
rpt_dbc_traject_pk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
rpt_verrichting_idx2: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
rpt_verrichting_idx1: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_class_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_cast_source_target_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
MdSmgr: 8192 total in 1 blocks; 7312 free (0 chunks); 880 used
LockTable (locallock hash): 8192 total in 1 blocks; 3912 free (0
chunks); 4280 used
Timezones: 47592 total in 2 blocks; 5968 free (0 chunks); 41624 used
ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used
ERROR: out of memory
DETAIL: Failed on request of size 98.
*Indexes*
CREATE INDEX rpt_verrichting_idx1
ON rpt.rpt_verrichting
USING btree
(dbcnr)
TABLESPACE rpt_index;
CREATE INDEX rpt_verrichting_idx2
ON rpt.rpt_verrichting
USING btree
(vc_patientnr)
TABLESPACE rpt_index;
CREATE INDEX rpt_dbc_traject_idx1
ON rpt.rpt_dbc_traject
USING btree
(vc_patientnr, agb_specialisme_nr)
TABLESPACE rpt_index_all;
^ permalink raw reply [nested|flat] 4+ messages in thread
* out of memory problem
@ 2010-11-09 10:39 Till Kirchner <till.kirchner@vti.bund.de>
0 siblings, 2 replies; 4+ messages in thread
From: Till Kirchner @ 2010-11-09 10:39 UTC (permalink / raw)
To: pgsql-performance
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: out of memory problem
@ 2010-11-09 15:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Till Kirchner <till.kirchner@vti.bund.de>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2010-11-09 15:22 UTC (permalink / raw)
To: Till Kirchner <till.kirchner@vti.bund.de>; +Cc: pgsql-performance
Till Kirchner <till.kirchner@vti.bund.de> writes:
> I get an out of memory problem I don't understand.
It's pretty clear that something is leaking memory in the per-query
context:
> ExecutorState: 1833967692 total in 230 blocks; 9008 free (3
> chunks); 1833958684 used
There doesn't seem to be anything in your query that is known to cause
that sort of thing, so I'm guessing that the leak is being caused by
the postgis functions you're using. You might ask about this on the
postgis lists.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: out of memory problem
@ 2010-11-09 16:02 Bob Lunney <bob_lunney@yahoo.com>
parent: Till Kirchner <till.kirchner@vti.bund.de>
1 sibling, 0 replies; 4+ messages in thread
From: Bob Lunney @ 2010-11-09 16:02 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, Till Kirchner <till.kirchner@vti.bund.de>
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-11-09 16:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-12 07:38 Out of Memory Problem. nicky <nicky@valuecare.nl>
2010-11-09 10:39 out of memory problem Till Kirchner <till.kirchner@vti.bund.de>
2010-11-09 15:22 ` Re: out of memory problem Tom Lane <tgl@sss.pgh.pa.us>
2010-11-09 16:02 ` Re: out of memory problem Bob Lunney <bob_lunney@yahoo.com>
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