pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOut of Memory
24+ messages / 13 participants
[nested] [flat]
* Out of Memory
@ 2006-11-06 20:46 Abu Mushayeed <amushayeed@hotmail.com>
2006-11-06 22:24 ` Re: Out of Memory Abu Mushayeed <abumushayeed@yahoo.com>
0 siblings, 1 reply; 24+ messages in thread
From: Abu Mushayeed @ 2006-11-06 20:46 UTC (permalink / raw)
To: pgsql-admin
I am trying to run the following query
SELECT
cdc.cus_nbr,
cdc.indiv_fkey,
MAX(
CASE
WHEN UPPER(pay.pay_typ) IN
('B','G','I','L','R','X','Y') THEN 'Y'
WHEN pay.pay_typ IN
('0','1','2','3','4','5','6','7','8','9') THEN 'P'
ELSE 'N'
END
),
MAX(
CASE UPPER(pay.pay_typ)
WHEN 'E' THEN 'Y'
ELSE 'N'
END
)
FROM
cdm.cdm_ddw_customer cdc,
cdm.cdm_ddw_cust_paytype pay
WHERE
pay.cus_nbr = cdc.cus_nbr
AND cdc.indiv_fkey IS NOT NULL
AND cdc.lst_dte >= (select start_date from cdm_epiphany.inc_date) --
'2003-11-15'::date --
AND cdc.lst_dte < (select end_date from cdm_epiphany.inc_date)
--'2006-10-16'::date --
GROUP BY
cdc.cus_nbr,
cdc.indiv_fkey
Note: The table cdm_epiphany.inc_date has only one row.
When I do the explain plan, I get the following:
HashAggregate (cost=672585.68..679836.00 rows=90629 width=21)
InitPlan
-> Seq Scan on inc_date (cost=0.00..1.01 rows=1 width=4)
-> Seq Scan on inc_date (cost=0.00..1.01 rows=1 width=4)
-> Merge Join (cost=17667.93..671646.97 rows=93669 width=21)
Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
-> Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype pay
(cost=0.00..593522.69 rows=23620542 width=13)
-> Sort (cost=17667.93..17894.51 rows=90629 width=16)
Sort Key: cdc.cus_nbr
-> Index Scan using cdm_ddwcust_lstdate_idx on
cdm_ddw_customer cdc (cost=0.00..10205.68 rows=90629 width=16)
Index Cond: ((lst_dte >= $0) AND (lst_dte < $1))
Filter: (indiv_fkey IS NOT NULL)
Now, if I change the query to :
GroupAggregate (cost=4952407.62..6300386.04 rows=14506983 width=21)
-> Sort (cost=4952407.62..4989891.57 rows=14993583 width=21)
Sort Key: cdc.cus_nbr, cdc.indiv_fkey
-> Merge Join (cost=0.00..2889809.31 rows=14993583 width=21)
Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
-> Index Scan using ddwcus_pk on cdm_ddw_customer cdc
(cost=0.00..2051240.77 rows=14506983 width=16)
Filter: ((indiv_fkey IS NOT NULL) AND (lst_dte >=
'2003-11-15'::date) AND (lst_dte < '2006-10-16'::date))
-> Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype
pay (cost=0.00..593522.69 rows=23620542 width=13)
Notice, the row returned from the two queries are way off. I have analyzed,
reanalyzed the table, changed the cdc.cus_nbr, cdc.indiv_fkey and
cdc.lst_dte columns statistics value to 300 and reanalyzed but I cannot get
to give me the same exact or closer row count.
The query outcome is "Out of Memory".
I would appreciate if someone can provide some guidance.
Thanks
Abu
_________________________________________________________________
Get FREE company branded e-mail accounts and business Web site from
Microsoft Office Live
http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Out of Memory
2006-11-06 20:46 Out of Memory Abu Mushayeed <amushayeed@hotmail.com>
@ 2006-11-06 22:24 ` Abu Mushayeed <abumushayeed@yahoo.com>
2006-11-07 04:52 ` Re: Out of Memory Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Abu Mushayeed @ 2006-11-06 22:24 UTC (permalink / raw)
To: Abu Mushayeed <amushayeed@hotmail.com>; pgsql-admin
I really could appreciate some help. I tried to run the following query and I get the following dump
Query:
----------------------------------------------------------------------------------------------------
SELECT
COUNT(*)
/*
cdc.cus_nbr,
cdc.indiv_fkey,
MAX(
CASE
WHEN UPPER(pay.pay_typ) IN ('B','G','I','L','R','X','Y') THEN 'Y'
WHEN pay.pay_typ IN ('0','1','2','3','4','5','6','7','8','9') THEN 'P'
ELSE 'N'
END
),
MAX(
CASE UPPER(pay.pay_typ)
WHEN 'E' THEN 'Y'
ELSE 'N'
END
)
*/
FROM
cdm.cdm_ddw_customer cdc,
cdm.cdm_ddw_cust_paytype pay,
cdm_epiphany.inc_date x
WHERE
pay.cus_nbr = cdc.cus_nbr
AND cdc.indiv_fkey IS NOT NULL
AND cdc.lst_dte >= x.start_date -- '2003-11-15'::date --
AND cdc.lst_dte < x.end_date -- '2006-10-16'::date --
GROUP BY
cdc.cus_nbr,
cdc.indiv_fkey
====================================================
Query Plan:
-------------------
HashAggregate (cost=1166943.74..1192962.77 rows=2081523 width=16)
-> Merge Join (cost=467536.46..1151332.31 rows=2081523 width=16)
Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
-> Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype pay (cost=0.00..593522.69 rows=23620542 width=8)
-> Sort (cost=467536.46..472571.38 rows=2013969 width=16)
Sort Key: cdc.cus_nbr
-> Nested Loop (cost=0.00..256657.69 rows=2013969 width=16)
-> Seq Scan on inc_date x (cost=0.00..1.01 rows=1 width=8)
-> Index Scan using cdm_ddwcust_lstdate_idx on cdm_ddw_customer cdc (cost=0.00..226447.15 rows=2013969 width=20)
Index Cond: ((cdc.lst_dte >= "outer".start_date) AND (cdc.lst_dte < "outer".end_date))
Filter: (indiv_fkey IS NOT NULL)
====================================================
Dump:
---------------------------------------------------------------------------
TopMemoryContext: 61976 total in 6 blocks; 11176 free (9 chunks); 50800 used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336 used
Record information cache: 8192 total in 1 blocks; 1864 free (0 chunks); 6328 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
MessageContext: 57344 total in 3 blocks; 19296 free (80 chunks); 38048 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; 880 free (0 chunks); 144 used
ExecutorState: 24576 total in 2 blocks; 7000 free (4 chunks); 17576 used
TupleSort: 142637924 total in 29 blocks; 125794424 free (863578 chunks); 16843500 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: 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
AggContext: -2002788352 total in 292 blocks; 5016 free (152 chunks); -2002793368 used
TupleHashTable: 547610648 total in 77 blocks; 223032 free (284 chunks); 547387616 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; 80144 free (0 chunks); 435952 used
paytyp_cust_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
ddwcust_indiv_fkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
ddw_cust_zip_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
cdm_ddwcust_lstdate_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
ddwcus_pk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_database_oid_index: 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_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; 6880 free (0 chunks); 1312 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 (3 chunks); 16 used
2006-11-06 14:20:12 PSTamushayeedmdc_oz11.16.152.174ERROR: out of memory
2006-11-06 14:20:12 PSTamushayeedmdc_oz11.16.152.174DETAIL: Failed on request of size 68.
Abu Mushayeed <amushayeed@hotmail.com> wrote: I am trying to run the following query
SELECT
cdc.cus_nbr,
cdc.indiv_fkey,
MAX(
CASE
WHEN UPPER(pay.pay_typ) IN
('B','G','I','L','R','X','Y') THEN 'Y'
WHEN pay.pay_typ IN
('0','1','2','3','4','5','6','7','8','9') THEN 'P'
ELSE 'N'
END
),
MAX(
CASE UPPER(pay.pay_typ)
WHEN 'E' THEN 'Y'
ELSE 'N'
END
)
FROM
cdm.cdm_ddw_customer cdc,
cdm.cdm_ddw_cust_paytype pay
WHERE
pay.cus_nbr = cdc.cus_nbr
AND cdc.indiv_fkey IS NOT NULL
AND cdc.lst_dte >= (select start_date from cdm_epiphany.inc_date) --
'2003-11-15'::date --
AND cdc.lst_dte < (select end_date from cdm_epiphany.inc_date)
--'2006-10-16'::date --
GROUP BY
cdc.cus_nbr,
cdc.indiv_fkey
Note: The table cdm_epiphany.inc_date has only one row.
When I do the explain plan, I get the following:
HashAggregate (cost=672585.68..679836.00 rows=90629 width=21)
InitPlan
-> Seq Scan on inc_date (cost=0.00..1.01 rows=1 width=4)
-> Seq Scan on inc_date (cost=0.00..1.01 rows=1 width=4)
-> Merge Join (cost=17667.93..671646.97 rows=93669 width=21)
Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
-> Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype pay
(cost=0.00..593522.69 rows=23620542 width=13)
-> Sort (cost=17667.93..17894.51 rows=90629 width=16)
Sort Key: cdc.cus_nbr
-> Index Scan using cdm_ddwcust_lstdate_idx on
cdm_ddw_customer cdc (cost=0.00..10205.68 rows=90629 width=16)
Index Cond: ((lst_dte >= $0) AND (lst_dte < $1))
Filter: (indiv_fkey IS NOT NULL)
Now, if I change the query to :
GroupAggregate (cost=4952407.62..6300386.04 rows=14506983 width=21)
-> Sort (cost=4952407.62..4989891.57 rows=14993583 width=21)
Sort Key: cdc.cus_nbr, cdc.indiv_fkey
-> Merge Join (cost=0.00..2889809.31 rows=14993583 width=21)
Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
-> Index Scan using ddwcus_pk on cdm_ddw_customer cdc
(cost=0.00..2051240.77 rows=14506983 width=16)
Filter: ((indiv_fkey IS NOT NULL) AND (lst_dte >=
'2003-11-15'::date) AND (lst_dte < '2006-10-16'::date))
-> Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype
pay (cost=0.00..593522.69 rows=23620542 width=13)
Notice, the row returned from the two queries are way off. I have analyzed,
reanalyzed the table, changed the cdc.cus_nbr, cdc.indiv_fkey and
cdc.lst_dte columns statistics value to 300 and reanalyzed but I cannot get
to give me the same exact or closer row count.
The query outcome is "Out of Memory".
I would appreciate if someone can provide some guidance.
Thanks
Abu
_________________________________________________________________
Get FREE company branded e-mail accounts and business Web site from
Microsoft Office Live
http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------------
Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call rates.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Out of Memory
2006-11-06 20:46 Out of Memory Abu Mushayeed <amushayeed@hotmail.com>
2006-11-06 22:24 ` Re: Out of Memory Abu Mushayeed <abumushayeed@yahoo.com>
@ 2006-11-07 04:52 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 24+ messages in thread
From: Tom Lane @ 2006-11-07 04:52 UTC (permalink / raw)
To: Abu Mushayeed <abumushayeed@yahoo.com>; +Cc: Abu Mushayeed <amushayeed@hotmail.com>; pgsql-admin
Abu Mushayeed <abumushayeed@yahoo.com> writes:
> I really could appreciate some help.
If nothing else, "set enable_hashagg = off" should help. But have you
analyzed these tables lately? Perhaps you have work_mem set too high?
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* out of memory
@ 2007-09-12 01:36 Tena Sakai <tsakai@gallo.ucsf.edu>
2007-09-12 03:58 ` Re: out of memory Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Tena Sakai @ 2007-09-12 01:36 UTC (permalink / raw)
To: pgsql-admin
Hi Everybody,
I saw something I had never seen with psql.
> canon=#
> canon=# select * from public.genotype;
> out of memory
> [tsakai@vixen tws]$ echo $?
> 1
> [tsakai@vixen tws]$
The version is 8.2.4 and platform is redhat linux
on dell server. The table has 36 million rows.
I don't suppose there is anything I can do to prevent
such failure (short of installing more memory)?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2007-09-12 01:36 out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2007-09-12 03:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-12 04:15 ` Re: out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 2007-09-12 03:58 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-admin
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>> canon=3D# select * from public.genotype;
>> out of memory
> The version is 8.2.4 and platform is redhat linux
> on dell server. The table has 36 million rows.
Try \set FETCH_COUNT 1000
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2007-09-12 01:36 out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
2007-09-12 03:58 ` Re: out of memory Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-09-12 04:15 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2007-09-12 08:12 ` Re: out of memory Tino Schwarze <postgresql@tisc.de>
0 siblings, 1 reply; 24+ messages in thread
From: Tena Sakai @ 2007-09-12 04:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thank you, Tom.
But there's a problem, for which I hope you have
more wisdom. The reason why I tried that query
is because someone tried the same thing via odbc
from pc quering postgres database and got the
same error ("out of memory").
What can a pc user do? Anything?
Regards,
Tena
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tue 9/11/2007 8:58 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] out of memory
"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>> canon=3D# select * from public.genotype;
>> out of memory
> The version is 8.2.4 and platform is redhat linux
> on dell server. The table has 36 million rows.
Try \set FETCH_COUNT 1000
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2007-09-12 01:36 out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
2007-09-12 03:58 ` Re: out of memory Tom Lane <tgl@sss.pgh.pa.us>
2007-09-12 04:15 ` Re: out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
@ 2007-09-12 08:12 ` Tino Schwarze <postgresql@tisc.de>
2007-09-12 14:20 ` Re: out of memory Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Tino Schwarze @ 2007-09-12 08:12 UTC (permalink / raw)
To: pgsql-admin
On Tue, Sep 11, 2007 at 09:15:56PM -0700, Tena Sakai wrote:
> But there's a problem, for which I hope you have
> more wisdom. The reason why I tried that query
> is because someone tried the same thing via odbc
> from pc quering postgres database and got the
> same error ("out of memory").
>
> What can a pc user do? Anything?
Tell the user that he/she should use an appropiate WHERE clause. You
might also consider adding more swap to the machine. Then it will go
horribly slow, but it will work.
HTH,
Tino.
--
www.spiritualdesign-chemnitz.de
www.lebensraum11.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2007-09-12 01:36 out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
2007-09-12 03:58 ` Re: out of memory Tom Lane <tgl@sss.pgh.pa.us>
2007-09-12 04:15 ` Re: out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
2007-09-12 08:12 ` Re: out of memory Tino Schwarze <postgresql@tisc.de>
@ 2007-09-12 14:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-12 15:25 ` Re: out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 2007-09-12 14:20 UTC (permalink / raw)
To: Tino Schwarze <postgresql@tisc.de>; +Cc: pgsql-admin@postgresql.org, "Tena Sakai" <tsakai@gallo.ucsf.edu>
Tino Schwarze <postgresql@tisc.de> writes:
> On Tue, Sep 11, 2007 at 09:15:56PM -0700, Tena Sakai wrote:
>> But there's a problem, for which I hope you have
>> more wisdom. The reason why I tried that query
>> is because someone tried the same thing via odbc
>> from pc quering postgres database and got the
>> same error ("out of memory").
>>
>> What can a pc user do? Anything?
> Tell the user that he/she should use an appropiate WHERE clause. You
> might also consider adding more swap to the machine. Then it will go
> horribly slow, but it will work.
Or turn the query into a cursor so you can FETCH a reasonable number of
rows at a time.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2007-09-12 01:36 out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
2007-09-12 03:58 ` Re: out of memory Tom Lane <tgl@sss.pgh.pa.us>
2007-09-12 04:15 ` Re: out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
2007-09-12 08:12 ` Re: out of memory Tino Schwarze <postgresql@tisc.de>
2007-09-12 14:20 ` Re: out of memory Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-09-12 15:25 ` Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 0 replies; 24+ messages in thread
From: Tena Sakai @ 2007-09-12 15:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Tino Schwarze <postgresql@tisc.de>; +Cc: pgsql-admin
Many thanks, Tom and Tino.
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wed 9/12/2007 7:20 AM
To: Tino Schwarze
Cc: pgsql-admin@postgresql.org; Tena Sakai
Subject: Re: [ADMIN] out of memory
Tino Schwarze <postgresql@tisc.de> writes:
> On Tue, Sep 11, 2007 at 09:15:56PM -0700, Tena Sakai wrote:
>> But there's a problem, for which I hope you have
>> more wisdom. The reason why I tried that query
>> is because someone tried the same thing via odbc
>> from pc quering postgres database and got the
>> same error ("out of memory").
>>
>> What can a pc user do? Anything?
> Tell the user that he/she should use an appropiate WHERE clause. You
> might also consider adding more swap to the machine. Then it will go
> horribly slow, but it will work.
Or turn the query into a cursor so you can FETCH a reasonable number of
rows at a time.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* out of memory
@ 2009-03-12 18:17 Fabricio <fabrixio1@hotmail.com>
2009-03-12 21:03 ` Re: out of memory Vladimir Rusinov <vladimir@greenmice.info>
2009-03-18 22:20 ` Re: out of memory Fabricio <fabrixio1@hotmail.com>
2009-03-19 00:28 ` Re: out of memory Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 3 replies; 24+ messages in thread
From: Fabricio @ 2009-03-12 18:17 UTC (permalink / raw)
To: pgsql-admin
Hi
I have a dedicated database server with 16 GB of RAM.
and the oom-killer is killing my database
this is the output kernel:
postmaster invoked oom-killer: gfp_mask=0xd0, order=1, oomkilladj=0
Pid: 16667, comm: postmaster Tainted: G W 2.6.27.6 #7
[<c10510f9>] oom_kill_process+0x103/0x1d5
[<c11c14cc>] security_capable+0x9/0xa
[<c1050f0d>] badness+0x118/0x201
[<c10513fc>] out_of_memory+0x231/0x265
[<c1053300>] __alloc_pages_internal+0x322/0x447
[<c106ac5e>] alloc_pages_current+0x59/0x89
[<c1051c2c>] __get_free_pages+0xa/0x16
[<c101f815>] copy_process+0xa2/0xeaa
[<c1020683>] do_fork+0x66/0x2c3
[<c1001a59>] sys_clone+0x32/0x36
[<c100334a>] syscall_call+0x7/0xb
=======================
Mem-Info:
Node 0 DMA per-cpu:
CPU 0: hi: 0, btch: 1 usd: 0
CPU 1: hi: 0, btch: 1 usd: 0
CPU 2: hi: 0, btch: 1 usd: 0
CPU 3: hi: 0, btch: 1 usd: 0
CPU 4: hi: 0, btch: 1 usd: 0
CPU 5: hi: 0, btch: 1 usd: 0
CPU 6: hi: 0, btch: 1 usd: 0
CPU 7: hi: 0, btch: 1 usd: 0
CPU 8: hi: 0, btch: 1 usd: 0
CPU 9: hi: 0, btch: 1 usd: 0
CPU 10: hi: 0, btch: 1 usd: 0
CPU 11: hi: 0, btch: 1 usd: 0
CPU 12: hi: 0, btch: 1 usd: 0
CPU 13: hi: 0, btch: 1 usd: 0
CPU 14: hi: 0, btch: 1 usd: 0
CPU 15: hi: 0, btch: 1 usd: 0
Node 0 Normal per-cpu:
CPU 0: hi: 186, btch: 31 usd: 0
CPU 1: hi: 186, btch: 31 usd: 30
CPU 2: hi: 186, btch: 31 usd: 0
CPU 3: hi: 186, btch: 31 usd: 0
CPU 4: hi: 186, btch: 31 usd: 0
CPU 5: hi: 186, btch: 31 usd: 0
CPU 6: hi: 186, btch: 31 usd: 26
CPU 7: hi: 186, btch: 31 usd: 26
CPU 8: hi: 186, btch: 31 usd: 0
CPU 9: hi: 186, btch: 31 usd: 0
CPU 10: hi: 186, btch: 31 usd: 0
CPU 11: hi: 186, btch: 31 usd: 0
CPU 12: hi: 186, btch: 31 usd: 0
CPU 13: hi: 186, btch: 31 usd: 0
CPU 14: hi: 186, btch: 31 usd: 0
CPU 15: hi: 186, btch: 31 usd: 25
Node 0 HighMem per-cpu:
CPU 0: hi: 186, btch: 31 usd: 0
CPU 1: hi: 186, btch: 31 usd: 30
CPU 2: hi: 186, btch: 31 usd: 0
CPU 3: hi: 186, btch: 31 usd: 0
CPU 4: hi: 186, btch: 31 usd: 0
CPU 5: hi: 186, btch: 31 usd: 167
CPU 6: hi: 186, btch: 31 usd: 25
CPU 7: hi: 186, btch: 31 usd: 19
CPU 8: hi: 186, btch: 31 usd: 0
CPU 9: hi: 186, btch: 31 usd: 0
CPU 10: hi: 186, btch: 31 usd: 0
CPU 11: hi: 186, btch: 31 usd: 0
CPU 12: hi: 186, btch: 31 usd: 0
CPU 13: hi: 186, btch: 31 usd: 0
CPU 14: hi: 186, btch: 31 usd: 0
CPU 15: hi: 186, btch: 31 usd: 23
Active:1573667 inactive:609150 dirty:46 writeback:107 unstable:0
free:1750894 slab:31196 mapped:538832 pagetables:143359 bounce:0
Node 0 DMA free:3540kB min:64kB low:80kB high:96kB active:0kB inactive:0kB prese nt:15836kB pages_scanned:0 all_unreclaimable? no
lowmem_reserve[]: 0 873 16095 16095
Node 0 Normal free:28132kB min:3744kB low:4680kB high:5616kB active:840kB inacti ve:892kB present:894080kB pages_scanned:0 all_unreclaimable? no
lowmem_reserve[]: 0 0 121778 121778
Node 0 HighMem free:6971904kB min:512kB low:16836kB high:33160kB active:6293828k B inactive:2435708kB present:15587608kB pages_scanned:0 all_unreclaimable? no
lowmem_reserve[]: 0 0 0 0
Node 0 DMA: 113*4kB 11*8kB 1*16kB 1*32kB 0*64kB 0*128kB 0*256kB 0*512kB 1*1024kB 1*2048kB 0*4096kB = 3660kB
Node 0 Normal: 6429*4kB 147*8kB 9*16kB 2*32kB 2*64kB 1*128kB 0*256kB 1*512kB 1*1 024kB 0*2048kB 0*4096kB = 28892kB
Node 0 HighMem: 690211*4kB 364384*8kB 72902*16kB 3650*32kB 17*64kB 50*128kB 5*25 6kB 2*512kB 1*1024kB 0*2048kB 1*4096kB = 6974060kB
1920296 total pagecache pages
0 pages in swap cache
Swap cache stats: add 913280, delete 913280, find 207387/229468
Free swap = 1754452kB
Total swap = 3904552kB
4390911 pages RAM
4161535 pages HighMem
268987 pages reserved
6066507 pages shared
1384540 pages non-shared
Out of memory: kill process 13279 (postmaster) score 546058 or a child
Killed process 13279 (postmaster)
This is the output of free -m before and after the kill
free -m
total used free shared buffers cached
Mem: 16101 9302 6799 0 3 7521
-/+ buffers/cache: 1776 14324
Swap: 3813 2099 1713
free -m
total used free shared buffers cached
Mem: 16101 5606 10494 0 7 5410
-/+ buffers/cache: 188 15912
Swap: 3813 2099 1713
the sysctl.conf with
kernel.shmmax = 10737418240
vm.overcommit_memory = 2
I do not what why the oom-killer kill the postgres if there are available memory.
I would appreciate any help
Regars...
_________________________________________________________________
¿Quieres transformarte en un emoticón de Messenger? Descubre cómo
www.messengernews.com.mx=
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2009-03-12 18:17 out of memory Fabricio <fabrixio1@hotmail.com>
@ 2009-03-12 21:03 ` Vladimir Rusinov <vladimir@greenmice.info>
2009-03-13 20:15 ` Re: out of memory Fabricio <fabrixio1@hotmail.com>
2 siblings, 1 reply; 24+ messages in thread
From: Vladimir Rusinov @ 2009-03-12 21:03 UTC (permalink / raw)
To: Fabricio <fabrixio1@hotmail.com>; +Cc: pgsql-admin
On Thu, Mar 12, 2009 at 9:17 PM, Fabricio <fabrixio1@hotmail.com> wrote:
>
> Hi
>
> I have a dedicated database server with 16 GB of RAM.
>
> and the oom-killer is killing my database
>
What kernel and architecture are you running? Show your `uname -a` please.
--
Vladimir Rusinov
http://greenmice.info/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2009-03-12 18:17 out of memory Fabricio <fabrixio1@hotmail.com>
2009-03-12 21:03 ` Re: out of memory Vladimir Rusinov <vladimir@greenmice.info>
@ 2009-03-13 20:15 ` Fabricio <fabrixio1@hotmail.com>
2009-03-19 18:36 ` Re: out of memory Vladimir Rusinov <vladimir@greenmice.info>
0 siblings, 1 reply; 24+ messages in thread
From: Fabricio @ 2009-03-13 20:15 UTC (permalink / raw)
To: vladimir@greenmice.info; +Cc: pgsql-admin
Hello...
Linux 2.6.27.6 #7 SMP Sun Nov 16 00:48:35 MST 2008 i686 i686 i386 GNU/Linux
Slackware 11 and Postgres 8.1.15
thanks...
Date: Fri, 13 Mar 2009 00:03:16 +0300
Subject: Re: [ADMIN] out of memory
From: vladimir@greenmice.info
To: fabrixio1@hotmail.com
CC: pgsql-admin@postgresql.org
On Thu, Mar 12, 2009 at 9:17 PM, Fabricio <fabrixio1@hotmail.com> wrote:
Hi
I have a dedicated database server with 16 GB of RAM.
and the oom-killer is killing my database
What kernel and architecture are you running? Show your `uname -a` please.
--
Vladimir Rusinov
http://greenmice.info/
_________________________________________________________________
¿Conoces todas las novedades de Messenger?
www.messengernews.com.mx=
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2009-03-12 18:17 out of memory Fabricio <fabrixio1@hotmail.com>
2009-03-12 21:03 ` Re: out of memory Vladimir Rusinov <vladimir@greenmice.info>
2009-03-13 20:15 ` Re: out of memory Fabricio <fabrixio1@hotmail.com>
@ 2009-03-19 18:36 ` Vladimir Rusinov <vladimir@greenmice.info>
0 siblings, 0 replies; 24+ messages in thread
From: Vladimir Rusinov @ 2009-03-19 18:36 UTC (permalink / raw)
To: Fabricio <fabrixio1@hotmail.com>; +Cc: pgsql-admin
On Fri, Mar 13, 2009 at 11:15 PM, Fabricio <fabrixio1@hotmail.com> wrote:
>
> Hello...
>
> Linux 2.6.27.6 #7 SMP Sun Nov 16 00:48:35 MST 2008 i686 i686 i386 GNU/Linux
> Slackware 11 and Postgres 8.1.15
>
Go download 64-bit OS.
--
Vladimir Rusinov
http://greenmice.info/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2009-03-12 18:17 out of memory Fabricio <fabrixio1@hotmail.com>
@ 2009-03-18 22:20 ` Fabricio <fabrixio1@hotmail.com>
2 siblings, 0 replies; 24+ messages in thread
From: Fabricio @ 2009-03-18 22:20 UTC (permalink / raw)
To: pgsql-admin
Hi
there's no comments?
Fabricio
From: fabrixio1@hotmail.com
To: pgsql-admin@postgresql.org
Subject: [ADMIN] out of memory
Date: Thu, 12 Mar 2009 11:17:52 -0700
Hi
I have a dedicated database server with 16 GB of RAM.
Linux 2.6.27.6 #7 SMP Sun Nov 16 00:48:35 MST 2008 i686 i686 i386 GNU/Linux
Slackware 11 and Postgres 8.1.15
and the oom-killer is killing my database
this is the output kernel:
postmaster invoked oom-killer: gfp_mask=0xd0, order=1, oomkilladj=0
Pid: 16667, comm: postmaster Tainted: G W 2.6.27.6 #7
[<c10510f9>] oom_kill_process+0x103/0x1d5
[<c11c14cc>] security_capable+0x9/0xa
[<c1050f0d>] badness+0x118/0x201
[<c10513fc>] out_of_memory+0x231/0x265
[<c1053300>] __alloc_pages_internal+0x322/0x447
[<c106ac5e>] alloc_pages_current+0x59/0x89
[<c1051c2c>] __get_free_pages+0xa/0x16
[<c101f815>] copy_process+0xa2/0xeaa
[<c1020683>] do_fork+0x66/0x2c3
[<c1001a59>] sys_clone+0x32/0x36
[<c100334a>] syscall_call+0x7/0xb
=======================
Mem-Info:
Node 0 DMA per-cpu:
CPU 0: hi: 0, btch: 1 usd: 0
CPU 1: hi: 0, btch: 1 usd: 0
CPU 2: hi: 0, btch: 1 usd: 0
CPU 3: hi: 0, btch: 1 usd: 0
CPU 4: hi: 0, btch: 1 usd: 0
CPU 5: hi: 0, btch: 1 usd: 0
CPU 6: hi: 0, btch: 1 usd: 0
CPU 7: hi: 0, btch: 1 usd: 0
CPU 8: hi: 0, btch: 1 usd: 0
CPU 9: hi: 0, btch: 1 usd: 0
CPU 10: hi: 0, btch: 1 usd: 0
CPU 11: hi: 0, btch: 1 usd: 0
CPU 12: hi: 0, btch: 1 usd: 0
CPU 13: hi: 0, btch: 1 usd: 0
CPU 14: hi: 0, btch: 1 usd: 0
CPU 15: hi: 0, btch: 1 usd: 0
Node 0 Normal per-cpu:
CPU 0: hi: 186, btch: 31 usd: 0
CPU 1: hi: 186, btch: 31 usd: 30
CPU 2: hi: 186, btch: 31 usd: 0
CPU 3: hi: 186, btch: 31 usd: 0
CPU 4: hi: 186, btch: 31 usd: 0
CPU 5: hi: 186, btch: 31 usd: 0
CPU 6: hi: 186, btch: 31 usd: 26
CPU 7: hi: 186, btch: 31 usd: 26
CPU 8: hi: 186, btch: 31 usd: 0
CPU 9: hi: 186, btch: 31 usd: 0
CPU 10: hi: 186, btch: 31 usd: 0
CPU 11: hi: 186, btch: 31 usd: 0
CPU 12: hi: 186, btch: 31 usd: 0
CPU 13: hi: 186, btch: 31 usd: 0
CPU 14: hi: 186, btch: 31 usd: 0
CPU 15: hi: 186, btch: 31 usd: 25
Node 0 HighMem per-cpu:
CPU 0: hi: 186, btch: 31 usd: 0
CPU 1: hi: 186, btch: 31 usd: 30
CPU 2: hi: 186, btch: 31 usd: 0
CPU 3: hi: 186, btch: 31 usd: 0
CPU 4: hi: 186, btch: 31 usd: 0
CPU 5: hi: 186, btch: 31 usd: 167
CPU 6: hi: 186, btch: 31 usd: 25
CPU 7: hi: 186, btch: 31 usd: 19
CPU 8: hi: 186, btch: 31 usd: 0
CPU 9: hi: 186, btch: 31 usd: 0
CPU 10: hi: 186, btch: 31 usd: 0
CPU 11: hi: 186, btch: 31 usd: 0
CPU 12: hi: 186, btch: 31 usd: 0
CPU 13: hi: 186, btch: 31 usd: 0
CPU 14: hi: 186, btch: 31 usd: 0
CPU 15: hi: 186, btch: 31 usd: 23
Active:1573667 inactive:609150 dirty:46 writeback:107 unstable:0
free:1750894 slab:31196 mapped:538832 pagetables:143359 bounce:0
Node 0 DMA free:3540kB min:64kB low:80kB high:96kB active:0kB inactive:0kB prese nt:15836kB pages_scanned:0 all_unreclaimable? no
lowmem_reserve[]: 0 873 16095 16095
Node 0 Normal free:28132kB min:3744kB low:4680kB high:5616kB active:840kB inacti ve:892kB present:894080kB pages_scanned:0 all_unreclaimable? no
lowmem_reserve[]: 0 0 121778 121778
Node 0 HighMem free:6971904kB min:512kB low:16836kB high:33160kB active:6293828k B inactive:2435708kB present:15587608kB pages_scanned:0 all_unreclaimable? no
lowmem_reserve[]: 0 0 0 0
Node 0 DMA: 113*4kB 11*8kB 1*16kB 1*32kB 0*64kB 0*128kB 0*256kB 0*512kB 1*1024kB 1*2048kB 0*4096kB = 3660kB
Node 0 Normal: 6429*4kB 147*8kB 9*16kB 2*32kB 2*64kB 1*128kB 0*256kB 1*512kB 1*1 024kB 0*2048kB 0*4096kB = 28892kB
Node 0 HighMem: 690211*4kB 364384*8kB 72902*16kB 3650*32kB 17*64kB 50*128kB 5*25 6kB 2*512kB 1*1024kB 0*2048kB 1*4096kB = 6974060kB
1920296 total pagecache pages
0 pages in swap cache
Swap cache stats: add 913280, delete 913280, find 207387/229468
Free swap = 1754452kB
Total swap = 3904552kB
4390911 pages RAM
4161535 pages HighMem
268987 pages reserved
6066507 pages shared
1384540 pages non-shared
Out of memory: kill process 13279 (postmaster) score 546058 or a child
Killed process 13279 (postmaster)
This is the output of free -m before and after the kill
free -m
total used free shared buffers cached
Mem: 16101 9302 6799 0 3 7521
-/+ buffers/cache: 1776 14324
Swap: 3813 2099 1713
free -m
total used free shared buffers cached
Mem: 16101 5606 10494 0 7 5410
-/+ buffers/cache: 188 15912
Swap: 3813 2099 1713
the sysctl.conf with
kernel.shmmax = 10737418240
vm.overcommit_memory = 2
I do not what why the oom-killer kill the postgres if there are available memory.
I would appreciate any help
Regars...
No olvides consultar tus correos desde la ventana principal de Messenger
_________________________________________________________________
¿Qué hay de nuevo con tus contactos de Messenger?
www.messengernews.com.mx=
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2009-03-12 18:17 out of memory Fabricio <fabrixio1@hotmail.com>
@ 2009-03-19 00:28 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-03-20 16:15 ` Re: out of memory Fabricio <fabrixio1@hotmail.com>
2 siblings, 1 reply; 24+ messages in thread
From: Alvaro Herrera @ 2009-03-19 00:28 UTC (permalink / raw)
To: Fabricio <fabrixio1@hotmail.com>; +Cc: pgsql-admin
Fabricio wrote:
>
>
> Hi
>
> I have a dedicated database server with 16 GB of RAM.
>
> and the oom-killer is killing my database
Try disabling memory overcommit; see 17.4.3 here:
http://www.postgresql.org/docs/8.3/interactive/kernel-resources.html
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory
2009-03-12 18:17 out of memory Fabricio <fabrixio1@hotmail.com>
2009-03-19 00:28 ` Re: out of memory Alvaro Herrera <alvherre@commandprompt.com>
@ 2009-03-20 16:15 ` Fabricio <fabrixio1@hotmail.com>
0 siblings, 0 replies; 24+ messages in thread
From: Fabricio @ 2009-03-20 16:15 UTC (permalink / raw)
To: alvherre@commandprompt.com; +Cc: pgsql-admin
Thanks for answer Alvaro
Already i have memory overcommit disabled.
I've always had with the value 2
vm.overcommit_memory = 2
Fabricio
> Date: Wed, 18 Mar 2009 20:28:02 -0400
> From: alvherre@commandprompt.com
> To: fabrixio1@hotmail.com
> CC: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] out of memory
>
> Fabricio wrote:
> >
> >
> > Hi
> >
> > I have a dedicated database server with 16 GB of RAM.
> >
> > and the oom-killer is killing my database
>
> Try disabling memory overcommit; see 17.4.3 here:
> http://www.postgresql.org/docs/8.3/interactive/kernel-resources.html
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
_________________________________________________________________
¿Conoces todas las novedades de Messenger?
www.messengernews.com.mx=
^ permalink raw reply [nested|flat] 24+ messages in thread
* out of memory ...
@ 2010-05-03 11:15 Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2010-05-03 11:56 ` Re: out of memory ... Cédric Villemain <cedric.villemain.debian@gmail.com>
0 siblings, 1 reply; 24+ messages in thread
From: Jan-Peter Seifert @ 2010-05-03 11:15 UTC (permalink / raw)
To: pgsql-admin
Hello,
how fatal is it for the server/data if a server runs out of memory?
Happened on a Windows server some time ago. A transaction got too large. In the log it began with:
------------
TopMemoryContext: 37171728 total in 4046 blocks; 67640 free (4060 chunks); 37104088 used
TopTransactionContext: 24576 total in 2 blocks; 18848 free (17 chunks); 5728 used
CurTransactionContext: 8192 total in 1 blocks; 8176 free (1 chunks); 16 used
...
------------
Although all other clients had been closed no new connection was possible to terminate the session responsible for the out of memory problem. I guess it's because this transaction had been started by a superuser ...
------------
In the end the server was shut down via the service manager.
On the next start of the server the log contained the following lines:
------------
LOG: database system was interrupted; last known up at 2010-03-26 11:22:36 CET
LOG: database system was not properly shut down; automatic recovery in progress
LOG: record with zero length at 1F/D2454CD0
------------
The data still seemed to be okay - all the other transactions made before seem to be in the database.
Is it okay to continue using the database(s) or is there a risk of hidden data corruption (fsync is on)?
Could you tell me, please?
Thank you very much,
Peter
--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory ...
2010-05-03 11:15 out of memory ... Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
@ 2010-05-03 11:56 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2010-05-06 20:29 ` Re: out of memory ... Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
0 siblings, 1 reply; 24+ messages in thread
From: Cédric Villemain @ 2010-05-03 11:56 UTC (permalink / raw)
To: Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>; +Cc: pgsql-admin
2010/5/3 Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>:
> Hello,
>
> how fatal is it for the server/data if a server runs out of memory?
>
> Happened on a Windows server some time ago. A transaction got too large. In the log it began with:
>
> ------------
> TopMemoryContext: 37171728 total in 4046 blocks; 67640 free (4060 chunks); 37104088 used
> TopTransactionContext: 24576 total in 2 blocks; 18848 free (17 chunks); 5728 used
> CurTransactionContext: 8192 total in 1 blocks; 8176 free (1 chunks); 16 used
> ...
> ------------
>
> Although all other clients had been closed no new connection was possible to terminate the session responsible for the out of memory problem. I guess it's because this transaction had been started by a superuser ...
>
> ------------
>
> In the end the server was shut down via the service manager.
>
> On the next start of the server the log contained the following lines:
>
> ------------
> LOG: database system was interrupted; last known up at 2010-03-26 11:22:36 CET
> LOG: database system was not properly shut down; automatic recovery in progress
> LOG: record with zero length at 1F/D2454CD0
> ------------
>
> The data still seemed to be okay - all the other transactions made before seem to be in the database.
>
> Is it okay to continue using the database(s) or is there a risk of hidden data corruption (fsync is on)?
okay to continue if your filesystem didn't have issues (if postgres
start, then it is ok from its side)
>
> Could you tell me, please?
>
> Thank you very much,
>
> Peter
>
>
> --
> GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
Cédric Villemain
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: out of memory ...
2010-05-03 11:15 out of memory ... Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2010-05-03 11:56 ` Re: out of memory ... Cédric Villemain <cedric.villemain.debian@gmail.com>
@ 2010-05-06 20:29 ` Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
0 siblings, 0 replies; 24+ messages in thread
From: Jan-Peter Seifert @ 2010-05-06 20:29 UTC (permalink / raw)
To: Cédric Villemain <cedric.villemain.debian@gmail.com>; +Cc: pgsql-admin
Hello,
>> ------------
>> LOG: database system was interrupted; last known up at 2010-03-26 11:22:36 CET
>> LOG: database system was not properly shut down; automatic recovery in progress
>> LOG: record with zero length at 1F/D2454CD0
>> ------------
>>
>> The data still seemed to be okay - all the other transactions made before seem to be in the database.
>>
>> Is it okay to continue using the database(s) or is there a risk of hidden data corruption (fsync is on)?
>
> okay to continue if your filesystem didn't have issues (if postgres
> start, then it is ok from its side)
Sounds good.
Thank you very much for your reply.
Peter
^ permalink raw reply [nested|flat] 24+ messages in thread
* Out of memory
@ 2011-07-21 14:11 Johann Spies <jspies@sun.ac.za>
2011-07-21 14:36 ` Re: Out of memory Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Johann Spies @ 2011-07-21 14:11 UTC (permalink / raw)
To: pgsql-admin
On a computer with 2G Ram running Debian Squeeze and Postgresql 8.4.8-0squeeze2
I made a dump using 'pg_dump kb > kb.sql'.
I copied the file to another computer with 8G RAM running Debian
wheezy/sid/Postgresql 8.4.8-2 and tried to load that data by running
'psql -f kb.sql' - a process which produced a lot of errors ending with
psql:kb.sql:4189263: invalid command \N
psql:kb.sql:4189277: invalid command \N
psql:kb.sql:4189278: invalid command \N
psql:kb.sql:4189279: invalid command \nMost
psql:kb.sql:4189280: invalid command \N
psql:kb.sql:4189281: invalid command \N
psql:kb.sql:4189282: invalid command \N
psql:kb.sql:4189283: invalid command \N
psql:kb.sql:4189284: invalid command \N
psql:kb.sql:7991569: ERROR: out of memory
DETAIL: Cannot enlarge string buffer containing 0 bytes by 1311240426 more bytes.
What is causing the 'invalid command' lines?
And the memory problem?
There are several foreign keys in the database.
Regards
Johann
--
Johann Spies Telefoon: 021-808 4699
Databestuurder / Data manager
Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology
Universiteit Stellenbosch.
"Children, obey your parents in the Lord: for this is
right." Ephesians 6:1
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Out of memory
2011-07-21 14:11 Out of memory Johann Spies <jspies@sun.ac.za>
@ 2011-07-21 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-07-22 08:55 ` Re: Out of memory Johann Spies <jspies@sun.ac.za>
0 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 2011-07-21 14:36 UTC (permalink / raw)
To: Johann Spies <jspies@sun.ac.za>; +Cc: pgsql-admin
Johann Spies <jspies@sun.ac.za> writes:
> On a computer with 2G Ram running Debian Squeeze and Postgresql 8.4.8-0squeeze2
> I made a dump using 'pg_dump kb > kb.sql'.
> I copied the file to another computer with 8G RAM running Debian
> wheezy/sid/Postgresql 8.4.8-2 and tried to load that data by running
> 'psql -f kb.sql' - a process which produced a lot of errors ending with
> psql:kb.sql:4189263: invalid command \N
> psql:kb.sql:4189277: invalid command \N
Almost always, when you get a cascade of error messages, the thing to
look at is the *first* error, or first few errors. Not the last ones.
In this case I'd guess that a COPY command failed and psql is now trying
to process the COPY data as SQL commands, but you'll never be able to
tell any more than that without seeing the error message that the COPY
reported.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Out of memory
2011-07-21 14:11 Out of memory Johann Spies <jspies@sun.ac.za>
2011-07-21 14:36 ` Re: Out of memory Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-07-22 08:55 ` Johann Spies <jspies@sun.ac.za>
0 siblings, 0 replies; 24+ messages in thread
From: Johann Spies @ 2011-07-22 08:55 UTC (permalink / raw)
To: pgsql-admin
On Thu, Jul 21, 2011 at 04:36:56PM +0200, Tom Lane wrote:
>
> Almost always, when you get a cascade of error messages, the thing to
> look at is the *first* error, or first few errors. Not the last ones.
>
> In this case I'd guess that a COPY command failed and psql is now trying
> to process the COPY data as SQL commands, but you'll never be able to
> tell any more than that without seeing the error message that the COPY
> reported.
>
Thanks Tom. That is something that makes a lot of sense.
I have done two things and one or both of them made a difference:
moved the database to a partition with more free space and I deleted and
recreated the database.
I could successfully import the data now.
Regards
Johann
--
Johann Spies Telefoon: 021-808 4699
Databestuurder / Data manager
Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology
Universiteit Stellenbosch.
"Children, obey your parents in the Lord: for this is
right." Ephesians 6:1
^ permalink raw reply [nested|flat] 24+ messages in thread
* Out of memory...
@ 2012-02-17 15:41 Cassiano, Marco <mcassiano@manord.com>
2012-02-17 21:00 ` Re: Out of memory... Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 24+ messages in thread
From: Cassiano, Marco @ 2012-02-17 15:41 UTC (permalink / raw)
To: pgsql-admin
Hi all,
This morning my db experienced 10 minutes of "out of memory" condition with the log filled up of messages like :
TopMemoryContext: 90856 total in 13 blocks; 7936 free (6 chunks); 82920 used
TopTransactionContext: 24576 total in 2 blocks; 21360 free (15 chunks); 3216 used
TOAST to main relid map: 24576 total in 2 blocks; 11872 free (5 chunks); 12704 used
AV worker: 24576 total in 2 blocks; 19312 free (9 chunks); 5264 used
Autovacuum Portal: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
Vacuum: 8192 total in 1 blocks; 8080 free (0 chunks); 112 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
smgr relation table: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 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: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 24576 total in 2 blocks; 13872 free (3 chunks); 10704 used
CacheMemoryContext: 817840 total in 20 blocks; 151408 free (2 chunks); 666432 used
mmfg.cc_records_2_mmfg_nome_file: 2048 total in 1 blocks; 776 free (0 chunks); 1272 used
mmfg.cc_records_1_societa,cliente,anno,stagione,collezione,clas: 2048 total in 1 blocks; 224 free (0 chunks); 1824 used
cc_records_pkey: 2048 total in 1 blocks; 224 free (0 chunks); 1824 used
pg_index_indrelid_index: 2048 total in 1 blocks; 728 free (0 chunks); 1320 used
..
pg_authid_rolname_index: 3072 total in 2 blocks; 1768 free (4 chunks); 1304 used
MdSmgr: 8192 total in 1 blocks; 7968 free (0 chunks); 224 used
LOCALLOCK hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
Postmaster: 57344 total in 3 blocks; 48640 free (331 chunks); 8704 used
ErrorContext: 8192 total in 1 blocks; 8160 free (4 chunks); 32 used
2012-02-17 11:08:47 CET 0 4f3e272f.65db - ERROR: out of memory
2012-02-17 11:08:47 CET 0 4f3e272f.65db - DETAIL: Failed on request of size 16731918.
2012-02-17 11:08:47 CET 0 4f3e272f.65db - CONTEXT: automatic vacuum of table "mdn.mmfg.cc_records"
2012-02-17 11:08:53 CET 0 4f3e272f.65db - LOG: automatic vacuum of table "mdn.mmfg.mo7_records": index scans: 0
OR
TopMemoryContext: 164632 total in 22 blocks; 12328 free (44 chunks); 152304 used
TopTransactionContext: 8192 total in 1 blocks; 7328 free (0 chunks); 864 used
RI compare cache: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
RI query cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used
Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
MessageContext: 524288 total in 7 blocks; 48672 free (6 chunks); 475616 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
smgr relation table: 57344 total in 3 blocks; 17872 free (10 chunks); 39472 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; 7888 free (0 chunks); 304 used
PortalHeapMemory: 1024 total in 1 blocks; 824 free (0 chunks); 200 used
ExecutorState: 122880 total in 4 blocks; 8152 free (2 chunks); 114728 used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 3170352 total in 10 blocks; 304 free (5 chunks); 3170048 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
AggContext: 8192 total in 1 blocks; 8016 free (0 chunks); 176 used
TupleHashTable: 253952 total in 5 blocks; 119344 free (16 chunks); 134608 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: 24576 total in 2 blocks; 5552 free (3 chunks); 19024 used
CacheMemoryContext: 4618048 total in 36 blocks; 986456 free (5 chunks); 3631592 used
AND ALSO
movcor.movcor_pf_dett_1_mod,var: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
CachedPlan: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
CachedPlanSource: 3072 total in 2 blocks; 1832 free (1 chunks); 1240 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 3072 total in 2 blocks; 768 free (0 chunks); 2304 used
..
..
CachedPlanSource: 3072 total in 2 blocks; 1968 free (2 chunks); 1104 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 1024 total in 1 blocks; 176 free (0 chunks); 848 used
CachedPlanSource: 3072 total in 2 blocks; 1736 free (1 chunks); 1336 used
AND
2012-02-17 11:19:31 CET 0 4f05cb33.1c0d - LOG: could not fork new process for connection: Cannot allocate memory
There was an autovaccum running on a big table saying it was "to avoid xid wraparound"
It happened only once till this morning, and that time Postgresql finally crashed closing all the connections and restarting automatically with a recovery.
My configuration is :
Postgresql 9.1.2 compiled and running on Redhat 5 64 bit
DB Size : about 100 GB
RAM 5 GB
Shared Buffers 1 GB
temp_buffers = 8MB
work_mem = 12MB
maintenance_work_mem = 300MB
Could you help to understand
1) How can I understand what is filling up memory ?
2) How can I avoid (or limit) this kind of situations ?
Thank you very much!
Marco
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Out of memory...
2012-02-17 15:41 Out of memory... Cassiano, Marco <mcassiano@manord.com>
@ 2012-02-17 21:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 24+ messages in thread
From: Kevin Grittner @ 2012-02-17 21:00 UTC (permalink / raw)
To: Marco Cassiano <mcassiano@manord.com>; pgsql-admin
"Cassiano, Marco" <mcassiano@manord.com> wrote:
> There was an autovaccum running on a big table saying it was "to
> avoid xid wraparound"
> My configuration is :
>
> Postgresql 9.1.2 compiled and running on Redhat 5 64 bit
> DB Size : about 100 GB
>
> RAM 5 GB
> Shared Buffers 1 GB
> temp_buffers = 8MB
> work_mem = 12MB
> maintenance_work_mem = 300MB
Are those the only settings changed from their defaults? If not, or
you're not sure, please post the output from running the query on
this page:
http://wiki.postgresql.org/wiki/Server_Configuration
-Kevin
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2012-02-17 21:00 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-06 20:46 Out of Memory Abu Mushayeed <amushayeed@hotmail.com>
2006-11-06 22:24 ` Abu Mushayeed <abumushayeed@yahoo.com>
2006-11-07 04:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-12 01:36 out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
2007-09-12 03:58 ` Re: out of memory Tom Lane <tgl@sss.pgh.pa.us>
2007-09-12 04:15 ` Re: out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
2007-09-12 08:12 ` Re: out of memory Tino Schwarze <postgresql@tisc.de>
2007-09-12 14:20 ` Re: out of memory Tom Lane <tgl@sss.pgh.pa.us>
2007-09-12 15:25 ` Re: out of memory Tena Sakai <tsakai@gallo.ucsf.edu>
2009-03-12 18:17 out of memory Fabricio <fabrixio1@hotmail.com>
2009-03-12 21:03 ` Re: out of memory Vladimir Rusinov <vladimir@greenmice.info>
2009-03-13 20:15 ` Re: out of memory Fabricio <fabrixio1@hotmail.com>
2009-03-19 18:36 ` Re: out of memory Vladimir Rusinov <vladimir@greenmice.info>
2009-03-18 22:20 ` Re: out of memory Fabricio <fabrixio1@hotmail.com>
2009-03-19 00:28 ` Re: out of memory Alvaro Herrera <alvherre@commandprompt.com>
2009-03-20 16:15 ` Re: out of memory Fabricio <fabrixio1@hotmail.com>
2010-05-03 11:15 out of memory ... Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2010-05-03 11:56 ` Re: out of memory ... Cédric Villemain <cedric.villemain.debian@gmail.com>
2010-05-06 20:29 ` Re: out of memory ... Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2011-07-21 14:11 Out of memory Johann Spies <jspies@sun.ac.za>
2011-07-21 14:36 ` Re: Out of memory Tom Lane <tgl@sss.pgh.pa.us>
2011-07-22 08:55 ` Re: Out of memory Johann Spies <jspies@sun.ac.za>
2012-02-17 15:41 Out of memory... Cassiano, Marco <mcassiano@manord.com>
2012-02-17 21:00 ` Re: Out of memory... Kevin Grittner <Kevin.Grittner@wicourts.gov>
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