Introducing IBM solidDB High Availability and Transparent Connectivity

IBM® solidDB® is a high performance in-memory database offering that is optimized for speed and availability. This article focuses specifically on the availability capability by introducing you to the solidDB High Availability (HA) and Transparent Connectivity (TC) features. The article discusses the benefits that these features provide, and how they impact performance. It also shows you how to configure the features, describes a test application, and points you to a demonstration video.

Sami Salkosuo (sami.salkosuo@fi.ibm.com), Software Architect, EMC

Sami Salkosuo works as a Software IT Architect at IBM Software Group in Finland.


developerWorks Contributing author
        level

25 March 2010

Also available in Vietnamese Portuguese

Introduction

The solidDB High Availability (HA) feature is used to implement HotStandby (HSB) functionality. HSB enables a secondary server to run in parallel with the primary server and keep an up-to-date copy of the data in the primary server. The solidDB Transparent Connectivity (TC) feature HSB servers from applications and enables an application to make a single logical connection to servers in HSB mode. IBM solidDB drivers, including the Java Database Connectivity (JDBC) driver, maintain connections to servers so that there is a secondary standby connection to the secondary HSB server in case the connection to the primary server is lost. The JDBC driver indicates the loss of a connection with a special error code.

The Resources section at the end of this article provides links to the solidDB product documentation, which includes complete details on High Availability, HotStandby, and Transparent Connectivity.


Architecture

Figure 1 illustrates the architecture of a solidDB HA configuration. (The figure is adapted from diagrams in the IBM solidDB Information Center.)

Figure 1. High Availability architecture
Shows primary and secondary nodes, each with HA controller and server, connected through JDBC to HA manager GUI.

Following are descriptions of the components in the above figure:

  • Node 1 is the physical machine that includes the primary solidDB server and HA Controller.
  • Node 2 is the physical machine that includes the secondary solidDB server and HA Controller. The secondary solidDB server is always kept up-to-date with the primary solidDB server.
  • Primary server includes the primary IBM solidDB database. Applications connect to this server and perform transactions against the primary database.
  • Secondary server includes the secondary database. If the primary server fails, the HA Controller (HAC) performs failover processing that switches the secondary server to be the primary server.
  • HA Controller (HAC) is the automatic redundancy management program for IBM solidDB HSB. HAC detects failures, performs failovers, and restarts servers when necessary. HAC also has an API that enables HA Managers to connect to it.
  • HA Manager is a GUI-based tool that shows the status of HotStandby servers and the state of HA Controllers. The HA Manager also Includes basic functionality for managing the HAC. This tool is used in the demonstration to simulate a failure on the primary server and make a switch to the secondary server.

Benefits and performance

The main benefit of High Availability in an IBM solidDB environment is that applications are shielded from the effects of a failure of the primary database. An HA configuration along with Transparent Connectivity allows applications to be fully usable even if a database fails during a transaction. An additional benefit is that applications that utilize an HA configuration may show an improvement in performance.

Performance

This section elaborates on how performance might actually improve when you use an HA configuration in IBM solidDB.

IBM solidDB performance is affected by elements such as configuration, database usage, available memory, and network and I/O activity. The latter three are the most common elements that affect the performance of IBM solidDB (and most other databases). The information below explains how configuration and specific usage scenarios affect IBM solidDB performance, and how you can tune solidDB accordingly. However, it does not go into the details of how to perform the tuning.

The elements that most affect IBM solidDB performance are:

  • Transaction logging durability
  • Replication protocol between servers
  • Acknowledgement policy for replication protocol
  • Read-only transactions executed on the secondary server
  • Server's internal parallelism

You can set transaction durability to be strict, relaxed, or adaptive. The strict durability setting means that a transaction is written to the log as soon as it is committed. The relaxed durability setting means that a transaction is written to the log when the server is less busy or when the server can write several transactions at the same time. The adaptive durability setting means that the primary server operates in relaxed logging mode if the secondary server is also active. The strict logging setting is the safest; relaxed is the fastest.

The replication protocol between servers relates to the safeness level of the replication. You can set the replication protocol between servers to be either 1-safe or 2-safe. With the 1-safe setting, control returns to the application immediately after a transaction is committed on the primary server; replication to secondary server is done asynchronously. With the 2-safe setting, control returns to the application only after the transaction is committed on both the primary and secondary servers. The 1-safe setting is the fastest; the 2-safe setting is safest.

The acknowledgement policy configuration affects performance when the replication protocol is set to 2-safe. You can set acknowledgement policy to 2-safe received, 2-safe visible, or 2-safe durable. This setting determines when the secondary server sends an acknowledgement to the primary server. With the 2-safe received setting, the acknowledgement is sent when the data is received. With the 2-safe visible setting, the acknowledgement is sent when the data is visible to applications. With the 2-safe durable setting, the acknowledgement is sent when the data is written to disk.

The last two elements that affect performance are reading from secondary server and internal parallelism. These elements are enabled by applications. With an HA configuration, it is possible to read data from both primary and secondary servers in order to distribute load evenly between the two. Internal parallelism simply means that applications may use multiple database connections to access the solidDB database and thus improve performance in many cases.


