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.
Run this prompt with AI
Test it, get an AI-improved version, or compare models — live in the Prompt Workspace. No copy-paste.
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.
Related prompts
-
MySQL InnoDB Buffer Pool & Config Tuning Prompt
Right-size the InnoDB buffer pool and related memory/IO settings for a described workload and host.
-
MySQL Durability & Flush Settings Tuning Prompt
Balance InnoDB durability against throughput by reviewing flush, sync, and doublewrite settings for a given workload and hardware.
-
MySQL Per-Connection Buffer & Memory-Blowup Tuning Prompt
Size per-connection buffers (sort/join/read/tmp) to stop OOM kills and RSS growth under connection load.
-
MySQL InnoDB Redo Log & Checkpoint Stall Tuning Prompt
Diagnose write stalls and checkpoint bursts caused by an undersized or misconfigured InnoDB redo log, and size redo capacity and flushing for the workload.
More MySQL prompts & error guides
Browse every MySQL prompt and troubleshooting guide in one place.
Reading prompts? Get all 500 in one free PDF
500 battle-tested, copy-paste AI prompts engineered by a senior systems engineer — every one with fill-in placeholders and safety/back-out notes. Drop your email and it's yours.
- 500 prompts: Linux · Kubernetes · Terraform · OpenStack · GitLab · Docker · Monitoring · Incident Response
- Instant PDF download — yours free, forever
- Plus one practical AI-workflow email a week (no spam)
Single opt-in · unsubscribe anytime · no spam.