🛠️ Tuning my.cnf for Your Workload With AI

There’s a genre of MySQL advice that’s just a my.cnf someone posted in 2014, copied across a thousand servers with no thought to whether the workload behind any of them resembles the original. I’ve cleaned up the aftermath plenty of times: a max_connections of 4000 on a box with 8 GB of RAM that OOM-kills under load, a query_cache_size set on a version where the query cache no longer exists, a tmp_table_size so small that every GROUP BY spills to disk. Config tuning has to start from what your database is actually doing, and that lives in the status counters.

The counters are the data; the config is the response. AI is good at reading the counters, telling you which ones are out of healthy range, and proposing a config change tied to a specific number. I use it that way and then I change one setting at a time and watch the counter move. The model interprets; the running server is the only thing that confirms.

Gather the real workload signals first

Before touching my.cnf, I pull the counters that describe the workload. The key ones:

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Table_open_cache%';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';

These tell the story: how many connections you really peak at (not the theoretical max), how often temp tables spill to disk, whether the table cache is churning, whether connections are being aborted. I let this run across a representative window so I’m tuning to real traffic, not a quiet moment.

Let AI map counters to config

I paste the counters plus the current my.cnf and the box’s RAM and core count. The prompt:

Here are my MySQL global status counters, my current my.cnf, and the server has 32 GB RAM and 8 cores. Tell me which counters indicate a misconfiguration and what specific config change addresses each one. For every recommendation, cite the counter and value that justifies it, and account for total RAM so we don’t oversubscribe memory. Flag any settings in my.cnf that are deprecated or removed in my version.

A grounded response:

Created_tmp_disk_tables is 41% of Created_tmp_tables, which is high — many sorts and group-bys are spilling to disk because tmp_table_size/max_heap_table_size (currently 16M) are too small. Raise both to 64M. Max_used_connections peaked at 180 but max_connections is 2000; each connection reserves per-thread buffers, so 2000 is wasting memory it will never use — lower it to ~400 for headroom. Your config sets query_cache_size, which is removed in MySQL 8.0; delete it or the server may warn/fail. With 32 GB RAM, innodb_buffer_pool_size of 8G is conservative — 20-22G is reasonable here given nothing else heavy runs on the box.

Every claim is tied to a number. That’s what I demand, because a recommendation without a counter behind it is just the 2014 template again.

The config changes, made deliberately

The resulting my.cnf section might look like:

[mysqld]
innodb_buffer_pool_size = 22G
max_connections         = 400
tmp_table_size          = 64M
max_heap_table_size     = 64M
table_open_cache        = 4000
table_definition_cache  = 2000
join_buffer_size        = 4M
sort_buffer_size        = 4M

The per-session buffers — join_buffer_size, sort_buffer_size, read_buffer_size — are the ones I’m most cautious about, because they’re allocated per connection. A 16M sort_buffer_size times 400 connections is 6.4 GB that can appear under load and OOM the box. AI sometimes recommends generous per-session buffers without doing that multiplication, so I always make it show the worst-case total: buffer_size * max_connections. If that number plus the buffer pool exceeds RAM, the config is dangerous regardless of how good each individual value looks.

Change one knob, watch its counter

Most of these can be set dynamically, so I test without restarts:

SET GLOBAL tmp_table_size = 67108864;
SET GLOBAL max_heap_table_size = 67108864;

Then I let traffic flow and re-check the exact counter the change was meant to fix. Did Created_tmp_disk_tables as a fraction of Created_tmp_tables actually drop? If yes, the change earned its place in my.cnf. If the ratio didn’t move, the spill was caused by something the buffer size can’t fix — a BLOB/TEXT column in the temp table, which forces disk regardless of size — and I revert. This one-knob-at-a-time loop is the entire discipline, and it’s what separates real tuning from cargo-culting. The MySQL category covers the InnoDB-specific knobs separately.

The failure modes to guard against

• Memory oversubscription. The single most dangerous AI mistake is recommending memory totals that exceed RAM. Always give it the RAM number and make it sum buffer pool plus worst-case per-session buffers.
• Deprecated variables. Models trained on older content suggest query_cache_size, innodb_additional_mem_pool_size, and other removed settings. Cross-check every variable against your version, or the server may refuse to start.
• Tuning to a quiet window. Counters from a low-traffic period lie. Gather them under representative load.
• Restart surprises. Some settings only apply on restart. I make AI label which changes are dynamic and which need a bounce, so a “harmless” config push doesn’t turn into an unplanned restart.

I keep a config-review prompt saved with the “cite the counter,” “account for total RAM,” and “flag deprecated variables” instructions baked in — there’s a starter at /prompts/. AI reads the workload counters and proposes a config tied to real numbers far faster than I’d do it by hand. The running server, watched one counter at a time, is the only thing that confirms the change helped. Tune to your data, not to someone else’s template, and let the counters — not a chat window — have the final word.