Обсуждение: Problems with memory

Hi

 I'm setting up a big database , and when i say big, i mean BIG, the problem with this is that some times, when a do a query the database run out of memory, so I really need to increase the amount of memory reserved to postgress almost 10x, could anyone tell me how can i do that? i mean, what configuration file and variables do i have to modify?

Thanks

pau

--
Pau Marc Muñoz Torres

Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)

telèfon: 93 5812807
Email : paumarc.munoz@bioinf.uab.cat

Pau Marc Munoz Torres wrote:
> Hi
>
>  I'm setting up a big database , and when i say big, i mean BIG,

I have to say, I've seen more accurate stats given. How many Terabytes
are we talking about?

 > the problem
> with this is that some times, when a do a query the database run out of
> memory,

What precisely do you mean by "run out of memory" - is this work-mem or
shared-mem? How do you know this is what's happening?

 > so I really need to increase the amount of memory reserved to
> postgress almost 10x, could anyone tell me how can i do that? i mean, what
> configuration file and variables do i have to modify?

What memory are you trying to reserve for PostgreSQL? Is it shared-mem
you're talking about? If you let us know where you found the manuals
confusing, we can try to improve them.

--
   Richard Huxton
   Archonet Ltd

On Wed, May 7, 2008 at 11:55 AM, Pau Marc Munoz Torres
<paumarc@gmail.com> wrote:
> Hi
>
>  I'm setting up a big database , and when i say big, i mean BIG, the problem
> with this is that some times, when a do a query the database run out of
> memory, so I really need to increase the amount of memory reserved to
> postgress almost 10x, could anyone tell me how can i do that? i mean, what
> configuration file and variables do i have to modify?

All available memory of the machine is automatically reserved for the
database by the operating system, more or less.   At most you can
reserve memory for certain particular operations of the database at
the expense of others.

merlin

Hi

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 where made up using a pgsql language to save disk space, and they "work" (see the table schema under those lines)



 Column |     Type     | Modifiers
--------+--------------+-----------
 id     | integer      |
 p1     | character(1) |
 p4     | character(1) |
 p6     | character(1) |
 p7     | character(1) |
 p9     | character(1) |
Indexes:
    "h2iab" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying))
    "h2iad" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character varying))
    "h2iak" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character varying))
    "h2ied" btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character varying))
    "hladqa10501" btree (idr(p1, p4, p6, p7, p9, 'HLA-DQA1*0501'::character varying))
    "hladqb10201" btree (idr(p1, p4, p6, p7, p9, 'HLA-DQB1*0201'::character varying))
    "hladr" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR'::character varying))
    "hladr1" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR1'::character varying))
    "hladr13" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR13'::character varying))
    "hladr3" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR3'::character varying))
    "hladr7" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR7'::character varying))
    "hladrb10101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying))
    "hladrb10102" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0102'::character varying))
    "hladrb10301" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0301'::character varying))
    "hladrb10302" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0302'::character varying))
    "hladrb10401" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0401'::character varying))
    "hladrb10402" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0402'::character varying))
    "hladrb10701" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0701'::character varying))
    "hladrb10802" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0802'::character varying))
    "hladrb10901" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0901'::character varying))
    "hladrb11101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1101'::character varying))
    "hladrb11102" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1102'::character varying))
    "hladrb11103" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1103'::character varying))
    "hladrb11104" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1104'::character varying))
    "hladrb11301" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1301'::character varying))
    "hladrb11302" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1302'::character varying))
    "hladrb11501" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1501'::character varying))
    "hladrb40101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB4*0101'::character varying))
    "hladrb50101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101'::character varying))




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

if i ask for explanation for both queries works:

mhc2db=> explain select count(*) from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')<-2;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=66188.88..66188.89 rows=1 width=0)
   ->  Bitmap Heap Scan on precalc  (cost=17615.20..63522.21 rows=1066667 width=0)
         Recheck Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) < -2::double precision)
         ->  Bitmap Index Scan on hladrb10101  (cost=0.00..17348.54 rows=1066667 width=0)
               Index Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) < -2::double precision)
(5 rows)

mhc2db=> explain select count(*) from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')>-2;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=66188.88..66188.89 rows=1 width=0)
   ->  Bitmap Heap Scan on precalc  (cost=17615.20..63522.21 rows=1066667 width=0)
         Recheck Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) > -2::double precision)
         ->  Bitmap Index Scan on hladrb10101  (cost=0.00..17348.54 rows=1066667 width=0)
               Index Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) > -2::double precision)
(5 rows)

and the index used are the correct ones

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.


Any idea?
 
Thanks

pau

--
Pau Marc Muñoz Torres

Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)

telèfon: 93 5812807
Email : paumarc.munoz@bioinf.uab.cat

Pau Marc Munoz Torres wrote:
> Hi
>
> 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
> where made up using a pgsql language to save disk space, and they "work"
> (see the table schema under those lines)

You have 29 indexes on a table with 6 columns?
But only 32000 rows?

>  Column |     Type     | Modifiers
> --------+--------------+-----------
>  id     | integer      |
>  p1     | character(1) |
>  p4     | character(1) |
>  p6     | character(1) |
>  p7     | character(1) |
>  p9     | character(1) |
> Indexes:
>     "h2iab" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying))
>     "h2iad" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character varying))
>     "h2iak" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character varying))
>     "h2ied" btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character varying))
etc.

OK, so you have 29 different functional indexes which use your columns
and then a fixed parameter. Looks odd to me, but I suppose you might
have good reason.

Oh - and it's not necessarily saving you any disk space - the index
values need to be stored.

> 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

Do you have a table/view called pssms in your search-path? Because
that's what the error is about. Might it be a case-sensitive issue - do
you have a table called PSSMS instead?

> if i ask for explanation for both queries works:
>
> mhc2db=> explain select count(*) from precalc where idr(p1, p4, p6, p7, p9,
> 'HLA-DRB1*0101')<-2;
[snip]

> mhc2db=> explain select count(*) from precalc where idr(p1, p4, p6, p7, p9,
> 'HLA-DRB1*0101')>-2;
[snip]

> and the index used are the correct ones
>
> 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.

For what reason? I still don't see any out-of-memory errors.

--
   Richard Huxton
   Archonet Ltd

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