MySQL and Hardware Information

Oct 26, 2009 / By Sheeri Cabral

Tags:

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?

9 Responses to “MySQL and Hardware Information”

  • Ryan says:

    For an I/O bound MySQL workload, I really like the SunFire X4140/X4150 — 8 local disks (with SATA/SAS/SSD options) in a 1RU box with 12 cores and 128GB of RAM and hardware BBU raid card.

  • Mark R says:

    Quite interesting…

    We also use Dell Powervault MD3000 with Dell 1950s with 32G of ram.

    Our servers get almost *no* select queries (only 100s per hour at busy times), but a very high load of inserts, typically 300 rows per second 24/7 (not individual inserts; they insert in batches). Our tables have lots of columns and indexes; it’s an auditing reporting system.

    We use MyISAM which does have some drawbacks but is very space efficient. We use PACK_KEYS and DELAY_KEY_WRITE as well as various other tweaks.

    redundancy is got by using replication and slave servers with similar hardware but bigger discs; the master servers have typically 300G – 1.5T of data each.

  • Daniel says:

    We run a 2TB MySQL instance:

    IBM HS21 blade server with 16GB RAM and 2 quad core Xeon cpus

    Storage is an IBM DS3400 FC array with 24 15k RPM SAS disks

    OS is OpenSolaris 2009.06

  • I have been working with databases for more years than Pythian has been around, and I have been asked this question too many times in my career.

    Here Sheeri is presenting a few examples from the real world.

    What I don’t really understand is *why* does it have 2 votes against in Planet MySQL, can someone explain this to me?

    My $.02

  • kedar says:

    How about RAID or is that out of scope?
    Long back I happened to be here [http://www.sizinglounge.com/index.php?swid[]=10&users[]=400&swid[]=10&users[]=400&appscount=2&aid=1&pid=1&util=70&storageSize=576&months=36&pue=25&discount=0&energy=16&labour=6000&license=0&vendors[]=10&cat[]=1&cat[]=2&cat[]=3&pf[]=1&pf[]=2&pf[]=3&pf[]=4&pf[]=5&submitted=TRUE]

    How much does it make sense? Can any one explain considering default settings eg. my-large.cnf?

  • [...] On the Pythian blog, Sheeri Cabral shared a brief survey of large data sets and their hardware stats in MySQL and hardware information. [...]

  • Matt Reid says:

    Here are some real-world stats from some of the servers I admin. There are more big ones but these are just the ones on the top of my head right now. MySQL scales well and pushes tons of traffic on the internet – you just have to design it to do so correctly.

    1. 50K queries per second, 10K connections – internet ad serving company. several slave servers for reporting and analytics. hardware: 8 core opterons, 32GB ram, attached to an EMC SAN utilizing 120GB dataset. many small queries and query cache hits. several scripts running every hour to ensure the query cache is preloaded with the proper data. MySQL 5.0.68-percona

    2. 8K queries per second, 1000 connections, lots of memcached and custom caching on the app – also an internet ad serving company. several slave servers for reporting and analyics. hardware: 16 core, 64G ram, EMC SAN using 180GB dataset. MySQL 5.1.x -percona

    3. 2K queries per second, 1400 connections, 32 CDN servers + 4 java bytecode processing servers, 4 slave servers for read-only traffic. hardware: 8 core opterons, 32GB RAM, EMC SAN with 250GB data space. MySQL 5.0.68-percona

  • Camila says:

    Sheeri
    you said “The primary site has 2 machines connected to the same LUNs on a HA
    setup”. Could you say more details about this ha setup? ex, is it linux ha ou custom scripts…
    Thanks!

  • Sheeri says:

    Camila — it’s using regular linux-ha.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>