The fifth chapter of PostgreSQL replication set up synchronous replication (2)

5.2 Understanding actual impact and performance

In this chapter, we have discussed the actual impact as well as the performance impact. But is there any good theoretical example? Let's do a simple benchmark test to see how replication is done. We do such tests to show you that the levels of endurance are not just a minor topic, but they are critical to performance.

Let's assume a simple test: In the following scenario, we have connected to two equally powerful machines (3 GHz, 8 GB RAM) over 1 Gbit of network. The two machines are adjacent to each other. To demonstrate the impact of synchronous replication, we use the default settings for Shared_buffers and all other memory parameters, just set Fsync to off to ensure that the disk wait impact is reduced to almost 0.

The test is simple: we use a table with only one integer field and 10000 transactions that include only one INSERT statement:

INSERT into T_test VALUES (1);

We can try this full synchronous replication (synchronous_ commit = ON):

Real 0m6.043s

User 0m0.131s

SYS 0m0.169s

As you can see, the test takes about six seconds to complete. Now repeat the test using Synchronous_commit = local (which actually means asynchronous replication):

Real 0m0.909s

User 0m0.101s

SYS 0m0.142s

In this simple test, you can see a six times-fold increase in speed. This is, of course, a case of violence, which does not fully reflect the reality (which is not the goal). To understand what is important, synchronous replication and asynchronous replication are not a few percentage points apart. This underscores our point of view: As long as you really need to replicate synchronously, if you really need to use synchronous replication, make sure that your number of simultaneous transactions is absolutely small.

Also, make sure that your network meets the needs of your work. Replicating data synchronously with a network connection with a high latency will definitely disrupt your system's performance. Keep in mind that there is no way to solve this problem with expensive hardware. In fact, doubling your server's clock speed is useless to you because the real limit always comes from network latency and only from network latency.

[The performance loss of only one connection is certainly larger than in the case of many connections. Keep in mind that you can work in parallel, network latency does not use more of our I/O or CPU bandwidth, so we can reduce the impact of slow transactions by initiating more concurrent work. ]

When using synchronous replication, how can you ensure that performance does not suffer too much? Basically, there are several important recommendations that have been proven helpful:

• Use longer transactions: Remember, the system must ensure that the submitted data is available on both servers; we don't care about the middle process of the transaction because no one outside your transaction will see the data. Longer transactions can significantly reduce network traffic.

• Run parallel tasks: If you have more than one transaction running at the same time, it will certainly benefit performance. The reason is that the remote server will return the Xlog internal location that is considered a secure process (flush or receive). This approach ensures that multiple transactions may be approved at the same time.

The fifth chapter of PostgreSQL replication set up synchronous replication (2)