[PERFORM] Query Fails with error calloc - Cannot alocate memory
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 buffers 24
Sort Mem 40960
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
[PERFORM] Unsubscribe
Unsubscribe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Performance advice
I agree a route map would really help. > -Original Message- > From: Hilary Forbes [SMTP:[EMAIL PROTECTED] > Sent: 25 June 2003 10:12 > To: Rod Taylor > Cc: [EMAIL PROTECTED] > Subject: Re: [PERFORM] Performance advice > > PM4JI but from my point of view this has been a most useful thread. I too > have found it difficult to find the right bit of documentation on > performance. I *think* what is needed is some sort of a route map, Poor > Performance - start here. Then some questions with sections of the > documentation you should go to. > > Hilary > > At 13:00 24/06/2003 -0400, you wrote: > >> configure it properly and trial & error. I do think the documentation > could > >> be enhanced a bit here, but I'm sure there are some users who don't > make > > > >Do you have any specific thoughts about documentation? Areas of > >confusion? Was it difficult to find the information in question, or was > >it simply unavailable? > > > >-- > >Rod Taylor <[EMAIL PROTECTED]> > > > >PGP Key: http://www.rbt.ca/rbtpub.asc > > > Hilary Forbes > - > DMR Computer Limited: http://www.dmr.co.uk/ > Direct line: 01689 889950 > Switchboard: (44) 1689 86 Fax: (44) 1689 860330 > E-mail: [EMAIL PROTECTED] > > ** > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
FW: [PERFORM] Version 7 question
What would be the best value range for effective_cache_size on Postgres 7.3.2, assuming say 1.5 GB of RAM and shared_buffers set to 8192, and shmmax set to 750mb? And what are the most important factors one should take into account in determining the value? > -Original Message- > From: scott.marlowe [SMTP:[EMAIL PROTECTED] > Sent: 01 July 2003 02:56 > To: Michael Mattox > Cc: Hilary Forbes; [EMAIL PROTECTED] > Subject: Re: [PERFORM] Version 7 question > > 8192 is only 64 megs of RAM, not much, but a good number. Keep in mind > that the kernel tends to be better at buffering huge amounts of disk, > while postgresql is better left to use buffers that are large enough for > the current working set (i.e. not your whole database, just the largest > amount of data you're slinging about on a regular basis in one query.) > > On a machine with 1.5 gig of RAM, I've found settings as high as 32768 > (256 megs of ram) to run well, but anything over that doesn't help. Of > course, we don't toss around more than a hundred meg or so at a time. If > > our result sets were in the gigabyte range, I'd A: want more memory and B: > > Give more of it to postgresql. > > The original poster was, I believe running 7.0.x, which is way old, so no, > > I don't think there was an equivalent of effective_cache_size in that > version. Upgrading would be far easier than performance tuning 7.0. since > > the query planner was much simpler (i.e. more prone to make bad decisions) > > in 7.0. > > On Tue, 1 Jul 2003, Michael Mattox wrote: > > > I have my shared buffers at 8192 and my effective cache at 64000 (which > is > > 500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs and > > I've been asking my boss for another 512megs for over a month now. I > have > > no idea if my buffers are too high/low. > > > > Michael > > > > > -Original Message- > > > From: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED] Behalf Of Hilary > > > Forbes > > > Sent: Tuesday, July 01, 2003 2:10 PM > > > To: [EMAIL PROTECTED] > > > Subject: [PERFORM] Version 7 question > > > > > > > > > I'm just trying to improve performance on version 7 before doing > > > some tests and hopefully upgrading to 7.3. > > > > > > At the moment we have > > > B=64 (no of shared buffers) > > > N=32 (no of connections) > > > in postmaster.opt which I take it is the equivalent of the new > > > postgresql.conf file. > > > > > > From all that is being written about later versions I suspect > > > that this is far too low. Would I be fairly safe in making the > > > no of shared buffers larger? Also is there an equivalent of > > > effective_cache_size that I can set for version 7? > > > > > > Many thanks in advance > > > Hilary > > > > > > > > > > > > > > > Hilary Forbes > > > - > > > DMR Computer Limited: http://www.dmr.co.uk/ > > > Direct line: 01689 889950 > > > Switchboard: (44) 1689 86 Fax: (44) 1689 860330 > > > E-mail: [EMAIL PROTECTED] > > > > > > ** > > > > > > > > > ---(end of > broadcast)--- > > > TIP 5: Have you checked our extensive FAQ? > > > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] Effective Cache Size
Thanks. Some theoretical questions. The documentation says that Effective Cache Size "sets the optimizer's assumption about the effective size of the disk cache ( that is, the portion of the kernel's disk cache that will be used for PostgreSQL data files ). What then will be the effect of setting this too high? And too low? How does it impact on other applications eg Java ? > -Original Message- > From: scott.marlowe [SMTP:[EMAIL PROTECTED] > Sent: 01 July 2003 03:20 > To: Howard Oblowitz > Cc: [EMAIL PROTECTED] > Subject: Re: FW: [PERFORM] Version 7 question > > The best way to set it is to let the machine run under normal load for a > while, then look at the cache / buffer usage using top (or some other > program, top works fine for this). > > My server with 1.5 gig ram shows 862328K cache right now. So, just divide > > by page size (usually 8192) and I get ~ 100,000 blocks. > > On Tue, 1 Jul 2003, Howard Oblowitz wrote: > > > What would be the best value range for effective_cache_size > > on Postgres 7.3.2, assuming say 1.5 GB of RAM and > > shared_buffers set to 8192, and shmmax set to 750mb? > > > > And what are the most important factors one should take > > into account in determining the value? > > > > > > > > > -Original Message- > > > From: scott.marlowe [SMTP:[EMAIL PROTECTED] > > > Sent: 01 July 2003 02:56 > > > To: Michael Mattox > > > Cc: Hilary Forbes; [EMAIL PROTECTED] > > > Subject: Re: [PERFORM] Version 7 question > > > > > > 8192 is only 64 megs of RAM, not much, but a good number. Keep in > mind > > > that the kernel tends to be better at buffering huge amounts of disk, > > > while postgresql is better left to use buffers that are large enough > for > > > the current working set (i.e. not your whole database, just the > largest > > > amount of data you're slinging about on a regular basis in one query.) > > > > > > On a machine with 1.5 gig of RAM, I've found settings as high as 32768 > > > > (256 megs of ram) to run well, but anything over that doesn't help. > Of > > > course, we don't toss around more than a hundred meg or so at a time. > If > > > > > > our result sets were in the gigabyte range, I'd A: want more memory > and B: > > > > > > Give more of it to postgresql. > > > > > > The original poster was, I believe running 7.0.x, which is way old, so > no, > > > > > > I don't think there was an equivalent of effective_cache_size in that > > > version. Upgrading would be far easier than performance tuning 7.0. > since > > > > > > the query planner was much simpler (i.e. more prone to make bad > decisions) > > > > > > in 7.0. > > > > > > On Tue, 1 Jul 2003, Michael Mattox wrote: > > > > > > > I have my shared buffers at 8192 and my effective cache at 64000 > (which > > > is > > > > 500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs > and > > > > I've been asking my boss for another 512megs for over a month now. > I > > > have > > > > no idea if my buffers are too high/low. > > > > > > > > Michael > > > > > > > > > -Original Message- > > > > > From: [EMAIL PROTECTED] > > > > > [mailto:[EMAIL PROTECTED] Behalf Of Hilary > > > > > Forbes > > > > > Sent: Tuesday, July 01, 2003 2:10 PM > > > > > To: [EMAIL PROTECTED] > > > > > Subject: [PERFORM] Version 7 question > > > > > > > > > > > > > > > I'm just trying to improve performance on version 7 before doing > > > > > some tests and hopefully upgrading to 7.3. > > > > > > > > > > At the moment we have > > > > > B=64 (no of shared buffers) > > > > > N=32 (no of connections) > > > > > in postmaster.opt which I take it is the equivalent of the new > > > > > postgresql.conf file. > > > > > > > > > > From all that is being written about later versions I suspect > > > > > that this is far too low. Would I be fairly safe in making the > > > > > no of shared buffers larger? Also is there an equivalent of > > > > > effective_cache_size that I can set for version 7? > > > > > > > > > > Many thanks in ad
