Master-Slave Replication: Eliminate the Database Bottleneck
Your web application slows down on report pages? The database can't handle user influx, and a single server is a single point of failure. We've seen this dozens of times: SELECT queries block INSERT, analytical reports bring down production, and backups on a live database cause downtime. Each such problem costs time and money. The solution is a turnkey master-slave replication setup. We guarantee fault tolerance and offload the primary server, proven on 100+ projects.
Replication (Master-Slave or Primary-Replica) is asynchronous or synchronous data transfer from the primary server to one or more replicas. It allows scaling read operations: up to 80% of queries can be directed to replicas, leaving the master for writes only. This reduces latency and eliminates resource contention. With our experience configuring 50+ projects, we implement this architecture in 1–3 days.
Why Is Master-Slave Replication Critical for Your Application?
Without replication, you risk:
- Outage: on master failure, data is unavailable until recovery. Average manual downtime: 2–4 hours.
- Degradation: analytical queries block writes, increasing TTFB by 3–5 times.
- Expensive backups: dumping a live master locks tables, causing downtime.
Compared to a single database, an architecture with one replica handles up to 5 times more read queries. With ProxySQL, the improvement can reach 10x. For example, an e-commerce project after replication reduced report query response time from 12 seconds to 0.8 seconds—15 times faster.
When Should You Use Synchronous Replication?
Synchronous replication guarantees zero data loss on master failure. It is essential for financial transactions or data-critical applications. However, the cost is a 30–50% increase in write latency and reduced throughput. We recommend synchronous mode for the core application and asynchronous for analytics.
How We Set Up Replication in PostgreSQL and MySQL
We use only proven approaches: streaming replication for PostgreSQL and GTID-based replication for MySQL. Key differences are shown below:
| Parameter | PostgreSQL | MySQL |
|---|---|---|
| Default mode | Asynchronous | Asynchronous |
| Synchronous mode | synchronous_standby_names | rpl_semi_sync_master |
| Initialization tool | pg_basebackup | mysqldump + position / AUTO_POSITION |
| Routing | pgBouncer / Pgpool-II | ProxySQL / MySQL Router |
| Automatic failover | Patroni / repmgr | Orchestrator / MHA |
Typical configuration mistakes: incorrect wal_level (should be replica or logical), insufficient max_wal_senders for multiple replicas, ignoring replication lag (no monitoring), and writing to a replica in read-only mode—often causes data mismatch.
Example PostgreSQL master configuration:
# postgresql.conf wal_level = replica max_wal_senders = 10 wal_keep_size = 1GB synchronous_commit = on synchronous_standby_names = 'replica1' Initializing the replica:
pg_basebackup -h master -U replication -D /var/lib/postgresql/14/main -P -Xs -R Example MySQL master configuration:
[mysqld] server-id = 1 log_bin = /var/log/mysql/mysql-bin.log binlog_format = ROW gtid_mode = ON enforce_gtid_consistency = ON Starting the MySQL replica with GTID:
CHANGE MASTER TO MASTER_HOST='192.168.1.10', MASTER_USER='replication', MASTER_PASSWORD='xxx', MASTER_AUTO_POSITION=1; START SLAVE; Routing reads through ProxySQL:
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, 'master', 3306); INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, 'replica', 3306); INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup) VALUES (1, 1, '^SELECT', 20), (2, 1, '.*', 10); LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL QUERY RULES TO RUNTIME; Common configuration mistakes
-
wal_levelnot set toreplica— streaming replication doesn't work. -
max_wal_senderstoo small for number of replicas — replicas cannot connect. - No monitoring of replication lag — lag goes unnoticed until too late.
- Attempting to write to replica in read-only mode — causes data inconsistency.
What Does the Replication Setup Process Include?
- Audit current load and architecture: measure peak RPS, latency, database size.
- Choose topology: single or multiple replicas, asynchronous or synchronous.
- Configure master:
wal_level,max_wal_senders,gtid_mode. - Initialize replicas via
pg_basebackupormysqldump. - Set up routing (ProxySQL / pgBouncer) and split read/write queries.
- Monitor lag: Prometheus + Grafana with alerts at >60 seconds.
- Document configuration and failover procedures, train the team.
Comparison of Asynchronous vs Synchronous Replication
| Parameter | Asynchronous | Synchronous |
|---|---|---|
| Write latency | Low (0.1–1 ms) | High (2–10 ms) |
| Data loss on failure | Possible (up to seconds) | Zero |
| Throughput | High | 30–50% lower |
| Master load | Minimal | Moderate |
What the Work Includes
- Full documentation of configuration and failover procedures.
- Backups and scripts for quick recovery.
- Access to monitoring (Grafana, Telegram alerts).
- Team training: how to check replication status and perform switchover.
Timelines and Cost
Timeframes depend on complexity:
- One replica + basic monitoring: 1 day.
- Replication with ProxySQL and failover: 2–3 days.
Cost is determined individually after analysis. However, the investment pays off quickly: infrastructure savings from read replicas can reach 40%, and recovery time after failure drops from hours to minutes. A typical project pays off in 2–3 months. If you already have a project, contact us for a free assessment and get a fault-tolerant architecture.