Extreme testing streaming replication in PostgreSQL 9.1

had the task of implementing streaming replication Postgresql 9.1 in production. And since before her business had decided to spend her "extreme testing".

This was running two virtual machines VMWare Player with a Ubuntu Server. Installed and configured streaming replication on Postgresql 9.1.

sync Speed

The host machine was running a script that adds the test table of the replicated database in a loop many rows. The script was running in 3 threads.

Virtual machines were created, the load on the CPU (infinite loop in bash) and read the data from the test table. Checked and unbalanced load — it is disconnected on slave, then master.

The results turned out very nice picture. Although they were estimated "by eye", but was not seen out of sync for 1-2 seconds. And, this desynchronization occurred only at very high load (multiple concurrent load processes) and for a short time.

Stability during power interruptions

fsync=off

To accelerate update operations, and insert a previous version of Postgresql was used with the option fsync=off. It was tested the behavior of replication with fsync disabled and the hard shutdown of the virtual machine (simulating a power cut).

The results were quite predictable. After its launch and restore a database it appeared that the current point in the binary log on the master is earlier than the point processed on the slave. Ie base fully out of synchronization and need to synchronize servers from scratch (according to the usual procedure of creating slave database — copying the entire data directory).

fsync=on

After synchronization of the servers we tested the behavior of replication in the case when fsync is enabled.

During hard off salve, nothing happened. He was up and "catch up with" the log master.

More interesting situation when you disable the master. When it is accurate disable type "turned off, waited, turned on, checked", all was fine. The base was raised, restored and synchronized with the slave.

Further, we made a simulation "of the flashing of electricity" in the data center. The master server was hard rebooted multiple times at arbitrary points in time. Usually these moments came at the time of starting the Postgresql server or later. In itogda, the database on the master went up fine, but the slave has some problems. In the log the slave began popping up message about wrong size of some record of the transaction and it is not synchronized with the master. However, when you restart Postgresql on the slave again well earned.

Performance insert depending on value fsync

Has been tested in several cycles, the dependence of the rate of insertion from the value of fsync. The results were that with fsync=off speed is higher by about 3.3%. Whether this accelerated loss of base during a power outage is to solve each independently. I personally intend to use Postgresql exclusively with fsync=on.

Switch slave to master mode using a file trigger

The switch works well. But there is one "but". This switch slave to become a completely independent master. I.e. it is impossible to return him back to a slave state without a full sync with the master. This is a consequence of the fact that it creates a new time line — timeline. Also, in consequence of this, if you have multiple slave, all others are disconnected from the new master. For their connection required a full sync.

Results

As a result of these tests I personally have any confidence that streming replication in Postgresql 9.1 is quite efficient and reliable.

Article based on information from habrahabr.ru