Let's dig deeper.
How will you load the data? How many rows per second? Batched? Etc.
Let's see the exact schema -- SHOW CREATE TABLE. Every byte saved there will save a terabyte of disk space and some I/O. Every INDEX is a potential INSERT bottleneck.
Also, let's see the SELECTs you intend to perform. Formulating them will be the biggest optimization.
Have you considered "summary tables"? You really should. That can often give 10x performance improvement. Other vendors have "materialized views"; MySQL leaves that up to the user.
Sometimes I advise people not to even store the raw data, but summarize it before storing it. That might turn your trillion rows into only 100B rows. Poof! You have saved 10x. Note also, you can have any number of machines doing such summarizing (in Perl, or C++, or Java, or whatever, then batch INSERTing (or LOAD DATAing) into common table(s). If possible, I recommend this. Most of the other comments I have still apply, since 100B is Big.
Employing multiple cores is of no use if you are disk bound and/or network bound. Have you calculated the disk and Ethernet bandwidths you need? I predict that you will be I/O bound, and your questions about multiple cores will be moot.
What is your RAID setup like? That will be critical in I/O performance.
Loading a trillion rows will take weeks; could take years. Let's do an example:
Assumptions:
100 bytes/row.
4KB per disk write.
6-disk striping in RAID, with a good write cache
5ms/disk write (the actual write, not the write to the cache; after all, writing to disk is the ultimate bottleneck)
86,400 seconds/day
Calculations:
40 (4K/100) records per block (ignores index generation)
1200 blocks written per second (6*1000/5)
4 billion rows written per day (40*1200*86400)
So, back of envelope calculation says nearly a month to build your database. For that matter, it will take another month to sequentially scan through all of it. (The CPU required for a table scan is trivial compared to the I/O.) If you have multiple queries hitting different parts of the table(s), the I/O will still be much greater than the CPU requirements.
Next example. Let's read every row, but randomly.
200 rows/second/drive read (this assumes indexes are fully cached, but data is not -- a semi-reasonable assumption for huge tables).
1200 rows/sec (6*200)
100M rows/day (1200*86400)
10K days needed to read every one of the trillion rows -- that's nearly 3 decades!
How much RAM will you have? This is somewhat secondary, since there is no chance of getting enough RAM (on a single machine) to cache more than a small fraction of the data or indexes.
MyISAM or InnoDB? Typically this leads to 2x-3x difference in disk space required.
InnoDB has a limit of 64TB for a single table + index(es). You may be pushing that limit.
NDB Cluster is worth considering. That will sort of pool the resources of multiple machines -- vaguely like Oracle's RAC.
SSDs? Probably would have trouble configuring enough SSDs in one system to hold a trillion rows. SSDs might give you 10x performance boost in the I/O area. This will probably not be enough to raise the CPU performance enough to be a concern. It may, however, threaten your Ethernet bandwidth.
Is the data "shardable"? That is, can you split the data across multiple machines? Alas, MySQL does not have an out-of-the-box Sharding solution. A third-party, "Spider", does:
http://forge.mysql.com/wiki/The_Spider_Storage_Engine
Sharding, if done right, can scale performance sort of linearly with the number of machines you spread the data across. Two exceptions:
* There is possibly significant overhead to start with, due to having to hop from another machine to the machine containing the desired data.
* There can be significant saving (10x?) if you have enough machines so that all the data/indexes are cached in RAM. (That's a lot of machines, each with a lot of RAM; how's your budget?) 100TB / 256GB/machine = 400 machines would be needed to cache everything. (Because of overhead, etc, probably need 500-600 machines.)
The only practical way to deal with a trillion rows, by any vendor, is to shard the data.
Once you have sharded the data, you have some serious issues of losing data. (If you have a thousand machines, you should plan on one dying every week. Sometimes, you will have two crashes in a single day.) That leads to redundancy in the data -- not just at the disk level. Now the number of machines has at least doubled, without speeding up the loading and fetching processes.
I repeat, CPUs is a minor concern when talking about a trillion-row dataset.
I repeat, plan on sharding, if possible.
I repeat, design summary tables, if possible.
I will eagerly help you pursue this endeavor, but (as you see), I will be rather brutal in my warnings.