Replication & performance basics

A single MySQL server goes a long way, but production systems also need high availability (survive a server failure), read scaling (spread queries across machines) and predictable performance. This lesson introduces replication and the handful of settings and tools that matter most for MySQL performance.

How replication works#

   Source (primary)                         Replica
 ┌──────────────────┐                ┌───────────────────────┐
 │ writes ─► InnoDB │                │                       │
 │        ─► binlog ├──── network ──►│ receiver ─► relay log │
 └──────────────────┘                │ applier  ─► InnoDB    │
                                     └───────────────────────┘

1. The source records every committed change in its binary log.
2. Each replica connects as a client, and its receiver thread streams the binlog into a local relay log.
3. The replica's applier threads (several of them, in parallel, by default in 8.0.27+) replay those changes.

Replication is asynchronous by default: the source doesn't wait for replicas. They're usually milliseconds behind, but they can lag under heavy load, and the newest transactions can be lost if the source dies before they're copied.

What replication is used for:

• Read scaling: send reports and read-heavy traffic to replicas.
• High availability: promote a replica if the source fails.
• Backups: take backups from a replica without loading the source.
• Upgrades and migrations: replicate to a new version or new hardware, then switch over.

Setting up a replica with GTIDs#

GTIDs (global transaction identifiers) give every transaction a unique id like 3e11fa47-…:1-1234, so replicas know exactly what they've applied. Auto-positioning and failover become far simpler.

On the source, in my.cnf:

[mysqld]
server_id                = 1
log_bin                  = binlog
binlog_format            = ROW
gtid_mode                = ON
enforce_gtid_consistency = ON
bind-address             = 10.0.0.10

Create a replication account:

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'Repl-Pass-2026!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

(Yes, the privilege still has its old name.) On the replica:

[mysqld]
server_id                = 2
gtid_mode                = ON
enforce_gtid_consistency = ON
read_only                = ON
super_read_only          = ON
relay_log                = relay-bin

Seed the replica with a copy of the source's data, for example with mysqldump --single-transaction --source-data --set-gtid-purged=ON --all-databases, MySQL Shell's dump utilities, or the CLONE plugin. Then point it at the source:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '10.0.0.10',
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'Repl-Pass-2026!',
    SOURCE_AUTO_POSITION = 1,
    SOURCE_SSL = 1;

START REPLICA;
SHOW REPLICA STATUS\G

In the status output, check:

MySQL 8.0.22+ uses source/replica terminology (START REPLICA, SHOW REPLICA STATUS). Older docs say CHANGE MASTER TO, START SLAVE and SHOW SLAVE STATUS.

Replication topologies and options

• Semi-synchronous replication: the source waits until at least one replica has received each transaction before acknowledging the commit, so failover loses far less data.
• Group Replication / InnoDB Cluster: a group of 3+ servers that agree on every transaction, with automatic failover. MySQL Router sends connections to the right member.
• Managed services (Amazon RDS/Aurora, Google Cloud SQL, Azure) run all of this for you.

Replica pitfalls

• Replication lag means "read your own writes" can fail: a user saves a profile, the next page reads from a lagging replica and shows old data. Read critical data from the source, or route a user to the source just after they write.
• Writes on replicas cause conflicts, which is why super_read_only = ON matters.
• Use binlog_format = ROW (the default). It replicates the actual changed rows, which is safe even for non-deterministic statements.

Performance basics#

1. The buffer pool

InnoDB caches table and index pages in the buffer pool. If your working set fits in memory, most reads never touch disk. On a dedicated database server, give it 50–75% of RAM:

[mysqld]
innodb_buffer_pool_size = 12G      # on a 16 GB dedicated server

It can be resized online in MySQL 8 (SET GLOBAL innodb_buffer_pool_size = ...). Check how often reads are served from memory:

SELECT FORMAT(@@innodb_buffer_pool_size / 1024 / 1024, 0) AS buffer_pool_mb;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_reads (from disk) should be a tiny fraction of Innodb_buffer_pool_read_requests (logical reads). Alternatively, set innodb_dedicated_server = ON and MySQL sizes the buffer pool and redo log from the machine's RAM.

2. Other key settings

Change one thing at a time, measure, and keep the configuration in version control. Most real-world speed-ups come from indexes and query fixes, not configuration.

3. Find the slow queries

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;               -- seconds
SET GLOBAL log_queries_not_using_indexes = ON;  -- optional, noisy
SHOW VARIABLES LIKE 'slow_query_log_file';

Summarise the log from the shell:

mysqldumpslow -s t -t 10 /var/lib/mysql/host-slow.log   # top 10 by total time
pt-query-digest /var/lib/mysql/host-slow.log            # Percona Toolkit: richer report

With the Performance Schema (on by default), the sys schema answers "what's slow right now?" without a log file:

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

SELECT * FROM sys.schema_tables_with_full_table_scans LIMIT 5;
SELECT * FROM sys.schema_unused_indexes;

4. Watch the server

SHOW PROCESSLIST;                                   -- what's running now
SHOW GLOBAL STATUS LIKE 'Threads_%';                -- connections
SHOW GLOBAL STATUS LIKE 'Questions';                -- statements served
SHOW ENGINE INNODB STATUS\G                         -- locks, deadlocks, I/O

In production, export these metrics to a monitoring system (Prometheus with mysqld_exporter and Grafana, Percona Monitoring and Management, or your cloud provider's tools) and alert on replication lag, connections, slow-query rate and disk space.

Scaling, step by step#

1. Optimise queries and indexes. That's usually 10–100× wins.
2. Cache hot, rarely-changing data in the application (Redis, in-process caches).
3. Scale up: more RAM so the working set fits in the buffer pool, plus faster NVMe storage.
4. Read replicas for read-heavy workloads.
5. Partition very large tables by date (PARTITION BY RANGE) to make purging old data cheap.
6. Shard (split data across several sources by customer or region), only when one source can no longer handle the writes. Tools like Vitess help.

Course wrap-up#

You've gone from your first SELECT to replication. You can now design normalised schemas with solid constraints, write everything from joins to recursive CTEs and window functions, make queries fast with indexes and EXPLAIN, keep data correct under concurrency, automate with stored programs, and secure, back up and scale a MySQL server. The best next step is to build something real: model a small app's data, load realistic volumes, and practise reading EXPLAIN plans until it's second nature.