[PERFORM] Query Fails with error calloc - Cannot alocate memory

2005-12-06 Thread Howard Oblowitz 
Title: Query Fails with error calloc - Cannot alocate memory






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 buffers24

Sort Mem40960

Vacuum Mem   80192

Checkpoint Timeout  600

Enable Seqscan  false

Effective Cache Size 20


Results of explain analyze and expain analyze verbose:

explain analyze select * from flash_by_branches;

 QUERY PLAN 

--

Seq Scan on flash_by_branches (cost=1.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 1.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=1.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

Re: [PERFORM] Query Fails with error calloc - Cannot alocate memory

2005-12-06 Thread Christopher Kings-Lynne 
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 Mem40960

Vacuum Mem  80192

Checkpoint Timeout  600

Enable Seqscan  false

Effective Cache Size20


Results of explain analyze and expain analyze verbose:

explain analyze select * from flash_by_branches;

  QUERY 
PLAN 


--

 Seq Scan on flash_by_branches  (cost=1.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 1.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=1.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
 




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Query Fails with error calloc - Cannot alocate memory

2005-12-06 Thread Neil Conway 
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



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly