Thread: Large Tables(>1 Gb)

(I have a 500MHz PentIII, with 256 Mb RAM, UW SCSI, running Linux Kernel
2.2.9, with libc-2.1.2
I am running Postgres 7.0 which I compiled myself.)

So, I created a database, a table, and started dumping data into it.  Then
I added an index on the table.  Life was good.

After a few weeks, my table eclipsed approximately 1Gb, and when I looked
at it in my PG_DATA/database directory, I noticed that there were two
files:  MYTABLE and MYTABLE.1.  I was curious why this happened, but I
figured that Postgres must break up tables over 1Gb into multiple
files.(right?)

Then, while running psql, I did a "select * from MYTABLE;"  Well, psql just
sits there while the hard drive light blinks like crazy, pulling the table
up into memory.  I have 256Mb of RAM, so this takes awhile.  When I start
up "top" and watch my process table, the postgres backend is sucking up the
CPU time pulling the data and the psql frontend is sucking up the memory
accepting the results.

So, I figured that psql must be piling everything up in a "less" like
pager.  So, I kll the current request, do a "\pset pager" and toggle the
pager off.  I re-run the select *, and the same thing happens.

This time however, I let everything run until my memory taken up by the
psql process goes over 256Mb, which means that my system RAM is all used
up.  Then, my whole machine kinda locks up.  My load average hits 5(!) and
psql starts taking up well over 300Mb.  I am also running X.  As best I can
figure, my poor machine is getting hammered by physical memory being
disk-swapped while simultaneously trying to pull up a 1Gb database.  I
barely have enough CPU power left over for me to telnet in from another box
and kill psql!

(1)  I don't know what psql thinks it is doing, or why my kernel is letting
it do it, but...
(2)  I figure I can fix things....so:

I look around at some backend configuration parameters to see if I can get
Postgres to do some neat memory stuff(but later realize that it was the
front-end and not the backend that was eating up memory...I tried pg_dump
on the database/table, and stuff started spooling right away)

Rather than trying to fix the problem, I decided to subvert it by breaking
my table into a bunch of little tables, each one less than my RAM size, so
that I would never dig into SWAP space on a select *....(all of you who are
laugh at me, you can just quit reading right now).  Then I planned to
re-join all of the tables in a VIEW by doing a CREATE VIEW AS SELECT *
UNION SELECT * UNION...etc.  Then I find out that UNIONS and VIEWs aren't
implemented together....(I don't see this explicitly stated on the to-do
list either).

Then I started digging into the source code, trying to see if the query
parser was the reason that this wasn't implemented...perhaps I could help.
I don't quite see where it is.


Anyway, just wanted to see if all my assumptions are correct, or if anyone
has a better explanation for my observation, and/or some solutions.


Fred

On Thu, 29 Jun 2000 Fred_Zellinger@seagate.com wrote:

    This doesn't directly answer your question...but do you actually
have a need to select all of a 1GB table?  I've got about 1.5GB of data in
a table, but I can't think of an application that would need to pull it
all out in one query.

# Date: Thu, 29 Jun 2000 22:26:41 -0500
# From: Fred_Zellinger@seagate.com
# To: pgsql-general@hub.org
# Subject: [GENERAL] Large Tables(>1 Gb)
#
#
# (I have a 500MHz PentIII, with 256 Mb RAM, UW SCSI, running Linux Kernel
# 2.2.9, with libc-2.1.2
# I am running Postgres 7.0 which I compiled myself.)
#
# So, I created a database, a table, and started dumping data into it.  Then
# I added an index on the table.  Life was good.
#
# After a few weeks, my table eclipsed approximately 1Gb, and when I looked
# at it in my PG_DATA/database directory, I noticed that there were two
# files:  MYTABLE and MYTABLE.1.  I was curious why this happened, but I
# figured that Postgres must break up tables over 1Gb into multiple
# files.(right?)
#
# Then, while running psql, I did a "select * from MYTABLE;"  Well, psql just
# sits there while the hard drive light blinks like crazy, pulling the table
# up into memory.  I have 256Mb of RAM, so this takes awhile.  When I start
# up "top" and watch my process table, the postgres backend is sucking up the
# CPU time pulling the data and the psql frontend is sucking up the memory
# accepting the results.
#
# So, I figured that psql must be piling everything up in a "less" like
# pager.  So, I kll the current request, do a "\pset pager" and toggle the
# pager off.  I re-run the select *, and the same thing happens.
#
# This time however, I let everything run until my memory taken up by the
# psql process goes over 256Mb, which means that my system RAM is all used
# up.  Then, my whole machine kinda locks up.  My load average hits 5(!) and
# psql starts taking up well over 300Mb.  I am also running X.  As best I can
# figure, my poor machine is getting hammered by physical memory being
# disk-swapped while simultaneously trying to pull up a 1Gb database.  I
# barely have enough CPU power left over for me to telnet in from another box
# and kill psql!
#
# (1)  I don't know what psql thinks it is doing, or why my kernel is letting
# it do it, but...
# (2)  I figure I can fix things....so:
#
# I look around at some backend configuration parameters to see if I can get
# Postgres to do some neat memory stuff(but later realize that it was the
# front-end and not the backend that was eating up memory...I tried pg_dump
# on the database/table, and stuff started spooling right away)
#
# Rather than trying to fix the problem, I decided to subvert it by breaking
# my table into a bunch of little tables, each one less than my RAM size, so
# that I would never dig into SWAP space on a select *....(all of you who are
# laugh at me, you can just quit reading right now).  Then I planned to
# re-join all of the tables in a VIEW by doing a CREATE VIEW AS SELECT *
# UNION SELECT * UNION...etc.  Then I find out that UNIONS and VIEWs aren't
# implemented together....(I don't see this explicitly stated on the to-do
# list either).
#
# Then I started digging into the source code, trying to see if the query
# parser was the reason that this wasn't implemented...perhaps I could help.
# I don't quite see where it is.
#
#
# Anyway, just wanted to see if all my assumptions are correct, or if anyone
# has a better explanation for my observation, and/or some solutions.
#
#
# Fred
#
#
#
#
#

--
dustin sallings                            The world is watching America,
http://2852210114/~dustin/                 and America is watching TV.

On Thu, 29 Jun 2000 Fred_Zellinger@seagate.com wrote:

> I look around at some backend configuration parameters to see if I can get
> Postgres to do some neat memory stuff(but later realize that it was the
> front-end and not the backend that was eating up memory...I tried pg_dump
> on the database/table, and stuff started spooling right away)

> Rather than trying to fix the problem, I decided to subvert it by breaking
> my table into a bunch of little tables, each one less than my RAM size, so
> that I would never dig into SWAP space on a select *....(all of you who are
> laugh at me, you can just quit reading right now).

*stops laughing* ;-)

> Anyway, just wanted to see if all my assumptions are correct, or if anyone
> has a better explanation for my observation, and/or some solutions.

If you want to SELECT 1GB of data into RAM, you ought to have over 1GB of
RAM, don't you think?

What exactly is the problem you're trying to fix?


- Andrew

Hello,

> After a few weeks, my table eclipsed approximately 1Gb, and when I looked
> at it in my PG_DATA/database directory, I noticed that there were two
> files:  MYTABLE and MYTABLE.1.  I was curious why this happened, but I
> figured that Postgres must break up tables over 1Gb into multiple
> files.(right?)

Yeps.

> Then, while running psql, I did a "select * from MYTABLE;"  Well, psql just
> sits there while the hard drive light blinks like crazy, pulling the table
> up into memory.  I have 256Mb of RAM, so this takes awhile.  When I start
> up "top" and watch my process table, the postgres backend is sucking up the
> CPU time pulling the data and the psql frontend is sucking up the memory
> accepting the results.

It's OK. The problem is that postgres try to combine fukk answer for your
request in memory. And for sure this is a little bit hard for him. You just have not
enough memory for such games...

But if you would like to do such things you can do them. You should consider to use
either of 2 possible solutions:
1. Use cursors. This is the most natural way to do this. You just should create cursor
and then fetch data by some amount of tuples. Something like:
declare my_cursor cursor for select * from big_table;
fetch 1000;
fetch 1000;
close my_cursor;

2. Use limit & offset capability of postgres.

select * from big_table limit 1000 offset 0;
select * from big_table limit 1000 offset 1000;
...

> So, I figured that psql must be piling everything up in a "less" like
> pager.  So, I kll the current request, do a "\pset pager" and toggle the
> pager off.  I re-run the select *, and the same thing happens.
>
> This time however, I let everything run until my memory taken up by the
> psql process goes over 256Mb, which means that my system RAM is all used
> up.  Then, my whole machine kinda locks up.  My load average hits 5(!) and
> psql starts taking up well over 300Mb.  I am also running X.  As best I can
> figure, my poor machine is getting hammered by physical memory being
> disk-swapped while simultaneously trying to pull up a 1Gb database.  I
> barely have enough CPU power left over for me to telnet in from another box
> and kill psql!
>
> (1)  I don't know what psql thinks it is doing, or why my kernel is letting
> it do it, but...
> (2)  I figure I can fix things....so:
>
> I look around at some backend configuration parameters to see if I can get
> Postgres to do some neat memory stuff(but later realize that it was the
> front-end and not the backend that was eating up memory...I tried pg_dump
> on the database/table, and stuff started spooling right away)
>
> Rather than trying to fix the problem, I decided to subvert it by breaking
> my table into a bunch of little tables, each one less than my RAM size, so
> that I would never dig into SWAP space on a select *....(all of you who are
> laugh at me, you can just quit reading right now).  Then I planned to
> re-join all of the tables in a VIEW by doing a CREATE VIEW AS SELECT *
> UNION SELECT * UNION...etc.  Then I find out that UNIONS and VIEWs aren't
> implemented together....(I don't see this explicitly stated on the to-do
> list either).
>
> Then I started digging into the source code, trying to see if the query
> parser was the reason that this wasn't implemented...perhaps I could help.
> I don't quite see where it is.

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

Fred_Zellinger@seagate.com writes:
> After a few weeks, my table eclipsed approximately 1Gb, and when I looked
> at it in my PG_DATA/database directory, I noticed that there were two
> files:  MYTABLE and MYTABLE.1.  I was curious why this happened, but I
> figured that Postgres must break up tables over 1Gb into multiple
> files.(right?)

Check.  It's to work around OSes that don't handle large files.

> Then, while running psql, I did a "select * from MYTABLE;"  Well, psql just
> sits there while the hard drive light blinks like crazy, pulling the table
> up into memory.  I have 256Mb of RAM, so this takes awhile.  When I start
> up "top" and watch my process table, the postgres backend is sucking up the
> CPU time pulling the data and the psql frontend is sucking up the memory
> accepting the results.

Yeah.  libpq has this nifty little API that provides random access to
a query result set --- so it wants to suck the entire result set into
the client application's RAM before it will let the app have any of it.
Actually, there are error-handling reasons for doing it that way too.
But anyway the point is that that client-side API is not well designed
for huge result sets.  It's not a backend problem.

The usual workaround is to use DECLARE CURSOR and FETCH to grab the
result in bite-size chunks, like a few hundred or thousand rows at
a time.

Sooner or later someone will probably extend libpq to offer some kind
of "streaming" API for scanning through large result sets without
buffering them in client RAM.  Doesn't seem to have gotten to the top
of anyone's TODO list yet though... the FETCH solution works well
enough to keep the annoyance level down...

            regards, tom lane

Fred_Zellinger@seagate.com wrote:

