Effective optimization depends on good observability. The slow query log, enabled via slow_query_log and thresholded by long_query_time, captures queries that exceed runtime budgets; log_queries_not_using_indexes can additionally flag queries that scan without index use, and log_slow_admin_statements extends coverage to slow administrative commands, though these options must be used carefully on busy systems to avoid log floods. Analyzing the slow query log regularly identifies the most expensive queries so that effort can be prioritized where it has the biggest impact. performance_schema provides fine-grained instrumentation on waits, locks, IO, and statement statistics, and the sys schema offers curated views on top of it—making it easy to find top queries by digest, hot tables, or IO hotspots. Pairing slow logs with performance_schema data gives both pattern-level and resource-level insight. External slow-log analyzers such as Percona Toolkit's pt-query-digest aggregate and rank queries by time and frequency. Beyond averages, monitoring query latency percentiles such as p95 and p99 reveals tail latencies that averages hide, which is critical because the worst user experiences often dominate perceived performance. Digest-based aggregation (for example, events_statements_summary_by_digest) groups similar queries regardless of literal values, surfacing patterns rather than individual calls, while SHOW PROCESSLIST or events_statements_current help observe current activity.
When a single MySQL instance reaches its limits, scaling strategies come into play. Replication copies changes from a primary to one or more replicas, allowing reads to be offloaded; row-based replication is generally more reliable and predictable than statement-based, and GTID-based replication simplifies failover and topology changes by uniquely identifying transactions across servers. Semisynchronous replication waits for at least one replica to acknowledge a transaction before commit, trading some latency for reduced data loss risk, and parallel replication lets a replica apply independent transactions concurrently, benefiting workloads with transactions spread across different databases or partitions. Group Replication provides a fault-tolerant multi-primary or single-primary cluster with automatic failover, though consensus and conflict checks add coordination overhead that can raise write latency. Read-only replicas are a particularly effective way to isolate heavy analytics from OLTP, but replication lag means that reads on replicas can be stale, and many replicas multiply replication traffic on the primary. Connection pooling, often paired with smart proxies such as ProxySQL or MaxScale, reuses connections and can do read/write splitting, query caching, and failover routing without changes to application code, which is preferable to raising max_connections arbitrarily high. When write capacity is the bottleneck, sharding distributes data across multiple databases or servers, though at the cost of application complexity and the difficulty of cross-shard queries; per-user or per-host resource limits can also protect stability by preventing one tenant from consuming all connections.
Operational practices tie all of this together. Backups—logical via mysqldump or physical via hot backup tools—affect performance during the backup window, so they should be scheduled off-peak, run from replicas, or use incremental methods. Partitioning a large table, especially by RANGE on dates, enables partition pruning so that queries scan only relevant data, while dropping old partitions is much cheaper than deleting rows; the partition key should be included in indexes so pruning can work alongside index access, and over-partitioning must be avoided because too many partitions increase overhead and slow metadata operations. Sliding-window partitioning strategies that expire data by dropping whole partitions are an effective way to manage time-series or log data, and data archiving in general keeps hot tables lean by moving cold rows into archive tables or external storage. Online DDL allows schema changes with minimal locking, but each operation should still be tested, because some algorithms still need long metadata locks or large temporary space. Heavy analytical queries should be isolated from OLTP, either by running them on dedicated replicas or by pre-aggregating results in summary or rollup tables. Triggers add hidden work on every row modification and can become performance hotspots on heavy-write tables, so their logic is sometimes better placed in application or batch code. Heavy batch work should be broken into smaller chunks, use multi-row INSERTs or LOAD DATA INFILE for bulk loads with precautions around secondary indexes and foreign keys, and avoid running during peak traffic. Feature flags, canaries, and gradual rollout limit blast radius and let you monitor impact before full deployment, and circuit breakers and exponential backoff prevent connection storms and cascading failures from overwhelming MySQL. Index reasoning should be documented, expensive queries should be tagged with feature names so they can be correlated with metrics, and quarterly performance reviews help catch drift from new features or data growth. Finally, every change should be tested in a staging environment with before-and-after metrics and a rollback plan, with version upgrades staged carefully and, where possible, new indexes first tried out on replicas.