Thread

1. Large DB

   Mooney, Ryan <ryan.mooney@pnl.gov> — 2004-03-31T01:48:14Z

   Hello, 
   
   I have a single table that just went over 234GB in size with about 290M+
   rows.  I think that I'm starting to approach some limits since things
   have gotten quite a bit slower over the last couple days.  The table is
   really simple and I'm mostly doing simple data mining queries like the
   query included below.  These have gone from taking a under a few minutes
   to taking tens of minutes over the last week or so (a query like the
   below would generally return 1-6 million rows from the base table).  The
   queries use the indexes fairly well, although I suspect that the order
   of host/starttime is suboptimal (fewer hosts than starttime, and the
   table is naturally in starttime order).  I'm going to try adding an
   index on just starttime (and later just host) and see if I can tune the
   queries on that more.  I never delete rows from the table, only do
   inserts (up to around 11,000/minute mostly in one big burst every
   minute, this is anticipated to go up some over time).  There are about
   32 processes doing the inserts (on the same machine - yeah I know it'd
   be happier if they moved); I think it might help if there was only one,
   but for architectural reasons that won't happen for a while.
    
   This is on a dual 3Ghz xenon with 4G Ram and an IDE-SCSI raid array
   (ACNC) I'm running RH Fedora with kernel 2.4.22-1.2115.nptlsmp (we'd
   tried FBSD 4/5 early on, but the insert speeds were actually better with
   RH9 by a ~10% or so - this was pre fbsd 5.2, but it's a bit late to
   migrate easily now).
   
   I'm trying to figure out ways to squeak another ounce or two of
   performance out of this machine, I've included the things I've tuned so
   far below.
   
   The query processes are mostly stuck in D state so I expect that I'm
   hitting some hw limitations, but I'm only doing sub 15MB from the disk
   array (from iostat) and I know it can do in the 40-60MB range when we
   tested the raw speed, and only 400 or so tps which is also well under
   the arrays limits so I suspect that its thrashing a bit, this is also
   indicated by the contrast between rrqm/s (read requests merged per
   second) which is pushing 2000 and the actual r/s (read requests that
   were issued to the device) at around 400 or so (same as tps).  I suspect
   that a lot of the time is spent reading indexes, so a "better" indexing
   scheme may be my best bet.
   
   Estimating the table size
   -------------------------
   
   stats=> select relfilenode,relname from pg_class where
   relfilenode=37057796;
    relfilenode | relname
   -------------+---------
       37057796 | tp3
   
   du -sc 37057796* | grep total
   234002372       total
   
   However the two indexes are also - large (which may be part of the
   problem, which is why I'm trying just starttime for an index; They are
   currently in the 140-150G range).
   
   The query optimizer thinks I have ~ 290M rows (I'm not actually doing a
   real select count since the last time I did that was around 10M rows or
   so and it took a long while, I don't want to wait days :).
   ------------------------------
   stats=> explain select count(*) from tp3;
                                 QUERY PLAN
   -----------------------------------------------------------------------
    Aggregate  (cost=7632998.20..7632998.20 rows=1 width=0)
      ->  Seq Scan on tp3  (cost=0.00..6906493.16 rows=290602016 width=0)
   (2 rows)
   
   Table def
   ----------------------------
   stats=> \d tp3
                     Table "public.tp3"
      Column    |            Type             | Modifiers
   -------------+-----------------------------+-----------
    host        | character(4)                |
    point       | character varying(64)       |
    type        | character(1)                |
    cooked      | character(1)                |
    starttime   | timestamp without time zone |
    intervallen | interval                    |
    arrivetime  | timestamp without time zone |
    pvalue      | numeric                     |
   Indexes:
       "tp3_host_starttime" btree (host, starttime, cooked)
       "tp3_point_starttime" btree (point, starttime, cooked)
   
   
   Sample data mining query:
   ----------------------------
   select point, avg(pvalue) as avg from tp3 where host in ('node', 'node',
   ....) and starttime between 'timestamp' and 'timestamp' group by point
   
   Tuning done so far:
   ----------------------------
   $ cat /etc/sysctl.conf
   
   kernel.shmall=805306368
   kernel.shmmax=805306368
   
   $ egrep -v "^#|^$" postgresql.conf
   
   shared_buffers = 60800
   sort_mem = 1286720              # min 64, size in KB
   vacuum_mem = 102400             # min 1024, size in KB
   fsync=false   # Play fast and loose - whee
   max_files_per_process = 1000
   wal_buffers = 16
   checkpoint_segments = 20        
   checkpoint_timeout = 100
   effective_cache_size = 160000
   
   

2. Re: Large DB

   Ericson Smith <eric@did-it.com> — 2004-03-31T06:21:47Z

   The issue here might be just organizing the data differently. Or getting 
   an Opteron server with 16GB RAM :-) Based on the strength of the 
   developers recommendations in this newsgroup, we recently upgraded to a 
   dual Opteron 2GHZ with 16GB Ram and 15K hard drives. We set 
   shared_buffers to 40,000 (just about 320MB Ram), and the difference is 
   amazing. Just having the OS handle the caching has made all the 
   difference. You can actually see lots of blocks getting cached by the 
   OS. (RH Linux Enterprise in our case). In most cases, tables with 
   millions of records would get entirely cached in RAM, and there would be 
   no disk access whatsoever for selects in a few minutes.
   
   Based on the queries you run, is it possible to split up the schema into 
   different tables? Are the differences between timestamps in the sample 
   query usually small? We had a similar problem, although with a slightly 
   smaller data set -- but one that was going to keep growing.
   
   Our questions were: how could we scale? What about vacuuming our tables, 
   running analyze in a decent time? backing up? and so on. We found that 
   most of the queries we wanted were in the domain of a day. So we 
   actually split up that giant table and made one for each day. We could 
   have done it one for each week as well, but the daily tables worked well 
   for us. Sure, its a bit more work getting data over a long time period, 
   but those common queries were a cinch.
   
   We've also seen that in cases were we have to dump in thousands of 
   records every few minutes that select queries respond remarkedly faster 
   when frequent (one or two every hour) ANALYZE's are done even on those 
   daily tables which contain just a few mil records each.
   
   Tweaking the hardware IMHO would probably take you just a little 
   further, but you gotta think about what your response times will be in 
   another month based on your growth now. Can your schema stand it then?
   
   - Ericson Smith
   
   Mooney, Ryan wrote:
   
   >Hello, 
   >
   >I have a single table that just went over 234GB in size with about 290M+
   >rows.  I think that I'm starting to approach some limits since things
   >have gotten quite a bit slower over the last couple days.  The table is
   >really simple and I'm mostly doing simple data mining queries like the
   >query included below.  These have gone from taking a under a few minutes
   >to taking tens of minutes over the last week or so (a query like the
   >below would generally return 1-6 million rows from the base table).  The
   >queries use the indexes fairly well, although I suspect that the order
   >of host/starttime is suboptimal (fewer hosts than starttime, and the
   >table is naturally in starttime order).  I'm going to try adding an
   >index on just starttime (and later just host) and see if I can tune the
   >queries on that more.  I never delete rows from the table, only do
   >inserts (up to around 11,000/minute mostly in one big burst every
   >minute, this is anticipated to go up some over time).  There are about
   >32 processes doing the inserts (on the same machine - yeah I know it'd
   >be happier if they moved); I think it might help if there was only one,
   >but for architectural reasons that won't happen for a while.
   > 
   >This is on a dual 3Ghz xenon with 4G Ram and an IDE-SCSI raid array
   >(ACNC) I'm running RH Fedora with kernel 2.4.22-1.2115.nptlsmp (we'd
   >tried FBSD 4/5 early on, but the insert speeds were actually better with
   >RH9 by a ~10% or so - this was pre fbsd 5.2, but it's a bit late to
   >migrate easily now).
   >
   >I'm trying to figure out ways to squeak another ounce or two of
   >performance out of this machine, I've included the things I've tuned so
   >far below.
   >
   >The query processes are mostly stuck in D state so I expect that I'm
   >hitting some hw limitations, but I'm only doing sub 15MB from the disk
   >array (from iostat) and I know it can do in the 40-60MB range when we
   >tested the raw speed, and only 400 or so tps which is also well under
   >the arrays limits so I suspect that its thrashing a bit, this is also
   >indicated by the contrast between rrqm/s (read requests merged per
   >second) which is pushing 2000 and the actual r/s (read requests that
   >were issued to the device) at around 400 or so (same as tps).  I suspect
   >that a lot of the time is spent reading indexes, so a "better" indexing
   >scheme may be my best bet.
   >
   >Estimating the table size
   >-------------------------
   >
   >stats=> select relfilenode,relname from pg_class where
   >relfilenode=37057796;
   > relfilenode | relname
   >-------------+---------
   >    37057796 | tp3
   >
   >du -sc 37057796* | grep total
   >234002372       total
   >
   >However the two indexes are also - large (which may be part of the
   >problem, which is why I'm trying just starttime for an index; They are
   >currently in the 140-150G range).
   >
   >The query optimizer thinks I have ~ 290M rows (I'm not actually doing a
   >real select count since the last time I did that was around 10M rows or
   >so and it took a long while, I don't want to wait days :).
   >------------------------------
   >stats=> explain select count(*) from tp3;
   >                              QUERY PLAN
   >-----------------------------------------------------------------------
   > Aggregate  (cost=7632998.20..7632998.20 rows=1 width=0)
   >   ->  Seq Scan on tp3  (cost=0.00..6906493.16 rows=290602016 width=0)
   >(2 rows)
   >
   >Table def
   >----------------------------
   >stats=> \d tp3
   >                  Table "public.tp3"
   >   Column    |            Type             | Modifiers
   >-------------+-----------------------------+-----------
   > host        | character(4)                |
   > point       | character varying(64)       |
   > type        | character(1)                |
   > cooked      | character(1)                |
   > starttime   | timestamp without time zone |
   > intervallen | interval                    |
   > arrivetime  | timestamp without time zone |
   > pvalue      | numeric                     |
   >Indexes:
   >    "tp3_host_starttime" btree (host, starttime, cooked)
   >    "tp3_point_starttime" btree (point, starttime, cooked)
   >
   >
   >Sample data mining query:
   >----------------------------
   >select point, avg(pvalue) as avg from tp3 where host in ('node', 'node',
   >....) and starttime between 'timestamp' and 'timestamp' group by point
   >
   >Tuning done so far:
   >----------------------------
   >$ cat /etc/sysctl.conf
   >
   >kernel.shmall=805306368
   >kernel.shmmax=805306368
   >
   >$ egrep -v "^#|^$" postgresql.conf
   >
   >shared_buffers = 60800
   >sort_mem = 1286720              # min 64, size in KB
   >vacuum_mem = 102400             # min 1024, size in KB
   >fsync=false   # Play fast and loose - whee
   >max_files_per_process = 1000
   >wal_buffers = 16
   >checkpoint_segments = 20        
   >checkpoint_timeout = 100
   >effective_cache_size = 160000
   >
   >---------------------------(end of broadcast)---------------------------
   >TIP 3: if posting/reading through Usenet, please send an appropriate
   >      subscribe-nomail command to majordomo@postgresql.org so that your
   >      message can get through to the mailing list cleanly
   >  
   >
   
   
   

3. Re: Large DB

   Manfred Koizar <mkoi-pg@aon.at> — 2004-03-31T09:17:35Z

   On Tue, 30 Mar 2004 17:48:14 -0800, "Mooney, Ryan" <ryan.mooney@pnl.gov>
   wrote:
   >I have a single table that just went over 234GB in size with about 290M+
   >rows.
   
   That would mean ~ 800 bytes/row which, given your schema, is hard to
   believe unless there are lots of dead tuples lying around.
   
   >queries use the indexes fairly well, although I suspect that the order
   >of host/starttime is suboptimal (fewer hosts than starttime, and the
   >table is naturally in starttime order).  I'm going to try adding an
   >index on just starttime (and later just host) and see if I can tune the
   >queries on that more.
   
   Yes, if you are ready to switch OS for a 10% performance gain, getting
   your indices right should be no question.
   
   >  I never delete rows from the table, only do
   >inserts (up to around 11,000/minute mostly in one big burst every
   >minute, this is anticipated to go up some over time).
   
   How often do you ANALYSE?
   
   Have there been DELETEs or UPDATEs or aborted transactions in the past?
   Did you VACUUM or VACUUM FULL since then?
   
   > I'm only doing sub 15MB from the disk
   >array (from iostat) and I know it can do in the 40-60MB range when we
   >tested the raw speed,
   
   Sounds plausible for nonsequential I/O.
   
   >However the two indexes are also - large (which may be part of the
   >problem, which is why I'm trying just starttime for an index; They are
   >currently in the 140-150G range).
   
   This would be extreme index bloat which is only possible after massive
   DELETEs/UPDATEs.
   
   >stats=> explain select count(*) from tp3;
   >   ->  Seq Scan on tp3  (cost=0.00..6906493.16 rows=290602016 width=0)
   
   The planner thinks that the table size is 4M pages, 32GB.  The average
   tuple size of ~110 bytes (including tuple header) suits your schema
   quite nicely.
   
   >                  Table "public.tp3"
   >   Column    |            Type             | Modifiers
   >-------------+-----------------------------+-----------
   > host        | character(4)                |
   > point       | character varying(64)       |
   > type        | character(1)                |
   > cooked      | character(1)                |
   > starttime   | timestamp without time zone |
   > intervallen | interval                    |
   > arrivetime  | timestamp without time zone |
   > pvalue      | numeric                     |
   >Indexes:
   >    "tp3_host_starttime" btree (host, starttime, cooked)
   >    "tp3_point_starttime" btree (point, starttime, cooked)
   
   In my experience any reduction in average tuple size results directly in
   a proportional increase of throughput for large tables.  So here are
   some random thoughts:
   
   You said there are only a few hosts.  So moving the hosts into a
   separate table with an integer primary key would save 4 bytes per row.
   
   Datatype "char" (with quotes) needs only 1 byte, char(1) needs 5 bytes,
   both before padding.  Changing type and cooked from char(1) to "char"
   would save 12 bytes.
   
   And if you want to push it, you change hostid to smallint and rearrange
   the fields, saving 4 more padding bytes:
     hostid      | smallint
     type        | "char"
     cooked      | "char"
   
   What about point?  If there is a known small number of different values,
   move it into its own table.
   
   I'm not sure about the storage needs of numeric, might be at least 8
   bytes.  Consider using bigint. Someone please correct me if I'm wrong.
   
   Did you CREATE TABLE tp3 (...) WITHOUT OIDS?
   
   >Sample data mining query:
   >----------------------------
   >select point, avg(pvalue) as avg from tp3 where host in ('node', 'node',
   >....) and starttime between 'timestamp' and 'timestamp' group by point
   
   Show us EXPLAIN ANALYSE, please.
   
   >shared_buffers = 60800
   
   Looks a bit large to me.  But if your tests have shown it to be the best
   value, it should be ok.
   
   >sort_mem = 1286720              # min 64, size in KB
   
   This is more than 1GB, I think this is too high.
   
   >fsync=false   # Play fast and loose - whee
   
   How much did this help?
   
   >effective_cache_size = 160000
   
   Try more, say 320000 or even 400000.
   
   Servus
    Manfred