> (I have a 500MHz PentIII, with 256 Mb RAM, UW SCSI, running Linux Kernel
> 2.2.9, with libc-2.1.2
> I am running Postgres 7.0 which I compiled myself.)
>
> So, I created a database, a table, and started dumping data into it.  Then
> I added an index on the table.  Life was good.
>
> After a few weeks, my table eclipsed approximately 1Gb, and when I looked
> at it in my PG_DATA/database directory, I noticed that there were two
> files:  MYTABLE and MYTABLE.1.  I was curious why this happened, but I
> figured that Postgres must break up tables over 1Gb into multiple
> files.(right?)
>
> Then, while running psql, I did a "select * from MYTABLE;"  Well, psql just
> sits there while the hard drive light blinks like crazy, pulling the table
> up into memory.  I have 256Mb of RAM, so this takes awhile.  When I start
> up "top" and watch my process table, the postgres backend is sucking up the
> CPU time pulling the data and the psql frontend is sucking up the memory
> accepting the results.
>
> Fred

Okay, I didn't laugh the entire time...

I suggest you take a look at cursors.  I have the same thing.  There are times
I will need to select my entire >2Gig table but instead of doing:

    SELECT * FROM table ;

I do

    DECLARE tmp CURSOR FOR SELECT * FROM table ;

    do {
        FETCH 100 FORWARD FROM tmp ;
     } while there are rows left.

This only pulls 100 (or whatever number you specify) into memory at a time.

Jeff

You could also use LIMIT and OFFSET.. That's what I do (though my database
isn't to a gigabyte yet)..

Maybe using a CURSOR  is better, I'm not sure...

-Mitch

----- Original Message -----
From: Jeffery Collins <collins@onyx-technologies.com>
To: <Fred_Zellinger@seagate.com>
Cc: <pgsql-general@hub.org>
Sent: Friday, June 30, 2000 8:47 AM
Subject: Re: [GENERAL] Large Tables(>1 Gb)


> Fred_Zellinger@seagate.com wrote:
>
> > (I have a 500MHz PentIII, with 256 Mb RAM, UW SCSI, running Linux Kernel
> > 2.2.9, with libc-2.1.2
> > I am running Postgres 7.0 which I compiled myself.)
> >
> > So, I created a database, a table, and started dumping data into it.
Then
> > I added an index on the table.  Life was good.
> >
> > After a few weeks, my table eclipsed approximately 1Gb, and when I
looked
> > at it in my PG_DATA/database directory, I noticed that there were two
> > files:  MYTABLE and MYTABLE.1.  I was curious why this happened, but I
> > figured that Postgres must break up tables over 1Gb into multiple
> > files.(right?)
> >
> > Then, while running psql, I did a "select * from MYTABLE;"  Well, psql
just
> > sits there while the hard drive light blinks like crazy, pulling the
table
> > up into memory.  I have 256Mb of RAM, so this takes awhile.  When I
start
> > up "top" and watch my process table, the postgres backend is sucking up
the
> > CPU time pulling the data and the psql frontend is sucking up the memory
> > accepting the results.
> >
> > Fred
>
> Okay, I didn't laugh the entire time...
>
> I suggest you take a look at cursors.  I have the same thing.  There are
times
> I will need to select my entire >2Gig table but instead of doing:
>
>     SELECT * FROM table ;
>
> I do
>
>     DECLARE tmp CURSOR FOR SELECT * FROM table ;
>
>     do {
>         FETCH 100 FORWARD FROM tmp ;
>      } while there are rows left.
>
> This only pulls 100 (or whatever number you specify) into memory at a
time.
>
> Jeff
>
>
>

we use cursors and they perform well for us for
selects.
our largest table is just over 7.5g containing
38mil+ rows...but we have a lot of tables over
1 gig...

mikeo


