Extreme testing of PostgreSQL 9.1 streaming replication

There was a task to introduce Postgresql 9.1 streaming replication in production. And since they hadn’t dealt with her before; they decided to conduct her “extreme testing”.

To do this, two VMWare Player virtual machines were running from Ubuntu Server. Installed and configured streaming replication on Postgresql 9.1.

Sync speed

A script was launched from the host machine, adding multiple rows to the test table of the replicated database in a loop. The script was run in 3 threads.

On virtual machines, a load was created both on the processor (an infinite loop in bash) and reading data from the test table. The uneven load was also checked - it was switched off on the slave, then on the master.
')
The results were a very nice picture. Although they were rated "by eye", out of sync was not seen for more than 1-2 seconds. And then, such a desynchronization occurred only with a very high load (several parallel load processes) and for a short time.

Power outage stability

fsync = off

To speed up the update and paste operations, the previous version of Postgresql was used with the fsync = off option. The replication behavior was tested when fsync is disabled and the virtual machine is turned off hard (power-off simulation).

The results were quite predictable. After its launch and recovery of the database, it turned out that the current point in the binary log on master is earlier than the processed point on the slave. Those. the database is completely out of sync and servers need to be synchronized from scratch (according to the usual procedure for creating a slave database, copying the entire data directory).

fsync = on

After server synchronization, replication behavior was tested when fsync is enabled.

With the “hard” shutdown of the salve, nothing much happened. He rose and “caught up” with the master magazine.

More interesting is the situation when master is disabled. With its accurate disconnection of the type “disconnected, waited, turned on, checked”, everything was normal. The base was raised, restored and synchronized with the slave.

Next was the simulation of "flashing electricity" in the data center. The master server was “hard” reloaded several times at arbitrary points in time. Typically, these moments fell at the time of the Postgresql server launch or a little later. As a result, the base on the master went up normally, but there were some minor problems with the slave. In the slave log, messages about the wrong size of some transaction record started popping up and it was not synchronized with the master. However, when restarting Postgresql on the slave, everything worked fine again.

Insert speed depending on the fsync value

The dependence of the insertion speed on the fsync value was tested in several cycles. According to the results, with fsync = off, the speed is about 3.3% higher. Is it worth such an acceleration of the loss of the base when the power is turned off - everyone should decide for themselves. I personally intend to use Postgresql exclusively with fsync = on.

Switching the slave to master mode using a file trigger

Switching works well. But there is one "but." With this switch, the slave becomes a completely independent master. Those. It is impossible to return it to the slave state without full synchronization with the master. This is due to the fact that a new timeline is being created on it - the timeline. Also, in consequence of this, if you use several slaves, all the others are disconnected from the new master. And for their connection requires their complete synchronization.

Results

As a result of these tests, I personally became confident that streming replication in Postgresql 9.1 is fully functional and reliable.