In this demo we show how not only does a locator provide load balancing among the SQLFire members from client connections but also high availability should a member crash or perhaps be brought down. This is illustrated below when using a JDBC Connection pool.
1. First we have started a locator and 2 SQLFire members as shown below.
Locator:
sqlf locator start -peer-discovery-address=localhost -peer-discovery-port=41111 -client-bind-address=localhost -client-port=1527
Members:
sqlf server start -server-groups=MYGROUP -locators=localhost[41111] -client-bind-address=localhost -client-port=1528 -dir=server1 &
sqlf server start -server-groups=MYGROUP -locators=localhost[41111] -client-bind-address=localhost -client-port=1529 -dir=server2 &
2. You can verify this by issuing a query as shown below , prior to running a JDBC Connection pool test.
Note: Notice how we connect to the locator client hostname/port rather then the individual SQLFire members.
[Sun Feb 05 21:38:50 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/simpledemo ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1527';
sqlf> select substr(netservers, 1, 30) as "Netservers", kind from sys.members;
Netservers |KIND
-----------------------------------------------
localhost/127.0.0.1[1529] |datastore(norma&
localhost/127.0.0.1[1528] |datastore(norma&
localhost/127.0.0.1[1527] |locator(normal)
3 rows selected
3. Here is our JDBC Connection Pool which shows that it's connecting to the locator itself to establish JDBC Conections.
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean id="sqlfireUcpDataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
<property name="URL" value="jdbc:sqlfire://localhost:1527/" />
<property name="connectionFactoryClassName" value="com.vmware.sqlfire.jdbc.ClientDriver" />
<property name="connectionPoolName" value="SQLFIRE_UCP_POOL" />
<property name="minPoolSize" value="5" />
<property name="maxPoolSize" value="20" />
<property name="initialPoolSize" value="5" />
</bean>
</beans>
4. At this point we create a test class which simply gets 5 connections and displays which SQLFire member we are connected to. So the process is as follows to test both load balancing and high availability when a member disappears, ensuring that existing connections fail over to the remaining SQLFire member.
- start JDBC Connection pool
- get 5 connections
- display each connection server side SQLFire member (It should load balance between our servers in this case we have 2 of them)
- Sleep for 10 seconds
- get 5 connections (verify that indeed we are only connected to the surviving member)
5. Output as follows.
Feb 5, 2012 9:59:26 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@657d5d2a: startup date [Sun Feb 05 21:59:26 EST 2012]; root of context hierarchy
Feb 5, 2012 9:59:26 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [application-config-SQLFIRE.xml]
Feb 5, 2012 9:59:26 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@62da3a1e: defining beans [sqlfireUcpDataSource]; root of factory hierarchy
Feb 5, 2012 9:59:27 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: $Proxy0@1dfd868
Feb 5, 2012 9:59:27 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Getting 5 connections from pool
Connection 0 [id=192-168-1-4.tpgi.com.au(1426):55091/50932, netserver=localhost/127.0.0.1[1528]]
Connection 1 [id=192-168-1-4.tpgi.com.au(1426):55091/50932, netserver=localhost/127.0.0.1[1528]]
Connection 2 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 3 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 4 [id=192-168-1-4.tpgi.com.au(1426):55091/50932, netserver=localhost/127.0.0.1[1528]]
Feb 5, 2012 9:59:27 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Closing all connections from pool
Feb 5, 2012 9:59:27 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: ** Pool Details **
NumberOfAvailableConnections: 5
BorrowedConnectionsCount: 0
Sleeping for 20 seconds, shutdown a SQLFire server at this point...
6. Shutdown a server as shown below , before the existing program wakes up..
[Sun Feb 05 21:45:34 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/simpledemo ] $ sqlf server stop -dir=server1
The SQLFire Server has stopped.
7. Verify final output as follows
....
Feb 5, 2012 9:59:47 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: $Proxy0@580754fc
Feb 5, 2012 9:59:47 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Getting 5 connections from pool
Connection 0 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 1 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 2 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 3 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Connection 4 [id=192-168-1-4.tpgi.com.au(1425):52022/50935, netserver=localhost/127.0.0.1[1529]]
Feb 5, 2012 9:59:47 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Closing all connections from pool
Feb 5, 2012 9:59:47 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: ** Pool Details **
NumberOfAvailableConnections: 5
BorrowedConnectionsCount: 0
As you can see from the output above we have switched over all existing connections in our JDBC pool to use the remaining SQLFire member only.
The test class used here is as follows.
package vmware.au.sqlfire.pool;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import oracle.ucp.jdbc.PoolDataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestSQLFireJDBCPool
{
private Logger log = Logger.getLogger("com.vmware.sqlfire");
private ApplicationContext context;
private static final String BEAN_NAME = "sqlfireUcpDataSource";
private PoolDataSource pds;
public TestSQLFireJDBCPool()
{
context = new ClassPathXmlApplicationContext("application-config-SQLFIRE.xml");
pds = (PoolDataSource) context.getBean(BEAN_NAME);
}
public void run()
{
Connection conn = null;
List<Connection> connections = new ArrayList<Connection>();
try
{
// ensure pool is started by getting a connection object
conn = pds.getConnection();
log.info(conn.toString());
conn.close();
log.info("Getting 5 connections from pool");
for (int i = 0; i < 5; i++)
{
connections.add(pds.getConnection());
}
for (int i = 0; i < 5; i++)
{
getInstanceDetails(connections.get(i), i);
}
log.info("Closing all connections from pool");
for (Connection connection: connections)
{
connection.close();
}
log.info(displayPoolDetails());
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
connections = null;
}
}
public void getInstanceDetails (Connection conn, int i) throws SQLException
{
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
String idSql = "select dsid() from sysibm.sysdummy1";
String netSeverSql = "select netservers from sys.members where id = ?";
stmt = conn.createStatement();
rset = stmt.executeQuery(idSql);
rset.next();
String id = rset.getString(1);
rset = null;
pstmt = conn.prepareStatement(netSeverSql);
pstmt.setString(1, id);
rset = pstmt.executeQuery();
rset.next();
String netServer = rset.getString(1);
System.out.println
(String.format("Connection %s [id=%s, netserver=%s]", i, id, netServer));
rset.close();
stmt.close();
pstmt.close();
}
public String displayPoolDetails () throws SQLException
{
StringBuffer sb = new StringBuffer();
sb.append("** Pool Details **\n");
sb.append("NumberOfAvailableConnections: " +
pds.getAvailableConnectionsCount());
sb.append("\nBorrowedConnectionsCount: " +
pds.getBorrowedConnectionsCount());
sb.append("\n");
return sb.toString();
}
/**
* @param args
* @throws InterruptedException
*/
public static void main(String[] args) throws InterruptedException
{
TestSQLFireJDBCPool test = new TestSQLFireJDBCPool();
test.run();
System.out.println("\nSleeping for 20 seconds, shutdown a SQLFire server at this point...\n");
Thread.sleep(20000);
test.run();
}
}