Configuration

Configuring IBM solidDB for High Availability is a quick and easy task. Use the following steps to deploy a solidDB HA environment with minimal changes to the default configuration values:

  1. Install IBM solidDB on two servers. Name the servers Primary and Secondary.
  2. On each server create a working directory (for example, c:\soliddb) and create a database named dba. Use dba as the database username and password.
  3. In the working directory of the server named Primary, create a configuration file named solid.ini, as shown in Listing 1:
    Listing 1. solid.ini configuration file for Primary
    [HotStandby]
    HSBEnabled=yes
    Connect=tcp 192.168.1.37 1964

    Replace the IP address with the IP address of your Secondary server. (1964 is the default port number for IBM solidDB.)
  4. In the working directory of the server named Primary, create a configuration file named solidhac.ini, as shown in Listing 2:
    Listing 2. solidhac.ini configuration file for Primary
    [HAController]
    Listen=tcp 3155
    
    Username=dba
    Password=dba
    DBUsername=dba
    DBPassword=dba
    
    EnableDBProcessControl=Yes
    
    [LocalDB]
    Connect=tcp 127.0.0.1 1964
    StartScript=c:\soliddb\start_solid.bat
    
    [RemoteDB]
    Connect=tcp 192.168.1.37 1964
    • The value following Listen=tcp is the listener port number of the HA Controller.
    • The DBUsername and DBPassword are for accessing the HA Controller and the solidDB database named dba that you created in step 2.
    • The EnableDBProcessControl=Yes setting indicates that HAC will control starting and stopping the solidDB process.
    • The LocalDB and RemoteDB settings define the IP addresses and ports for databases, and in the case of LocalDB, the path to the start script for the local database. For example, the content of the start script might be: start c:\IBM\solidDB\solidDB6.5\bin\solid.exe -cC:\soliddb.
  5. Start the HA Controller on Primary using the following command:
    solidhac.exe -c c:\soliddb

    solidhac.exe is located in /bin of your solidDB installation directory. Because you enabled process control for HAC in the previous step, starting HAC also starts solidDB database.
  6. In the working directory of the server named Secondary, create a configuration file named solid.ini, as shown in Listing 3:
    Listing 3. solid.ini configuration file for Secondary
    [HotStandby]
    HSBEnabled=yes
    Connect=tcp 192.168.1.33 1964

    Replace the IP address with the IP address of your Primary server. (1964 is the default port number for IBM solidDB.)
  7. In the working directory of the server named Secondary, create a configuration file named solidhac.ini, as shown in Listing 4:
    Listing 4. solidhac.ini configuration file for Secondary
    [HAController]
    Listen=tcp 3155
    
    Username=dba
    Password=dba
    DBUsername=dba
    DBPassword=dba
    
    EnableDBProcessControl=Yes
    
    [LocalDB]
    Connect=tcp 127.0.0.1 1964
    StartScript=c:\soliddb\start_solid.bat
    
    [RemoteDB]
    Connect=tcp 192.168.1.33 1964
  8. Start the HA Controller on Secondary using the following command:
    solidhac.exe -c c:\soliddb

    solidhac.exe is located in /bin of your solidDB installation directory. Because you enabled process control for HAC in the previous step, starting HAC also starts solidDB database. The command also initiates coordination with the Primary server to set up HA configuration.
  9. Next, you will use the HA Manager to visualize solidDB HA configuration and to test HA. HA Manager is a Java application located in /samples/hac of your solidDB installation directory. In the HA Manager working directory, create a configuration file named HAManager.ini, as shown in Listing 5:
    Listing 5. HAManager.ini
    ; All the following lines are mandatory.
    Window_title = HA Manager
    Header_text  = IBM solidDB HotStandby
    
    ; Display names, host addresses and port numbers
    ; of the SolidHAC (HA Controllers) instances
    ;Server 1 HA Controller
    ;----------------------
    Server1_name = Primary
    Server1_host = 192.168.1.33
    Server1_port = 3155
    Server1_user = dba
    Server1_pass = dba
    ;
    ;Server 2 HA Controller
    ;-----------------------
    Server2_name = Secondary
    Server2_host = 192.168.1.37
    Server2_port = 3155
    Server2_user = dba
    Server2_pass = dba

    Change the above entries as necessary to connect to the HA Controllers in your Primary and Secondary servers.
  10. Start the HA Manager using the following command:
    java -jar HAManager.jar

    Figure 2 shows a sample visualization of an HA configuration as displayed by the HA Manager.
    Figure 2. HA Manager showing IBM solidDB HotStandby configuration
    HSB visualization of two nodes with button to Switch roles. Each node has buttons for Shutdown, Administrative, and Suspend.

Test application

This section shows you how to create a test client so that you can see how IBM solidDB High Availability affects applications. Listing 6 contains the source code for a simple Java client that inserts one million rows into a database. The code is also available in the Download section at the end of this article. The code is developed using Java version 6. Before using the sample, make sure that your CLASSPATH environment variable includes the solidDB JDBC Driver .jar file installation path.

