From 100 TPS to 10,000 TPS: A Systematic Tuning Path for MySQL

Reaching 10,000 transactions per second on MySQL is not a single configuration change — it is a sequence of architectural decisions, each addressing the bottleneck that the previous step exposed. This guide walks that sequence from first principles.

Most MySQL performance articles open with a list of variables to paste into my.cnf. The problem with that approach: tuning without a bottleneck model is noise. A buffer pool increase does nothing if you are already hitting a thread concurrency ceiling. A connection pool does nothing if query selectivity is destroying index efficiency at single-digit concurrency.

The correct model is sequential: identify the binding constraint at the current load tier, fix it, verify it, then move to the next tier. The five phases below reflect how production MySQL systems actually scale, from a single tuned instance handling 100–500 TPS through to a distributed architecture capable of sustaining 10,000 TPS under write load.

Phase 1 - InnoDB internals: getting the single instance right (100 → 500 TPS)

Before adding hardware or topology, the single MySQL instance must be correctly sized and configured. Most default configurations are tuned for minimal memory consumption on a development machine, not for production write throughput. Three variables dominate at this phase.

Buffer pool: the single most impactful setting

The InnoDB buffer pool caches data pages, index pages, and the change buffer in memory. Every read that hits the buffer pool avoids a disk I/O. Every read that misses it waits. The health metric is buffer pool hit rate:

SELECT (1 - (
  (SELECT variable_value FROM performance_schema.global_status
   WHERE variable_name = 'Innodb_buffer_pool_reads') /
  (SELECT variable_value FROM performance_schema.global_status
   WHERE variable_name = 'Innodb_buffer_pool_read_requests')
)) * 100 AS hit_rate_pct;

• A healthy hit rate is above 99%. Below 95% means the buffer pool is too small for the working set
• On a dedicated MySQL server, set innodb_buffer_pool_size to 70–80% of total RAM
• If the entire working dataset fits in RAM, set the buffer pool to dataset size plus 20% headroom
• Set innodb_buffer_pool_instances to 1 per GB of buffer pool size to reduce internal mutex contention — for a 32 GB pool, use 32 instances

Redo log capacity: the write throughput governor

• The redo log records every InnoDB page modification before those pages are flushed to disk, it is the WAL (Write-Ahead Log) mechanism that enables crash recovery
• If the redo log fills before dirty pages can be flushed, MySQL stalls all write transactions until space is cleared, these stalls appear as periodic latency spikes under load
• In MySQL 8.0.30+, innodb_redo_log_capacity replaces the older innodb_log_file_size + innodb_log_files_in_group pair
• For write-heavy OLTP workloads, start at 4–8 GB and size upward if write stalls persist. On pre-8.0.30, setting innodb_log_file_size = 2G with 2 log files is a reasonable baseline
• Target: redo log should cover 60–90 minutes of peak write volume to smooth out flush bursts

Flush strategy: durability vs. throughput

innodb_flush_log_at_trx_commit controls when the redo log is synced to durable storage on each commit. This is one of the highest-leverage settings for write-heavy workloads:

IO capacity: matching InnoDB flush rate to storage

• Set innodb_io_capacity to match the actual IOPS capability of the underlying storage: 2,000 for fast SSDs, 400–800 for slower SSDs, 200 for spinning disk
• Set innodb_io_capacity_max to 2–4x innodb_io_capacity to allow burst flushing during high dirty page accumulation
• Use innodb_flush_method = O_DIRECT on Linux to bypass double-buffering between InnoDB and the OS page cache — reduces memory pressure and improves buffer pool efficiency

Phase 2 - Query and schema optimization (500 → 1,500 TPS)

At 500 TPS, InnoDB contention becomes visible. The problem is almost always bad queries holding locks or executing full table scans under concurrent load. No amount of buffer pool tuning compensates for a query that reads 10 million rows to return 3.

Reading the slow query log and EXPLAIN

• Enable slow_query_log with long_query_time = 0.1 (100 ms) — anything slower is a tuning candidate
• Use EXPLAIN FORMAT=JSON to inspect the full execution plan including filtered row estimates, join types, and index usage
• A type: ALL in EXPLAIN on a table larger than a few thousand rows is a blocking issue — it means a full scan is running under every execution at that concurrency level
• Use pt-query-digest from Percona Toolkit to aggregate slow log output and rank queries by cumulative query time, not just worst individual execution

Index strategy for OLTP

• Composite indexes should match the selectivity order of the query's WHERE clause — put the most selective column first
• Covering indexes (where the index contains all columns the query needs) eliminate the secondary lookup to the clustered index — for high-frequency SELECT queries, this is a significant gain
• Avoid over-indexing write-heavy tables — each INSERT, UPDATE, and DELETE must maintain every index on the table, increasing write amplification
• Use pt-duplicate-key-checker to identify redundant indexes that are consuming maintenance cost without providing selectivity benefit

