Search This Blog

Tuesday 14 May 2013

Naming Members in vFabric SQLFire

I find it useful to give a member a meaningful  name. In SQLFire you could simply give each member a name by adding a property "name" as follows to the sqlfire.properties file for the member.

sqlfire.properties

# sqlfire.properties for data store or accessor member
license-serial-number=XXXXXXXXXXX
name=server1


Note: The same can be done with GemFire as well.

Then when the system is up the ID for each system member includes the given name as shown below.

  
sqlf> select substr(id, 1 , 35) as "Member" from sys.members;
Member                             
-----------------------------------
172.16.62.1(server2:38971)<v2>:4265
172.16.62.1(server1:38970)<v1>:1660
127.0.0.1(38744):29535             

3 rows selected

Thursday 2 May 2013

JMX access to vFabric SQLFire

With the release of vFabric SQLFire 11 we can now start a JMX manager with the locator itself. To do that we add the following to the sqlfire.properties file of the locator itself.

jmx-manager=true
jmx-manager-start=true
jmx-manager-ssl=false
jmx-manager-http-port=8083


Then with the locator started we can verify we have it running on the default port of 1099 as shown below.

[Thu May 02 09:45:49 papicella@:~/sqlfire/vFabric_SQLFire_11_b40332/pasdemos/agent-test/locator ] $ netstat -an | grep 1099
tcp4       0      0  127.0.0.1.1099         127.0.0.1.64803        ESTABLISHED
tcp4       0      0  127.0.0.1.1099         127.0.0.1.64801        ESTABLISHED
tcp4       0      0  127.0.0.1.64801        127.0.0.1.1099         ESTABLISHED
tcp4       0      0  127.0.0.1.64803        127.0.0.1.1099         ESTABLISHED
tcp4       0      0  127.0.0.1.1099         127.0.0.1.64799        ESTABLISHED
tcp4       0      0  127.0.0.1.64799        127.0.0.1.1099         ESTABLISHED
tcp46      0      0  *.1099                 *.*                    LISTEN    


Finally start jconsole and connect using a service URL as follows

Format:

service:jmx:rmi://{hotname}/jndi/rmi://{hostname}:1099/jmxrmi

Example:

service:jmx:rmi://Pas-Apicellas-MacBook-Pro.local/jndi/rmi://Pas-Apicellas-MacBook-Pro.local:1099/jmxrmi

Once connected you can browse the MBean as shown in the image below.



More Information

http://pubs.vmware.com/vfabric53/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.1/manage_guide/jmx/jmx_intro.html

Wednesday 1 May 2013

Explain Plan in vFabric SQLFire improved

With the recently released vFabric SQLFire 11 version the query execution plan is much easier to read then previously. An example below.

  
[Wed May 01 11:32:11 papicella@:~/sqlfire/vFabric_SQLFire_11_b40332/pasdemos/sqlfire ] $ sqlf
sqlf version 10.4
sqlf> connect peer 'bind-address=localhost;mcast-port=12333;host-data=false' as peerClient;
sqlf> explain select * from emp where deptno = 20;
MEMBER_PLAN                                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------
ORIGINATOR 192.168.14.167(73118)<v6>:61492 BEGIN TIME 2013-05-01 11:32:39.735 END TIME 2013-05-01 11:32:39.777
DISTRIBUTION to &
Slowest Member Plan:
member 192.168.14.167(72048)<v1>:42223 begin_execution 2013-05-01 11:32:39.74 end_execution 2013-05-01 11:&
Fastest Member Plan:
member 192.168.14.167(72048)<v1>:42223 begin_execution 2013-05-01 11:32:39.74 end_execution 2013-05-01 11:&

3 rows selected
sqlf> select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
STMT_ID                             |STMT_TEXT                                                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
00000001-ffff-ffff-ffff-000400000016| select * from emp where deptno = <?>                                                                                           

1 row selected
sqlf> explain '00000001-ffff-ffff-ffff-000400000016';
stmt_id 00000001-ffff-ffff-ffff-000400000016 SQL_stmt select * from emp where deptno = <?> begin_execution 2013-05-01 11:32:39.735 end_execution 2013-05-01 11:32:39.777
QUERY-SCATTER  execute_time 0.0 ms
  QUERY-SEND 
    RESULT-RECEIVE 
      SEQUENTIAL-ITERATION (0.38%) execute_time 0.136 ms returned_rows 5 no_opens 1
        RESULT-HOLDER  returned_rows 5 no_opens 1
          DISTRIBUTION-END (99.61%) execute_time 35.073 ms returned_rows 5
member 192.168.14.167(72048)<v1>:42223 begin_execution 2013-05-01 11:32:39.74 end_execution 2013-05-01 11:32:39.774
QUERY-RECEIVE 
  RESULT-SEND 
    RESULT-HOLDER  returned_rows 5 no_opens 1
      ROWIDSCAN (1.71%) execute_time 0.148 ms returned_rows 5 no_opens 1 node_details EMP : 
        CONSTRAINTSCAN (98.28%) execute_time 8.482 ms returned_rows 5 no_opens 1 scan_qualifiers None scanned_object APP.6__EMP__DEPTNO:base-table:APP.EMP scan_type  node_details WHERE : ((DEPTNO = CONSTANT:20) and true)