The parts of the code shown in bold are explained further following the listing.

Listing 6. SolidDBTestClient.java
package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class SolidDBTestClient
{
  private final static int MAX_ROWS = 5000;

  private static String jdbcURL = 
    "jdbc:solid://192.168.1.33:1964,192.168.1.37:1964/dba/dba";

  public static void main(String[] args)
  {
    Connection conn = null;
    try
    {
    
      Class.forName("solid.jdbc.SolidDriver");

      // create db connection
      Properties props = new Properties();
      props.setProperty("solid_tf_level", "SESSION");

      conn = DriverManager.getConnection(jdbcURL, props);
    
      createTables(conn);

      long start=System.currentTimeMillis();

      InsertThread it = new InsertThread(conn);
      Thread t = new Thread(it);
      t.start();
      t.join();

      long end=System.currentTimeMillis();
      long totalTime=end-start;
      double avg=(1.0*totalTime)/MAX_ROWS;
      System.out.println("Total time: "+totalTime);
      System.out.println("Avg   time: "+avg);
      
    }    
    catch (Exception e)
    {
      e.printStackTrace();
    }
    finally
    {
      if (conn != null)
      {
        try
        {
          conn.close();
        }
        catch (SQLException e)
        {
        }
      }
    }
  }

  private static void createTables(Connection conn)
  {
    try
    {
      String sql = "CREATE TABLE sample (id INTEGER PRIMARY KEY, time BIGINT)";
      Statement stmt = conn.createStatement();
      stmt.executeUpdate(sql);
      stmt.close();
    }
    catch (Exception e)
    {
      //ignore
    }
  }

  static class InsertThread implements Runnable
  {
    private Connection conn;

    public InsertThread(Connection conn)
    {
      this.conn = conn;
    }

    private void doInsert(String sql, int i) throws SQLException
    {
      Statement stmt = null;
      try
      {
        stmt = conn.createStatement();
        stmt.execute(sql);
        System.out.print(i + "\r");
      }
      finally
      {
        stmt.close();
      }
    }

    public void run()
    {
      String sql = "delete from sample";
      try
      {
        conn.createStatement().executeUpdate(sql);
      }
      catch (SQLException e1)
      {
        System.out.println(e1.toString());
      }
      for (int i = 1; i <= MAX_ROWS; i++)
      {
        sql = "insert into sample values(" + i + ","
            + System.nanoTime() + ")";
        try
        {
          doInsert(sql, i);
        }
        catch (SQLException e)
        {
          System.out.println();
          System.out.println(e.toString());
          
          if (e.getSQLState().equals("HY000"))
          {
            System.out.println("Rolling back connection..");
            try
            {
              conn.rollback();
              // do new insert after rollback
              System.out.println("Inserting "+i+" again..");
              doInsert(sql, i);
            }
            catch (SQLException e1)
            {
              System.out.println(e1.toString());
            }
          }
          else
          {
            e.printStackTrace();
          }
        }
      }
    }
  }
}

The first section in bold shows the JDBC URL for solidDB HSB configuration. Notice how the IP addresses and port numbers of both of the servers in the HSB configuration are present in the URL.

The second bold section sets the solid_tf_level property as SESSION. This property sets the Transparent Connectivity level. The SESSION value means that the connection and connection session are preserved. Other valid values are CONNECTION, which preserves the connection but not the session, and NONE, which preserves neither the connection nor the session.

The third bold section contains code for handling database failover and connection switching. In case of a failover, the solidDB database driver throws an SQLException with the special state HY000. If failover occurs, this section of code calls the rollback() method of the Java Connection object to refresh the connection. After the rollback, the application continues as before.

How configuration affects performance

To show how HA configuration can affect performance, the author used the test application from the previous section to insert 5000 rows into a database. Table 1 compares the performance based on different architectures and logging settings.

Table 1. IBM solidDB configuration affects on performance
ArchitectureLoggingAverage time (msec)
StandaloneStrict14.1344
StandaloneRelaxed5.125
HA, HotStandbyStrict17.3314
HA, HotStandbyRelaxed5.472

The results show that using relaxed logging in an HA configuration only slightly affects performance.

The tests were run using two IBM solidDBs as VMWare virtual images in a single server in a WLAN network.


Demonstration

To see the concepts described in this article in action, you can watch this YouTube video: IBM solidDB High Availability Demonstration. Figure 3 shows a screenshot from the video.

Figure 3. Demonstration video
Screenshot from the IBM solidDB HA Demonstration YouTube video

Summary

This article demonstrated how to configure IBM solidDB High Availability and how applicationscan take advantage of the Transparent Connectivity feature of IBM solidDB.

IBM solidDB High Availability, combined with the extreme speed of IBM solidDB, provides a very robust database layer for applications. In addition, the very small amount (if any) of administration required for IBM solidDB, makes IBM solidDB a good database choice for any ISV application.


Download

DescriptionNameSize
SolidDB Test Client Java programSolidDBTestClient.zip2KB

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=477512
ArticleTitle=Introducing IBM solidDB High Availability and Transparent Connectivity
publish-date=03252010