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/

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

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/