vFabric SQLFire User's Guide VMware vFabric SQLFire 1.0.3 VMware vFabric Suite 5.1 This document supports the version of each product listed and supports all subsequent versions until the document is replaced by a new edition. To check for more recent editions of this document, see http://www.vmware.com/support/pubs.
You can find the most up-to-date technical documentation on the VMware Web site at: http://www.vmware.com/support/ The VMware Web site also provides the latest product updates. If you have comments about this documentation, submit your feedback to: docfeedback@vmware.com Copyright © 2012 VMware, Inc. All rights reserved. This product is protected by U.S. and international copyright and intellectual property laws. VMware products are covered by one or more patents listed at http://www.vmware.
Contents About the SQLFire User's Guide.................................................................................13 Supported Configurations and System Requirements.............................................15 Part I: Getting Started with vFabric SQLFire............................................1 Chapter 1: Overview of vFabric SQLFire..................................................3 Data Flow Diagram...............................................................................................
Implement a Partitioning Strategy...............................................................................................................31 Persist Tables to Disk..................................................................................................................................34 Add Servers to the Cluster and Stop Servers.............................................................................................35 Perform Additional Tasks...............................................
Contents Disk Store Management.............................................................................................................................79 Chapter 15: Exporting and Importing Data with vFabric SQLFire.......89 Using SQLF Commands to Export and Import Data..................................................................................89 Using Apache DdlUtils to Import Data........................................................................................................
Connecting to a SQLFire Distributed System...........................................................................................133 Editing Tables............................................................................................................................................134 Chapter 23: Understanding the Data Consistency Model...................135 Data Consistency Concepts....................................................................................................................
Contents Chapter 31: Using a RowLoader to Load Existing Data......................175 How SQLFire Invokes a RowLoader.........................................................................................................175 Implementing the RowLoader Interface....................................................................................................176 Using the JDBCRowLoader Example.......................................................................................................
Specify Client Connection Information......................................................................................................216 Define Server Groups...............................................................................................................................216 Execute SQL When You Start a Server.....................................................................................................217 Using Additional Boot Properties..............................................
Contents Example Configuration.............................................................................................................................281 Starting the JMX Agent.............................................................................................................................282 Stopping the JMX Agent...........................................................................................................................286 Configuring JMX Connectors and Adapters.............
sqlf merge-logs.........................................................................................................................................353 sqlf remove-jar..........................................................................................................................................353 sqlf replace-jar..........................................................................................................................................355 sqlf revoke-missing-disk-store...........
Contents rollback......................................................................................................................................................417 run.............................................................................................................................................................418 set connection...........................................................................................................................................418 show...............
SYSTABLEPERMS system table..............................................................................................................620 SYSTABLES system table........................................................................................................................622 SYSTRIGGERS system table...................................................................................................................624 SYSVIEWS system table..............................................................
About the SQLFire User's Guide Revised July 31, 2012. The SQLFire User's Guide provides step-by-step procedures for installing, configuring, managing, and developing applications with VMware® vFabric™ SQLFire. The guide also provides a complete reference for the SQLFire tools, supported SQL statements, and APIs.
Supported Configurations and System Requirements Before installing vFabric SQLFire, make sure your system meets the minimum system requirements for installing and running the product. Before installing vFabric SQLFire, make sure your system meets the minimum system requirements for installing and running the product.
Host Machine Requirements Requirements for each host: • A supported Java SE installation. • File system that supports long file names. • Adequate per-user quota of file handles (ulimit for Linux) • TCP/IP. • System clock set to the correct time. • For each Linux host, the hostname and host files must be properly configured. See the system manpages for hostname and hosts. • Time synchronization service such as Network Time Protocol (NTP).
Part 1 Getting Started with vFabric SQLFire Getting Started with vFabric SQLFire provides step-by-step procedures for installing, configuring, and using VMware® vFabric™ SQLFire. The guide also explains main concepts and provides tutorials to help you quickly begin using SQLFire.
Chapter 1 Overview of vFabric SQLFire vFabric SQLFire is a memory-optimized, distributed database management system designed for applications that have demanding scalability and availability requirements. Applications can manage database tables entirely in memory, or they can persist tables to disk to reload the data after restarting the system. A SQLFire distributed system can be easily scaled out using commodity hardware.
Getting Started with vFabric SQLFire Data Flow Diagram GemFire, Apache Derby, and SQL Components vFabric SQLFire incorporates core vFabric GemFire technology and Apache Derby RDBMS components to provide a high-performance, distributed database management system. SQLFire extends standard SQL statements where necessary for creating and managing tables and configuring the SQLFire system. The sections that follow document how SQLFire utilizes the GemFire and Derby component functionality.
Overview of vFabric SQLFire The SQLFire community site provides a comparison of SQLFire to other data management systems, such as vFabric GemFire. Apache Derby RDBMS Components SQLFire integrates vFabric GemFire functionality with several components of the Apache Derby relational database management system (RDBMS): • JDBC driver. SQLFire supports a native, high performant JDBC driver (peer driver) and a thin JDBC driver. The peer driver is based on the Derby embedded driver and JDBC 4.
Chapter 2 Understanding the SQLFire Distributed System A SQLFire deployment consists of distributed member processes that connect to each other to form a peer-to-peer network, also known as a distributed system or SQLFire cluster. The sections that follow explain the interactions of main system components and processes. Tutorials on page 27 help you get started configuring and using a SQLFire distributed system.
Getting Started with vFabric SQLFire Servers, Peer Clients, and Server Groups A SQLFire server is a process that hosts data and is a member of a peer-to-peer distributed system. SQLFire servers run in Java Virtual Machines (JVMs). You start a SQLFire server using the sqlf tool from a command prompt or terminal window. sqlf launches servers as standalone processes that are akin to database servers.
Understanding the SQLFire Distributed System with certain consistency guarantees. Each member in the group participates in membership decisions, which ensures that either all members see a new member or no members see it. The membership coordinator, a key component of the GMS, handles "join" and "leave" requests, and also handles members that are suspected of having left the system.
Getting Started with vFabric SQLFire order_credit_check(?) "); callableStmt.setArray(1, ); // SQLFire data-aware procedure invocation CallableStatement callableStmt = connection.prepareCall("{CALL order_credit_check() " + "ON TABLE Orders WHERE customerID IN (?)}"); callableStmt.
Chapter 3 Installing vFabric SQLFire You can install vFabric SQLFire from the VMware yum repository (RHEL only) or from a downloaded JAR file. Installation procedures also vary according to whether you obtained vFabric SQLFire as a standalone product or as part of VMware® vFabric Suite™ Advanced. Installation Note for vFabric Suite Customers vFabric SQLFire is available as a standalone product installation, and as part of vFabric Suite Advanced.
Getting Started with vFabric SQLFire For RHEL 5: rpm -Uvh http://repo.vmware.com/pub/rhel5/vfabric/5.1/vfabric-5.1-repo-5.1-1.noarch.rpm rpm -Uvh http://repo.vmware.com/pub/rhel5/vfabric-all/vfabric-all-repo-1-1.noarch.rpm For RHEL 6: rpm -Uvh http://repo.vmware.com/pub/rhel6/vfabric/5.1/vfabric-5.1-repo-5.1-1.noarch.rpm rpm -Uvh http://repo.vmware.com/pub/rhel6/vfabric-all/vfabric-all-repo-1-1.noarch.rpm 3.
Installing vFabric SQLFire • If you want to start up only a local locator process and not a local server instance, set the LOCATOR property to locator_only. This sets up a redundant locator configuration; be sure you add the locator IP addresses and port numbers to the LOCATOR_IP_STRING; an example is shown in the configuration file. • Finally, set the LOCATOR property to remote if you want to start a local server instance that relies on having locator processes running on one or more remote hosts.
Getting Started with vFabric SQLFire 7. To begin using the sqlf utility to start servers or execute other commands, add the vFabric_SQLFire_10x/bin directory to your path. For example: export PATH=$PATH:/vFabric_SQLFire_10x/bin The sqlf script automatically sets the class path relative to the installation directory. 8. Repeat this procedure to install and license SQLFire on each different computer where you want to run a SQLFire member.
Chapter 4 Activating vFabric SQLFire Licensing vFabric SQLFire includes a default evaluation license that enables you to run the product tutorial and perform simple evaluation activities. You can obtain custom evaluation licensing and purchase production licensing from your VMware account manager or the VMware license portal. Understand vFabric SQLFire License Options vFabric SQLFire has a number of licensing options. Read the following sections to understand the different license types.
Getting Started with vFabric SQLFire SQLFire licenses do not restrict the number of clients that can connect to a distributed system. Any number of thin clients and peer clients (SQLFire accessor members) are permitted. vFabric SQLFire Local Licensing and vFabric Suite Licensing If you obtained vFabric SQLFire as a standalone product, you install a license locally on each physical and virtual machine that runs vFabric SQLFire.
Activating vFabric SQLFire Licensing Installing a vFabric SQLFire Professional License 1. Obtain a product vFabric SQLFire Professional license from VMware. 2. Install the license in SQLFire. Choose one of the following options: • On each SQLFire data store member, add the vFabric SQLFire license serial number directly to the sqlfire.properties file. See Option 1: Install Licenses Using sqlfire.
Getting Started with vFabric SQLFire 1. Obtain a vFabric SQLFire Enterprise license. You can use the same serial number in each distributed system; however, as a best practice you should obtain a unique serial number for each distributed system that communicates over the WAN. 2. Install the license in SQLFire. Choose one of the following options: • On each data store member in the distributed system, add the vFabric SQLFire Enterprise serial number directly to the sqlfire.properties file.
Activating vFabric SQLFire Licensing Leave these files alone. Do not edit or delete these files, and do not alter the permissions on the files or the directory where these files are located. These files are created using the default permissions of the user who is starting up the SQLFire process. To stop or start the SQLFire process, a user needs write permissions for this directory and the files in it.
Getting Started with vFabric SQLFire This is the recommended way to install licensing for standalone SQLFire deployments. (For SQLFire deployments installed on vSphere virtual machines as part of a vFabric Suite deployment, VMware recommends that you use the vFabric License Server. See vFabric Suite Only: Configure vFabric SQLFire for vFabric License Server on page 20.) For example: # sqlfire.
Activating vFabric SQLFire Licensing Verify Your License and Check Your License Usage You can verify the validity of your license and monitor your license usage. If you install an invalid serial number or if SQLFire cannot obtain a dynamic license from the vFabric License Server, SQLFire fails to start and throws an exception. In Getting Started with vFabric Suite, see "Using the vFabric Licensing Check Tool" to learn how to verify your license.
Chapter 5 Upgrading vFabric SQLFire SQLFire 1.0.x is compatible with persistence files (disk stores) created in earlier versions of SQLFire. If you use the JAR file installation program, simply install SQLFire 1.0.x into a new, dedicated directory, and update your path to use the new vFabric_SQLFire_10x/bin directory (for example, vFabric_SQLFire_103/bin). Refer to the Release Notes to learn about changes that were introduced in the latest update.
Getting Started with vFabric SQLFire 4. If you use the default SQLFire server configuration, then you must copy user data from the old server installation directory to the new, shared data directory (/var/opt/vmware/sqlfire). For example, if you were upgrading from version 1.0.1 you would enter: prompt# cp -Rp /opt/vfabric/sqlfire/vFabric_SQLFire_101/datadictionary \ /var/opt/vmware/sqlfire/ prompt# cp -p /opt/vfabric/sqlfire/vFabric_SQLFire_101/vf.sf-license.cfg \ /var/opt/vmware/sqlfire/ 5.
Chapter 6 Connect to vFabric SQLFire with JDBC Tools Third-party JDBC tools can help you browse data in tables, issue SQL commands, design new tables, and so forth. You can configure these tools to use the SQLFire JDBC thin client driver to connect to a SQLFire distributed system. Although the instructions for setting up each tool vary, the general process for establishing a connection involves configuring the JDBC client driver and setting JDBC connection URL properties. Follow these basic steps: 1.
Chapter 7 Tutorials Learn to configure and use SQLFire features such as table replication and partitioning, persisting data to disk, and dynamically resizing the cluster. Main Steps The tutorial is divided into the following steps, which explain how to set up a cluster of SQLFire servers on multiple Java VMs and then distribute data across the cluster. Perform the steps in the sequence shown.
Getting Started with vFabric SQLFire 4. To manage client connections to the available SQLFire server members, the cluster in this tutorial uses a SQLFire locator member. Create a new directory for the locator member: mkdir locator A locator maintains a list of available servers in the cluster, and updates that list as servers join and leave the cluster. Locators also load balance client connections across all available servers. 5.
Tutorials sqlf server start -dir=server2 -locators=ip_address[10101] -client-bind-address=ip_address -client-port=1529 & In each command, the -locators option defines the peer discovery address to use for joining the SQLFire distributed system. (Production deployments generally use multiple locator members, in which case you would specify a comma-separated list of locator host[port] connections when starting a server.
Getting Started with vFabric SQLFire 3. Print a brief list of the available sqlf commands: help; 4. To connect to the SQLFire cluster using the JDBC thin client driver, use the connect client command and specify the host and port number of the SQLFire locator: connect client 'ip_address:1527'; Notice that SQLFire does not have the concept of a "database".
Tutorials ECONOMY_SEATS INTEGER, BUSINESS_SEATS INTEGER, FIRSTCLASS_SEATS INTEGER ); 0 rows inserted/updated/deleted [...] 2. Run the loadTables.sql script to populate the tables with data: run 'loadTables.sql'; The script output completes with: sqlf> insert into FLIGHTAVAILABILITY values ('US1357',2,'2004-04-18',0,0,3); 1 row inserted/updated/deleted 3. Enter the following command to show the table names that you created (tables in the APP schema): show tables in APP; 4.
Getting Started with vFabric SQLFire The AIRLINES, CITIES, COUNTRIES, and MAPS tables are treated as dimension tables, and are replicated across the SQLFire cluster. In the tutorial it is assumed that applications frequently join these related tables based on the FLIGHT_ID column, which is chosen as the partitioning column. FLIGHTS, FLIGHTS_HISTORY, and FLIGHTAVAILABILITY are fact tables, and they will be partitioned.
Tutorials ) PARTITION BY COLUMN (FLIGHT_ID); The remaining facts tables are also partitioned, and also colocated with the FLIGHTS table.
Getting Started with vFabric SQLFire Persist Tables to Disk At this point, the SQLFire cluster manages the ToursDB tables only in memory. In this procedure you persist table data to disk. Procedure 1. In a separate terminal window or GUI editor, examine the contents of the create_persistent_schema.sql script. Notice that this script uses the PERSISTENT keyword in each CREATE TABLE statement.
Tutorials When you restart the servers, you may notice messages similar to: [info 2012/07/24 10:18:11.949 PDT tid=0x1] Region /_DDL_STMTS_META_REGION initialized with data from /10.118.33.206:/Users/yozie/vFabric_SQLFire_103/server1/./datadictionary created at timestamp 1343149012698 version 0 diskStoreId 1fc6a853-69d6-4ffe-8029-218acf165c34 is waiting for the data previously hosted at [/10.118.33.206:/Users/yozie/vFabric_SQLFire_103/server2/.
Getting Started with vFabric SQLFire 8. You can stop an individual SQLFire server by using the sqlf server stop command and specifying the server directory.
Chapter 8 vFabric SQLFire Features and Benefits The sections that follow summarize main features. The SQLFire community site provides additional information about the features and benefits of SQLFire and a comparison of SQLFire to other data management systems. See also the SQLFire product page.
Part 2 Managing Your Data in vFabric SQLFire Managing Your Data in vFabric SQLFire describes how to design your database schema to take advantage of replication and partitioning in a distributed SQLFire cluster. You can use this information to design new databases, or to adapt an existing database for use with SQLFire. This guide also describes how to persist your data to disk store files, and it provides a simple procedure for importing an existing database into SQLFire.
Chapter 9 Designing vFabric SQLFire Databases Unlike in traditional database design, SQLFire requires the designer to analyze the common access patterns and choose a partitioning strategy that results in queries that are pruned to a single partition. In addition, fact tables may need to be replicated in order to make data available for join operations against partitioned data. This chapter describes the basic table design principles that you can use to achieve linear scaling with SQLFire.
Managing Your Data in vFabric SQLFire • In an online auction application, you may need to stream incoming auction bids to hundreds of clients with very low latency. To do so, you must manage selected "hot" auctions on a single partition so that they receive sufficient processing power. As the processing demand increases, add more partitions and route the application logic that matches bids to clients to the data store itself.
Designing vFabric SQLFire Databases • Use parallel, data-aware procedures to run the logic for the query on the member that stores some or all of the data (to minimize data distribution hops). The procedure should execute multiple queries and then join the results using application code. Using Data-Aware Stored Procedures on page 143 provides more information. • Split the query into multiple queries, and perform the join in the application client code.
Managing Your Data in vFabric SQLFire Step 1: Determine the entity groups Entity groups are generally course-grained entities that have children, grand children, and so forth, and they are commonly used in queries. This example chooses these entity groups: Entity group Description Customer This group uses the customer identity along with orders and order details as the children. Product This group uses product details along with the associated supplier information.
Designing vFabric SQLFire Databases Entity group Tables Order Details Product Product Suppliers Category Step 3: Define the partitioning key for each group In this example, the partitioning keys are: Entity group Partitioning key Customer CustomerID Product ProductID This example uses customerID as the partitioning key for the Customer group. The customer row and all associated orders will be collocated into a single partition.
Managing Your Data in vFabric SQLFire So for the product entity group, table Products is partitioned by ProductID, and the Suppliers and Categories tables are replicated to all of the members where Products is partitioned. Applications can now join Products, Suppliers and categories. For example: select * from Products p , Suppliers s, Categories c where c.categoryID = p.categoryID and p.supplierID = s.supplierID and p.
Chapter 10 Using Server Groups to Manage Data Use server groups to control where table data is stored. Server Groups Overview A server group specifies the SQLFire members that will host data for a table. You use a server group to logically group SQLFire data stores for managing a table's data. Any number of SQLFire members that host data can participate in one or more server groups. You specify named server groups when you start a SQLFire data store.
Managing Your Data in vFabric SQLFire A SQLFire member that is booted with host-data=false is an accessor and does not host table data, even if you specify one or more server groups. However, peer clients that host data can also participate in server groups.
Using Server Groups to Manage Data By default, all servers that host data are added to the "default" server group. Different logical database schema are often managed in different server groups. For example, an order management system might manage all customers and their orders in an "Orders" schema deployed to one server group. The same system might manage shipping and logistics data in a different server group.
Managing Your Data in vFabric SQLFire For example, if you start a SQLFire server from the command line with sqlf, use the server-groups property to specify the names of one or more server groups that the server should join: sqlf server start -server-groups=OrdersDB,OrdersReplicationGrp,DBProcessInstance1 In this example, the SQLFire server participates in three server groups: OrdersDB, OrdersReplicationGrp and DBProcessInstance1.
Chapter 11 Partitioning Tables Horizontal partitioning involves spreading a large data set (many rows in a table) across members in a cluster. SQLFire uses a variant of the consistent hash algorithm to ensure that data is uniformly balanced across all members of the target server group. How Table Partitioning Works You specify the partitioning strategy of a table in the PARTITION BY clause of the CREATE TABLE statement.
Managing Your Data in vFabric SQLFire Figure 2: Partitioned Table Data Although each bucket is assigned to one or more specific servers, you can use a procedure to relocate buckets in a running system, in order to improve the utilization of resources across the cluster. See Rebalancing Partitioned Data on SQLFire Members on page 59. Understanding Where Data Is Stored SQLFire uses a table's partitioning column values and the partitioning strategy to calculate routing values (typically integer values).
Partitioning Tables Figure 3: Partitioned Data in Buckets If you set the redundant-copies for the table to be greater than zero, SQLFire designates one of the copies of each bucket as the primary copy. All writes to the bucket go through the primary copy. This ensures that all copies of the bucket are consistent.
Managing Your Data in vFabric SQLFire Creating Partitioned Tables You create a partitioned table on a set of servers identified by named server groups (or on the default server group if you do not specify a named server group). Clauses in the CREATE TABLE statement determine how table data is partitioned, colocated, and replicated across the server group.
Partitioning Tables [ EXPIRE { TABLE | ENTRY } WITH { IDLETIME value | TIMETOLIVE value} ACTION { DESTROY | INVALIDATE } ]* [ PERSISTENT ] [ 'disk-store-name' ] [ ASYNCHRONOUS | SYNCHRONOUS ] Note: This topic focuses only on the partitioning_clause. The CREATE TABLE reference page describes all of the options in more detail. The partitioning_clause controls the location and distribution of data in server groups.
Managing Your Data in vFabric SQLFire Partitioning strategy Description List partitioning The PARTITION BY LIST clause specifies the set of values of a field that should be colocated to optimize queries and to support cross-table joins. It is not necessary to list all of the possible values for the field. Any the values that are not part of the list are automatically partitioned in the server group, but with no guarantee of locality for those values.
Partitioning Tables VALUES BETWEEN 31 AND 50 ); Partition Based on a List When you use the PARTITION BY LIST clause, specify a column name and one or more lists of column values to use for partitioning.
Managing Your Data in vFabric SQLFire ranges). The columns must also be of the same type, not considering constraints. Any REDUNDANCY or BUCKETS clause must also be the same as the tables with which it is colocated. Note: In order for two partitioned tables to be colocated, the SERVER GROUPS clauses in both CREATE TABLE statements must be identical.
Partitioning Tables Set the redundancy-zone boot property to ensure that SQLFire places redundant copies on specific zones that you define. For example, to ensure that redundant copies are placed on different racks, set the redundancy-zone for each machine to the logical name of the rack on which the machine runs.
Managing Your Data in vFabric SQLFire For tables that are configured with expiration based on idle time, the rebalancing operation resets the last accessed time of the table entries on buckets that are moved. When to Rebalance a Partitioned Table You typically want to trigger rebalancing when overall capacity is increased or reduced through member startup, shut down or failure.
Chapter 12 Replicating Tables SQLFire server groups control which SQLFire data store members replicate the table's data. SQLFire replicates table data both when a new table is initialized in a cluster and when replicated tables are updated. How SQLFire Replicates Tables SQLFire replicates every single row of a replicated table synchronously to each table replica in the target server group(s).
Managing Your Data in vFabric SQLFire Also note that this version of SQLFire supports joins only on co-located data. Instead of using partitioning in all cases, you should consider having applications replicate smaller tables that are joined with other partitioned tables. Note: If multiple applications update the same row of a replicated table at the same time outside of a transaction, the table data can become out of sync when SQLFire replicates those updates.
Chapter 13 Estimating Memory Requirements Designing a SQLFire database also involves estimating the memory requirements for your data based on the size of the actual table values and indexes, the overhead that SQLFire requires for your data, and the overall usage pattern for your data. You can estimate the memory requirements for tables using general guidelines for SQLFire overhead. Or, you can load tables with representative data and then query the SQLFire SYS.
Managing Your Data in vFabric SQLFire Viewing Memory Usage in SYS.MEMORYANALYTICS SQLFire includes instrumentation to display the memory used by individual tables and indexes in a SQLFire member. You can view this memory usage information by starting a Java agent process when you boot SQLFire members, and then querying the SYS.MEMORYANALYTICS virtual table from a client connection. Enabling SYS.MEMORYANALYTICS Follow this procedure to enable the SYS.MEMORYANALYTICS virtual table.
Estimating Memory Requirements APP.FLIGHTS.DESTINDEX (Index Entry Size, Value Size, Row Count) |dyozie-e4310(6880):3439/59731 |4888,3784,87 [...] Understanding Table and Index Values Querying the SYS.MEMORYANALYTICS table provides run-time information about the tables and indexes available in SQLFire. Table Values Each table has a single row in SYS.MEMORYANALYTICS identitfied with the SQLENTITY format: schema_name.table_name (Entry Size, Value Size, Row Count).
Managing Your Data in vFabric SQLFire For example, the following row from SYS.MEMORYANALYTICS shows that APP.FLIGHTS.DESTINDEX uses 2803 bytes for skip lists: SQLENTITY |ID |MEMORY ---------------------------------------------------------------APP.FLIGHTS.DESTINDEX (Index Entry Overhead, SkipList Size, Max& |dyozie-e4310(6880):3439/59731 |2104,696,3 (2803 = 2.74 kb) The second row for an index uses the SQLENTITY format: schema_name.table_name.index_name (Index Entry Size, Value Size, Row Count).
Estimating Memory Requirements |dyozie-e4310(6880):3439/59731 |5072,0,3288 (8360 = 8.16 kb) [...
Chapter 14 Using Disk Stores to Persist Data You can persist table data to disk as a backup to the in-memory copy, or overflow table data to disk when memory use gets too high. Overview of Disk Stores The two disk store options, overflow and persistence, can be used individually or together. Overflow uses disk stores as an extension of in-memory table management for both partitioned and replicated tables. Persistence stores a redundant copy of all table data managed in a peer.
Managing Your Data in vFabric SQLFire data stores available in the distributed system, SQLFire throws a data store unavailable exception (SQLState: X0Z08). You must start locators and data stores before starting peer clients in your distributed system. If you start a peer client as the first member of a distributed system, the client initializes an empty data dictionary for the distributed system as a whole.
Using Disk Stores to Persist Data Disk Store Directories When you create a disk store, optionally specify the location of directories where sqlf stores persistence-related files. sqlf generates disk store artifacts in a directory that it chooses in the following order: 1. If you provide absolute directory paths, sqlf uses the paths as-is. You must ensure that the proper directory structure exists. 2.
Managing Your Data in vFabric SQLFire First Part of File Name: Usage Identifier Usage Identifier Values Usage Examples OVERFLOW Oplog data from overflow tables and queues OVERFLOWoverflowDS1_1.crf only. BACKUP Oplog data from persistent and persistent+overflow tables and queues. BACKUPoverflowDS1.if, BACKUPSQLF-DEFAULT-DISKSTORE.if DRLK_IF Access control - locking the disk store. DRLK_IFoverflowDS1.lk, DRLK_IFSQLF-DEFAULT-DISKSTORE.
Using Disk Stores to Persist Data File Extension Values Usage Notes drf Oplog: delete operations Pre-allocated 10% of the total max-oplog-size at creation. Disk Store Operation Logs At creation, each operation log is initialized at the disk store's MAXLOGSIZE value, with the size divided between the crf and drf files. SQLFire only truncates the unused space on a clean shutdown (for example, sqlf server stop or sqlf shut-down-all).
Managing Your Data in vFabric SQLFire Avoiding Seeks Random access on disk, which causes disk heads to seek for hundreds of concurrent thread, is probably the single biggest reason why traditional databases do not scale. Average disk seek times today are still 2ms or higher. SQLFire manages most (or all) of the data in cluster memory, and all reads are served without navigating through BTree-based indexes and data files on disk. This is the case when data is persistent.
Using Disk Stores to Persist Data Consider these guidelines when designing disk stores: • Tables can be overflowed, persisted, or both. For efficiency, place table data that is overflowed on one disk store with a dedicated physical disk. Place table data that is persisted, or persisted and overflowed, on another disk store with on a different physical disk. For example, gateway sender, AsyncEventListener, and DBSynchronizer queues are always overflowed and may be persisted.
Managing Your Data in vFabric SQLFire Using the Default Disk Store Tables that do not name a disk store but specify persistence or overflow in their CREATE TABLE statement are automatically assigned to the default disk store, SQLF-DEFAULT-DISKSTORE. Also, gateway, AsyncEventListener, and DBSynchronizer queues always use the default disk store.
Using Disk Stores to Persist Data Optimizing Availability and Performance Be aware of what you can do to optimize availability and performance of disk stores. Consider these guidelines: • When you start your system, start all the members that have persistent tables in parallel. Create and use startup scripts for consistency and completeness. • Shut down your system using the sqlf shut-down-all command. This is an ordered shutdown that positions your disk stores for a faster startup.
Managing Your Data in vFabric SQLFire If your log level is info or below, the system provides messaging about the wait. Here, the disk store for hostA has the most recent data and the hostB member is waiting for it. [info 2010/04/09 10:48:26.039 PDT CacheRunner tid=0x1] Region /persistent_PR initialized with data from /10.80.10.64:/export/straw3/users/jpearson/GemFireTesting/hostB/ backupDirectory created at timestamp 1270834766425 version 0 is waiting for the data previously hosted at [/10.80.10.
Using Disk Stores to Persist Data The following example bash script starts members in parallel. The script waits for the startup to finish and exits with an error status if one of the jobs fails. #!/bin/bash ssh servera "cd /my/directory; sqlf server start & ssh serverb "cd /my/directory; sqlf server start & STATUS=0; for job in `jobs -p` do echo $job wait $job; JOB_STATUS=$?; test $STATUS -eq 0 && STATUS=$JOB_STATUS; done exit $STATUS; 2. Respond to any member startup hangs.
Managing Your Data in vFabric SQLFire For the complete command syntax of any sqlf command, run sqlf --help at the command line. Online sqlf Disk Store Operations For online operations, sqlf connects to a distributed system and sends the operation requests to the members that have disk stores. These commands do not run on offline disk stores. You must provide the command with a distributed system properties in a sqlfire.
Using Disk Stores to Persist Data • Example Compaction Run on page 82 How Compaction Works When a DML operation is added to a disk store, any preexisting operation record for the same record becomes obsolete, and SQLFire marks it as garbage. For example, when you update a record, the update operation is added to the store. If you delete the record later, the delete operation is added and the update operation becomes garbage.
Managing Your Data in vFabric SQLFire • Set the disk store attribute ALLOWFORCECOMPACTION to true. This causes SQLFire to maintain extra data about the files so that it can compact on demand. This is disabled by default to save space. You can run manual online compaction at any time while the system is running. Oplogs eligible for compaction based on the COMPACTIONTHRESHOLD are compacted into the current oplog. • Run manual compaction as needed.
Using Disk Stores to Persist Data -rw-rw-r-drwxrwxr-x -rw-rw-r--rw-rw-r--rw-rw-r-drwxrwxr-x 1 3 1 1 1 2 jpearson jpearson jpearson jpearson jpearson jpearson users users users users users users 25 1024 7085 18 1070 512 Apr Apr Apr Apr Apr Apr 7 7 7 7 7 7 14:56 15:02 15:06 15:07 15:07 15:07 BACKUPds1_3.crf .. BACKUPds1.if BACKUPds1_4.drf BACKUPds1_4.crf . bash-2.
Managing Your Data in vFabric SQLFire You can specify a directory by one of two methods; the command in the procedure below uses the first method. • Use a single physical location, such as a network file server. Example: /export/fileServerDirectory/sqlfBackupLocation • Use a directory that is local to all host machines in the system. Example: ./sqlfBackupLocation Back Up an Online Disk Store 1. Run the backup command, providing your backup directory location.
Using Disk Stores to Persist Data Procedure 1. Read the restore scripts to see where they will place the files and make sure the destination locations are ready. The restore scripts refuse to copy over files with the same names. 2. Run each restore script on the host where the backup originated. In Windows, the file is restore.bat and in Linux, it is restore.sh. The restore copies the files back to their original location.
Managing Your Data in vFabric SQLFire Why Disk Stores Go Missing Disk stores usually go missing because their member fails to start. The member can fail to start for a number of reasons, including: • Disk store file corruption. You can check on this by validating the disk store.
Using Disk Stores to Persist Data /export/straw3/users/jpearson/testGemFire/hostB/DS1 sqlf revoke-missing-disk-store straw.gemstone.com /export/straw3/users/jpearson/testGemFire/hostB/DS1 Connecting to distributed system: mcast=/239.192.81.2:12348 revocation was successful ... Note: Make sure this sqlf call can find a sqlfire.properties file to locate the distributed system. Or, specify the multicast port or locator properties to connect to the cluster (for example, -mcast-port= port_number).
Chapter 15 Exporting and Importing Data with vFabric SQLFire You can use either sqlf commands or Apache DdlUtils to import data into SQLFire. SQLFire installs and uses DdlUtils for sqlf import and export functionality. Using SQLF Commands to Export and Import Data The sqlf utility provides several commands that use the DdlUtils 1.1 API to export and import database schemas and table data. You can use these sqlf commands with SQLFire and other JDBC datasources.
Managing Your Data in vFabric SQLFire Export, Alter, and Import a Database Schema Using SQLF Use sqlf commands to export the schema and data from a third-party database, and then import the schema and data to vFabric SQLFire. Procedure 1. To use the sqlf export commands with a third-party database, you require a JDBC driver and connection URL for the database. Use a Java client application such as SQuirreL SQL to verify that you can connect to the database.
Exporting and Importing Data with vFabric SQLFire -url=jdbc:mysql://localhost:3306/sqlfdb?user=username&password=password 7. Use the sqlf write-data-to-db command and specify both the data XML file and the schema XML file to import the data to SQLFire: sqlf write-data-to-db -files=mysql-data.xml -schema-files=mysql-schema.xml -client-bind-address=localhost -client-port=1527 8. If necessary, manually add triggers, views, and Java stored procedures in the SQLFire database.
Managing Your Data in vFabric SQLFire 4. Make sure that you can connect to the external database and to SQLFire servers. You will need to supply the connection URL and properties for both databases when using DdlUtils. 5. Edit the build.xml file in the ddlutils/example directory to specify the JDBC connection information for both SQLFire and the database from which you are importing data. The example build file contains sample entries for SQLFire and MySQL.
Exporting and Importing Data with vFabric SQLFire --> PAGE 110Managing Your Data in vFabric SQLFire
