Databases No. 200

MySQL Tuning skill for Claude Code

Fix the top digests, keep replicas caught up. Claude ranks statements by total latency from performance_schema, reads EXPLAIN ANALYZE to spot scans, filesorts and implicit conversions, and builds composite indexes in InnoDB's clustered-key reality. It sizes the buffer pool and redo log, diagnoses replica lag by cause, retires indexes via INVISIBLE first, and picks INSTANT, INPLACE or gh-ost per schema change.

namemysql-tuning
size169 lines · ≈2,200 tokens
files2
licenseMIT
updatedOct 20, 2026

What Claude sees before loading it

This description is the only part of the skill that sits in context all the time. Claude reads it to decide when to load the full playbook below.

description: Tunes MySQL 8.0/8.4 LTS and MariaDB servers and queries - InnoDB buffer pool and redo log sizing, reading EXPLAIN and EXPLAIN ANALYZE, composite and covering indexes, invisible indexes for safe drops, performance_schema and sys schema digests, replication lag diagnosis, and online schema changes with ALGORITHM=INSTANT/INPLACE, gh-ost, or pt-online-schema-change. Use when a MySQL query is slow, the slow log fills up, replicas lag, ALTER TABLE would lock a big table, or reviewing my.cnf settings.

Try asking

Our MySQL slow log is full of this orders query - fix it
The read replica lags 20 minutes every night at 2am, find out why
Change this column type on a 400M-row table without locking writes

Use it when

A MySQL or MariaDB query is slow or the slow log is growing
Replicas fall behind the source
An ALTER TABLE targets a large, busy table
Reviewing my.cnf or a parameter group after an upgrade to 8.4

Skip it when

The database is PostgreSQL - use sql-query-tuning
The problem is lock contention design in application code - use transactions-locking

How to check the result

Target digests show lower total latency in performance_schema over a comparable window, EXPLAIN ANALYZE uses the new index, and heartbeat lag stays under threshold at peak.

The common mistake it prevents

Running an INSTANT or INPLACE ALTER while a long transaction holds the table: the metadata lock wait queues every later query behind the ALTER and stalls the app.

~/.claude/skills/mysql-tuning/SKILL.md

--- name: mysql-tuning description: Tunes MySQL 8.0/8.4 LTS and MariaDB servers and queries - InnoDB buffer pool and redo log sizing, reading EXPLAIN and EXPLAIN ANALYZE,… license: MIT ---

Tuning is done when the top statements by total time (from performance_schema, not guesses) use selective indexes, the buffer pool holds the working set, replicas keep up at peak, and every schema change on a large table has a non-blocking plan. Change one setting at a time and measure.

Workflow

- [ ] 1. Identify version, flavor, and hosting (8.0, 8.4 LTS, 9.x, MariaDB; RDS/Aurora/self-managed)
- [ ] 2. Find the top statements by total latency
- [ ] 3. EXPLAIN / EXPLAIN ANALYZE each; fix indexes or the query
- [ ] 4. Check server-level health: buffer pool, redo, temp tables, connections
- [ ] 5. Check replication lag and its cause
- [ ] 6. Plan schema changes with the least-locking method
- [ ] 7. Verify with before/after digests

Run the queries in references/diagnostic-queries.md for steps 2, 4, and 5; they assume performance_schema is on (default) and the sys schema exists.

2. Top statements

SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg, rows_sent_avg
FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 15;

Rank by total latency, not average: a 3 ms query run 2M times beats a 4 s nightly report. A high rows_examined_avg / rows_sent_avg ratio marks a missing or wrong index.

3. EXPLAIN

EXPLAIN FORMAT=TREE SELECT ...;     -- plan shape
EXPLAIN ANALYZE SELECT ...;         -- runs the query; actual rows and time per step (8.0.18+)