Transaction design: keeping locks short

• InnoDB row locks are held for the duration of the transaction — long-running transactions increase lock contention on hot rows and degrade TPS at the concurrency level, not just at the query level
• Move any non-DB operations (HTTP calls, file writes, computation) outside of transaction boundaries before the first DML statement
• Use SELECT ... FOR UPDATE only where you actually intend to update — unnecessary locking on reads blocks concurrent writers on the same rows
• Monitor lock wait statistics: SHOW STATUS LIKE 'Innodb_row_lock_waits' rising under load indicates transaction design issues, not infrastructure limits

Monitoring checkpoint: At 1,000+ concurrent connections, check Threads_running via SHOW GLOBAL STATUS. If Threads_running exceeds the number of CPU cores consistently, you have either a query problem (long-running queries blocking threads) or a connection problem — addressed in Phase 3. Threads_connected growing without Threads_running growing means connection accumulation, not compute saturation.

Phase 3 - Connection layer: ProxySQL and pooling (1,500 → 3,000 TPS)

Every MySQL connection is a heavyweight OS thread — memory allocation, authentication overhead, and THD (thread handle) lifecycle cost. At high concurrency, the overhead of creating and destroying connections becomes a significant fraction of total query latency. The fix is connection pooling at the proxy layer.

Why max_connections is not the solution

• Increasing max_connections beyond the server's thread concurrency capacity does not increase TPS — it increases memory consumption and contention
• A MySQL server maxing out at 5,000 TPS with 200 connections will handle the same 5,000 TPS across 10,000 connections — the additional connections add queue depth, not capacity
• Each MySQL thread consumes approximately 4–8 MB of stack memory — 5,000 connections without pooling can exceed 40 GB of memory on the connection overhead alone

ProxySQL: connection multiplexing and read/write split

• ProxySQL accepts up to 50,000+ client connections and multiplexes them into a backend pool of 200–500 MySQL connections — eliminating per-connection overhead at the server level
• Read/write split routes SELECT queries to replicas and DML to the primary using configurable query rules via regex matching on mysql_query_rules
• Weight-based load balancing across multiple replicas: UPDATE mysql_servers SET weight=10 WHERE hostname='replica1' — no application code changes required
• ProxySQL's query cache (where appropriate) can serve repeated read-heavy queries from memory without hitting MySQL at all — particularly effective for dashboard or reporting queries with predictable parameter sets
• Configuration changes at runtime with zero downtime: LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK — no proxy restart, no connection drop

MySQL thread and connection settings

• Set max_connections on MySQL to match the backend connection count from ProxySQL — typically 500–1,000 — not the application's total client count
• Set wait_timeout and interactive_timeout to 300 seconds to reclaim idle backend connections faster
• Enable thread_cache_size = 64 to reuse thread objects on reconnect instead of destroying and reallocating them

Phase 4 - Read scaling with replicas (3,000 → 6,000 TPS)

Most OLTP workloads are read-dominant typically 70–90% reads by query count. Once the primary is handling writes near its capacity, read replicas absorb the read load and double or triple total system TPS without touching write capacity.

Replication setup and GTID

• Use GTID-based replication (gtid_mode = ON, enforce_gtid_consistency = ON) — it simplifies failover, enables ProxySQL GTID tracking for causal consistency, and avoids position-based replication drift issues
• Use binlog_format = ROW for deterministic replication of DML — statement-based replication can produce divergence on non-deterministic functions
• Set replica_preserve_commit_order = ON with replica_parallel_workers = 4–16 to enable parallel replay on replicas and reduce replication lag under high write load
• Monitor replica lag via Seconds_Behind_Source in SHOW REPLICA STATUS — sustained lag above 5 seconds means replica worker cannot keep up with primary write rate

Scaling read capacity

• Read replicas do not increase write capacity — every write to the primary must still be replicated to every replica, so write load grows with replica count
• For read-heavy workloads (80%+ reads), 3–4 replicas behind ProxySQL can push aggregate system TPS beyond 10,000 while the primary handles 2,000–3,000 writes/second
• Direct long-running reporting or analytics queries to a dedicated replica or a delayed replica — preventing them from competing with OLTP reads on the same server
• Use max_replication_lag = 60 in ProxySQL's mysql_servers table to automatically remove a lagging replica from the read pool before it serves stale data

Phase 5 - Write scaling: partitioning and sharding (6,000 → 10,000 TPS)

Replica scaling hits a hard ceiling on the write path — a single MySQL primary has a finite write throughput regardless of how many replicas receive the binlog. At 6,000+ TPS with a significant write fraction, the only solutions are write distribution: table partitioning for range-based access patterns, or horizontal sharding across multiple primaries.

