MySQL Simplified

This is a pre-release version of my book, MySQL Simplified. To sign-up to hear when it's ready to ship, enter your e-mail below!

Hardware for MySQL

This is a simple guide to buying hardware for a dedicated MySQL box. If you already know about this, just skip to installation. Note that there’s a special section for AWS that’ll give you tips on finding the right instance size for your use. Discussions of hardware are only useful for heavily used applications. Here’s a good rule of thumb for whether you need to continue to read:

Otherwise, just get standard server hardware if you’re using your own hardware. Here’s a basic spec:

On virtualized machines, get the equivalent of this (increase the disk space size if you’re on a system that doesn’t preallocate the entire disk size). If you’re using AWS, use a large, at least.


Database servers are very much tied to hardware. Their main function is to translate a declarative language, SQL, to physical read and write commands. This puts stress on CPU, memory and disk. Your workload does not put an even level of stress on each resource, so it’s your job to figure out how to balance those needs while balancing your budget. Your hardware is limited by its weakest component: if your disk is saturated, your high powered CPU won’t do much good.

You can scale application servers by buying more boxes because most applications do not maintain state. Relational databases are made to store state, and so they must scale by buying bigger boxes, since one server can be the single source of truth. With replication, you can scale out reads, but one master has to accept all writes.

Hardware choice is important, even if you use a managed service like RDS. Moving a server to bigger hardware, even virtual hardware, will cause downtime and carries risk. On the other hand, purchasing too much power is a waste of money. Hardware costs rise exponentially as you try to stuff more power into one machine.

Since you don’t have unlimited funds, you need to focus on what’s most important. If you are building an application, think about the use cases and determine if they are more write or read intensive. A high-write system will need fast disks but less memory. A read-heavy system will need enough memory to keep the hot data (both the rows and the indexes) in memory. CPU is generally needed in every case, but you’ll need it especially if you have complex queries with many joins, functions, and ordering.

SSDs are amazing and provide incredible IOs for the money. They also provide random IOs at rates that are similar to sequential IOs whereas spinning disks are much better at the latter than the former.

More likely to be disk-saturated, but not if your disks are much more powerful than your CPU’s MySQL is not parallel, so multiple cores are not good Rate CPU’s with PassMark Software's CPU Benchmark Charts

Measuring Current Resource Usage

It’s best to capacity plan when you have an idea of how many users (or companies) are using your product in relationship to the hardware they use. Then you can align capacity with business plans such as, we plan to grow our customers by 100%. If you’re on a system that’s running at 60%, you know you need at least a 20% increase in hardware resources, and more likely a 50% increase. There are three things that can stress resources: more users, more features, and more data. Increase any of those and you increase your hardware utilization.

If you’re just building a new product and you have no idea what the usage pattern will be like, guess and build a small test system. With libraries like Faker for Ruby and Python, you can create tables that roughly approximate your data model in a matter of minutes. For the most part, any sort of test is better than shooting in the dark.

Measuring CPUs

You should get a monitor on your current database system and watch how CPU is used. By running top, you can see how much load is on your system. If you’re seeing high queue waits, you should make sure to get a new CPU. Even if you just use your own development machine, you can begin to measure how much CPU your application is using. Then compare your CPU’s power to the options you are considering for your server. On Linux, you can get your current CPU model by running this command:

$ cat /proc/cpuinfo

I often use to compare CPU models. This will not give you absolutes about how your CPU will run your workload, but it does provide a good comparison (the Redding CoreDump3000X is 150% faster than our current processor Obsoleton CeleryT.5).

Measuring IOs

When considering a new system, you should think about how many read or write operations the system will need to perform, or how many IOPs do you need to plan for? IOPs are also called transfers (xfers) or transactions per second (tps). A database is very sensitive to IOPs since it’s main job is reading and writing data.

Typically, an InnoDB transaction will take 1-5 IOPs, but your results may vary. You can measure what your system is currently running at by bringing up iostat. I’d recommend running this for a 24 hour period, then combing through the data to get the maximum and the average.

Here’s how to create a task that will sample your iops every second:

$ stdbuf -o0 iostat 1 -t | stdbuf -o0 grep 'sda\|sdb' | stdbuf -o0 awk '{print d, $1, $2}' "d=$(date)" >> /tmp/iops.txt

Let’s unpack this so you know what’s going on. The iostat command (part of sysstat package) gets the tps since the last check. The stdbuf commands unbuffers the output so that each time iostat prints out a line, it immediately passes through all the pipes. The grep command filters the iops down to just those devices you use to store data. The symbols “\|” is a logical OR.

This shouldn’t have much overhead, but you can monitor top to make sure. With this information, you can start looking at hard disks or at AWS’s EBS IOPs to make sure that they meet your needs.

Measuring Memory

A page in memory is the same as a page on disk, so measuring how much memory you need is as simple as understanding how big your active data is. You can show the size of each table by running this command:

\> Show Table Status Like '<table>'\G

This will give you both the data size and the memory size. If you mainly access 10% of a table, then just multiply it by 0.10 to get the actual size in memory it will take. If you have a currently running system, you can run:

\> Show Engine InnoDB Status\G

And look at the hit ratio in the Buffer Cache portion to see how often InnoDB needs to read from disk because the page it requires is not in memory. If 99% of queries just hit the buffer cache, I’d say you’re good.

Selecting the Right Disk

Similar to choosing the right spouse, choosing the right disk is pretty important to an application whose main function to store data. The other difficulty is the fact that disks are often better than each other by orders of magnitude. For instance, a regular desktop HDD might have a capacity of a hundred or so IOPS and a latency of a few milliseconds while an enterprise SSD weighs in a million IOPS with a 20 microsecond latency. Disks come is all capacities, throughputs, and latencies.