At 10:19 AM 6/30/00 -0400, Mitch Vincent wrote:
>You could also use LIMIT and OFFSET.. That's what I do (though my database
>isn't to a gigabyte yet)..
>
>Maybe using a CURSOR  is better, I'm not sure...
>
>-Mitch
>
>----- Original Message -----
>From: Jeffery Collins <collins@onyx-technologies.com>
>To: <Fred_Zellinger@seagate.com>
>Cc: <pgsql-general@hub.org>
>Sent: Friday, June 30, 2000 8:47 AM
>Subject: Re: [GENERAL] Large Tables(>1 Gb)
>
>
>> Fred_Zellinger@seagate.com wrote:
>>
>> > (I have a 500MHz PentIII, with 256 Mb RAM, UW SCSI, running Linux Kernel
>> > 2.2.9, with libc-2.1.2
>> > I am running Postgres 7.0 which I compiled myself.)
>> >
>> > So, I created a database, a table, and started dumping data into it.
>Then
>> > I added an index on the table.  Life was good.
>> >
>> > After a few weeks, my table eclipsed approximately 1Gb, and when I
>looked
>> > at it in my PG_DATA/database directory, I noticed that there were two
>> > files:  MYTABLE and MYTABLE.1.  I was curious why this happened, but I
>> > figured that Postgres must break up tables over 1Gb into multiple
>> > files.(right?)
>> >
>> > Then, while running psql, I did a "select * from MYTABLE;"  Well, psql
>just
>> > sits there while the hard drive light blinks like crazy, pulling the
>table
>> > up into memory.  I have 256Mb of RAM, so this takes awhile.  When I
>start
>> > up "top" and watch my process table, the postgres backend is sucking up
>the
>> > CPU time pulling the data and the psql frontend is sucking up the memory
>> > accepting the results.
>> >
>> > Fred
>>
>> Okay, I didn't laugh the entire time...
>>
>> I suggest you take a look at cursors.  I have the same thing.  There are
>times
>> I will need to select my entire >2Gig table but instead of doing:
>>
>>     SELECT * FROM table ;
>>
>> I do
>>
>>     DECLARE tmp CURSOR FOR SELECT * FROM table ;
>>
>>     do {
>>         FETCH 100 FORWARD FROM tmp ;
>>      } while there are rows left.
>>
>> This only pulls 100 (or whatever number you specify) into memory at a
>time.
>>
>> Jeff
>>
>>
>>
>

Denis Perchine <dyp@perchine.com> writes:
> 2. Use limit & offset capability of postgres.

> select * from big_table limit 1000 offset 0;
> select * from big_table limit 1000 offset 1000;

This is a risky way to do it --- the Postgres optimizer considers
limit/offset when choosing a plan, and is quite capable of choosing
different plans that yield different tuple orderings depending on the
size of the offset+limit.  For a plain SELECT as above you'd probably
be safe enough, but in more complex cases such as having potentially-
indexable WHERE clauses you'll very likely get bitten, unless you have
an ORDER BY clause to guarantee a unique tuple ordering.

Another advantage of FETCH is that you get a consistent result set
even if other backends are modifying the table, since it all happens
within one transaction.

            regards, tom lane

Thanks for all the great responses on this(doing select * from large tables
and hanging psql).

Here is what I have:
--- psql uses libpq which tries to load everything into memory before
spooling it.
--- use cursors to FETCH selected amount of rows and then spool those.
--- use "select * from big_table limit 1000 offset 0;" for simple queries.

Sometimes you want to do a simple select * from mytable just to get a look
at the data, but you don't care which data.

I am about to go take my multiple broken up tables and dump them back into
one table(and then shut off all those BASH shell scripts I wrote which
checked the system date and created new monthly tables if needed...good
scripting practice but a waste of time).

However, there is still something bugging me.  Even though many people
related stories of 7.5 Gb+ Dbs, I still can't make that little voice in me
quit saying "breaking things into smaller chunks means faster work"
theories.

There must exist a relationship between file sizes and DB performance.
This relationship can be broken into 3 parts:
1.  How the hardware is arranged to pull in large files(fragmentation,
partitions, etc)
2.  How the underlying OS deals with large files
3.  How Postgres deals with(or is affected by) large files.

I imagine that the first two are the dominant factors in the relationship,
but does anyone have any experience with how small/removed of a factor the
internals Postgres are?  Are there any internal coding concerns that have
had to deal with this(like the one mentioned about files being split at
about 1Gb)?

(Curious) Fred

Fred_Zellinger@seagate.com wrote:

> However, there is still something bugging me.  Even though many people
> related stories of 7.5 Gb+ Dbs, I still can't make that little voice in me
> quit saying "breaking things into smaller chunks means faster work"
> theories.
>
> There must exist a relationship between file sizes and DB performance.

