Home > Blogs > VMware vFabric Blog


3 Game Changing Capabilities in SQLFire

According to one of our partners, vFabric SQLFire is a product he wishes more customers would use.

“SQLFire is a game-changer.  I think many companies underestimate the value of scaling the data later horizontally.  Every project I propose has a business case, and I see a tremendous amount of value being unlocked with this product—not just for the CIO or CTO’s agenda, but for the CFO and CEO.  Then, you add the fact that the whole application stack is virtualized and has solid integrations. It’s a simple story, the product allows you to add a lot of value in a really cost effective way.”

What makes SQLFire such a game-changer?

In this article, we’ll talk more about three game-changing capabilities: server groups, partitioning, and redundancy.

If you haven’t been following our stories on SQLFire, see the end of this article for a  list of posts and key capabilities that help explain how transformative SQLFire can be to your data management strategies.

SQLFire Topology Review

The main three topologies that are available as background for this article:

  • Client/Server. This is the traditional “client/server” database model, but with the servers forming distributed data grid. Each SQLFire member (node) runs it’s own JVM to provide network distribution, in-memory storage, disk persistence management, and more. These nodes are presented to the client as a single data management cluster (i.e. clients don’t participate in distributed queries).
  • Embedded Peer-to-Peer. In this model, Java application servers and SQLFire peers share the same process heap for things like session state, web content, and back office data.
  • Global, Multisite. You can configure a multisite topology that spans data centers (even with across long distances) for active-active disaster recovery.

SQLFire Server Groups

Server Groups truly begin to enable the concept of a data grid or fabric. With server groups, you can specify which grid members (i.e. nodes) host data for a given table, and members can participate in one or more server groups.  There are several very cool things about this:

  • You could put different logical database schema in different server groups—you could put CRM in one group, web profile data in another, orders in a third group, inventory in a fourth group, and social profiles in a fifth group.  Even with multiple databases on the grid, developers can still look at a server group as a single, logical database server but gain access to much more data.
  • SQLFire can colocate related data on to a single node (for performance reasons) and dynamically change membership, add/remove capacity, rebalance tables, or run stored procedures in a parallel fashion across these data sets. So, if you wanted to, you could do things like dynamically a) put a set of CRM, web, order, inventory, and social data on a node, b) increase capacity temporarily, c) run a map-reduce style job, and d) make the resulting data-set available to consuming applications at high scale.
  • Server groups can be aligned to certain business units and service levels for management purposes.
  • As well, we can do things like allocate more static data or reference data on a few nodes while rapidly changing or high-volume data could be populated on many nodes to be cost effective.

As you can see, Server Groups really enable the data store layer to act as more than one traditional database, it’s a fabric where multiple, traditional data-stores can come together.

SQLFire’s Horizontal Partitioning

Many companies seek the ability to scale the data layer horizontally. This means rows are held separately on different tables.  Many have looked at sharding approaches to place fewer rows in a table, reduce index size of that table, and distribute portions of that table across many machines to increase performance.

With SQLFire, sharding is basically built-in as part of the underlying service. SQLFire uses the concept of buckets as the unit for storage of data, redundancy, and migration/rebalancing.  This allows a frequently manipulated table to be placed across nodes in more manageable partitions.  You can hash-partition a table based on primary key, internally generated unique row ID, columns other than primary key, range-partitioning, and list-partitioning.

Let’s look at an example. In the figure below, the example shows the Flights table partitioned by FLIGHT_ID (primary key for Flights table) into three buckets, where the first bucket for rows 1 to 3 resides on vFabric SQLFire server 1, the second bucket for rows 4 to 6 resides on vFabric SQLFire server 2, and the third bucket for rows 7 to 9 resides on vFabric SQLFire server 3. vFabric SQLFire directs that all access to flights data by the FLIGHT_ID primary key for rows 1 to 3 are executed on vFabric SQLFire Server 1, and similarly for the other rows. vFabric SQLFire automatically manages all partitioning through this bucketing system so long as the designer has provided the correct PARTITION BY COLUMN(FLIGHT_ID) clause in the table definition.

To extend the example, let’s place it in the context of airline flights where we can see how redundancy and partitioning is used across both actively changing data and reference data with various data relationships and expected joins.

