How to manage a huge database?
100 million rows in a table does not make a database "huge" by today's standards

This is also not MySql related in most ways - because you leave pretty fat the server software level and talk hardware.

The standard answer to this is that it all depends on your workload profile. Is it transactional, reporting, OLAP?

You mentioned that your largest table is 100 million rows. How does this compare to the other tables? Are they much smaller, or about the same size?

You may want:


As much RAM as you can. It is nice that servers these days should be able to load this size table in memory, isn't it?
As much disk performance as you can get.


This is where special hardware gets into the game fast. The last project I did in this area had 3x768gb flash disks with write back cache and 2x96gb with 48 cores each. However, this was an Oracle RAC and probably way beyond what MySQL could handle.

Anyhow, your best bet would be to go SSD for the IO side, memory for the buffer side, and with something in the 100 million row range you should be able to keep a significant part of that in memory. Using a 64gb server and 48gb buffer for that table I get  480 bytes per row (table, index). That wont be exact, but it gives you an idea that you can easily (64gb servers are quite cheap) fit the data in memory.

Here is a hint: Databases are not large when the fit in memory. They are large when you can not fit them into decent server hardware.