Operating System and Hardware Optimization

Table of Contents

Operating System and Hardware Optimization

Prev: performance-schema Next: optimizing-server-settings

What Limits MySQL’s Performance?

I/O saturation is much less than common than CPU exhaustion. Memory exhaustion is more common.

How to select CPUs for MySQL

Balancing Memory and Disk Resources

Caching, Reads, and Writes

High memory allows for faster reads, since reads don’t have to hit disk: As well, caching is good for writes, because it allows more writes to be batched before flushing to disk. As well, various modifications can be modified in memory, and then minified before being persisted to disk.

This is why most databases persist to a Write-Ahead log, which allows changes to be made to memory.

Solid-State Storage

SSDs are standard for most database systems. They allow for much better random read and write performance, better sequential read and write performance, and better support for hard drives.

An Overview of Flash Memory

SSDs are faster for reads, but are much more complex for writes: Cells can only be rewritten with an erase cycle that wears out the block. As well, the number of erase cycles a block can tolerate depends on the underlying technology it uses.

This is mainly dependent on firmware, drivers, etc, which can make performance worse, and wear leveling and write amplification caused by moving blocks around wears out SSDs faster than HDDs.

Garbage Collection

SSDs are much more prone to Garbage Collection pauses, because erase instructions can take a few milliseconds, compared to a write to disk, which is a few hundred microseconds.

RAID performance optimization

level Synopsis Redundancy Disks required Faster reads Faster writes
RAID 0 Cheap, fast, dangerous No N Yes Yes
RAID 1 Fast reads, simple, safe Yes 2 (usually) Yes No
RAID 5 Cheap, fast with SSDs Yes N + 1 Yes Depends
RAID 6 Like RAID 5 but more resilient Yes N + 2 Yes Depends
RAID 10 Expensive, fast, safe Yes 2N Yes Yes
RAID 50 For very large data stores Yes 2(N + 1) Yes Yes

RAID arrays should be heavily monitored, as they can fail in a myriad of ways, and some failures are more catastrophic than others.

Network Configuration

A slow network will cripple MySQL performance. DNS is a common problem: MySQL does a forward and backward resolution, which can be slow.

This can be jumped over with skip_name_resolve option. But this only allows MySQL to be connected to with ip addresses, or localhost.

Sometimes you might need to allow more local ports:

echo 1024 65535 > /proc/sys/net/ipv4/ip_local_port_range

Or allow more connections:

echo 4096 > /proc/sys/net/ipv4/tcp_max_syn_backl

Or change the timeout that comes after closing a socket:

echo <value> > /proc/sys/net/ipv4/tcp_fin_timeout

Choosing a Filesystem

On Linux, you might be best using a journaling filesystem, like ext4, XFS, or ZFS.

Make sure to check for the useless options and disable them for each filesystem.

Here are some tradeoffs:

Filesystem Operating system Journaling Large directories
ext3 GNU/Linux Optional Optional/partial
ext4 GNU/Linux Yes Yes
Journaled File System (JFS) GNU/Linux Yes No
NTFS Windows Yes Yes
ReiserFS GNU/Linux Yes Yes
UFS (Solaris) Solaris Yes Tunable
UFS (FreeBSD) FreeBSD No Optional/partial
UFS2 FreeBSD No Optional/partial
XFS GNU/Linux Yes Yes
ZFS GNU/Linux, Solaris, FreeBSD Yes Yes

XFS is a good default.

Choosing a Disk Queue Scheduler

On Linux, the queue scheduler determines the order in which requests to a block device are actually sent to the underlying device.

The default is Completely Fair Queueing or cfq, which is good for laptops and desktops, but poor for servers.

Catting the queue scheduler will highlight the active scheduler in brackets.

cat /sys/block/sda/queue/scheduler
noop deadline [cfq]

Noop and deadline are about equal in benchmarks. It’s just important to replace [cfq].

Memory and Swapping

MySQL performs best with a lot of memory to hold as a cache to avoid disk access. As well, the performance of the allocator can be slow, (glibmalloc) which can be replaced with tcmalloc or jemalloc.

Swapping occurs when the operating system writes some virtual memory to disk because it doesn’t have enough physical memory to hold it.

The authors recommend running your MySQL without swap space enabled, which may lead to process termination, but avoids the slowness of swap.

The default swappiness is 60:

cat /proc/sys/vm/swappiness
60

Set it to 0 for servers.

echo 0 > /proc/sys/vm/swappiness

Some other tricks involve using innodb_flush_method=O_DIRECT to relieve I/O pressure or set memlock which locks MySQL in memory. This can cause MySQL to crash if it tries to allocate more memory it doesn’t have though.

Operating System Status

iostat, vmstat, and mpstat are good tools to read more about the status of the operating system.

As well, perf, the linux profiler, and strace is a good way to look at what uses so much CPU.

Prev: performance-schema Next: optimizing-server-settings