Thread: Expectations of MEM requirements for a DB with large tables.

Hello to all,

I am new to the PostgreSQL world, as well as the Linux world... kind of a
double whammy...

Anyway, I crashed my system the other day when I did a "select *" from one
of my large tables (about 5.5gb in size). Now this is not something that
will normally happen, as I would normally have some criteria to reduce the
output size, but it got me thinking...

Does anyone know what the ratio of data output size (say from a select) to
the amount of
RAM used is?

Hopefully the answer is not 1 to 1... as I would need to buy ALOT of RAM...

Other relevant info, RedHat Linux 2.2.17 kernel, PostgreSQL 7.0.2.

Thank you in advance for any and all help.

Note: I posted this to "Admin" also, but I'm not quite sure if it's really a
"installation" question, so I'm posting it here also.

-Michael Miyabara-McCaskey

On Sun, Nov 05, 2000 at 09:17:52PM -0800, Michael Miyabara-McCaskey wrote:
> Anyway, I crashed my system the other day when I did a "select *" from one
> of my large tables (about 5.5gb in size). Now this is not something that
> will normally happen, as I would normally have some criteria to reduce the
> output size, but it got me thinking...
>
> Does anyone know what the ratio of data output size (say from a select) to
> the amount of RAM used is?

You are really asking two questions:  how much memory does the back end
take to execute that query, and how much memory does the front end
(psql, I assume) take to receive the response.

To answer the first, the back-ends allocate a fixed pool of buffers when
they start up, and never use more RAM than is in that pool.  If they
need more temporary space (ie for sorting), they will create temporary
files as necessary.

To answer the second, if you do a plain "SELECT *", it will buffer the
entire response set into RAM before printing anything out.  If you have
more than a trivial number of records to fetch from the database (and
5.5GB is certainly more than trivial), use a cursor and only fetch a few
hundred at a time.
--
Bruce Guenter <bruceg@em.ca>                       http://em.ca/~bruceg/

 Mike,

> Anyway, I crashed  my system the other  day when I did a  "select *" from
> one
> of my large tables (about 5.5gb in size).

 Well.... It  takes abit more than  that to actually crash  the system. Can
you  give  more details?  What  _exactly_  happened?  Did it  hang?  Kernel
panicked? Something else.

> Does anyone know what  the ratio of data output size  (say from a select)
> to
> the amount of
> RAM used is?

 It simply can't be  other than 1 : 1. Well, in a  sense. Did you use psql?
Other app? If the  latter, what interface: API, DBI, other?  Was it a local
or remote request?

> Other relevant info, RedHat Linux 2.2.17 kernel, PostgreSQL 7.0.2.

 Is this a  genuine kernel, or the  one shipped by RH? Did  you build PGSQL
from sources or  was it a pre-built  binary? How much RAM do  you have, how
fast and big is your swap? Swap settings? Other processes running?
 As you see, there  are more questions than answers at  this stage. Tell us
more. Tuning the system has never been easy.

Bruce,

Your assumptions were absolutely on target.

I appreciate the fact that you de-coupled my question as well.

As I was in fact using "psql", this certainly explains my system dropping to
it's knees...

Out of curiosity, if I were using something else besides "psql" would this
have still been a problem?  Or is pgsql uncommon in this respect?

-Michael


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruce Guenter
> Sent: Sunday, November 05, 2000 9:34 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Expectations of MEM requirements for a DB with
> large tables.
>
>
> On Sun, Nov 05, 2000 at 09:17:52PM -0800, Michael
> Miyabara-McCaskey wrote:
> > Anyway, I crashed my system the other day when I did a
> "select *" from one
> > of my large tables (about 5.5gb in size). Now this is not
> something that
> > will normally happen, as I would normally have some
> criteria to reduce the
> > output size, but it got me thinking...
> >
> > Does anyone know what the ratio of data output size (say
> from a select) to
> > the amount of RAM used is?
>
> You are really asking two questions:  how much memory does
> the back end
> take to execute that query, and how much memory does the front end
> (psql, I assume) take to receive the response.
>
> To answer the first, the back-ends allocate a fixed pool of
> buffers when
> they start up, and never use more RAM than is in that pool.  If they
> need more temporary space (ie for sorting), they will create temporary
> files as necessary.
>
> To answer the second, if you do a plain "SELECT *", it will buffer the
> entire response set into RAM before printing anything out.
> If you have
> more than a trivial number of records to fetch from the database (and
> 5.5GB is certainly more than trivial), use a cursor and only
> fetch a few
> hundred at a time.
> --
> Bruce Guenter <bruceg@em.ca>
http://em.ca/~bruceg/

Kuroi,

Thank you for the response.

I was not specifically thinking of diagnosing my crash, but if you are
curious...

 - about a 1000 error messgaes saying that kswapd could not do something
relating to allocating memory.

 - after stopping the "psql" job, it still took about 20 minutes before my
Linux OS recovered enough to reboot.

 - yep, used "psql", plan on using Perl-DBI soon... I guess from your
response I better make sure that whatever I use never does a full blown
'select *' on that table.

 - 2.2.17 from kernel.org, w/RAID patch.

 - PostgreSQL 7.0.2 was installed via the RPM from RedHat.

 - 98MB of RAM (this is a simple test system only)

 - 256MB of SWAP on a Linux SWAP Partition.

 - SWAP settings? Hmm... sorry this is where my novice level shows its
colors, didn't know I could modify it.

 - Other processes, sure tons of them...

Anyway, based upon your response, I understand that the 1 to 1 ratio is what
crashed my system.

Hmm, now you have me wondering though... I recall reading something that
mentioned that data storage size in PostgreSQL is about 6 times the size of
standard text, so, if my table size is about 5.5GB on the disk does this
mean I really could only need about well say... 5.5GB / 6 = 936MB of RAM?

Any idea?

-Michael Miyabara-McCaskey


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of KuroiNeko
> Sent: Sunday, November 05, 2000 9:54 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Expectations of MEM requirements for a DB with
> large tables.
>
>
>
>  Mike,
>
> > Anyway, I crashed  my system the other  day when I did a
> "select *" from
> > one
> > of my large tables (about 5.5gb in size).
>
>  Well.... It  takes abit more than  that to actually crash
> the system. Can
> you  give  more details?  What  _exactly_  happened?  Did it
> hang?  Kernel
> panicked? Something else.
>
> > Does anyone know what  the ratio of data output size  (say
> from a select)
> > to
> > the amount of
> > RAM used is?
>
>  It simply can't be  other than 1 : 1. Well, in a  sense. Did
> you use psql?
> Other app? If the  latter, what interface: API, DBI, other?
> Was it a local
> or remote request?
>
> > Other relevant info, RedHat Linux 2.2.17 kernel, PostgreSQL 7.0.2.
>
>  Is this a  genuine kernel, or the  one shipped by RH? Did
> you build PGSQL
> from sources or  was it a pre-built  binary? How much RAM do
> you have, how
> fast and big is your swap? Swap settings? Other processes running?
>  As you see, there  are more questions than answers at  this
> stage. Tell us
> more. Tuning the system has never been easy.
>

On Sun, Nov 05, 2000 at 10:41:12PM -0800, Michael Miyabara-McCaskey wrote:
> Out of curiosity, if I were using something else besides "psql" would this
> have still been a problem?

Yes.  The library interface in libpq will pull the entire record set
into memory unless you use cursors.
--
Bruce Guenter <bruceg@em.ca>                       http://em.ca/~bruceg/