There are several aspects to consider when tuning performance for a HotStandby (HSB) system. With a high availability (redundant) system, the effect of replicating the changed data reliably and consistently to another server plays an important role.
This topic describes performance considerations in HSB setups. However, before you consider how to tune HSB setups, remember to optimize the SQL query and schema design. You can often correct poor performance by improving SQL queries, indexes, and so on. Actual throughput and response times depend on many factors, including (but not limited to) the speed of the network, the amount of other traffic on the network, the complexity of the SQL statements, and the number of SQL statements per transaction. The general environment factors, such as amount of memory and disk speed, also affect performance.
In an HSB setup, consider the following main performance elements:
▪ Latency or response times – how quickly a single read or a write operation is completed.
▪ Throughput – how much of the total query or transaction volume the two-server system can handle.
▪ Data safeness – the extent to which transactions are safely persisted on the same server (to disk) or to the next server (over the network).
▪ Failover times – how quickly the system can continue to provide a service after a single-server failure, including the error detection time.
▪ Recovery times – how quickly and how automatically the system recovers to a high availability state after the failure has been resolved.
The following configuration and setup options can be used to optimize HSB performance:
▪ Adaptive durability - if you want to preserve transactions over single failures.
▪ 1-Safe replication protocol - if minor transaction loss over failures is acceptable.
▪ 2-Safe replication protocol and a suitable 2-Safe Acknowledgement Policy - to enforce maximum safety.
▪ Load balancing - to direct read-only transactions to the secondary server.
With the load balancing feature in HotStandby, clients can connect to the secondary server and perform read-only operations. In some situations, you can spread the load and improve overall system performance by having read-only clients connect to the secondary server to perform read operations. Load balancing is useful for work such as report-generation or queries, where you want to read several records but not change any of the data.
▪ Internal parallelism
To ensure that your system takes advantage of parallelism, consider spreading your transactions across several connections rather than submitting all transactions through the same connection.
When you use the HotStandby (HSB) component, every transaction that contains a write operation is executed twice, first on the primary server and then on the secondary server. In some situations, a single transaction might take twice as long with HSB as without HSB. However, this does not mean that overall throughput decreases by 50%. The servers have a high degree of parallelism, and while the secondary server is working on one transaction, the primary server can work on another transaction.
Note The more queries you run in parallel, the more memory the server needs. Thus, adding connections and running queries in parallel does not always increase throughput, especially in systems that do not have a large amount of memory. You might have to experiment to find the optimal number of queries to run at a time.
In summary, unless you need the highest possible level of safety, you can increase performance by using the following configuration settings:
▪ Use adaptive logging by setting Logging.DurabilityLevel to 2.
▪ Use 2-safe received mode by setting HotStandby.2SafeAckPolicy to 1.
Even if you use the less safe settings (adaptive durability and 2-safe received mode), you are still protected from data loss unless there are at least two failures, for example, when both servers go down nearly simultaneously. As with any database system, important data should be backed up and archived at a separate site; HSB is not a substitute for backing up your data.
You can run a backup on either of the HSB servers. Often it is the secondary server that has more resources available for creating the backup.
The performance of data replication during normal operation depends on the setting of the durability level and safeness level. Additionally, when 2-safe replication is used, the acknowledgement policy that is used in 2-safe mode affects the latency time, as perceived by the application.