What do you think the best mysql solution would be for a site that receives about 1-2 million uniques a day? The site is very dynamic but most of the queries are called over and over again. You think clustering would be the way to go for this or just get a very powerful server and use some high tech chaching? or do both? Please suggest what you think is the best solution and if you been through this before.
Can you describe the usage? Is this a standard website, a forum, something else? Big difference in what's possible based on the anticipated usage. We have a single dual Quad CPU server that can handle about 1600 transactions per second (mostly reads) before it starts bogging down. I think with some better tuning it could probably do 2800 or so. At 1600/sec, roughly 6M per hour, should more than cover 2M page views per day assuming that the per user database usage is reasonable. Anyway, just an example. In any case, I would definitely fine tune the database parameters, query cache, and the hardware. Also make sure you're using a front-end caching system. Not sure what language the site is in, but APC (http://www.php.net/apc/) is a great addition to a php installation. There's going to be front-end caching for just about every platform out there. They will significantly reduce load on a database for recurring queries. Also, clustering introduces a mess of administration and other headaches, so I would look into it only after concluding that a single server, or even a replication setup will not work. If you need that sort of computing power, you may want to look at a cloud solution for the hosting, as the hardware when you get into a 3 or 4 server replication or clustered setup is going to cost upwards of $20K.
* Optimize scripts as much as possible * Right configuration * Spitting it up into many servers. Creating a sub-domain for each function. The new Intel processor is just amazing for hosting, so I've heard.