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/en/storage-requirements.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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
