Re: Problems with memory

From: Shane Ambler <pgsql(at)Sheeky(dot)Biz>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Pau Marc Munoz Torres <paumarc(at)gmail(dot)com>, Merlin Moncure <mmoncure(at)gmail(dot)com>, pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Problems with memory
Date: 2008-05-08 16:02:58
Message-ID: 48232432.9080702@Sheeky.Biz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Richard Huxton wrote:
> Pau Marc Munoz Torres wrote:
>> Hi

To your initial question all configurable options can be found and
adjusted in the postgresql.conf which can be found in the top of your
data dir.

You can find more detail on what the options are for at
http://www.postgresql.org/docs/8.3/interactive/runtime-config.html

Although I doubt it will fix your problem you will most likely want to
tune them to get the best performance from your db once it goes live.

>> maybe i should give you some more explanations of my problem.
>>
>> The reason for which i think that postgresql run out of memory is that: I
>> have a relation with 6 fields, 29 indexes and 32000 registers, the
>> registers

So how much ram does your machine have? have you looked at how much
postgres is using while it is running the queries?

What CPU's and disks/raid controller?

>> when i do a query as:
>>
>> select * from precalc where idr(p1, p4, p6, p7, p9,
>> 'HLA-DRB1*0101')<-2; it
>> works and return 128030 registers
>>
>> if i do
>>
>> select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')>-2;
>> 3071970 registers, it don't work
>> ERROR: relation "pssms" does not exist
>> CONTEXT: SQL statement "select score from PSSMS where AA= $1 and
>> POS=1 and
>> MOLEC= $2 "
>> PL/pgSQL function "idr" line 11 at SQL statement

Start with the idr function - this is where the error comes from.
The error doesn't mention out of memory.

>
>> if i ask for explanation for both queries works:

explain will show what the planner expects to do. explain analyse will
actually run through the query and give exact row counts returned etc.
and will hit the error that you get when running the query.

>>
>> If for that reason that i think that my machine runs out of memory, by
>> the
>> way, this is not the biggest table that i have others have more than
>> 503000000 registers, so if I try to do a cross select between tables it
>> could be worse.

You may hear from people on this list that have db's storing many
TeraBytes of data.

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Toby Chavez 2008-05-08 17:11:26 Re: Using a composite SQL type in C
Previous Message Peter Eisentraut 2008-05-08 15:26:17 Re: now i'm really confused. insert/update does autocast, where sometimes.