Eu costumava trabalhar para uma empresa de comércio eletrônico com um site que tinha vários milhões de acessos por dia. Tivemos um único DELL PE 1750 com 2 CPUs de núcleo único e 2 GB de RAM, tamanho de banco de dados aprox. 4GB. Nos horários de pico, este servidor tratou de mais de 50 mil consultas por segundo.
Tendo dito isto: o banco de dados foi bem estruturado, todas as consultas foram afinadas (tivemos sessões semanais analisando os logs de consultas lentas e corrigindo consultas e índices) e a configuração do servidor também foi ajustada. O cache é definitivamente uma boa idéia, mas o MySQL faz isso de qualquer maneira, você só precisa analisar o desempenho e, em seguida, ajustar como sua memória é usada (cache de consulta versus outras opções).A partir dessa experiência, posso dizer que o maior impacto é causado pela falta de índices, índices incorretos e design de banco de dados inválido (por exemplo, campos de cadeia longa como chaves primárias e sem sentido).