The problem
A common 2011 setup is one database master and one replica on separate VPS instances. The master dies, for example from a filesystem fault or a host failure. The replica is promoted, and the application comes back. Then users report that their last few actions are missing: orders, comments and edits that were acknowledged as saved.
The same setup produces a second, quieter problem. The engine was picked from folklore, such as 'MySQL is fast, PostgreSQL is safe', and nobody checked what the chosen engine actually guarantees at commit time. Typical claims that do not hold up:
- MySQL replication is purely asynchronous. This was true up to 5.1. MySQL 5.5 ships an optional semisynchronous mode.
- PostgreSQL replication is always zero-loss. PostgreSQL 9.1 can be synchronous, but it is asynchronous until it is configured otherwise.
- A buffer pool or log setting copied from a tuning guide is harmless. Some of these settings trade durability for speed.
Why it happens
With classic asynchronous replication the master writes the transaction to its own log, returns success to the client, and ships the change to the replica afterwards. Between those two moments there is a window. If the master is lost inside it, the replica never receives the transactions, and the promoted replica is missing data that clients were told was committed.
Local durability settings widen the window further. In MySQL, innodb_flush_log_at_trx_commit = 2 writes the InnoDB log to the operating system at each commit but flushes it to disk only about once a second. A crash of the mysqld process alone loses nothing. An operating system crash or power loss can lose roughly the last second of commits. Setting 0 is weaker still. Without sync_binlog = 1, the binary log that replicas read can also lag behind what InnoDB has committed.
Neither engine is the cause. The cause is a commit that does not wait for a second machine.
Source of truth
The MySQL reference manual page on semisynchronous replication installation and configuration is at https://dev.mysql.com/doc/refman/5.7/en/replication-semisync-installation.html. It is the 5.7 edition, which is later than the 2011 era of this article. The mechanism it describes is the one introduced in 5.5. The page confirms the following:
- Semisynchronous replication is provided by plugins, one on the source and one on each replica. The plugins are checked through the INFORMATION_SCHEMA plugins table.
- The relevant variables are
rpl_semi_sync_master_enabled,rpl_semi_sync_master_timeoutandrpl_semi_sync_slave_enabled. They can be changed at runtime withSET GLOBALor set in an option file. - Both enable variables default to 0. The timeout is in milliseconds and defaults to 10000, which is ten seconds.
For PostgreSQL, the confirmed facts come from a PostgreSQL-focused write-up: https://www.crunchydata.com/blog/synchronous-replication-in-postgresql. It states that streaming replication and hot standbys arrived in 9.0 and synchronous replication in 9.1. It also states that a replica is made synchronous by adding its name to synchronous_standby_names in the primary's postgresql.conf and reloading the configuration. That page describes later syntax as well (FIRST and ANY lists). Those forms do not exist in 9.1 and are not used here. Before relying on any parameter, check it against the manual that matches the installed server version.
Ways to fix it
1. Keep asynchronous replication and accept the window
Acceptable when losing a second or two of writes is tolerable, as with a cache-like store or a forum. Mitigate with frequent backups and binary log or WAL archiving. This does not close the window.
2. MySQL 5.5 semisynchronous replication
The master waits after commit until at least one replica confirms it has received the event. Receipt is not the same as having applied it. If no replica answers within the timeout, the master falls back to asynchronous behaviour and carries on.
3. PostgreSQL 9.1 synchronous replication
A commit returns only after the named standby has acknowledged the WAL. In 9.1 the standby's application_name must match an entry in synchronous_standby_names, and the value * matches any standby. If no synchronous standby is connected, commits block. They do not fall back silently. This is stricter than option 2. It also means a lost standby can stall all writes until an administrator intervenes. Per-session or per-transaction relaxation is possible with synchronous_commit.
4. Shared or block-level replication
DRBD in synchronous mode under a single database instance is another way to avoid the window. It needs a cluster manager, and the failover is a cold start of the engine on the surviving node.
| Method | Loss window | If the replica disappears |
|---|---|---|
| Asynchronous | Yes | Master continues |
| MySQL 5.5 semisync | Small, closed while the replica is reachable | Master waits for the timeout, then continues asynchronously |
| PostgreSQL 9.1 synchronous | None for acknowledged commits, once the standby has flushed the WAL | Commits block until a standby returns or the setting is changed |
| DRBD protocol C | None at the block level | Depends on the cluster manager |
The PostgreSQL 9.1 configuration for option 3 looks like this:
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 64
synchronous_standby_names = 'standby1'
The standby needs hot_standby = on if it must serve read-only queries. Its recovery.conf needs standby_mode = 'on' and a primary_conninfo string that includes application_name=standby1. The master's pg_hba.conf needs a replication entry for the standby's address.
Which fix and why
For an existing MySQL 5.5 installation that already has a working master and replica, semisynchronous replication is the fitting choice. It needs no migration and no change to the application or the schema. It needs two plugins and a few variables, and the setting can be reverted at runtime. The timeout fallback keeps the site writable if the replica fails. For many web workloads that is the right trade-off, because availability of writes matters as much as the last second of data.
The choice would be different in two cases. If the workload needs a strict zero-loss guarantee and the engine is not yet fixed, PostgreSQL 9.1 synchronous replication is the stronger tool. It will not quietly downgrade, and its serializable isolation level is a real feature of 9.1. If the project is a single-server read-heavy site, option 1 is simpler and the extra machinery has no benefit.
Semisync alone is not enough. The master also needs innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1. Otherwise the master itself may lose commits it has already acknowledged. The value 2 is a deliberate speed trade for workloads that tolerate losing about a second of commits on an operating system crash.
Step by step
These steps assume a working asynchronous master and replica on MySQL 5.5, with log-bin enabled on the master, unique server-id values, and a replica that is already running. No output is reproduced below. The expected values are described in the text.
1. Master durability settings
In the [mysqld] section of /etc/my.cnf on the master:
[mysqld]
server-id = 1
log-bin = mysql-bin
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
Restart the service. The service name depends on the package, usually mysql or mysqld.
service mysqld restart
2. Install and enable the master plugin
The plugin is installed once and is remembered across restarts. Run these in the mysql client on the master:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;
The timeout is in milliseconds, and the default of 10000 is long for a web request. Pick a value that matches the longest stall the application can accept.
3. Install and enable the replica plugin
Run these in the mysql client on the replica. The I/O thread has to reconnect for the new mode to apply.
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
4. Make the settings permanent
The option-file entries work only after the plugin is installed, because the server rejects unknown variables at startup. Add them afterwards.
[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000
On the replica:
[mysqld]
rpl_semi_sync_slave_enabled = 1
5. Verify on the master
SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
SHOW STATUS LIKE 'Rpl_semi_sync_master_clients';
SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx';
The status variable should read ON and the client count should be at least 1. The yes_tx counter increases with each commit acknowledged by a replica.
6. Test the fallback in a maintenance window
Stop the replica service, run a write on the master, and observe that it takes about as long as the timeout. The status variable should then change to OFF. Start the replica again and check that the status returns to ON once it has caught up.
service mysqld stop
Looking back
Semisync narrows the loss window, but it does not remove it in every case. Receipt by the replica is acknowledged, not application of the event, so a promoted replica may still be catching up. After a timeout, the master is asynchronous until a replica has caught up and the status returns to ON. Writes made during that period are exposed again. A monitoring check on Rpl_semi_sync_master_status is therefore part of the fix, not an extra.
The other points from the original comparison need correcting. PostgreSQL is released under the PostgreSQL License, a permissive BSD-style licence, not MIT. Claims that SSD IOPS grow 'exponentially' have no basis. Storage speed does matter for a database that commits often, but a number should come from a measurement on the actual hardware. The query_cache_size line from common tuning guides adds a global lock that hurts write-heavy workloads, so it is not a default to copy. A innodb_buffer_pool_size of about half the RAM on a 4 GB machine is a reasonable starting point only if the instance is dedicated to the database.
Choose PostgreSQL 9.1 synchronous replication when a stalled write is better than a lost one. Choose plain asynchronous replication with archived logs when simplicity is worth a small loss window. Choose DRBD when the application cannot be changed and one logical database instance must survive a node loss.