MySQL my.cnf Workload Review Prompt
Review a my.cnf against a described workload and host, flagging risky, missing, and outdated settings.
- Target user
- DBAs and SREs auditing a MySQL/MariaDB configuration
- Difficulty
- Intermediate
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior MySQL/MariaDB DBA performing a configuration review. You know which my.cnf settings matter (innodb_buffer_pool_size, innodb_log_file_size/redo capacity, innodb_flush_log_at_trx_commit, innodb_flush_method, max_connections, the per-connection buffers like sort_buffer_size and join_buffer_size, tmp_table_size/max_heap_table_size, character-set settings), which are dangerous, and which are leftover cargo-cult from old guides (e.g. query_cache on modern MySQL). I will provide: - The full `[mysqld]` section of my.cnf: [PASTE MY.CNF] - Host resources (RAM, CPU, storage type): [PASTE] - MySQL/MariaDB version: [PASTE] - The workload: OLTP vs analytics, read/write ratio, connection count, peak concurrency, durability requirement: [DESCRIBE] Review it: 1. **Memory budget.** Add up the buffer pool plus the worst-case per-connection memory (per-connection buffers x max_connections) plus temp tables, and check it fits in RAM with OS headroom. Flag any setting that risks swap or OOM at peak. 2. **Durability and crash safety.** Check innodb_flush_log_at_trx_commit, sync_binlog, and innodb_flush_method against the stated durability requirement. Do not silently weaken durability. 3. **Obsolete / dangerous settings.** Flag deprecated or removed options for this version (query cache, old InnoDB flags), settings that are wildly off for the host, and anything copy-pasted that doesn't fit the workload. 4. **Missing settings** that this workload needs (e.g. character-set-server=utf8mb4, appropriate redo log size, slow query log enabled, max_connections sized to the app pool). 5. **Charset/collation** defaults — recommend utf8mb4 with a sane collation if not already set. Output: (a) Memory budget table, (b) Risky settings to change now, (c) Obsolete settings to remove, (d) Missing settings to add, (e) Recommended diffs with rationale, (f) Which need a restart. Guardrails: change a few settings at a time and measure; apply on a replica or staging host first and back up my.cnf and data; do not weaken durability (flush_log_at_trx_commit, sync_binlog) below what the app needs without sign-off; restart-required changes need a maintenance window; never apply config changes to prod without review.
Why this prompt works
Most production my.cnf files are archaeological layers of copied snippets, half of them tuned for a different host or a MySQL version that no longer exists. This prompt attacks that directly by demanding a memory budget first: buffer pool plus worst-case per-connection buffers times max_connections plus temp tables, checked against RAM. That single calculation catches the most common and most dangerous misconfiguration — settings that look fine at idle but drive the server into swap or OOM at peak concurrency.
It separates the three kinds of problems a config review must find: settings that are actively dangerous, settings that are obsolete (the query cache being the classic example removed in MySQL 8), and settings that are simply missing for the workload. Treating these as distinct categories produces a far more actionable review than a flat list of “consider tuning these.”
The guardrails keep the human in control of the irreversible trade-offs. Durability settings like innodb_flush_log_at_trx_commit and sync_binlog directly control whether committed transactions survive a crash, so the prompt refuses to weaken them without explicit sign-off, flags which changes need a restart, and insists on replica testing. That is the difference between a config review that improves stability and one that quietly introduces a data-loss window.