Choosing the Right RAID Level for MySQL: A Practical Guide

1. RAID Basics

RAID (Redundant Array of Independent Disks) combines multiple inexpensive disks into a single logical unit to improve throughput, increase capacity, and provide fault tolerance. It can boost read/write speed, expand storage, and protect data against single‑disk failures, which is essential for database storage.

2. Common RAID Levels

RAID‑0 : Striped data across disks for maximum performance; no redundancy, so a single disk failure results in data loss. Suitable only for workloads where speed matters more than safety.

RAID‑1 : Mirrored disks; provides high reliability but halves usable capacity. All writes go to both disks, so performance is lower than RAID‑0.

RAID‑10 : Combines RAID‑1 and RAID‑0 (mirroring then striping). Offers both performance and fault tolerance but requires at least four disks.

RAID‑5 : Striped with distributed parity; balances performance, capacity, and redundancy. Requires a minimum of three disks; write performance is slower than RAID‑0 due to parity calculations.

3. Determining RAID Level, Write Policy, and Battery Status

Use the MegaCli64 utility to query the controller. Example commands: [root@host ~]# MegaCli64 -LdInfo -lAll -aALL The output includes fields such as Primary, Secondary, and RAID Level Qualifier. While the Primary value often indicates the RAID level, it cannot distinguish RAID‑1 from RAID‑10 when both show Primary‑1, Secondary‑0, Qualifier‑0. To differentiate, examine: Number Of Drives per span – disks in each mirrored group. Span Depth – number of such groups.

If Span Depth equals 1, the configuration is RAID‑1; otherwise it is RAID‑10. Another indicator is Primary‑1, Secondary‑3, Qualifier‑0, which also denotes RAID‑10.

Write policies affect I/O performance:

WriteBack : Writes go to the controller cache first, offering higher performance but requiring a functional battery to avoid data loss.

WriteThrough : Writes go directly to disks, safer when the battery is absent or faulty.

Check the current policy with:

[root@host ~]# MegaCli64 -LDInfo -Lall -aALL | grep 'Cache Policy'

Typical output shows both Default Cache Policy and Current Cache Policy, e.g., WriteBack, ReadAhead, Direct, No Write Cache if Bad BBU.

Battery health can be inspected via:

[root@host ~]# MegaCli64 -adpbbucmd -aAll | grep -E 'Battery State|Charger Status|isSOHGood|Relative State of Charge'

Key fields: Battery State: Operational – battery is functioning. Relative State of Charge: 98% – charge level; below 15% may force a switch from WriteBack to WriteThrough. Charger Status: Complete – charging finished. isSOHGood: Yes – health indicator.

4. Recommended RAID for MySQL

Based on the characteristics of MySQL file types, the following RAID choices are suggested:

Data files (frm, ibd) : High safety and performance required. Prefer SSDs with RAID‑10 if budget allows; RAID‑5 is an alternative.

Binary logs : Very frequent writes; safety is important for replication. Two SATA disks in RAID‑1 provide a good balance.

Redo and shared tablespace files : High reliability needed. RAID‑10 is ideal; RAID‑1 is acceptable when budget is limited.

These recommendations align RAID capabilities with MySQL workload demands, ensuring both data protection and I/O efficiency.