Tuesday, 29 May 2012

SQLFire multi-site WAN replication

In then latest SQLFire 102 release we have introduced muti-site WAN deployments. The example below shows how quickly we can get this setup with simple SQL calls on both sites. In this very simple example we will have 2 distributed SQLFire sites being setup as a multi-site WAN deployment scenario.

The steps below switch from one site to the other so be careful to run the SQL on the correct site.

Note: This demo is designed to on a single laptop/desktop, hence the refernces to localhost

Site 1

1. Start a locator as follows for the first distributed SQLFire system

> sqlf locator start -peer-discovery-address=localhost -peer-discovery-port=10101 -locators=localhost:10101 -conserve-sockets=false -distributed-system-id=1 -remote-locators=localhost[20202] -client-bind-address=localhost -client-port=1527

2. start 2 SQLFire nodes as shown below.

> sqlf server start -server-groups=MYGROUP -locators=localhost[10101] -client-bind-address=localhost -client-port=1529 -dir=server1 &
sqlf server start -server-groups=MYGROUP -locators=localhost[10101] -client-bind-address=localhost -client-port=1530 -dir=server2 &

Site 2

3. Start a locator as follows for the second distributed SQLFire system

> sqlf locator start -peer-discovery-address=localhost -peer-discovery-port=20202 -locators=localhost:20202 -conserve-sockets=false -distributed-system-id=2 -remote-locators=localhost[10101] -client-bind-address=localhost -client-port=1528

4. start 2 SQLFire nodes as shown below.

> sqlf server start -server-groups=MYGROUP -locators=localhost[20202] -client-bind-address=localhost -client-port=1531 -dir=server1 &
sqlf server start -server-groups=MYGROUP -locators=localhost[20202] -client-bind-address=localhost -client-port=1532 -dir=server2 &

Site 1

Create sender/receiver as shown below.

5. Create sender as follows
  
create diskstore cluster1store;

CREATE GATEWAYSENDER cluster2sender
(
  REMOTEDSID 2
  ENABLEPERSISTENCE true
  DISKSTORENAME cluster1store 
)
SERVER GROUPS (MYGROUP); 

6. Create receiver as follows
  
CREATE GATEWAYRECEIVER test_receiver (startport 1550 endport 1561) 
server groups (MYGROUP);

Site 2

Create sender/receiver as well as the table we wish to replicate between sites.

7. Create sender as follows
  
create diskstore cluster2store;

CREATE GATEWAYSENDER cluster1sender
(
  REMOTEDSID 1
  ENABLEPERSISTENCE true
  DISKSTORENAME cluster2store 
)
SERVER GROUPS (MYGROUP);

8. Create receiver as follows
  
CREATE GATEWAYRECEIVER test_receiver (startport 1550 endport 1561) 
server groups (MYGROUP);

Site 1

Create the table we wish to replicate. This must be done on each site as DDL is not replicated, only DML is.

9. Create table as shown below
  
CREATE TABLE test_table 
(ID INT NOT NULL, NAME VARCHAR(10))
GATEWAYSENDER(cluster2sender) SERVER GROUPS (MYGROUP);

Site 2

Create the table we wish to replicate. This must be done on each site as DDL is not replicated, only DML is.

10. Create table as shown below.
  
CREATE TABLE test_table 
(ID INT NOT NULL, NAME VARCHAR(10))
GATEWAYSENDER(cluster1sender) SERVER GROUPS (MYGROUP);

Verifying the setup

With this now setup we simply need to connect to either distributed system and insert some data into our table as shown below.

11. Connect to the first distributed system and insert some data as shown below.
  
