In this chapter, we have talked about practical implications as well as performance implications already. But, what good is a theoretical example? Let us do a simple benchmark and see how replication behaves. We do this kind of testing to show you that various levels of durability are not just a minor topic, they are the key to performance.
Let us assume a simple test: In the following scenario, we have connected two equally powerful machines (3 GHz, 8 GB RAM) over a 1 Gbit network. The two machines are next to each other. To demonstrate the impact of synchronous replication, we left shared_buffers and all other memory parameters as default and only changed fsync to off to make sure that the effect of disk wait is reduced to practically zero.
The test is simple: We used a one-column table with just one integer field and 10,000 single transactions consisting of just one INSERT statement:
INSERT INTO t_test VALUES (1);
We can try this with full, synchronous replication (synchronous_ commit = on):
real 0m6.043s user 0m0.131s sys 0m0.169s
As you can see, the test took around six seconds to complete. The test can be repeated with synchronous_commit = local now (which effectively means asynchronous replication):
real 0m0.909s user 0m0.101s sys 0m0.142s
In this simple test, you can see that the speed has gone up by us much as six times. Of course, this is a brute-force example, which does not fully reflect reality (this was not the goal anyway). What is important to understand, however, is that synchronous versus asynchronous replication is not a matter of a couple of percentage points or so. This should stress our point even more: Replicate synchronously only if it is really needed, and if you really have to use synchronous replication, make sure that you limit the number of synchronous transactions to an absolute minimum.
Also, please make sure that your network is up to the job; replicating data synchronously over network connections with high latency will definitely kill your system performance like nothing else. Keep in mind, there is no way to solve this issue by throwing expensive hardware at the problem. Doubling the clock speed of your servers will do practically nothing for you because the real limitation will always come from network latency and from network latency only.
Tip
The performance penalty with just one connection is definitely a lot larger than in the case of many connections. Remember, things can be done in parallel and network latency does not make us more I/O or CPU bound, so, we can reduce the impact of slow transactions by firing up more concurrent work.
When synchronous replication is used, how can you still make sure that performance does not suffer too much? Basically, there are a couple of important suggestions that have proven to be helpful:
- Use longer transactions: Remember, the system must ensure on commit that data is available on two servers; we don't care in the middle of a transaction because anybody outside your transaction would not see the data anyway. A longer transaction will dramatically reduce network communication.
- Run stuff concurrently: If you have more than one transaction going on at the same time, it will definitely be beneficial to the performance. The reason is that the remote server will return the position inside the XLOG considered to be processed safely (flushed or accepted). This method ensures that many transactions might be confirmed at the same time.