Column / note	Bad sign	Fix
`type: ALL`	full table scan on a big table	index the filter columns
`type: index`	full index scan	index is not selective for the predicate
`key: NULL` with `possible_keys` listed	optimizer rejected the index (stats, low selectivity, type mismatch)	`ANALYZE TABLE`, fix implicit conversion, check selectivity
`rows` far above actual (ANALYZE)	stale or missing statistics	`ANALYZE TABLE t`; histograms for non-indexed columns
`Using filesort` on a large set	ORDER BY not served by index	extend the index with the sort columns
`Using temporary`	GROUP BY/DISTINCT materializes	index the group columns or rewrite
`Using index`	covering, good	-

Index rules for InnoDB:

Composite order: equality columns first, then one range or sort column. Columns after the first range condition do not narrow the index scan.
Every secondary index implicitly ends with the primary key, so (status) already covers WHERE status = ? ORDER BY id. Keep primary keys short (BIGINT or ordered UUIDs stored as BINARY(16)); random UUID PKs fragment the clustered index.
Implicit conversions kill index use: WHERE phone = 5551234 on a VARCHAR column, or joining utf8mb4_0900_ai_ci to utf8mb3 columns. Match types and collations.
Functions on indexed columns (WHERE DATE(created_at) = ...) need a range rewrite or a functional index: CREATE INDEX idx ON t ((DATE(created_at))); (8.0.13+).
Histograms help non-indexed filter columns: ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

Safe index drops: make it invisible first, watch for regressions, then drop with approval.

ALTER TABLE orders ALTER INDEX idx_legacy INVISIBLE;
-- observe a full business cycle; revert with VISIBLE if anything slows
ALTER TABLE orders DROP INDEX idx_legacy;

Candidate lists come from sys.schema_unused_indexes (since last restart; check every replica) and sys.schema_redundant_indexes.

4. Server settings that matter

Setting	Guidance
`innodb_buffer_pool_size`	60-75% of RAM on a dedicated host; check hit ratio and `Innodb_buffer_pool_wait_free`. Resizable online
`innodb_dedicated_server=ON`	lets MySQL size buffer pool and redo automatically on dedicated hosts; do not combine with manual values
`innodb_redo_log_capacity`	8.0.30+ replaces `innodb_log_file_size`; size for roughly an hour of peak redo writes
`innodb_flush_log_at_trx_commit`	1 for durability (default). 2 trades up to ~1 s of commits on OS crash for throughput - user decision
`sync_binlog`	1 (default) for crash-safe replication
`innodb_io_capacity` / `_max`	8.4 defaults are SSD-oriented (10000 / 2x); lower for spinning disks
`max_connections`	do not raise blindly; each connection uses per-thread buffers. Pool in the app or use ProxySQL
`tmp_table_size` / `max_heap_table_size`	raise only if `Created_tmp_disk_tables` is high and the queries cannot be fixed
`long_query_time`	0.5-1 s with `slow_query_log=ON`; `log_queries_not_using_indexes` only briefly

MySQL 8.4 changed defaults (for example innodb_adaptive_hash_index=OFF, innodb_change_buffering=none, higher io_capacity). When upgrading from 8.0, compare SHOW VARIABLES before and after rather than assuming old values carried over. 8.4 also disables mysql_native_password by default; old clients must use caching_sha2_password.

Managed services (RDS, Aurora, Cloud SQL) set many of these through parameter groups and ignore some (Aurora has no traditional redo log); change them there, not in my.cnf.

5. Replication lag

SHOW REPLICA STATUS\G      -- 8.0.22+ naming; Seconds_Behind_Source, Replica_SQL_Running

Seconds_Behind_Source is coarse and can read 0 while stalled. For real lag, use a heartbeat table (pt-heartbeat) or compare GTID sets.

Cause	Evidence	Fix
One huge transaction (bulk UPDATE/DELETE)	lag jumps then drains	batch writes in chunks of 1k-10k rows with commits
Single-threaded apply	high source write rate, replica CPU one core busy	`replica_parallel_workers` > 1 (default 4 in 8.0.27+), `replica_preserve_commit_order=ON`
Tables without primary keys under ROW format	full scans per row event on the replica	add primary keys (`sql_require_primary_key=ON` for new tables)
Replica undersized or doing heavy reads	I/O or CPU saturated	separate reporting replica, bigger instance
DDL on source	lag equal to ALTER duration	online schema change tools (below)

