SISTEMA CARDIOVASCULAR CORAZÓN Y PERICARDIO

MySQL can’t split up a single query across multiple cores, but it can run multiple queries concurrently and having more cores gives you more concurrency. In a high-volume environment, more cores are better.

Right now, the best CPU on the market is the Intel Xeon SandyBridge 26xx Series. You can put up to two CPUs per box, each of which can have up to 8-cores each. That gives you 32 concurrently executing threads with Intel’s Hyper Threading. On top of that, they make them as fast as 3.5GHz per core.

Memory

This one is easy. Get the most amount of memory that you can possibly cram into your motherboard. As long as you have more data than memory, there is no reason (other than cost) why you should avoid maximizing the memory in your database servers. The more memory you have, the bigger you can setinnodb_buffer_pool_size.

Database Server: Ultimate MySQL Tuning Guide 79 We run ours with 64GBs per slave, but 128GB, 256GB, or even 512GB in a box isn’t unheard of nowadays. At the time of writing, a 16GB stick of ECC/Registered DDR3 runs around $175, so even 512GB only costs around $5500.

If you go with the Intel Xeon 26xx CPUs, SuperMicro even makes a motherboard with enough sockets to fit 768GB. Crazy times we live in.

Storage

Making the right storage decisions is very important to the performance of a MySQL cluster. Not all storage is built the same and a master/slave setup means that you have vastly different storage requirements. What works on your master database server won’t necessarily work well for your slave server, and vice-versa.

Hard Drives

I highly recommend using high-end server hard drives for your master database. Grab a couple of 10,000 or 15,000 RPM drives and put them into a RAID (discussed below).

Why not go for SSDs on your master? Remember what we talked about when sizing outinnodb_- log_file_size? For writes, MySQL can use mainly sequential I/O by taking advantage of a correctly sized log file. Sequential I/O is fast, even on rotational disks, so for a write-only master database server, it’s not really necessary to pay the price for SSDs.

Put your Master Server’s disks into a RAID! Don’t play with fire. Your drives will fail, usually at the worst time, and will cause downtime.

Don’t use RAID-5! The cost/GB is tempting in a RAID-5 but it’s too slow to use on a high- volume database server. If you’re working on the cheap, use a RAID-1.

Do use a RAID-10 RAID-10 is combination of RAID-1 and RAID-0. RAID-0, by itself, offers the best performance but is dangerous because losing a single disk will cause downtime. In RAID-10, two RAID-0’s are mirrored with RAID-1. Losing a disk won’t cause any downtime.

RAID-10’s downfall is that it has a bad cost/GB ratio. For example, four 600GB drives in a RAID- 10 (2.4TB of raw space) will only yield a 1.2TB RAID array.

Use hardware RAID Controller Don’t mess around with Software RAID. It’s not worth it. Investing in hardware RAID for your master server is the right move, from both a speed and durability point of view. Why hardware RAID?

• RAID cards have internal memory (often around 512MB) that is used to cache data before writing it to a disk. This makesfsync(and subsequently, any write to a disk) very fast. • Good RAID cards offer a battery or capacitor backup, which guarantees that the data inside

Database Server: Ultimate MySQL Tuning Guide 80 I really like the Adaptec 5405Z RAID card⁶⁵. It’s PCI-Express, has a 512MB write cache + 4GB flash cache, can connect up to 256 drives, and works great with Linux.

Solid-State Drives

For your slave MySQL servers, SSDs are where it’s at. Your slaves are going to be handling an unrelenting workload of random reads- which rotational disks are very bad at. On the other hand, SSDs are several orders of magnitude faster than rotational disks are at random I/O. It’s not uncommon for an SSD to push 30,000 random IOPS while your typical hard drive can barely do 200 IOPS with the same workload.

The downside of SSDs is that they’re expensive. The prices are coming down, especially in the consumer market, but price per GB is still high.

One consideration to make when looking at SSDs is that the performance between brands and products can be VASTLY different. You can’t compare just GBs anymore, you have to look at the raw IOPS performance. Some companies make 256GB SSDs with 5,000 IOPS and others with 500,000 IOPS.

Flash Wear Due to the underlying technology that’s used to make SSD drives, it’s possible to wear them out if you do heavy writing to them. The topic of wear endurance is pretty controversial. Depending on the manufacturer and type of flash-memory used, it’s hard to wrap your head around it and get a definitive answer.

My conclusion: I don’t worry about it. Our slaves are replaceable and with our HAProxy load- balancing setup, a crashed slave doesn’t create any downtime.

If you’re worried about it, one way to mitigate the problem is to write your InnoDB Log Files and Binary Logs on a separate, non-SSD drive. You can change the location of the log file location with theinnodb_log_group_home_dirsetting and similarly, the location of the Binary Log can be changed with thelog_binsetting. Because the binary log files store temporary data and only depend on sequential writes, non-SSD drives can be fast enough, without wearing down your flash drives.

If you’re really worried about flash wear, the easiest solution is to look for the most durable types of flash drives. The biggest differentiator between flash storage is the type of storage cells used- SLC(Single Layer Cell),MLC(Multi Layer Cell), and (less commonly)TLC(Three Layer Cell). SLC Flash Storage can only hold one bit per cell, while MLC holds two bits per cell and TLC can hold three. MLC and TLC have a capacity advantage because they can store more data in a single cell, increasing storage density and reducing costs.

The downsides to MLC and TLC, though, is that with more “states” represented in a single cell, it needs to be more exact. Everyone loves the glass-of-water analogy here, so let’s use that. Imagine that a cell of flash storage was represented by a glass of water. If it was an SLC cell, and only had to store one bit of data, you could do that pretty easy. An empty class would be0and a full glass would be1. Even if the glass got inaccurate over time, and a little water was leftover

Database Server: Ultimate MySQL Tuning Guide 81 when emptying it, you could still assume that it’s empty (0), if there was a little bit of water left in the bottom.

Now imagine if the glass of water was an MLC cell. Now you need to represent 2 bits in a single cup. How would you do that? Well, maybe an empty glass would be00, 1/4 full would be01, 1/2 full would be10, and completely full would be11. It’s doable, but your measurements need to be more precise— if there was a little water leftover in the empty cup, you might get confused whether or not it’s empty (00) or 1/4 full (01). Add in TLC, needing to have 3 bits in a single cell, and the need for precision gets even more demanding.

So, if flash wear is a big deal for you, SLC is the way to go. You’ll pay for it though, it can be up to 5x more expensive and typically tops out at a lower drive capacity.

Cell Type Lifespan per Cell (number of erase cycles)

SLC 100,000 MLC 10,000 TLC 5,000

RAID It’s not necessary to use RAID on your slave servers (since we don’t need the drives to sustain failure- we just replace the drive and rebuild the slave), but you can use it to increase your disk space or improve performance.

It’s often cheaper to buy 2x smaller high-end SSDs then 1x larger high-end SSDs. For example, if you need 400GB of SSD storage, you could either buy a single 400GB drive or 2x 200GB drives and put them in a RAID-0.

You can also use this technique to squeeze more IOPS out of your SSDs. Two SSDs in a RAID-0 will offer almost 2x the number IOPS when compared to a single drive.