Hi,

I have a table (structure below) which will hold 2.5 billion rows. I'm
currently choosing the hardware i'll need. Does anybody know what the
minimum spec of machine is likely to be that I comfortably use? I
imagine the table will have to be Innodb split across a number of files.
It will also need careful indexing to be able to access with rapidly. I
was thinking of something along the lines of the Dell PowerEdge 1950 or
2950?

TIA
Rich


mysql> show columns from table1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| p | varchar(50) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| call_1 | int(11) | YES | | NULL | |
| prob_1 | float | YES | | NULL | |
| call_2 | int(11) | YES | | NULL | |
| prob_2 | float | YES | | NULL | |
| call_3 | int(11) | YES | | NULL | |
| prob_3 | float | YES | | NULL | |
| coh | varchar(10) | YES | | NULL | |
| ana | varchar(15) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+

Rich, one consideration is of course disk space - you'll want to make sure
that 2.5 billion * row length will fit on your disks.

Offhand, you've got (referencing
http://dev.mysql.com/doc/refman/5.0/...uirements.html)
78 bytes in 3 varchars (50 + 10 + 15, + 3 for internal use)
16 bytes in 4 ints
12 bytes in 3 floats
---
106 bytes max per record

106 bytes * 2500000000 records = 246 GB

Any indices you add to your data will consume space on top of that, and
you'll need space available for temp tables etc. as well. Personally, I
would double that 246 GB as a safe starting point. With that much data you
will want to consider indexing only prefixes (first few characters) of the
varchar columns, to make indexes smaller & more likely to fit in RAM. That
being said, with this much data, loading the machine up on RAM will help
obviously.

Don't know your requirements but a MyISAM table might treat you better as in
my experience that storage engine excels at raw speed for something like
this. You would consume more disk space but speed might also improve if you
used fixed-length rows by declaring CHAR instead of VARCHAR. MyISAM has a
max table size of around 64TB but the limitation is often the filesystem;
MERGE tables could help work around that. If the data is not going to
change then an archive (compressed) table might help as well, as the data
will consume less disk space and therefore can be read off disk faster.

But if you need transactions etc. then of course MyISAM is out.

HTH,
Dan


On 2/14/07, richard <rich@thevillas.eclipse.co.uk> wrote:
>
>
> Hi,
>
> I have a table (structure below) which will hold 2.5 billion rows. I'm
> currently choosing the hardware i'll need. Does anybody know what the
> minimum spec of machine is likely to be that I comfortably use? I
> imagine the table will have to be Innodb split across a number of files.
> It will also need careful indexing to be able to access with rapidly. I
> was thinking of something along the lines of the Dell PowerEdge 1950 or
> 2950?
>
> TIA
> Rich
>
>
> mysql> show columns from table1;
> +------------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+-------------+------+-----+---------+-------+
> | p | varchar(50) | YES | | NULL | |
> | id | int(11) | YES | | NULL | |
> | call_1 | int(11) | YES | | NULL | |
> | prob_1 | float | YES | | NULL | |
> | call_2 | int(11) | YES | | NULL | |
> | prob_2 | float | YES | | NULL | |
> | call_3 | int(11) | YES | | NULL | |
> | prob_3 | float | YES | | NULL | |
> | coh | varchar(10) | YES | | NULL | |
> | ana | varchar(15) | YES | | NULL | |
> +------------+-------------+------+-----+---------+-------+
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

Sorry for the double-post, but another thought if it's not obvious already:
with such a large dataset, making sure to employ striping and high-RPM disks
will be important for speed. I'd recommend looking at RAID 1+0 with dual
SCSI channels and 15K RPM disks if you're looking in the Dell PowerEdge
series. The more disks you can stripe across, the better (to a point of
course). Multiple SCSI channels help too. RAID 5 is pretty effective too
for read speed.

Dan


On 2/14/07, richard <rich@thevillas.eclipse.co.uk> wrote:
>
>
> Hi,
>
> I have a table (structure below) which will hold 2.5 billion rows. I'm
> currently choosing the hardware i'll need. Does anybody know what the
> minimum spec of machine is likely to be that I comfortably use? I
> imagine the table will have to be Innodb split across a number of files.
> It will also need careful indexing to be able to access with rapidly. I
> was thinking of something along the lines of the Dell PowerEdge 1950 or
> 2950?
>
> TIA
> Rich
>
>
> mysql> show columns from table1;
> +------------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+-------------+------+-----+---------+-------+
> | p | varchar(50) | YES | | NULL | |
> | id | int(11) | YES | | NULL | |
> | call_1 | int(11) | YES | | NULL | |
> | prob_1 | float | YES | | NULL | |
> | call_2 | int(11) | YES | | NULL | |
> | prob_2 | float | YES | | NULL | |
> | call_3 | int(11) | YES | | NULL | |
> | prob_3 | float | YES | | NULL | |
> | coh | varchar(10) | YES | | NULL | |
> | ana | varchar(15) | YES | | NULL | |
> +------------+-------------+------+-----+---------+-------+
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>