If your data doesn't completely fit into main memory, at least some of
it will have to be saved off-line somewhere.  Your question is: should
the off-line portion be split into more than one file to speed
performance?

I won't try to be precise here.  There are good textbooks on the subject
if your interested.  I've just been reading one, actually, but it's at
home and I don't remember the name :(  Knuth would of course be good
reading on the subject.

Maybe think of it this way: what's the difference between one file and
two, really?  You've basically just got a bunch of bits on a block
device, either way.  By saving your data to a single file, you have more
control of the data layout, so you can organize it in the manner most
appropriate to your needs.

________________________
Ron Peterson
rpeterson@yellowbank.com

You should probably be looking into cursors if you're attempting to grab
a 1Gb result set, otherwise the system is going to try to pass the entire
result set to the front end in one big lump, which is what you're probably
seeing.

I haven't played with them really, but probably something like...
begin;
declare testcursor cursor for select * from MYTABLE;
fetch 100 in testcursor;
<fetch repeated until you stop getting results>
close testcursor;
end;

might work better.

Stephan Szabo
sszabo@bigpanda.com

On Thu, 29 Jun 2000 Fred_Zellinger@seagate.com wrote:

>
> (I have a 500MHz PentIII, with 256 Mb RAM, UW SCSI, running Linux Kernel
> 2.2.9, with libc-2.1.2
> I am running Postgres 7.0 which I compiled myself.)
>
> So, I created a database, a table, and started dumping data into it.  Then
> I added an index on the table.  Life was good.
>
> After a few weeks, my table eclipsed approximately 1Gb, and when I looked
> at it in my PG_DATA/database directory, I noticed that there were two
> files:  MYTABLE and MYTABLE.1.  I was curious why this happened, but I
> figured that Postgres must break up tables over 1Gb into multiple
> files.(right?)
>
> Then, while running psql, I did a "select * from MYTABLE;"  Well, psql just
> sits there while the hard drive light blinks like crazy, pulling the table
> up into memory.  I have 256Mb of RAM, so this takes awhile.  When I start
> up "top" and watch my process table, the postgres backend is sucking up the
> CPU time pulling the data and the psql frontend is sucking up the memory
> accepting the results.
>
> So, I figured that psql must be piling everything up in a "less" like
> pager.  So, I kll the current request, do a "\pset pager" and toggle the
> pager off.  I re-run the select *, and the same thing happens.
>
> This time however, I let everything run until my memory taken up by the
> psql process goes over 256Mb, which means that my system RAM is all used
> up.  Then, my whole machine kinda locks up.  My load average hits 5(!) and
> psql starts taking up well over 300Mb.  I am also running X.  As best I can
> figure, my poor machine is getting hammered by physical memory being
> disk-swapped while simultaneously trying to pull up a 1Gb database.  I
> barely have enough CPU power left over for me to telnet in from another box
> and kill psql!
>
> (1)  I don't know what psql thinks it is doing, or why my kernel is letting
> it do it, but...
> (2)  I figure I can fix things....so:
>
> I look around at some backend configuration parameters to see if I can get
> Postgres to do some neat memory stuff(but later realize that it was the
> front-end and not the backend that was eating up memory...I tried pg_dump
> on the database/table, and stuff started spooling right away)
>
> Rather than trying to fix the problem, I decided to subvert it by breaking
> my table into a bunch of little tables, each one less than my RAM size, so
> that I would never dig into SWAP space on a select *....(all of you who are
> laugh at me, you can just quit reading right now).  Then I planned to
> re-join all of the tables in a VIEW by doing a CREATE VIEW AS SELECT *
> UNION SELECT * UNION...etc.  Then I find out that UNIONS and VIEWs aren't
> implemented together....(I don't see this explicitly stated on the to-do
> list either).
>
> Then I started digging into the source code, trying to see if the query
> parser was the reason that this wasn't implemented...perhaps I could help.
> I don't quite see where it is.
>
>
> Anyway, just wanted to see if all my assumptions are correct, or if anyone
> has a better explanation for my observation, and/or some solutions.
>
>
> Fred
>
>
>
>