Choosing Percona, MariaDB or MySQL and Proven MySQL Tuning & HA Strategies

Choosing Percona Server, MariaDB or MySQL

MySQL offers two storage engines: MyISAM (no transaction support) and InnoDB (transaction support). Since MySQL 5.5 the default engine is InnoDB. XtraDB is an enhanced InnoDB version optimized for modern hardware.

Percona Server is an independent, MySQL‑compatible distribution that can replace the storage engine with XtraDB without code changes and is the closest to the official MySQL Enterprise edition. It includes the high‑performance XtraDB engine, Percona XtraDB Cluster (PXC) for HA, and the percona‑toolkit DBA toolbox.

MariaDB was created by MySQL’s original developers to be fully compatible with MySQL APIs and command‑line tools. It ships the standard MyISAM and InnoDB engines, and from version 10.0.9 uses XtraDB (codenamed Aria) as a replacement for InnoDB.

Based on extensive experience, the recommendation is to prefer the Percona branch first, then MariaDB, and finally the official MySQL version if risk avoidance is paramount.

Common MySQL Tuning Strategies

1. Hardware‑Level Optimizations

Adjust server BIOS settings.

Enable Performance‑Per‑Watt Optimized (DAPC) mode to maximize CPU performance.

Set Memory Frequency to "Maximum Performance".

Enable Node Interleaving to avoid NUMA issues.

2. Disk I/O Optimizations

Use SSDs.

For RAID arrays, equip the controller with CACHE and BBU modules to boost IOPS.

Prefer RAID10 over RAID5.

3. Filesystem Optimizations

Use the deadline or noop I/O scheduler; avoid cfq.

Prefer XFS over ext3; ext4 is acceptable but XFS is recommended for heavy workloads.

Add mount options noatime,nodiratime,nobarrier (the latter is specific to XFS).

4. Kernel Parameter Optimizations

Adjust vm.swappiness to reduce swap usage (avoid setting to 0 on RHEL7/CentOS7+ to prevent OOM).

Tune vm.dirty_background_ratio and vm.dirty_ratio to ensure steady flushing of dirty pages.

Set net.ipv4.tcp_tw_recycle and net.ipv4.tcp_tw_reuse to 1 to reduce TIME_WAIT and improve TCP efficiency.

5. MySQL Parameter Recommendations

Set default-storage-engine=InnoDB and discontinue MyISAM.

Configure innodb_buffer_pool_size to 50‑70% of physical RAM for single‑instance InnoDB workloads.

Enable innodb_file_per_table=1 for separate tablespaces.

Set innodb_data_file_path=ibdata1:1G:autoextend instead of the default 10M.

Use innodb_log_file_size=256M and innodb_log_files_in_group=2 for most scenarios.

Adjust max_connections and max_connection_error based on workload.

Set open_files_limit, innodb_open_files, table_open_cache, and table_definition_cache to roughly ten times max_connections.

Reduce key_buffer_size to about 32M and disable the query cache.

Avoid overly large tmp_table_size, max_heap_table_size, sort_buffer_size, join_buffer_size, read_buffer_size, and read_rnd_buffer_size.

Typical MySQL High‑Availability Architectures

1. Master‑Slave Replication

MySQL’s built‑in replication copies binary logs from the master to slaves, replaying them to keep data consistent. Combining replication with a failover manager such as keepalived can achieve 99.999% SLA.

2. MMM / MHA Solution

MMM provides a scalable script suite for monitoring, failover, and management of MySQL master‑master replication. In a typical dual‑master, multi‑slave setup, replication ensures only one node is writable at any time, and MMM automatically switches to the other master on failure.

3. Heartbeat + SAN Solution

Failover is handled by the Heartbeat cluster manager, which monitors node health and restarts services on a surviving node. Data is shared via a Storage Area Network (SAN), achieving about 99.990% SLA.

4. Heartbeat + DRBD Solution

Similar to the previous design, Heartbeat manages failover, but data replication uses DRBD, a block‑level mirroring tool that synchronizes disks over the network without shared storage.

Classic MySQL Application Architecture Example

In a typical deployment, servers dbm157 (master) and dbm158 (standby) form a high‑availability write cluster using Heartbeat, DRBD, and MySQL. Read traffic is served by slaves dbs159, dbs160, and dbs161 behind an LVS + Keepalived load‑balancing layer, providing a virtual IP for front‑end application servers.

The write cluster synchronizes data from the master to the standby via DRBD, while the read slaves replicate from the master using MySQL’s native replication. LVS with Keepalived ensures failover and load distribution for read requests.