Thread

1. Large Tables(>1 Gb)

   fred_zellinger@seagate.com — 2000-06-30T03:26:41Z

   (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
   
   
   
   
   
   

2. Re: Large Tables(>1 Gb)

   Dustin Sallings <dustin@spy.net> — 2000-06-30T03:39:51Z

   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.
   
   
   

3. Re: Large Tables(>1 Gb)

   Andrew Snow <als@fl.net.au> — 2000-06-30T03:41:03Z

   
   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
   
   
   
   

4. Re: Large Tables(>1 Gb)

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-06-30T03:58:03Z

   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
   > 
   > 
   > 
   > 
   
   
   

5. Re: Large Tables(>1 Gb)

   Denis Perchine <dyp@perchine.com> — 2000-06-30T05:46:16Z

   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
   ----------------------------------
   
   

6. Re: Large Tables(>1 Gb)

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-30T05:50:01Z

   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
   
   

7. Re: Large Tables(>1 Gb)

   Jeffery Collins <collins@onyx-technologies.com> — 2000-06-30T12:47:02Z

   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
   
   
   
   

8. Re: Large Tables(>1 Gb)

   Mitch Vincent <mitch@venux.net> — 2000-06-30T14:19:52Z

   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
   >
   >
   >
   
   
   

9. Re: Large Tables(>1 Gb)

   mikeo <mikeo@spectrumtelecorp.com> — 2000-06-30T14:47:45Z

   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
   >>
   >>
   >>
   >
   
   

10. Re: Large Tables(>1 Gb)

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-30T15:32:16Z

    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