Mastering MySQL Configuration: Principles, Best Practices, and Sample Settings

Most developers focus on schema design, indexing, and query optimization, often overlooking MySQL configuration; this article aims to fill that gap by explaining how MySQL configuration works and offering practical guidance.

How MySQL Reads Configuration

MySQL obtains settings from command‑line options and configuration files (typically /etc/my.cnf or /etc/mysql/my.cnf on Unix). The server reads the file, strips comments and newlines, and merges the results with command‑line arguments. Always verify which file is actually used (e.g., using mysqld --verbose --help to list the files read).

Configuration File Structure

Each section begins with a bracketed name (e.g., [mysqld], [client]). The server reads the mysqld section; placing options in the wrong section renders them ineffective.

Variable Naming and Scope

Variables use lowercase with underscores or hyphens interchangeably (e.g., max_connections and max-connections). Scopes include global, session, and object‑level. Session variables default to global values and disappear when the connection ends.

Dynamic Variables

Some variables can be changed at runtime using SET GLOBAL or SET SESSION. Changing a global variable does not affect existing sessions; they inherit values only at connection start. Use @@global and @@session prefixes as needed. Both = and := work for SET statements, but := is required for user‑defined variables.

Units and Suffixes

Certain variables accept size suffixes (e.g., 1M = 1024 × 1024 bytes) in configuration files and command‑line arguments, but not in SQL SET statements, where numeric values or expressions must be used.

Good Configuration Habits

Do not infer a variable’s purpose from its name alone; verify its effect.

Avoid changing global values for variables that are usually session‑specific (e.g., sort_buffer_size).

Remember that larger values are not always better; monitor impact after changes.

Document configuration files and keep them under version control.

Bad Configuration Habits

Relying on generic cache‑hit‑rate ratios to set sizes without understanding workload characteristics.

Blindly applying third‑party tuning scripts without considering hardware and business requirements.

Sample Minimal my.cnf

[mysql]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
user = mysql
port = 3306
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid

datadir = /var/lib/mysql/

# MyISAM
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# Safety
max-allowed-packet = 16M
max-connect-errors = 1000000

# Binary logging
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1

# Logging
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log

# Caches and limits
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240

# InnoDB
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 12G

Key Sections Explained

General : user, port, storage engine, socket, PID file.

Data storage : datadir location.

MyISAM : key-buffer-size (index cache) and myisam-recover options.

Safety : packet size limits and connection‑error counter.

Binary logging : enable binlog, set retention, and sync policy.

Logging : error log, slow query log, and index‑usage logging.

Caches and limits : temporary table sizes, query cache disabled, connection limits, thread cache, file limits, table caches.

InnoDB : buffer pool size, log file size/group, flush method, transaction‑commit behavior, per‑table file option.

MyISAM Key Buffer Sizing

Determine the total size of MyISAM indexes (e.g., via

SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MYISAM'

or du -sch $(find /var/lib/mysql -name "*.MYI")) and set key_buffer_size to no more than 25‑50% of that value, adjusting after monitoring usage.

myisam-recover Options

Values include DEFAULT, BACKUP, FORCE, and QUICK. Combine as needed (e.g., BACKUP,FORCE) for small MyISAM tables; avoid for large tables due to long startup checks.

Safety‑Related Variables

max-allowed-packet

defaults to 4 M; increase to 16 M for large BLOB/TEXT data or replication. max-connect-errors defaults to 10; raise in trusted internal networks to avoid accidental lock‑outs.

Logging Configuration

Enable slow query logging and binlog; set expire_logs_days to retain logs for a reasonable period (e.g., 14 days) to prevent disk exhaustion.

Caches and Limits Details

tmp-table-size

and max-heap-table-size should be equal; larger values reduce on‑disk temp tables but increase memory usage. max-connections should be sized based on observed peak connections (monitor max_used_connections). thread-cache-size can be set to 20‑200 depending on threads-connected metrics. open-files-limit should be high (e.g., 65535) to avoid “too many open files” errors.

Table caches ( table-open-cache, table-definition-cache) improve metadata lookup; set generously for many tables.

InnoDB Configuration Highlights

innodb-buffer-pool-size

≈ total RAM – OS reserve – other processes – MyISAM/other caches; typically 70‑80% of RAM on a dedicated MySQL server. innodb-log-file-size and innodb-log-files-in-group control redo log capacity; larger logs reduce checkpoint frequency but increase recovery time. innodb-flush-log-at-trx-commit = 1 (safest), 0 (fastest, risk of loss), or 2 (balance). innodb-flush-method = O_DIRECT on Linux with battery‑backed RAID; otherwise default. innodb-file-per-table should be enabled for easier space reclamation.

Conclusion

MySQL offers a vast array of tunable parameters; start with a sensible minimal configuration, understand each option’s purpose, and iteratively adjust based on monitoring and workload characteristics.

References: High Performance MySQL (3rd ed.), MySQL official documentation, various online articles.