I am building a database server (mySQL) for a friend's business and I am wondering what sort of processing power I would need for a moderate to high traffic server (from a few thousand queries a day, to tens of thousands of queries a day). It will be a dedicated mySQL server (ie, will not perform any other sort of duties).
Will I require dual CPU? I assume a lot of RAM is needed... 512MB enough? What sort of setup would any recommend?
Multiple CPU's are a must when it comes to multi user database systems. Again the more memory the better. 512MB is probably the minimum required. One thing to consider is the disk layout. I don't know mySQL at all, more of an Oracle/Informix/SQLserver person, but make sure that often hit files do not sit on the same drives. If you are going for a Raid 5 or 0+1 setup then the more disks you have the better. The sweet point for a Raid 5 array is usually about 5 disks depending on the controller.
Hope this helps
If you're building a high-traffic database server, the first thing you're going to need is a real database engine.
Before you invest in hardware, get some decent database software. MySQL just isn't going to cut it for high traffic.
On the other hand, for tens of thousands of queries per day, I'll bet you'll find that a Pentium-66 can keep up quite easily. Thousands of queries per SECOND is what I'd consider a high traffic database server.
There sure as hell ARE user servicable parts inside!
Well, for thousands of queries a day, I've seen a P75 with 64MB and MSSQL 6.5 doing just fine. Thousands of quieries a day would actually be a low traffic server.
I'll have to agree with one of the other posts, high traffic would be thousands of queries a second.
We've got a server working at a local bank that has hundreds of queries a second serving 300 concurrent users under MSSQL 6.5. It's got dual PPro 200's, 1Gb of RAM, and 5 18GB 10,000RPM UW SCSI 3 drives in RAID 5, with a 64MB caching controller, and it does the job very nicely.
mySQL is not a very good choice for a 'production' SQL server. It's OK to play with and to learn on, but you will want a more robust solution if a business is counting on it for performance.
I recommend that you take a good look at PostgreSQL. http://www.postgresql.org/ There is a complete book on-line in PDF format if you need help with it.
- - - - - - -
Illigitimus non Carborundom
If you intend on using either Visual Studio or Office 2000 Developer Edition to develop these apps, then you should know that MSDE 1.0 comes with these for free redistribution. It is a 5 user SQL 7.0 which can be upgraded to 7.0 when the budget allows, or when the Compaq Proliant or Dell PowerEdge server arrives.
I use the MSDE to work on SQL apps on my notebook (MSDE is like a private SQL 7), and when I get to work, I simply point to the server instead of my own MSDE. It's slick.
Here's what MS says:
MSDE (Microsoft Data Engine) is a fully SQL Server 7.0 compatible data engine for building mobile and shared solutions that easily migrate to SQL Server 7.0. Solutions built with MSDE for Visual Studio 6.0 are freely distributable and leverage the enterprise-class reliability and features of SQL Server, including merge replication to enable mobile computing.