There are several aspects to consider when tuning performance for a HotStandby system. With a HA (redundant) system, the effect of replicating the changed data reliably and consistently to another node plays an important role.
This section describes performance considerations in HotStandby setups. However, before you consider how to tune HotStandby 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 a HotStandby setup, consider the following main performance elements:
▪Latency or response times – How quickly is a single read or a write operation completed?
▪Throughput – How much of the total query or transaction volume the two-node system can handle?
▪Data safeness – Does the system ensure that every transaction is safely persisted on the same node (to disk) or to the next node (over the network)?
▪Failover times – How quickly can the system continue to provide its service after a single-node failure, including the error detection time?
▪Recovery times – How quickly and how automatically does the system recover to an HA state after the failure has been resolved?
In solidDB® HotStandby setups, the following configuration and setup options can be used to optimize HotStandby performance:
▪Adaptive durability is useful when you want to preserve transactions over single failures.
▪1-Safe replication protocol is useful when minor transaction loss over failures is acceptable.
▪2-Safe replication protocol and a suitable 2-Safe Acknowledgement Policy enforces maximum safety.
▪Load balancing directs read-only transactions to the Secondary server.
With the load balancing feature in HotStandby, clients can connect to the Secondary 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 and perform their reads there. Load balancing is useful for work such as report-generation or “data warehousing” queries, where you want to read several records and do not want to change any of them.
▪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, once on the Primary, and once on the Secondary. Subsequently 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 50%. The servers have a high degree of parallelism, and while the Secondary is working on one transaction, the Primary 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.2SafeAckPolicyto 1.
Even if you use the less safe settings (adaptive durability and 2-safe received mode), you are still protected by HotStandby unless there are at least two failures, for example, when both servers go down nearly simultaneously. To protect against power failure, each server must be connected to an Uninterruptible Power Supply (UPS). Furthermore, as with any database system, important data should be backed up and archived at a separate site. HotStandby is not a substitute for backing up your data.
You can run the backup (ADMIN COMMAND backup) on either of the servers of the HSB pair. Often it is the secondary server that has more resources available for creating the backup.