Prev: performance-schema Next: optimizing-server-settings
I/O saturation is much less than common than CPU exhaustion. Memory exhaustion is more common.
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.
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.
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.
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.
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.
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
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.
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].
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.
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