6. Schema changes on big tables

Check the cheapest method first:

ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INSTANT;   -- 8.0.29+: any position
ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;

Stating ALGORITHM and LOCK makes MySQL error out instead of silently choosing a blocking copy. Even INSTANT/INPLACE need a brief metadata lock: a long-running transaction on the table blocks the ALTER, and every query queues behind the ALTER. Set SET SESSION lock_wait_timeout = 5; and retry.

Change	Method
Add nullable column, drop column (8.0.29+), rename column, change default	INSTANT
Add secondary index, most VARCHAR extensions within the same length-byte range	INPLACE, LOCK=NONE (replica still lags by build time)
Change column type, change PK, convert charset on big tables	gh-ost or pt-online-schema-change

gh-ost --host=replica1 --database=shop --table=orders \
  --alter="MODIFY amount DECIMAL(12,2) NOT NULL" \
  --max-load=Threads_running=25 --critical-load=Threads_running=100 \
  --chunk-size=1000 --postpone-cut-over-flag-file=/tmp/ghost.postpone --execute

gh-ost is triggerless (reads the binlog; needs binlog_format=ROW, binlog_row_image=FULL) and has limited foreign key support. pt-online-schema-change uses triggers and handles FKs with --alter-foreign-keys-method. Run either with --dry-run first.
Run without --execute and schedule the cut-over with the user; cut-over is the risky moment.

Gotchas

utf8 means utf8mb3 (3-byte, no emoji). Use utf8mb4.
SELECT ... FOR UPDATE under REPEATABLE READ (default) takes gap/next-key locks on ranges; unindexed predicates lock far more rows than intended.
COUNT(*) on large InnoDB tables scans an index; keep a counter table or use estimates.
OFFSET pagination degrades linearly; use keyset pagination on an indexed column.
Query cache was removed in 8.0; recommendations to enable it are outdated.
MariaDB has diverged (different optimizer, ANALYZE FORMAT=JSON, no sys by default in older versions, different JSON type); check flavor before copying syntax.

Verification

Re-run sys.statement_analysis (or reset digests with CALL sys.ps_truncate_all_tables(FALSE) with approval) and compare total latency for the target digests over the same traffic window.
EXPLAIN ANALYZE shows the new index, and actual rows examined drop.
Replica lag at peak stays under the agreed threshold (e.g. under 5 s) on a heartbeat.
For schema changes: dry run succeeded, row counts match, cut-over time recorded.

Bundled files

Loaded only when a step in the skill needs them. Review them here before installing.

references/diagnostic-queries.md 109 lines · 4.2 KB

# MySQL diagnostic queries

Read-only queries for MySQL 8.0/8.4. Run on the primary and on each replica where the
question is per-instance (index usage, lag). performance_schema counters reset on restart.

## Statements

```sql
-- Top digests by total time with full-scan and temp-table signals
SELECT schema_name, LEFT(digest_text, 120) AS q, count_star,
       ROUND(sum_timer_wait/1e12, 1) AS total_s,
       ROUND(avg_timer_wait/1e9, 2) AS avg_ms,
       sum_rows_examined, sum_rows_sent,
       sum_no_index_used, sum_created_tmp_disk_tables
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 20;

-- Statements doing full scans
SELECT * FROM sys.statements_with_full_table_scans ORDER BY total_latency DESC LIMIT 10;

-- Statements spilling temp tables to disk
SELECT * FROM sys.statements_with_temp_tables ORDER BY disk_tmp_tables DESC LIMIT 10;
```

## Indexes

```sql
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'shop';
SELECT table_name, redundant_index_name, dominant_index_name, sql_drop_index
FROM sys.schema_redundant_indexes WHERE table_schema = 'shop';

-- Index cardinality and size per table
SELECT index_name, stat_value AS pages
FROM mysql.innodb_index_stats
WHERE database_name = 'shop' AND table_name = 'orders' AND stat_name = 'size';

-- Tables without a primary key (replication and InnoDB clustering problems)
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON c.table_schema = t.table_schema AND c.table_name = t.table_name AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL
  AND t.table_schema NOT IN ('mysql','sys','performance_schema','information_schema');
```

