pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Fails with error calloc - Cannot alocate memory
3+ messages / 3 participants
[nested] [flat]
* Query Fails with error calloc - Cannot alocate memory
@ 2005-12-05 07:42 Howard Oblowitz <Howard.Oblowitz@lewisgroup.co.za>
0 siblings, 2 replies; 3+ messages in thread
From: Howard Oblowitz @ 2005-12-05 07:42 UTC (permalink / raw)
To: pgsql-performance
Hi ...
I am trying to run a query that selects 26 million rows from a
table with 68 byte rows.
When run on the Server via psql the following error occurs:
calloc : Cannot allocate memory
When run via ODBC from Cognos Framework Manager only works
if we limit the retrieval to 3 million rows.
I notice that the memory used by the query when run on the Server
increases
to about 2.4 GB before the query fails.
Postgres version is 7.3.4
Running on Linux Redhat 7.2
4 GB memory
7 Processor 2.5 Ghz
Shmmax set to 2 GB
Configuration Parameters
Shared Buffers 12 288
Max Connections 16
Wal buffers 24
Sort Mem 40960
Vacuum Mem 80192
Checkpoint Timeout 600
Enable Seqscan false
Effective Cache Size 200000
Results of explain analyze and expain analyze verbose:
explain analyze select * from flash_by_branches;
QUERY
PLAN
------------------------------------------------------------------------
----------------------------------------------------------------------
Seq Scan on flash_by_branches (cost=100000000.00..100567542.06
rows=26854106 width=68) (actual time=12.14..103936.35 rows=26854106
loops=1)
Total runtime: 122510.02 msec
(2 rows)
explain analyze verbose:
{ SEQSCAN
:startup_cost 100000000.00
:total_cost 100567542.06
:rows 26854106
:width 68
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 1043
:restypmod 8
:resname br_code
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 1
:vartype 1043
:vartypmod 8
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 23
:restypmod -1
:resname fty_code
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 3
:restype 1082
:restypmod -1
:resname period
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 3
:vartype 1082
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 4
:restype 1700
:restypmod 786436
:resname value
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 4
:vartype 1700
:vartypmod 786436
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 7
:restype 1700
:restypmod 786438
:resname value1
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 7
:vartype 1700
:vartypmod 786438
:varlevelsup 0
:varnoold 1
:varoattno 7
}
}
)
:qpqual <>
:lefttree <>
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:scanrelid 1
}
Seq Scan on flash_by_branches (cost=100000000.00..100567542.06
rows=26854106 width=68) (actual time=6.59..82501.15 rows=2685
4106 loops=1)
Total runtime: 102089.00 msec
(196 rows)
Please assist.
Thanks,
Howard Oblowitz
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.859 / Virus Database: 585 - Release Date: 14/02/2005
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query Fails with error calloc - Cannot alocate memory
@ 2005-12-07 04:18 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Howard Oblowitz <Howard.Oblowitz@lewisgroup.co.za>
1 sibling, 0 replies; 3+ messages in thread
From: Christopher Kings-Lynne @ 2005-12-07 04:18 UTC (permalink / raw)
To: Howard Oblowitz <Howard.Oblowitz@lewisgroup.co.za>; +Cc: pgsql-performance
If you're trying to retrieve 26 million rows into RAM in one go of
course it'll be trouble.
Just use a cursor. (DECLARE/FETCH/MOVE)
Chris
Howard Oblowitz wrote:
> Hi …
>
> I am trying to run a query that selects 26 million rows from a
>
> table with 68 byte rows.
>
> When run on the Server via psql the following error occurs:
>
> calloc : Cannot allocate memory
>
> When run via ODBC from Cognos Framework Manager only works
>
> if we limit the retrieval to 3 million rows.
>
> I notice that the memory used by the query when run on the Server increases
>
> to about 2.4 GB before the query fails.
>
> Postgres version is 7.3.4
>
> Running on Linux Redhat 7.2
>
> 4 GB memory
>
> 7 Processor 2.5 Ghz
>
> Shmmax set to 2 GB
>
> Configuration Parameters
>
> Shared Buffers 12 288
>
> Max Connections 16
>
> Wal buffers 24
>
> Sort Mem 40960
>
> Vacuum Mem 80192
>
> Checkpoint Timeout 600
>
> Enable Seqscan false
>
> Effective Cache Size 200000
>
>
> Results of explain analyze and expain analyze verbose:
>
> explain analyze select * from flash_by_branches;
>
> QUERY
> PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------------------
>
> Seq Scan on flash_by_branches (cost=100000000.00..100567542.06
> rows=26854106 width=68) (actual time=12.14..103936.35 rows=26854106 loops=1)
>
> Total runtime: 122510.02 msec
>
> (2 rows)
>
> explain analyze verbose:
>
> { SEQSCAN
>
> :startup_cost 100000000.00
>
> :total_cost 100567542.06
>
> :rows 26854106
>
> :width 68
>
> :qptargetlist (
>
> { TARGETENTRY
>
> :resdom
>
> { RESDOM
>
> :resno 1
>
> :restype 1043
>
> :restypmod 8
>
> :resname br_code
>
> :reskey 0
>
> :reskeyop 0
>
> :ressortgroupref 0
>
> :resjunk false
>
> }
>
> :expr
>
> { VAR
>
> :varno 1
>
> :varattno 1
>
> :vartype 1043
>
> :vartypmod 8
>
> :varlevelsup 0
>
> :varnoold 1
>
> :varoattno 1
>
> }
>
> }
>
> { TARGETENTRY
>
> :resdom
>
> { RESDOM
>
> :resno 2
>
> :restype 23
>
> :restypmod -1
>
> :resname fty_code
>
> :reskey 0
>
> :reskeyop 0
>
> :ressortgroupref 0
>
> :resjunk false
>
> }
>
> :expr
>
> { VAR
>
> :varno 1
>
> :varattno 2
>
> :vartype 23
>
> :vartypmod -1
>
> :varlevelsup 0
>
> :varnoold 1
>
> :varoattno 2
>
> }
>
> }
>
> { TARGETENTRY
>
> :resdom
>
> { RESDOM
>
> :resno 3
>
> :restype 1082
>
> :restypmod -1
>
> :resname period
>
> :reskey 0
>
> :reskeyop 0
>
> :ressortgroupref 0
>
> :resjunk false
>
> }
>
> :expr
>
> { VAR
>
> :varno 1
>
> :varattno 3
>
> :vartype 1082
>
> :vartypmod -1
>
> :varlevelsup 0
>
> :varnoold 1
>
> :varoattno 3
>
> }
>
> }
>
> { TARGETENTRY
>
> :resdom
>
> { RESDOM
>
> :resno 4
>
> :restype 1700
>
> :restypmod 786436
>
> :resname value
>
> :reskey 0
>
> :reskeyop 0
>
> :ressortgroupref 0
>
> :resjunk false
>
> }
>
> :expr
>
> { VAR
>
> :varno 1
>
> :varattno 4
>
> :vartype 1700
>
> :vartypmod 786436
>
> :varlevelsup 0
>
> :varnoold 1
>
> :varoattno 4
>
> }
>
> }
>
> { TARGETENTRY
>
> :resdom
>
> { RESDOM
>
> :resno 7
>
> :restype 1700
>
> :restypmod 786438
>
> :resname value1
>
> :reskey 0
>
> :reskeyop 0
>
> :ressortgroupref 0
>
> :resjunk false
>
> }
>
> :expr
>
> { VAR
>
> :varno 1
>
> :varattno 7
>
> :vartype 1700
>
> :vartypmod 786438
>
> :varlevelsup 0
>
> :varnoold 1
>
> :varoattno 7
>
> }
>
> }
>
> )
>
> :qpqual <>
>
> :lefttree <>
>
> :righttree <>
>
> :extprm ()
>
> :locprm ()
>
> :initplan <>
>
> :nprm 0
>
> :scanrelid 1
>
> }
>
> Seq Scan on flash_by_branches (cost=100000000.00..100567542.06
> rows=26854106 width=68) (actual time=6.59..82501.15 rows=2685
>
> 4106 loops=1)
>
> Total runtime: 102089.00 msec
>
> (196 rows)
>
>
>
> Please assist.
>
> Thanks,
>
> Howard Oblowitz
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.859 / Virus Database: 585 - Release Date: 14/02/2005
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query Fails with error calloc - Cannot alocate memory
@ 2005-12-07 06:31 Neil Conway <neilc@samurai.com>
parent: Howard Oblowitz <Howard.Oblowitz@lewisgroup.co.za>
1 sibling, 0 replies; 3+ messages in thread
From: Neil Conway @ 2005-12-07 06:31 UTC (permalink / raw)
To: Howard Oblowitz <Howard.Oblowitz@lewisgroup.co.za>; +Cc: pgsql-performance
On Mon, 2005-12-05 at 09:42 +0200, Howard Oblowitz wrote:
> I am trying to run a query that selects 26 million rows from a
> table with 68 byte rows.
>
> When run on the Server via psql the following error occurs:
>
> calloc : Cannot allocate memory
That's precisely what I'd expect: the backend will process the query and
begin sending back the entire result set to the client. The client will
attempt to allocate a local buffer to hold the entire result set, which
obviously fails in this case.
You probably want to explicitly create and manipulate a cursor via
DECLARE, FETCH, and the like -- Postgres will not attempt to do this
automatically (for good reason).
> Postgres version is 7.3.4
You should consider upgrading, 7.3 is quite old. At the very least, you
should probably be using the most recent 7.3.x release, 7.3.11.
-Neil
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-07 06:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-05 07:42 Query Fails with error calloc - Cannot alocate memory Howard Oblowitz <Howard.Oblowitz@lewisgroup.co.za>
2005-12-07 04:18 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-12-07 06:31 ` Neil Conway <neilc@samurai.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