JDBC driver by setting the property called solid_preferred_access to value READ_MOSTLY.

Each application connecting to the database can choose to use the load balancing functionality, or choose to use the Primary database for all queries (default).

The IBM solidDB: High Availability User Guide, SC23-9873 and the IBM solidDB: Programmer Guide, SC23-9870 contain more details about the supported functionality, and also samples of the connection strings/URLs to use. The solidDB installation package contains sample C and Java program code for both ODBC and JDBC applications using the load balancing functionality.

5.4.4 Linked applications versus client/server applications

Most of the discussion regarding transparent failover and load balancing are related to client/server use of solidDB, that is, where the client applications

connect to solidDB database server with a TCP/IP connection or similar socket based communication mechanism.

With the linked library mode, and with the shared memory mode, the application is more tightly and directly linked to one solidDB database instance. Although this may be enhanced in future solidDB releases, in the current release, the transparent failover and load balancing functionality is not yet supported for these applications.

5.5 Usage guidelines, use cases

IBM solidDB HotStandby comes with configuration parameters preset to offer the best trade-off of performance, availability and data safeness, in most common cases. In this section, we describe those trade-offs and show how other choices can be made if needed.

5.5.1 Performance considerations

Several aspects of performance must be considered when looking at the best performance for a HA system.

In addition to the topics discussed in the following text, the main performance optimization effort should be, as always with relational databases, targeted to optimize the SQL query and schema design. Poor performance is always best

134 IBM solidDB: Delivering Data with Extreme Speed

corrected by looking at and enhancing the SQL queries, indexes, other basic RDBMS performance elements.

With a HA (redundant) system, the effect of mirroring/replicating the changed data, reliably and consistently, to another node plays a role also. Consider the following performance elements:

Latency or response times: How quickly a single read or a write operation is completed?

Throughput: How much total query or transaction volume the two-node system can handle?

Data safeness: Does the system guarantee that every transaction is safely persisted, on the same node (to disk) or to the next node (over the network)?

Failover times: How quickly a loaded system can continue to provide its service after a single-node failure, including the error detection time?

Recovery times: How quickly (and how automatically) a system recovers to an HA state after the failure has been resolved?

Optimizing one of the areas might be easy with the configuration parameters available, but the need is often to find the best balance of all factors.

5.5.2 Behavior of reads and writes in a HA setup

Read queries execute only in one database node (databases are identical, having the same content for HA reasons), so there is no need to involve both databases for executing a single read query. Therefore, the response times are expected to be the same in a single db mode and in a HA setup.

Write operations are applied to both databases (insert, update, and delete, and

schema changes). Therefore, effects on the latency exist that also depend heavily on the safeness level used. With safeness, we mean whether the write

operation is safely persisted at the time of commitment, either to the persistent media (disk) or to the second database node over the network. This approach is to guarantee that a committed transaction is not lost in case of a failure.

The safeness level can be set with two controls, independently configured. The configuration can further be done at a system level (global), a connection level and a transaction level. Given this flexibility, there are plenty of options to optimize the trade-off of latency versus data safeness for each part of the application using the database.

Chapter 5. IBM solidDB high availability 135

The two controls are as follows:

Durability level, which can be strict or relaxed, that determines whether the log writing is synchronous or asynchronous, respectively.

Hot standby replication protocol safeness level, that can be 1-safe or 2-safe. When using 1-safe, the replication is asynchronous, and with 2-safe it is synchronous. The 2-safe level has also more granular options (received, visible, durable).

The response time, throughput, and data safeness of write operations are interrelated in terms of how the used configuration effects performance, and hence they are discussed in a combined fashion in the following sections.

5.5.3 Using asynchronous configurations with HA

As might be obvious, asynchrony leads to better performance overall, with the risk of losing data (a few of the latest transactions) in case of failures.

The most asynchronous approach is by using relaxed log writing in each database node (or no transaction logging at all) and 1-safe hot standby replication protocol, which also leads to shortest response times because the Primary database does not have to wait for local disk I/O or the Secondary database before it can complete the transaction commit. It also leads to best throughput, because transactions can be sent to the Secondary in groups, making the overall throughput higher.

Applications needing the fastest possible performance and tolerating the risk of losing some of the latest transactions with a failure situation should consider this configuration.

The risk of losing transactions can be reduced (but not totally eliminated) by shortening the maximum delays of the log writes or hot standby replication. The configuration parameters are called Logging.RelaxedMaxDelay and HotStandby.1SafeMaxDelay, respectively. Setting these parameters to a lower value than the default forces solidDB to persist or replicate the transactions earlier.

We must also mention one special feature here. Because the solidDB HotStandby technology is targeted for HA purposes, solidDB must guarantee,

even with the asynchronous replication mode, that the Secondary database is not too far behind in executing transactions received from the source of the

replication. This way can ensure a reasonable and up-to-date database state for

the Secondary database at all times. Therefore, there is an internal mechanism in the Primary database to start throttling the write operations if the defined limits

136 IBM solidDB: Delivering Data with Extreme Speed

of staying behind are reached. As a result, the throughput in the Primary can be limited by the throughput in the Secondary.

5.5.4 Using default solidDB HA setup

The default solidDB HotStandby configuration is using Adaptive Durability (see “Adaptive durability” on page 117). In a normal state, that translates to relaxed (asynchronous) log writing and synchronous (2-safe received) replication. This way ensures that all committed transactions are always replicated synchronously to the Secondary database at the time of the commit, and thus located at least in the memory of both database nodes. Compared to the fully asynchronous mode, the latency is longer for each write commit, because the Primary database has to wait for the Secondary database to at least acknowledge receiving the changes.

With this configuration, no data is lost in case of a single-node failure. In case of two-node failure, the risk is still there. This configuration is considered the best of both worlds because it eliminates the typical biggest performance problem, which is the (synchronous) disk I/O, but provides data safeness against any single node failures. Given normal maturity of proper server operating environments, a single node failure is rare, and the probability of a two-node failure at the same time, while always there, is even less likely to happen.

In answer to the always-asked question (How much performance overhead does this synchronous replication cause?), several sample answers are available.

Compared to a single database persisting all transactions properly and using synchronous log writing, this HA setup is, surprisingly enough, a faster mode. The explanation, however, is simple. The network I/O is faster than disk I/O.

Sending the write operations to Secondary database is faster than flushing the data onto hard disk.

5.5.5 The solidDB HA setup for best data safeness

Finally, the most safe and also the slowest configuration, is using 2-safe durable hot standby configuration, which means using synchronous replication and synchronous log writing in both database nodes. In this case, no data is lost even if both nodes fail at the same time, because every transaction is committed all the way to the disk in both nodes. The performance is approximately at the same level as with a single (stand-alone) server using synchronous (strict) logging.

Applications needing maximum safety for the write transactions should consider using the 2-safe durable solidDB HotStandby protocol configuration.

Chapter 5. IBM solidDB high availability 137