Обсуждение: 4G row table?

[linux, 700MHz athlon, 512MB RAM, 700GB 10kRPM SCSI HW RAID, postgresql 7.2]
We're setting up a DB of IC test data, which is very simple and regular, but large.
One project (we get three or four per year) has ~4 giga bits, each specified by
a few small integer values, e.g.:
 Name       Type Values
 ----------------------
 wafer      int     1-50
 die_row    int  2-7
 die_col    int  2-7
 testtype   string (~10 different short strings)
 vdd        int  0-25
 bit_col    int  0-127
 bit_row    int  0-511
 value      bit  0 or 1

with 4 billion(4e9) rows.  I would guess to make wafer, die_row, etc. be of
type "char", probably testtype a char too with a separate testtype lookup table.
Even so, this will be a huge table.

Questions: How much overhead will there be in the table in addition to the
9 bytes of data I see?  How big will the primary index on the first seven columns
be?  Will this schema work at all?

Of course, we could pack 128 bits into an 8 byte "text" field (or should we use bit(128)?),
but lose some ease of use, especially for naive (but important) users.

Comments, suggestions?

-- George

--
 I cannot think why the whole bed of the ocean is
 not one solid mass of oysters, so prolific they seem. Ah,
 I am wandering! Strange how the brain controls the brain!
    -- Sherlock Holmes in "The Dying Detective"

On Thu, 19 Dec 2002 14:10:58 -0500, george young <gry@ll.mit.edu>
wrote:
>with 4 billion(4e9) rows.  I would guess to make wafer, die_row, etc. be of
>type "char", probably testtype a char too with a separate testtype lookup table.
>Even so, this will be a huge table.

Don't know if you can store 0-127 in a "char" column ... Anyway, it
doesn't matter, if it does not cause the tuple size to cross a 4 byte
boundary, because the tuple size will be rounded up to a multiple of
4.

>Questions: How much overhead will there be in the table in addition to the
>9 bytes of data I see?

There is a page header (ca. 20 bytes) per page (8K by default).  Then
you have a tuple header and 4 bytes ItemIdData per tuple.

PG 7.2: Without NULLs a tuple header is 32 bytes, add 4 bytes for each
tuple containing at least one NULL column.

PG 7.3: 24 bytes tuple header (with and without NULLs, because you
have only 8 columns).

>How big will the primary index on the first seven columns be?

Don't know offhand.  No time now to dig it out.  Will answer tomorrow,
if nobody else jumps in ...

Servus
 Manfred

George,

> [linux, 700MHz athlon, 512MB RAM, 700GB 10kRPM SCSI HW RAID, postgresql 7.2]

What kind of RAID?  How many drives?  Will you be updating the data
frequently, or mostly just running reports on it?

With 4G rows, you will have *heavy* disk access, so the configuration and
quality of your disk array is a big concern.   You also might think about
upping th ememory if you can.

> We're setting up a DB of IC test data, which is very simple and regular, but
large.
> One project (we get three or four per year) has ~4 giga bits, each specified
by
> a few small integer values, e.g.:
>  Name       Type Values
>  ----------------------
>  wafer      int     1-50
>  die_row    int  2-7
>  die_col    int  2-7
>  testtype   string (~10 different short strings)
>  vdd        int  0-25
>  bit_col    int  0-127
>  bit_row    int  0-511
>  value      bit  0 or 1
>
> with 4 billion(4e9) rows.  I would guess to make wafer, die_row, etc. be of
> type "char", probably testtype a char too with a separate testtype lookup
table.
> Even so, this will be a huge table.

1. Use INT2 and not INT for the INT values above.  If you can hire a
PostgreSQL hacker, have them design a new data type for you, an unsigned INT1
which will cut your storage space even further.

2. Do not use CHAR for wafer & die-row.  CHAR requries min 3bytes storage;
INT2 is only 2 bytes.

3. If you can use a lookup table for testtype, make it another INT2 and create
a numeric key for the lookup table.

> Questions: How much overhead will there be in the table in addition to the
> 9 bytes of data I see?

There's more than 9 bytes in the above.  Count again.

> How big will the primary index on the first seven columns
> be?  Will this schema work at all?

As large as the 7 columns themselves, plus a little more.   I suggest creating
a surrogate key as an int8 sequence to refer to most rows.

> Of course, we could pack 128 bits into an 8 byte "text" field (or should we
use bit(128)?),
> but lose some ease of use, especially for naive (but important) users.

This is also unlikely to be more efficient due to the translation<->conversion
process requried to access the data when you query.

> Comments, suggestions?

Unless you have a *really* good RAID array, expect slow performance on this
hardware platform.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Josh:

Why do you say to expect slow performance on this hardware?   Is there
something specific about the configuration that worries you?  Or, just
lots of data in the database, so the data will be on disk and not in the
cache (system or postgresql)?

What do you classify as *slow*?  Obviously, he is dependent on the I/O
channel given the size of the tables.  So, good indexing will be
required to help on the queries.  No comments on the commit rate for
this data (I am guessing that it is slow, given the description of the
database), so that may or may not be an issue.

Depending on the type of queries, perhaps clustering will help, along
with some good partitioning indexes.

I just don't see the slow in the hardware.  Of course, if he is
targeting lots of concurrent queries, better add some additional
processors, or better yet, use ERSERVER and replicate the data to a farm
of machines.  [To avoid the I/O bottleneck of lots of concurrent queries
against these large tables].

I guess there are a lot of assumptions on the data's use to decide if
the hardware is adequate or not :-)

Charlie



Josh Berkus wrote:

>George,
>
>
>>[linux, 700MHz athlon, 512MB RAM, 700GB 10kRPM SCSI HW RAID, postgresql 7.2]
>>
>>
>
>What kind of RAID?  How many drives?  Will you be updating the data
>frequently, or mostly just running reports on it?
>
>
>Unless you have a *really* good RAID array, expect slow performance on this
>hardware platform.
>
>
>

--


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com

On Thu, 2002-12-19 at 13:10, george young wrote:
> [linux, 700MHz athlon, 512MB RAM, 700GB 10kRPM SCSI HW RAID, postgresql 7.2]
> We're setting up a DB of IC test data, which is very simple and regular, but large.
> One project (we get three or four per year) has ~4 giga bits, each specified by
> a few small integer values, e.g.:
>  Name       Type Values
>  ----------------------
>  wafer      int     1-50
>  die_row    int  2-7
>  die_col    int  2-7
>  testtype   string (~10 different short strings)
>  vdd        int  0-25
>  bit_col    int  0-127
>  bit_row    int  0-511
>  value      bit  0 or 1
>
> with 4 billion(4e9) rows.  I would guess to make wafer, die_row, etc. be of
> type "char", probably testtype a char too with a separate testtype lookup table.
> Even so, this will be a huge table.

How many records per day will be inserted?

Will they ever be updated?

Do you have to have *ALL* 4 billion records in the same table at the
same time?  As Josh Berkus mentioned, wafer thru bit_col can be
converted to INT2, if you make testtype use a lookup table; thus, each
tuple could be shrunk to 20 bytes, plus 24 bytes per tuple (in v7.3)
that would make the table a minimum of 189 billion bytes, not
including index!!!

Rethink your solution...

One possibility would to have a set of tables, with names like:
TEST_DATA_200301
TEST_DATA_200302
TEST_DATA_200303
TEST_DATA_200304
TEST_DATA_200305
TEST_DATA_200306
TEST_DATA_200307
TEST_DATA_<etc>

Then, each month do "CREATE VIEW TEST_DATA AS TEST_DATA_yyyymm" for the
current month.


> Questions: How much overhead will there be in the table in addition to the
> 9 bytes of data I see?  How big will the primary index on the first seven columns
> be?  Will this schema work at all?
>
> Of course, we could pack 128 bits into an 8 byte "text" field (or should we use bit(128)?),
> but lose some ease of use, especially for naive (but important) users.
>
> Comments, suggestions?
>
> -- George
>
--
+---------------------------------------------------------------+
| Ron Johnson, Jr.        mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA      http://members.cox.net/ron.l.johnson  |
|                                                               |
| "My advice to you is to get married: If you find a good wife, |
| you will be happy; if not, you will become a philosopher."    |
|    Socrates                                                   |
+---------------------------------------------------------------+

On Thu, 19 Dec 2002 14:10:58 -0500, george young <gry@ll.mit.edu>
wrote:
>with 4 billion(4e9) rows.
>How big will the primary index on the first seven columns be?

If you manage to pack the key into 8 bytes (by using a custom 1 byte
integer datatype) and if there are no NULLs:

  75 GB with a 100% fill factor,
 114 GB with a  66% fill factor,
realistically something in between.  Note that frequent updates can
cause index growth.

>Will this schema work at all?

You have a somewhat unusual identifier : payload ratio (8B : 1b).  It
depends on the planned use, but I'm not sure if *any* database is the
right solution.  You have "only" 30670848000 (30G) possible different
key combinations, more than 1/8 of them (4G) are actually used.  A
7-dimensional array of double-bits (1 bit to indicate a valid value
and 1 bit payload) would require not more than 8 GB.

If you plan to use a database because you have to answer ad-hoc
queries, you will almost certainly need additonal indices.

Servus
 Manfred

Charlie,

> Why do you say to expect slow performance on this hardware?   Is
> there something specific about the configuration that worries you?
>  Or, just lots of data in the database, so the data will be on disk
> and not in the cache (system or postgresql)?
> What do you classify as *slow*?  Obviously, he is dependent on the
> I/O channel given the size of the tables.  So, good indexing will be
> required to help on the queries.  No comments on the commit rate for
> this data (I am guessing that it is slow, given the description of
> the database), so that may or may not be an issue.
> Depending on the type of queries, perhaps clustering will help, along
> with some good partitioning indexes.
> I just don't see the slow in the hardware.  Of course, if he is
> targeting lots of concurrent queries, better add some additional
> processors, or better yet, use ERSERVER and replicate the data to a
> farm of machines.  [To avoid the I/O bottleneck of lots of concurrent
> queries against these large tables].
>
> I guess there are a lot of assumptions on the data's use to decide if
> the hardware is adequate or not :-)

Well, slow is relative.   It may be fast enough for him.  Me, I'd be
screaming in frustration.

Take, for example, an index scan on the primary key.  Assuming that he
can get the primary key down to 12 bytes per node using custom data
types, that's still:

12bytes * 4,000,000,000 rows = 48 GB for the index

As you can see, it's utterly impossible for him to load even the
primary key index into his 512 MB of RAM (of which no more than 200mb
can go to Postgres anyway without risking conflicts over RAM).   A
Sort-and-Limit on the primary key, for example, would require swapping
the index from RAM to swap space as much as 480 times! (though probably
more like 100 times on average)

With a slow RAID array and the hardware he described to us, this would
mean, most likely, that a simple sort-and-limit on primary key query
could take hours to execute.  Even with really fast disk access, we're
talking tens of minutes at least.

-Josh Berkus