[Tue May 29 10:47:47 papicella@:~/sqlfire/vFabric_SQLFire_102/pasdemos/wan-demo/dist1 ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1527';
sqlf> insert into test_table values (1, 'apples');
1 row inserted/updated/deleted
sqlf> commit;
sqlf> select * from test_table;
ID         |NAME      
----------------------
1          |apples    

1 row selected

12. Connect to the second distributed system to verify the data was replicated from test_table
  
[Tue May 29 10:46:31 papicella@:~/sqlfire/vFabric_SQLFire_102/pasdemos/wan-demo/dist2 ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1528';
sqlf> select * from test_table;
ID         |NAME      
----------------------
1          |apples    

1 row selected

For more information on multi site WAN deployments with SQLFire see the documentation below.

http://pubs.vmware.com/vfabric5/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.0/config_guide/topics/gateway-hubs/wan-steps.html


Monday, 21 May 2012

Simple example on how to caputure the query execution plan from SQLFire

The example below shows how we can capure the query plan for SQL running in SQLFire. Although any user can run an explain plan you must connect as a peer to show the query execution plan as the data is stored in the SYS.STATEMENTPLANS table.

1. Determine what indexes we currently have in the "APP" schema.

  
sqlf> show indexes in app;
TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES   
----------------------------------------------------------------------------
DEPT                |DEPTNO              |0     |3   |A   |NULL    |NULL    
EMP                 |EMPNO               |0     |3   |A   |NULL    |NULL    
EMP                 |DEPTNO              |1     |3   |A   |NULL    |NULL    
EMP                 |JOB                 |1     |3   |A   |NULL    |NULL    

4 rows selected

2. Run a query explain for an individual SQL statement and display the execution plan. In this example below we connect as a peer client to perform the opertion BUT we only need to conect as a peer client to actually display the execution plan and we can explain our SQL as a regular client user.
  
sqlf> connect peer 'host-data=false;mcast-port=12333';
sqlf> explain select * from emp where job = 'CLERK';
MEMBER_PLAN                                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------
ORIGINATOR 192-168-1-4.tpgi.com.au(2201)<v4>:11255/52510 BEGIN TIME 2012-05-21 20:02:03.659 END TIME 2012-05-21 20:02:03.694
DI&
Slowest Member Plan: 
member   192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450 begin_execution  2012-05-21 20:02:03.664 end_execu&
Fastest Member Plan: 
member   192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451 begin_execution  2012-05-21 20:02:03.664 end_execu&

3 rows selected
sqlf> select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
STMT_ID                             |STMT_TEXT                                                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
00000001-ffff-ffff-ffff-000300000016| select * from emp where job = 'CLERK'                                                                                          

1 row selected
sqlf> explain '00000001-ffff-ffff-ffff-000300000016';
stmt_id   00000001-ffff-ffff-ffff-000300000016 begin_execution  2012-05-21 20:02:03.659 end_execution  2012-05-21 20:02:03.694
QUERY-SCATTER execute_time 31696000 member_node 192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450,192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451
  QUERY-SEND execute_time 397000 member_node 192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450
    QUERY-SEND execute_time 178000 member_node 192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451
      RESULT-RECEIVE execute_time 150000 member_node 192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451
        RESULT-RECEIVE execute_time 35000 member_node 192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450
          SEQUENTIAL-ITERATION returned_rows 7 no_opens 1 execute_time 218000
            RESULT-HOLDER returned_rows 2 no_opens 1 execute_time 39000 member_node 192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451
              RESULT-HOLDER returned_rows 5 no_opens 1 execute_time 33000 member_node 192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450
                DISTRIBUTION-END returned_rows 7 execute_time 28521000
member   192-168-1-4.tpgi.com.au(2065)<v1>:55134/52451 begin_execution  2012-05-21 20:02:03.664 end_execution  2012-05-21 20:02:03.691
QUERY-RECEIVE execute_time 26501000 member_node 192-168-1-4.tpgi.com.au(2201)<v4>:11255/52510
  RESULT-SEND execute_time 35000 member_node 192-168-1-4.tpgi.com.au(2201)<v4>:11255/52510
    RESULT-HOLDER returned_rows 2 no_opens 1 execute_time 802000
      ROWIDSCAN returned_rows 2 no_opens 1 execute_time 62000
        INDEXSCAN returned_rows 2 no_opens 1 execute_time 6173000 scan_qualifiers None scanned_object EMP_JOB_IDX scan_type 
member   192-168-1-4.tpgi.com.au(2064)<v0>:39756/52450 begin_execution  2012-05-21 20:02:03.664 end_execution  2012-05-21 20:02:03.692
QUERY-RECEIVE execute_time 27574000 member_node 192-168-1-4.tpgi.com.au(2201)<v4>:11255/52510
  RESULT-SEND execute_time 25000 member_node 192-168-1-4.tpgi.com.au(2201)<v4>:11255/52510
    RESULT-HOLDER returned_rows 5 no_opens 1 execute_time 885000
      ROWIDSCAN returned_rows 5 no_opens 1 execute_time 108000
        INDEXSCAN returned_rows 5 no_opens 1 execute_time 6682000 scan_qualifiers None scanned_object EMP_JOB_IDX scan_type 
sqlf>

So the steps are as follows:

1. Run an explain for the SQL using the key word "EXPLAIN".
2. Connect as a peer
3. Show the query plan execution for the statement.

For more information on the query execution plan codes visit the link below.

http://pubs.vmware.com/vfabric5/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.0/manage_guide/explain-codes.html



Wednesday, 9 May 2012

vfabric Application Director 3 Tier Spring Travel Blueprint

Here are the steps used to create a 3 tier application using Spring travel with a MYSQL database. This is based on the screen shots from the previous blog entry.

1. Log into "vFabric Application Director" home page
2. Click on "Manage Applications".
3. Locate "Spring Travel" and select the icon for "Copy this application version" under the "Actions" column
4. Select radio option "Save as new application" and enter details as follows.



5. Click ok.
6. Click on the symbol  IMG6 "Convert to node array" to ensure we create a cluster of these VM's.
7. Set the cluster size to 2.
8. Click on the "Memory" column and set each VM to 1024M as shown below

IMG7

9. Press the "Save" button at the top right hand corner of the blueprint editor.

Note: Ignore any warning and save the changes.

10. Under "Logical Templates -> OS Templates" drop "CentOS56 32 bit 1.0.0" onto the canvas.
11.  Name it "load_balancer" and set the memory to 512M as shown below.

IMG8


12. Save the blueprint
13. Under "Services -> Web Servers" drop "Apache 2.2.0"
14. Name it "Apache_LB" for the "Details" column
15. In the top right hand corner use the icon IMG9to create a relationship from "Apache_LB" to "SpringTravelApp" as shown below.

IMG10

16. Save the blueprint
17. Click on "Apache_LB" and select the "Properties" column
18. Edit the property "http_node_ips" and ensure the blueprint property is set as shown below. This will we setup our http.conf for Apache to bind to the 2 tc Server VM's using the correct ip address.

IMG11

19. Edit the property "jvm_routes" and edit it use the blueprint value "all(SpringTravel:tcServer:JVM_ROUTE)". You should have something as follows.

IMG12

20. Save the blueprint.
21. Under "Logical Templates -> OS Templates" drop "CentOS56 32 bit 1.0.0" onto the canvas.
22. Name it "MySQLTier" and change the "Memory" to 1024M.
23. Under "Services -> Database Servers" drop "MySQL 5.0.0" onto the template
24. Under the "Properties"column edit the "db_root_password" to "welcome1" as shown below.

IMG13

25. Save the blueprint
26. In the top right hand corner use the icon IMG9to create a relationship from "SpringTravelApp" to "Mysql".
27. Drop from "Application Components" a "SQL Script" onto "Mysql"
28. In the top right hand corner use the icon IMG9to create a relationship from "SQL_SCRIPT" to "Mysql" which should give you a blueprint as follows at this point.

IMG14

29. Select "SpringTravelApp" and add a property as shown below to ensure we bind to the "MySQLTier" ip address.

IMG15

30. Edit the "war" property and set it's "Blueprint Value" to "http://dl.dropbox.com/u/15829935/se-demos/app-director/mysql/mysql-swf-booking-mvc.war"
31. Click on the "Actions" column
32. Double click on the "script" column for the lifecycle stage "CONFIGURE" and add contents as shown below.


#!/bin/sh

env > /tmp/env.txt

sleep 10

export mysqlip="${dbip}"

echo "mysql ip is ${mysqlip}"

cd $installdir/working/springsource-tc-server-standard/instance1/webapps/mysql-swf-booking-mvc/WEB-INF/config
sed -e "s/localhost/${mysqlip}/g" data-access-config.xml > data-access-config-NEW.xml
mv data-access-config-NEW.xml data-access-config.xml

33. Double click on the "script" column for the lifecycle stage "START" and add contents as shown below.

#!/bin/sh

env > /tmp/env.txt

cd $installdir/working/springsource-tc-server-standard/instance1/bin

./tcruntime-ctl.sh stop

./tcruntime-ctl.sh start


34. Click on "SQL_SCRIPT" on the "MySQLTier"
35. Click on the "Actions" column
36. Double click on the "script" column for the lifecycle stage "INSTALL" and add contents as shown below

#!/bin/bash

mysql -h localhost -u root -pwelcome1 < create database travel;
CREATE USER 'travel'@'%' IDENTIFIED BY 'travel';
GRANT ALL ON travel.* TO 'travel'@'%';
!

37. Save the blueprint
38. Click on the icon in the top right hand corner IMG16 to deploy the blueprint
39. Enter a deployment profile name as "SpringTravelMysql"
40. Click Ok
41. Click on "Map Details" button
42. Click Next
43. Click Next
44. Click Next
45. Click Deploy

A successfull deployment will look as follows once complete.