Checklists / MySQL

The MySQL production and performance checklist.

37 checks across configuration, backups and recovery, monitoring, indexes and queries, schema and table maintenance, connections and pooling, security, high availability and upgrades. Tick items off as you go. Your progress is saved in this browser.

1. Configuration

• innodb_buffer_pool_size set to roughly 50-75% of RAM on a dedicated server It caches data and indexes. The default of 128MB is far too small for production.
• Redo log sized for your write load (innodb_redo_log_capacity on 8.0.30+, innodb_log_file_size before) Too small and checkpoints stall writes during bursts. Too large and crash recovery takes longer.
• innodb_flush_log_at_trx_commit and sync_binlog are set on purpose Setting both to 1 is fully durable. Relaxing them trades up to a second of committed transactions for speed, so make it a decision.
• Strict sql_mode and the utf8mb4 character set Strict mode rejects bad data instead of silently truncating it. utf8mb4 is real UTF-8, while utf8 is a 3-byte subset.
• max_connections is deliberate, with pooling in front of it Each connection allocates its own buffers, so a high limit can exhaust RAM under load.

2. Backups and recovery

• Automated full backups are running on a schedule Use mysqldump --single-transaction for small databases, Percona XtraBackup or MySQL Enterprise Backup for large ones, or your managed provider's snapshots.
• Binary logging is enabled for point-in-time recovery Replay binary logs on top of a full backup to restore to just before a bad deploy or an accidental DELETE.
• A restore has been tested end to end, and timed Know your real recovery time.
• Backups are stored off the database host, in another account or region Losing the server or the cloud account must not also lose the backups.
• Retention is defined, binlog expiry is set, and backup failures raise an alert binlog_expire_logs_seconds should keep enough for recovery and replicas without filling the disk.

3. Monitoring

• The slow query log is on, with long_query_time set A threshold of 0.5s to 1s captures the outliers without drowning in noise. Summarize it with pt-query-digest.
• Performance Schema and the sys schema are enabled sys.statement_analysis ranks queries by total cost across your workload, not just single slow runs.
• Alerts exist for disk space, connection count, and replication lag A full disk stops writes. Hitting max_connections takes the application down.
• Long-running transactions are visible They hold locks and keep old row versions alive, which grows the undo history.

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

4. Indexes and queries

• The top queries have been checked with EXPLAIN ANALYZE (MySQL 8.0.18+) Compare estimated and actual rows and timing per step to see where the time goes.
• Composite indexes match your filters and sort order Column order matters: equality columns first, then range columns, then the ORDER BY.
• Unused and redundant indexes have been reviewed Every index slows down writes. Check sys.schema_unused_indexes and sys.schema_redundant_indexes after a representative uptime period.
• No implicit type or collation conversions in WHERE and JOIN conditions Comparing a string column to a number, or joining columns with different collations, can stop MySQL from using the index.
• N+1 query patterns and SELECT * have been found and removed Hundreds of tiny queries per request look fast individually and still dominate your total cost.

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes;

5. Schema and table maintenance

• Every InnoDB table has an explicit primary key Without one, InnoDB creates a hidden key, and row-based replication on tables without keys can be very slow.
• Table statistics are fresh (ANALYZE TABLE after bulk loads) Stale statistics lead the optimizer to pick bad plans.
• Large schema changes use online DDL, gh-ost, or pt-online-schema-change A plain ALTER TABLE on a big table can block writes or lag replicas for a long time.
• Write-heavy, delete-heavy, or large-value tables have been evaluated for an LSM engine such as TidesDB, on a staging copy LSM engines write less to disk than InnoDB's B-tree on write-heavy tables, and TidesDB's own sysbench results show 2x to 6x InnoDB throughput. It is young: Percona calls its MySQL 9.7 plugin experimental and not ready for production traffic, and lists replication and foreign keys as unsupported there. Check which MySQL or MariaDB versions it supports, and treat the benchmarks as vendor-run until you reproduce them.
• Long transactions and InnoDB history list length are watched A long-open transaction stops purge, which bloats undo logs and slows reads.

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', 'information_schema', 'performance_schema');

6. Connections and pooling

• A pooler (ProxySQL or an application-side pool) controls how many connections reach the database It lets many clients share a small number of server connections.
• Application pool sizes were calculated, not guessed Pool size per instance times the number of instances must fit within what the server can handle.
• wait_timeout and interactive_timeout are set They stop abandoned connections from piling up.
• An alert fires before Threads_connected approaches max_connections Once you hit the limit, new connections fail with 'Too many connections'.

7. Security

• The database is not reachable from the public internet Use private networking, a VPN, or an allowlist. Check bind-address and your firewall or security groups.
• No anonymous users, no remote root login, no test database Run mysql_secure_installation on self-hosted installs, or audit mysql.user directly.
• Each application connects with its own least-privilege account Avoid GRANT ALL on *.*. Limit the blast radius of a leaked credential or an SQL injection bug.
• TLS is required (require_secure_transport) and accounts use caching_sha2_password Without TLS, credentials and data cross the network in plain text.
• Patch releases are applied on a schedule Patch releases carry security and bug fixes and are designed to be safe to apply.

8. High availability and upgrades

• At least one replica exists, using GTID replication, and lag is monitored A replica is both your failover target and a place to move read traffic.
• Failover has been tested and the procedure is written down Confirm the replica promotes, the app reconnects, and see how much data you lose.
• Your MySQL version is still supported MySQL 5.7 reached end of life in October 2023 and 8.0 in April 2026. Check Oracle's lifecycle page for your version.
• An upgrade plan exists and has been tested on a copy of production data Run the MySQL Shell upgrade checker (util.checkForServerUpgrade) and rehearse the rollback.

Frequently asked questions