Boost MySQL Performance: Hardware, OS, and Configuration Tweaks

1. Hardware Layer Optimizations

Configure the server BIOS and select appropriate hardware to obtain the highest CPU and storage throughput while preventing NUMA‑related performance degradation.

1.1 CPU Settings

Set the BIOS power profile to Performance Per Watt Optimized (DAPC) to run the CPU at maximum frequency; disable power‑saving modes for database workloads.

Turn off C1E and all C‑States to keep the core frequency stable.

Select Maximum Performance for the memory frequency setting.

Enable Node Interleaving in the memory configuration to spread memory accesses evenly across NUMA nodes.

1.2 Disk I/O Optimizations

Prefer SSD or PCI‑e SSD devices; they provide orders of magnitude higher IOPS than spinning disks.

If a RAID controller is used, equip it with a cache module and a battery‑backed unit (BBU) and monitor their health regularly.

Set the RAID write policy to Write‑Back (WB) or Force WB (avoid Write‑Through (WT)) and disable aggressive read‑ahead.

Choose RAID‑10 over RAID‑5 for better write performance and rebuild speed.

When mechanical drives are unavoidable, select high‑speed models (e.g., 15 K RPM rather than 7.2 K RPM).

2. System Layer Optimizations

2.1 Filesystem Tuning

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

Prefer the xfs filesystem. ext4 is acceptable for modest traffic, while ext3 should be avoided.

Mount with noatime, nodiratime and, for XFS, nobarrier to reduce unnecessary metadata writes.

2.2 Kernel Parameter Adjustments

Set vm.swappiness to 5‑10 (or 0 on RHEL 7+ only if you accept possible OOM kills) to keep swap usage low.

Configure vm.dirty_background_ratio to 5‑10 and vm.dirty_ratio to roughly twice that value so dirty pages are flushed steadily.

Enable net.ipv4.tcp_tw_recycle=1 and net.ipv4.tcp_tw_reuse=1 to reduce the number of sockets in TIME_WAIT.

Read‑ahead ( read_ahead_kb) and request‑queue size ( nr_requests) have limited impact on mixed OLTP workloads; adjust only after benchmarking.

3. MySQL Layer Optimizations

3.1 Version Choice

Percona Server for MySQL provides roughly a 20 % performance increase, additional reliability features, and full compatibility with Oracle MySQL. MariaDB is also mature but introduces GTID‑related incompatibilities that may affect migration.

3.2 Key Configuration Parameters

Enable the thread‑pool plugin (available in Percona and MariaDB) to keep latency stable under high concurrency.

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

Allocate innodb_buffer_pool_size to 50‑70 % of physical RAM for a single‑instance InnoDB workload.

Adjust durability settings according to business needs:

innodb_flush_log_at_trx_commit = 1   # full durability
sync_binlog = 1

innodb_flush_log_at_trx_commit = 2   # moderate risk, better throughput
sync_binlog = 10

innodb_flush_log_at_trx_commit = 0   # maximum speed, possible data loss
sync_binlog = 0

Enable innodb_file_per_table=1 to give each table its own tablespace.

Set innodb_data_file_path=ibdata1:1G:autoextend (avoid the default 10 MB).

Configure innodb_log_file_size=256M and innodb_log_files_in_group=2 for most workloads.

Set long_query_time=0.05 (50 ms) to capture slow queries.

Scale max_connections, max_connection_errors, open_files_limit, innodb_open_files, table_open_cache and table_definition_cache to roughly ten times the value of max_connections.

Keep tmp_table_size and max_heap_table_size modest (e.g., ≤ 256 M) to avoid per‑connection OOM; similarly, limit sort_buffer_size, join_buffer_size, read_buffer_size, etc.

Set key_buffer_size≈32M and disable the query cache.

3.3 Schema Design and SQL Practices

Give every InnoDB table an artificial auto‑increment primary key.

Prefer short column lengths and declare NOT NULL whenever possible.

Avoid TEXT / BLOB columns in hot tables; if needed, move them to a separate table.

Never use SELECT *; list only required columns.

When indexing a VARCHAR(N), a prefix of about 50 % of the length is usually sufficient.

Rewrite subqueries as joins whenever feasible.

Ensure join columns share the same data type and are indexed.

Drive multi‑table joins with the smaller result set, not necessarily the smaller base table.

If an ORDER BY is required, the sort column must belong to the driving table to allow index usage.

Prefer composite indexes over many single‑column indexes, especially for low‑cardinality columns.

For pagination, fetch rows by primary‑key range first, then apply LIMIT.

3.4 Operational Recommendations

Keep individual table size below 10 GB and row count under 100 million to reduce the cost of online DDL.

Monitor mysqld memory consumption; avoid OOM and excessive swapping.

Running multiple MySQL instances on one host is only justified when a single instance cannot fully utilize the hardware.

Periodically run Percona Toolkit utilities to maintain index hygiene and replication consistency:

pt-duplicate-key-checker

pt-index-usage

pt-query-digest

pt-kill

pt-online-schema-change

pt-table-checksum

pt-table-sync

These guidelines should be adapted to the specific workload and hardware environment rather than applied blindly.