A fundamental question database administrators ask before deploying replication services is what they can expect in terms of performance. As described below, if a sufficient rate of data replication cannot be maintained, replication latency will grow to a level that makes replication suboptimal for many applications.
The EDB Postgres Platform 2017 from EnterpriseDB® (EDB™) elevates the performance and capabilities of integrated tools like replication designed specifically for high-performance Postgres infrastructures. The result is a more powerful and better-integrated architecture capable of supporting the demanding and complex workloads of today’s digital business applications.
The EDB Postgres Replication Server with Multi-master Replication (MMR), which is integrated into the EDB Postgres Platform, has been advanced to deliver greater performance as workloads have become heavier. To better understand performance and measure the improvements exhibited, EnterpriseDB has completed a three-month benchmarking program on the recently released EDB Postgres Replication Server v6.1 with MMR.
Over the three months, patterns in the benchmarking results led to refinement of the testing procedures to focus on those patterns. The following graph (see Figure 1) highlights the most interesting patterns:
- Replication latency values <= 45 seconds tended to remain <= 45 seconds regardless of the duration of the run.
- Replication latency values >= 60 seconds tended to increase fairly linearly as duration was increased and backlogs grew.
- Replication latency values > 45 seconds and < 60 seconds require more exploration, but appear to behave marginally like the <= 45 second latency cases.
- Replication latency calculations, in the <= 60 second range, varied by as much as 10 seconds for otherwise identical benchmarking runs. That variation may be attributable to differences in thread interaction from run to run or to short-term differences in PPAS latency from such ancillary processes as vacuuming.
- Under identical environmental conditions, EDB Postgres Replication Server v6.1 can sustain substantially higher continuous transaction rates than EDB Postgres Replication Server v6.0, i.e. up to 97% greater rates.
- Examination of the Pub Server logs suggests that there is opportunity for tuning EDB Postgres Replication Server v6.1 for specific data schemas, using available configuration parameters (xdb_pubserver.conf), for even better performance. These tests were run with identical settings for both versions 6.0 and 6.1.
- The specifics of these results are dependent upon the execution environment that they were tested on. However, the patterns should remain consistent on any reasonable configured environment.
Figure 1 – Sustainable Transaction Rate Graph by pgBench Load Factor (LF)
Aspects of MMR Performance
Replication performance is made up of many separate attributes that ultimately determine the sustainable, continuous, replication rate that can be expected by a user of the EDB Postgres Replication Server-MMR in a properly configured environment.
- DBMS Latency refers to the average time it takes for a database management system (DBMS) server to process a database transaction. This is usually measured in milliseconds. The DBMS latency occurs at both the source and target DBMS servers.
- Network Latency, in this context, refers to the time taken to communicate requests and responses among the various source and target nodes, participating in a replication process, over a network. Network latency is typically lower within a local area network (LAN) and higher across wide area networks (WAN), increasing as the geographical separation of nodes increases.
- Replication Latency refers to the time it takes for the effects of a database transaction to be propagated across the participating nodes of a replication set, usually measured in seconds. Replication latency is also affected by both DBMS and network latency and by the processing, disk, and memory resources of the computer servers upon which the replication processes are executed.
- Sustainable Replication Rate refers to the replication transaction rate that can be sustained continuously 24x7, measured in Transactions Per Second (TPS). This is the rate at which a replication system can process the effects of database transactions without creating a backlog of replication transactions waiting to be processed. This is the key measurement of replication performance. As long as the sustainable replication rate is greater than or equal to the load being applied to the replication system, replication latency will remain constant. If it is exceeded, replication latency will continue to grow as long as the load is applied – growing from seconds, to minutes, to hours as the unprocessed backlog builds (see Figure 2). DBMS latency, network latency, and replication latency all impact the sustainable replication rate and, in turn, the sustainable replication rate affects replication latency.
Figure 2 – Sustainable Transaction Rate
During the benchmarking process, each run is executed from an identical state. The only factors that varied among the runs were the EDB Postgres Replication Server-MMR version, the transaction generation load factor, and the duration of the test.
Execution Environment – All tests were executed on 6 AWS servers within the same Availability Zone (see Figure 3).
- DB1 (r3.8xlarge) – Database server (EDB Postgres Advanced Server 9.5) and MMR MDN.
- DB2 (r3.8xlarge) – Database server (EDB Postgres Advanced Server 9.5) and MMR Node 2.
- DB3 (r3.8xlarge) – Database server (EDB Postgres Advanced Server 9.5) and MMR Node 3.
- MMR (r3.8xlarge) – Replication Server.
- LG1 (m3.large) – pgBench load generator (to MDN).
- LG2 (m3.large) – pgBench load generator (to Node 2).
Figure 3 – Benchmark Server Topology
Replication Configuration – WAL-based EDB Postgres Replication Server – MMR in an Active (MDN) / Active (Node 2) / Passive (Node 3) configuration.
Software Used in Testing:
- Database Servers – EDB Postgres Advanced Server v184.108.40.206
- Replication Servers – EDB Postgres Replication Server v6.0.6 and EDB Postgres Replication Server v6.1.0 Alpha
- Benchmarking Tool – Postgres pgBench
- Delete and recreate fresh replication cluster with databases and publications using snapshot to ensure all databases have the same state.
- Start pgBench execution simultaneously from LG1 and LG2.
- After run completion, compute replication latency and record results.
- Save supporting data.
- Extract information for analysis.
The key measurement of MMR performance is the sustainable replication rate. As long as the sustainable replication rate is greater than or equal to the load being applied to the replication system, replication latency will remain constant at less than 45 seconds (see Figure 1). If the load on the replication system exceeds the sustainable replication rate, replication latency will grow as the backlog of unreplicated transactions grows.
EDB Postgres Replication Server v6.1 has exhibited a sustained replication rate of approximately 3000 TPS from multiple masters when running on the test environment, whereas the sustainable load for EDB Postgres Replication Server 6.0 was reached at approximately 1500 TPS. With this level of performance, DBAs can feel confident the performance from EDB Postgres Replication Server v6.1 with MMR can deliver optimal performance for most corporate workloads.
Dick Dowdell is a Senior Architect at EnterpriseDB.