## Buffer pool and I/O

```sql
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- miss ratio = Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests; aim well under 1%

SELECT * FROM sys.innodb_buffer_stats_by_table ORDER BY allocated DESC LIMIT 10;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';   -- nonzero: pool too small or flushing too slow
SHOW ENGINE INNODB STATUS\G    -- LATEST DETECTED DEADLOCK, history list length, pending I/O
```

History list length growing into the millions means a long-running transaction is
preventing purge; find it below.

## Connections and locks

```sql
SHOW GLOBAL STATUS LIKE 'Threads_%';           -- Threads_running sustained > CPU cores = saturation
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

-- Long transactions
SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
       trx_mysql_thread_id, LEFT(trx_query, 120) AS q
FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 10;

-- Who blocks whom
SELECT * FROM sys.innodb_lock_waits\G

-- Metadata lock waits (e.g. ALTER stuck behind an open transaction)
SELECT * FROM sys.schema_table_lock_waits\G
```

Killing a session (`KILL <id>`) rolls back its transaction, which can take as long as the
work it did. Ask the user before killing anything on production.

## Replication

```sql
SHOW REPLICA STATUS\G
-- Key fields: Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source,
-- Last_SQL_Error, Retrieved_Gtid_Set vs Executed_Gtid_Set

SELECT * FROM performance_schema.replication_applier_status_by_worker\G
-- per-worker last error and applied transaction timestamps

SHOW VARIABLES LIKE 'replica_parallel%';
SHOW VARIABLES LIKE 'binlog_transaction_dependency_tracking';  -- WRITESET helps parallel apply (removed/default in newer versions)
```

## Configuration snapshot

```sql
SELECT variable_name, variable_value FROM performance_schema.global_variables
WHERE variable_name IN ('innodb_buffer_pool_size','innodb_redo_log_capacity','innodb_flush_log_at_trx_commit',
  'sync_binlog','innodb_io_capacity','innodb_io_capacity_max','max_connections','innodb_adaptive_hash_index',
  'innodb_change_buffering','binlog_format','transaction_isolation','long_query_time','slow_query_log');

-- Settings changed from compiled defaults and where they came from
SELECT variable_name, variable_source, variable_path
FROM performance_schema.variables_info WHERE variable_source <> 'COMPILED';
```

Save this snapshot before and after any configuration change and attach both to the
change note.

About this skill

Written and maintained by the Claude Skills editors to the same editorial standard as every skill in the directory: a selective trigger, a real workflow, known pitfalls and a verification step. First published Oct 20, 2026, last updated Oct 20, 2026. MIT licensed.

Install

no restart needed

curl -fsSL https://claudeskills.page/install.sh | sh -s -- mysql-tuning

Installs to ~/.claude/skills/ — available in every project.

curl -fsSL https://claudeskills.page/install.sh | sh -s -- --project mysql-tuning

Installs to ./.claude/skills/ — commit it to share with your team.

curl -fsSLO https://claudeskills.page/downloads/mysql-tuning.zip
unzip -o mysql-tuning.zip -d ~/.claude/skills/

Download and unzip yourself. Swap the path for .claude/skills/ to scope it to a repo.

& ([scriptblock]::Create((irm https://claudeskills.page/install.ps1))) mysql-tuning

PowerShell 5.1+. Installs to %USERPROFILE%\.claude\skills\.

.zip

On Claude.ai, upload the zip under Settings → Skills. All methods

Archive contents

mysql-tuning/
SKILL.md
references/diagnostic-queries.md

What Claude sees before loading it

Try asking

Use it when

Skip it when

How to check the result

The common mistake it prevents

~/.claude/skills/mysql-tuning/SKILL.md

Workflow

2. Top statements

3. EXPLAIN

4. Server settings that matter

5. Replication lag

6. Schema changes on big tables

Gotchas

Verification

Bundled files

About this skill

Related skills

SQL Query Tuning

Transactions and Locking

Postgres Indexing

Zero-Downtime Migrations

ClickHouse Analytics

Drizzle ORM