People often ask “what’s the best hardware to run a database on?” And the answer, of course, is “it depends”. With MySQL, though, you can get good performance out of almost any hardware.
If you need *great* performance, and you have active databases with a large data set, here are some statistics on real life databases — feel free to add your own.
We define “large data set” as over 100 Gb, mostly because smaller data sets have an easier time with the available memory on a machine (even if it’s only 8 Gb) and backups are less intrusive — InnoDB Hot Backup and Xtrabackup are not really “hot” backups, they are “warm” backups, because there is load on the machine to copy the data files, and on large, active servers we have found that this load impacts query performance. As for how active a database is, we’ve found that equates to a peak production load of over 3,000 queries per second on a transactional database — that is, normal production load gets the server to over 3,000 queries per second at peak times — and a flat average of over 500 queries per second if there are definite quiet and peak times, or if the server is used for reporting or a combined reporting/transactional load. This flat average should be taken over a period of a week or more.
We’re only showing the hardware here, not the configurations, for our three busiest/largest environments. All the configurations shown here have 2 machines, for an active primary and either an active secondary (for read-only queries) or a passive secondary (quiet until needed):
A music distribution company runs the following for primary production (Data size in the 360-380GB range):
2x Sun SunFire X4600 M2 Servers using 4xDual Core Opteron 8220 with 32GB
of RAM attached to a Hatachi DF600F SAN. The SunFire X4600 is scalable
to 8 Sockets (32 Core) and 512GB of RAM.
National post office for a G8 nation = 1.2T of data (and growing fast!).
The primary site has 2 machines connected to the same LUNs on a HA
setup. We have an ‘active’ and a ‘passive’ master configured to kick-in
if the other node fails (only one mounts the LUNs with the data). Both
these servers have 4 x Quad-core Intel Xeon processors and 16G of RAM each.
An online marketing firm has ~600GB of Data
2x Dell PowerEdge R710 with 36GB of RAM and two Intel Xeon L5520 CPUs (Quad Core) – Servers supports up to 144GB of RAM and max of 2 Sockets
Storage: Combination of Local Storage (logs, etc) and an DELL PowerVault
MD 3000 Direct Attached Storage (shared).
What are your details?