Database Server Hardware

[sweepK]

Hello,
I am looking into building a new mySQL 5 database server and I would love to get some input from you guys to see if this is sufficient or if there might be a bottleneck somewhere.

The database server will be the backend for an application simultaneously being used by 1000-2000 users. We’re storing contact information and other basic text-based data. The tables will eventually get in the range of 1,000,000 rows and probably 30+ columns. The data in these tables is going to be related in many ways which means we’ll have all kinds of JOINs.

The hardware I am currently looking into is as follows:

2x Dual Core Intel Woodcrest 5130 Processors
4x 146 GB 10K SAS Harddrives in RAID1+0 (stripe and mirror)
4GB or 8GB RAM

The operating system is probably going to be Red Hat Enterprise 5 (I can get it without subscription cost).

Because of the nature of the application we’ll probably go with InnoDB as storage engine.

What do you think? Is that hardware sufficient or should we go with 8 instead of 4 cores, or more RAM?

[mwallace]

As ever, your mileage WILL vary.

Our current configuration handles:
3500 queries/sec - about 51% read, 49% write.
The total data size is about 66G in a single innodb tablespace.
The tablespace comprises 38G data and about 40G indices. Apparently I need to explore why those numbers don’t add up…

We’re running a 9+1 mirror set for data, and a 4+1 set for logs, but on the data side, we’re only using the equivalent throughput of 2-3 disks.

My recommendations will always work out to something along the lines of:

• Use remote storage wherever possible - SAN, SCSI-attached JBoD, whatever. Don’t get locked into the number of local spindles you can cram into your server chassis.
• Separate your data and your logs. If data is write-heavy, logs will be write-heavy as well, both for transaction logging and for replication logging.

The odds are good that a 4-disk 10K config will be fine for you, but don’t get locked into that being the max you can throw at the system.

CPU-wise, we’re running 2x single core Xeons - If you scale much beyond a couple of cores in an i/o intensive Innodb environment, you will probably run into innodb scalability issues. Throwing cores at the problem won’t help you. Check the mysqlperformanceblog, if I recall correctly, for relevant information.

Finally, if your data matters, use innodb for writes. If you’re super concerned about read performance and an innodb config won’t cut it for you, you can flip to myisam for reads, but I’m a much bigger fan of throwing hardware at the problem rather than risking table corruption and data loss.