I am doing preliminary research for a future project and would like to ask your opinions on what you suggest is best for maximum MySQL database speed as well as stability and reliability.

This project would be a web store with tables relating to product info, client info, community forums, financial data, etc. The product info tables would have the most querries, potentially up to tens of thousands of hits per second. I would like people to be able to login without any delays and to get search results back on products within a fraction of a second.

Would it make sense to put the product tables into their own database separate from the community forums and client info tables even if they are hosted on the same server? Would there be performance difference and/or better stability, reliability?

What about having these separate databases hosted on separate servers? Would that be better than having one all-embracing large database on one server or perhaps on a server cluster? Would there be scalability issues?

Please feel free to give your opinions, pros and cons. Thanks in advance.

That's a lot of hits! If you're designing a new system then I'd suggest not setting you're targets quite so high - it will only complicate matters and may be wasted effort if things don't pan out the way you're hoping. If you're really aiming for that type of throughput then why don't you simply employ experts from off these forums to help design your system (you could try Rudy or aschk).

You can have permanent connections (in PHP at least) which means there are no connection delays.

It would probably be better to separate the databases for many reasons.

You should be able to use standard software to run forums (like this site) and they probably require their own databases.

Replicating your product database - the one that's going to have all these hits - across multiple servers should allow you to allow more throughput without changing the code too much. Having different websites ( .com .co.uk .ne ) each with their own databases allows you to also spread the load as well. Buying a large server also helps

Mike

Thanks for your comments Mike. I intend to get professionals to put it all together but want to feel I know something about the topic beforehand.

Any more opinions from anyone?