Drive Criteria

Here are the basic characteristics that should be compared when selecting drives. From you earlier measurements, you should know whether IOPS is more important than capacity.

For spinning disks of rust, or HDD, you also want to look at:

SAS vs. SATA does not matter per se since they both have standards that support 3 or 6 GB/s. They do matter incidentally, since SAS disks are made for the enterprise disks while SATAs cover the spectrum.

Choosing SSDs

SSDs can provide ridiculous performance if you have money to spend. The IOPS of a single SSD, especially random IOPS, would put an entire storage array of HDDs to shame. There’s simply no comparison. In particular, there’s an SSD by Seagate called the X8 Accelerator that is capable of 1 million IOPs and can hold 2 TB. It will set you back 10 large, but imagine being able to write a billion rows within a few seconds. Disk would never be an issue for the vast majority of applications.

These disks have two great advantages: they have an impressive number of IOPS and they beat the pants off of traditional disks when it comes to their ability to read randomly. An HDD is very good at reading a file that’s grouped together on the disk, and that’s why you defragment your file system. With an SSD, random writes aren’t too much more expensive than a sequential read, since they like main memory, versus a drive that reads by moving an arm over the surface of a platter. Without spinning disks and arm, an SSD is much faster at starting the process of reading and writing, so latency is 50 microseconds versus a couple of milliseconds.

SSDs do come with two downsides: they are more expensive per GB of storage and they have a finite number of writes. Enterprise SSDs will allow more writes because they will simply reserve extra space so that when one part can’t be written to anymore, they can use their space parts.

Since they consist of memory and aren’t disks spinning at breakneck speeds, they also tend to break less frequently (HDDs break 5x more often). You still need to use RAID to ensure that a single disk failure brings down production.

Prices for disks will come down eventually and SSDs will become standard. But you can enjoy the future, today, with the power of solid-state disks. How’s that for marketing copy?

Choosing a storage array

A storage array is a great way to share disks between systems in a failover cluster. It’s also necessary if you have a bunch of disks, which a MySQL server often has. I’m not going to provide you with a model number and brand, but I will say that you need to find a solid professional who knows what kind of array to get and who will help you build a configuration that will meet your needs. This will not be cheap, but when you need it, you need it. Two notes: make sure it's battery-backed (if there's a write buffer that is lost when the power goes out) and there's a good way to monitor the system remotely, for when a disk or the battery fails.

Choosing a RAID array

I’m going to cut to the chase with this one: use RAID 10. Okay, you might not have that many disks to burn. In that case, use RAID 1. Stay away from RAID 5.

On systems that directly map disks to mount points (not SANs), you can separate the binary logs, the InnoDB logs, and InnoDB data files. If you have three RAID arrays, separate them all. If you have two, put the binary logs by themselves and the InnoDB logs and data files together.

Choosing a SAN

A SAN can be a beautiful thing. However, unless everyone in the storage business is visited by the ghosts of Christmas past, you’re going to pay an arm, a leg, the shirt off your back, and your firstborn. Why would someone pay scads of cash? Simple: when you need it, you need it. If you’re a start-up or you don’t have a ton of data, don’t even think of getting a SAN. I’m only including it here if you already have one.

SANs abstract the terrible things that are disks. What’s funny is that HDD themselves are abstracting away the terrible things that are magnetic platters (each disk has advanced error correction since the physical reads are often wrong). They allow you to add and replace drives without affecting the servers that use the SAN. You can add different tiers of underlying storage and the SAN software will automatically place your hot data in the fast tier. You can take snapshots of your database directory without MySQL even knowing.

A SAN can also give you a ton of spindles at once. This is good because when your data is spread over more drives, it can parallelize writes and reads, thus reducing a task that would take 60 ms when executed serially on a single disk into a task that takes 6 ms on 10 disks executed in parallel. While maybe you couldn’t devote 10 disks just to your application, a SAN allows you to share 10 or 20 or 30 disks among several servers.

There are downsides of course. If those other applications are also always hitting the SAN, you would then have to contend with them for IOPS. Since a SAN is connected over the network, it has higher latency. The cost per GB of a SAN is often much higher than even a RAID 10 array.

Choosing NAS

Don't use a NAS. A NAS only knows about files, and MySQL, InnoDB specifically, does not perform well under this circumstance.

Amazon Web Services

EC2 and RDS provide you with a range of options. You’ll want to compare instance size to more familiar terms. For instance, a large instance will give you the power of a nice PC in 2007, or of a tablet in 2013. This can be quite discouraging when your application seems snappy on your development box, but then slows to a crawl once you deploy it to AWS. But just like at Starbucks, a tall is a small, in AWS, a large is basically a tablet and something like a quadruple extra-large is very similar to a box you could buy for $10K from Dell.

Take a look at the charts available online for EC2 instance sizes and pricing. Prices tend to go down, so I’m not going to reprint it here. An EBS volume might get 100 IOPS, but you can also use provisioned IOPS to get 4000 IOPS, but you can RAID them together to get more performance. EBS is a SAN, so everything that I said about SANs apply to EBS (except price). Some people have reported that networks are slow, but I haven’t found that to be the case and I can’t find any documented proof of that claim.

This is a pre-release version of my book, MySQL Simplified. To sign-up to hear when it's ready to ship, enter your e-mail below!

 No spam

RSS Feed

My other sites

© Copyright 2011-2019 Noel Herrick. All rights reserved.