Table partitioning: buy time without architecture change

• Range partitioning on a date or ID column reduces per-query scan range and enables partition pruning — MySQL only reads the partitions that can contain matching rows
• Partitioning is transparent to the application — the same SQL query works against a partitioned table
• Old partitions can be dropped atomically with ALTER TABLE ... DROP PARTITION — faster and cheaper than DELETE on large tables and does not generate undo log bloat
• Partitioning does not distribute write load across machines — it reduces I/O per query on a single instance, which defers but does not eliminate the write ceiling

CREATE TABLE events (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  payload    JSON,
  PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION future VALUES LESS THAN MAXVALUE
);

Horizontal sharding: distributing write load across multiple primaries

• Sharding splits data across multiple independent MySQL instances — each shard handles a subset of rows determined by a shard key (user ID, tenant ID, geographic region)
• Each shard operates independently — writes to shard 1 do not touch shard 2, so write capacity scales linearly with shard count (in theory)
• ProxySQL routes queries to the correct shard by parsing the shard key from the WHERE clause using regex-based query rules — no application-level connection management required at the routing layer
• Vitess provides a complete sharding middleware layer for MySQL at extreme scale — it was built for YouTube's write volume and is production-proven for 10,000+ TPS write loads
• Cross-shard queries (JOINs across shard boundaries) require scatter-gather execution and are expensive — schema design must keep related data on the same shard wherever possible

Warning: Sharding is irreversible without significant engineering effort. Schema migrations, cross-shard transactions, and distributed joins all require explicit handling. Exhaust vertical scaling, partitioning, and read replica strategies before committing to a sharded architecture. The operational cost is real and ongoing.

The complete tuning parameter reference

FAQ

At what TPS should I start thinking about read replicas?‍

When Threads_running on the primary consistently stays above the CPU core count during read-heavy periods, the primary is CPU-bound on reads. That is the signal to offload reads to a replica, not a fixed TPS number. On a 16-core primary, if you are seeing Threads_running at 18–20 under a 2,000 TPS mixed read/write load, a replica helps immediately.

How many ProxySQL instances do I need?‍

ProxySQL is lightweight — a single instance can typically handle 50,000+ client connections and 30,000+ queries per second before becoming a bottleneck. For most workloads up to 10,000 TPS, two ProxySQL instances in an active/standby configuration with Keepalived is sufficient. The proxy is stateless per query — losing a ProxySQL instance causes a brief connection blip, not data loss.

Does MySQL 8.0 have any built-in improvements that affect this tuning path?‍

Yes, several. MySQL 8.0 introduced parallel query execution improvements, hash joins, and the replacement of innodb_log_file_size with innodb_redo_log_capacity (in 8.0.30+). The replica_parallel_workers implementation in 8.0 is also significantly better than in 5.7 — parallel logical clock-based replication reduces lag under high write load. If you are still on MySQL 5.7, upgrading to 8.0 or 8.4 should precede any major tuning effort.

What is the highest TPS achievable on a single MySQL primary before sharding is required?‍

On modern NVMe SSD hardware with a correctly tuned InnoDB configuration and innodb_flush_log_at_trx_commit=2, a single MySQL 8.0 primary can handle 5,000–8,000 write TPS on simple OLTP patterns (point INSERTs with indexed lookups). Sysbench benchmarks on high-end hardware show MySQL reaching over 1 million TPS on in-memory point selects, but that does not reflect real workloads. For mixed read-write OLTP with a realistic schema, plan the sharding threshold at 6,000–8,000 write TPS sustained under production conditions.

How do I validate that a tuning change actually improved throughput?‍

Run sysbench with oltp_read_write before and after each change, using a thread count equal to 2x your CPU core count. Hold all other variables constant. If using a production workload instead, compare the p95 query latency and Threads_running at equivalent load over a 30-minute window. Never tune and benchmark simultaneously — one change at a time, with a stabilization period in between.

MySQL performance at scale is a sequence of constraint removals, not a list of config values. Each phase above addresses one class of bottleneck. Applying Phase 4 configurations to a database that still has Phase 2 problems — unindexed queries and long-running transactions — produces no measurable improvement. Work the phases in order, benchmark between each, and treat the tuning path as a continuous practice rather than a one-time project.

Hitting a MySQL performance ceiling you cannot diagnose?

Mydbops engineers work inside MySQL performance problems daily — InnoDB tuning, ProxySQL architecture, replica lag diagnosis, slow query triage, and sharding strategy for production systems at scale. We operate Managed MySQL, Managed PostgreSQL, Managed MongoDB, and Managed Redis services, and our Database Consulting and Emergency DBA teams are available when performance issues cannot wait.