The schema below is typical of the master-detail design pattern seen in most RDBMS schemas with FLIGHTS, FLIGHTAVAILABILITY, and AIRLINES tables. FLIGHTS and FLIGHTAVAILABILITY have a one-to-many relationship. AIRLINES, however has a many-to-many relationship to FLIGHTS and FLIGHTAVAILABILITY.  The Flights table is partitioned by its primary key FLIGHT_ID and has a REDUNDANCY of 1. This means that there is one backup copy of each FLIGHT on a redundant vFabric SQLFire Server somewhere in the cluster. On the other hand, the FLIGHTAVILABILITY is partitioned by FLIGHT_ID and COLOCATE with FLIGHTS, and means that vFabric SQLFire manages the data partitions so that when there is a qualifying join between the FLIGHTS and FLIGHTAVILABILITY tables, the query executes within the same vFabric SQLFire member/memory space, optimizing performance. The AIRLINES table is reference data that does not change frequently and has a many-to-many relationship to FLIGHTS and FLIGHTAVAILABILITY. To configure this relationship in vFabric SQLFire, you can use the REPLICATE keyword to indicate to vFabric SQLFire to place a complete copy of the AIRLINES table on every vFabric SQLFire member in the distributed system.

SQLFire Redundancy

vFabric SQLFire let’s you keep a continuous, fault tolerant model. With SQLFire, you can choose how many redundant copies of specific data to keep. SQLFire then manages the synchronization of all data changes across the primary copy and all backup copies. When a server fails, vFabric SQLFire automatically reroutes any operations that were trying to read or write from the failed member to the surviving members. There are tradeoffs in performance when more copies are kept, but most production systems find that one redundant copy with asynchronous disk persistence provides a solid combination of performance, scalability, and reliability.

>> For More Info: A significant portion of this article was sourced from the SQLFire Best Practices guide led by Emad Benjamin. This article is the second in a series, following last week’s post on How SQLFire Performance Benchmark Blows the Traditional RDBMS Away.

Additional Reading

 

This entry was posted in SQLFire and tagged , , , , , on by .
Adam Bloom

About Adam Bloom

Adam Bloom has worked for 15+ years in the tech industry and has been a key contributor to the VMware vFabric Blog for the past year. He first started working on cloud-based apps in 1998 when he led the development and launch of WebMD 1.0’s B2C and B2B apps. He then spent several years in product marketing for a J2EE-based PaaS/SaaS start-up. Afterwards, he worked for Siebel as a consultant on large CRM engagements, then launched their online community and ran marketing operations. At Oracle, he led the worldwide implementation of Siebel CRM before spending some time at a Youtube competitor in Silicon Valley and working as a product marketer for Unica's SaaS-based marketing automation suite. He graduated from Georgia Tech with high honors and an undergraduate thesis in human computer interaction.

5 thoughts on “3 Game Changing Capabilities in SQLFire

  1. Jerry

    Check the video on this page to see how this game works.
    There are different factors that why one ought to use android for game development.

    The most important way you can distribute apps for free and paid.

    Also visit my blog … hack (Jerry)

    Reply
  2. mobile game hack tool

    Tɦe amalgamation оf reasoning, sequence solving, pattern recognition, logic, strategy аnd
    word completion ѡill put yoս straight tߋ the main stream оf tɦe game.
    Tɦis can channelize οut to be a real ougo operative adventure оn the
    moove of the application developer. Мost of the software
    սsed bƴ online casinos foor mobile games is based on Java
    - Script.

    Feell free tо surf to my webpage mobile game hack tool

    Reply
  3. sanlecuttfolk.wordpress.com

    Political cartoons aոd games are аlways Ƅееn
    а major іnteresting ƿart for tɦe viewers. Tɦiѕ can channelize օut to Ье a real outgo operativ adventure οn
    thе moѵе of the application developer. Ƭhis game has tߋ get ɑ rating 4 оut off
    5 for ɑ retro experience ԝith a modern twist.

    Feel free tօ visit my web site :: mobile game cheats – sanlecuttfolk.wordpress.com,

    Reply
  4. hack

    By far the coolest games Һave to be the online shooters,
    аnd I’m not just talking about a phone version оf Counterstrike.
    Rich client ѕide business logic ϲan often lead to unexpected security pitfalls.
    Νot օnly is this marketing technique extrmely effective, іt is also highly economical.

    Ƭake a look at mƴ paǥе … hack

    Reply
  5. mobile game cheats

    There are two versions, oոe written іn Python andd onе written іn Objective C
    targeting і – OS. Symbian mobile games arе increasingly Ьecoming essential source ߋf entertainment ɑnd
    beiոg most utmost capabilities іn thе wɦole wߋrld.
    keeps on changing thе recent boom inn mobile gaming sector ѕeems to Ƅe the ƅest alternative іn
    this chaotic and stressful life.

    mʏ web site – mobile game cheats

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>