Author Archives: Carter Shanklin

Love data? The SQLFire and GemFire teams are hiring.

HelpwantedThere's a lot of change underway in data. It's become clear that traditional database systems are not going to survive the transitionto cloud and fundamental changes are needed. Databases need to offer tunable consistency to have any hope of dealing with web scale.Databases need to handle geographic distribution effortlessly. Databases need to offer more flexible data models that support agile development. Databases can't be tied to purely physical consumption models.

VMware sees these new data programming and consumption models as very important to VMware's overall mission of simplifying IT.

VMware's got a lot going on in data, you might have heard of vFabric Data Director or vFabric Postgres. These are just a few of the many data projects VMware has in the works.

This blog focuses on SQLFire, a memory-optimized distributed SQL database, which today embodies some of these needed changes withmore on the way. VMware also has GemFire, a memory-optimized distributed key/value store. For many years GemFire has been the solution to some of the world's most challenging data problems, particularly when it comes to speed and low latency. (If you peek under the covers you'll see that SQLFire is built using GemFire technology and shares a lot of its great qualities.)

When it comes to GemFire and SQLFire, we're focused on high-performance transactional databases that are linearly scalable and globally distributed. We're looking for top talent to join us on our SQLFire / GemFire teams and join us on our journey to data in the cloud.

Engineering

We need talented developers, particularly those with deep Java, database or distributed systems backgrounds. See our job site for more details on GemFire jobs or SQLFire jobs. We're looking for people in a number of areas, including Beaverton/Portland, The Bay Area, and in Pune, India. It's your opportunity to work with a great team who builds amazing and disruptive technology, and in a great company on top of that.

Technical Evangelism

Love building demos? Can you write a great blog? Love travelling the world, meeting interesting people and talking about querying and horizontal scaling with them? Let's talk. See our job description for more details. If this sounds like a perfect fit for you, email me at cshanklin@vmware.com or our friendly recruiter bronsone@vmware.com.

Product Management

VMware's commitment level is high and our slate is pretty clean. It's a perfect opportunity for a product manager with great ideas about how data ought to work. Check out our job description and if that sounds like you, email me at cshanklin@vmware.com or bronsone@vmware.com.

Hoping to hear from you!

Using SQLFire as a read-only cache for MySQL.

A question came up in our forums about using SQLFire to cache data from a database using lazy loading. I wanted to provide a fairly concrete example that uses MySQL that should give you all the info you need to try it out yourself.

Why do this?

SQLFire can play a number of roles. For one thing it has its own persistence right out-of-the-box so it can be your database of record unassociated with any other database and brings big advantages in terms of speed, eliminating single-points-of-failure and so on. But chances are you have a database already and would like to get a feel for SQLFire without a huge commitment. In this case there are a couple of options, SQLFire can act as a read/write layer atop the database, persisting database to a more traditional RDBMS, or SQLFire can act as a read-only cache that loads data in as you need it. Many times this latter case is the least commitment way to try SQLFire out, after all your database may have stored procedures or other things that make it difficult to port to another database.

Putting a read-only cache atop an RDBMS helps by offloading some reads to another server. Let's think about a database that is constantly getting reads and writes for a lot of different users (high concurrency). This sort of situation easily leads to disk thrashing which leads to extremely bad performance unless you're willing to throw lots of money into storage. Serving a portion of reads in-memory cuts down on this thrash, making your write throughput go up on the underlying database. In addition, an in-memory architecture like SQLFire handles high concurrency extremely well since it doesn't suffer from thrashing problems the way disk-based databases do. Another way caching brings read throughput up is by not forcing every read to be consistent with every write. I'll talk about this more in the next section.

Before you begin, understand these.

  1. When it comes to read-only caching, SQLFire 1.0 supports accessing database rows based on primary key. This sort of access pattern is pretty similar to what you might do with a memcache or a Redis, except that you get RDBMS rows instead of objects, which has some advantages in terms of reusing code you already have.
  2. Second, SQLFire caches the data. That might sound silly to say, after all that's the goal, but it also means that when you use SQLFire to cache, the data in SQLFire will be stale to some extent. If SQLFire were to reach out to the database on every access there would be no performance benefit, probably just the opposite. Your application and use case need to be ok with data that is stale. By adopting this "eventually consistent" approach the system as a whole does less work and higher throughput is possible. As more and more applications are exposed to the big-I Internet people have been forced to realize that there are quite a few places in their application where eventual consistency is perfectly fine. How stale the data should be is configurable as we'll see below.

Step 1: Install and configure MySQL, including the MySQL JDBC Connector

I'm not going to give detailed instructions on this one, I'm going to assume you have a database up and running already. You should also get the MySQL JDBC connector. If you're on Ubuntu for instance just "apt-get install libmysql-java" and it will appear in /usr/share/java/mysql-connector-java.jar. Similar if you're on Fedora using yum. If you're on another platform I wish you luck in your adventure to track that file down, this blog will still be here when you return.

By the way, if you're using some database other than MySQL, very little changes in these instructions, all you need to do is find the appropriate JDBC driver and change the connection strings.

Step 2: Create a table and some data in MySQL.

Again I'm going to mostly assume that you know how to do this but I'll create a table called names and insert a few rows. It's important that the table use a primary key.

Step 3: Compile the JDBCRowLoader sample (and put it in a JAR file).

So it turns out there is some assembly required if you want to use SQLFire as a read-only cache. Luckily it's not too painful, everything you need is included in the SQLFire kit, when you install you'll have a directory called examples with some Java code in it. One of these files is JDBCRowLoader.java, which is a sample class that implements the RowLoader interface. There are a number of ways to approach the RowLoader interface but this example is quite general-purpose. To get things ready to use, run these three commands from within the examples directory:

  1. javac -classpath ../lib/sqlfire.jar JDBCRowLoader.java
  2. cd ..
  3. jar -cf examples.jar examples

Now you have a JAR file you can use as a row loader. This generic row loader even handles cases where you want fewer columns in SQLFire than in MySQL using the query-columns parameter. You can implement your own row loader if you need to. Why would you do that? Most likely you would want to apply some transformation to the data as you're reading it in. For one example you may have noticed that SQLFire 1.0 doesn't support the boolean datatype yet. If your MySQL table had boolean data, you could use your RowLoader to transform it into an integer value of 0 or 1 as appropriate. For this demo the generic RowLoader is more than enough.

Step 4: Start SQLFire with an extended classpath.

We need SQLFire to understand where the MySQL JDBC driver is, as well as our JDBCRowLoader code is. To do this we will need to use -classpath when starting SQLFire. Here's an example:

  • sqlf server start -classpath=/usr/share/java/mysql-connector-java.jar:/home/carter/sqlf/examples.jar

You will need to substitute the paths to your examples.jar and MySQL connector. After a successful start you can connect as you normally would.

Step 5: Re-create the table in SQLFire and call SYS.ATTACH_LOADER to hook the two together.

First we create a table as in MySQL except we don't populate any data. Next the special ATTACH_LOADER call is invoked to hook SQLFire to MySQL.

Here's what this should look like in your session.

  Linking SQLFire and MySQL

Now when you query SQLFire by primary key, rows will be loaded in on demand. You should also understand that if you don't specify a where clause when querying the table, SQLFire will report whatever keys have been cached thus far rather than everything in the underlying table. Chances are this is not what you want, so be careful of it.

Select from SQLFire caching MySQL

Step 6: Controlling cache size and data freshness.

The table created above is not the sort of thing you really want when you're caching with SQLFire for two reasons:

  1. First of all, data is never updated once it's placed into the cache. Ever. No matter how much the underlying data changes in MySQL, those changes will never make their way into SQLFire. Not likely what you want.
  2. The table will grow indefinitely large. Chances are you'd rather cache a subset of your data since you're using SQLFire as an in-memory layer.

Overcoming both these problems is very easy to do in SQLFire, using CREATE TABLE semantics. Here's an example:

Let's take a quick look at what this does. The first difference between this statement and the one above is the EVICTION BY LRUCOUNT 50000 clause. This means that SQLFire will maintain up to 50,000 entries in the cache before taking action. The action is configurable but this setting causes SQLFire to simply discard the data, perfectly appropriate for a cache. The second line is more interesting to most people, EXPIRE ENTRY WITH TIMETOLIVE 300. This says that an entry should be kept in the cache no more than 5 minutes. The net effect is to guarantee that data is never more than 5 minutes stale. After 5 minutes the data is discarded and must be re-fetched from the underlying database. If you have a fairly short TTL you might not even want to set a maximum cache size.

Variations.

Here are a few simple variations on this demo:

  1. Change your cache policy by changing the LRUCOUND or TIMETOLIVE parameters in the CREATE TABLE statement.
  2. If you want to act as a cache on top of Oracle or some other database, just change the url parameter to mention the appropriate driver and URL. Note you will need the respective JDBC JAR for that database to be in the classpath when you start SQLFire.

Questions? Comments? Join us in the SQLFire Community.

 

SQLFire 1.0 – the Data Fabric Sequel

Sizes_with_names_128x128
(Note this blog is simulposted here and on Jags Ramnaryan's blog. Jags is our Chief Architect and his blog is well worth subscribing to!)

This week we finally reached GA status for VMware vFabric SQLFire – a memory-optimized distributed SQL database delivering dynamic scalability and high performance for data-intensive modern applications.

In this post, I will highlight some important elements in our design and draw out some of our core values.

The current breed of popular NoSQL stores promote different approaches to data modelling, storage architectures and consistency models to solve the scalability and performance problems in relational databases. The overarching messages in all of them seems to suggest that the core of the problem with traditional relational databases is SQL.

But, ironically, the core of the scalability problem has little to do with SQL itself – it is the manner in which the traditional DB manages disk buffers, manages its locks and latches through a centralized architecture to preserve strict ACID properties that represents a challenge. Here is a slide from research at MIT and Brown university on where the time is spent in OLTP databases.

OLTP Where Does The Time Go

Design Center

With SQLFire we change the design center in a few interesting ways:

1) Optimize for main memory: we assume memory is abundant across a cluster of servers and optimize the design through highly concurrent data structures all resident in memory. The design is not concerned with buffering contiguous disk blocks in memory but rather manages application rows in memory hashmaps in a form so it can be directly consumed by clients. Changes are synchronously propagated to redundants in the cluster for HA.

2) Rethink ACID transactions: There is no support for strict serializable transactions but assume that most applications can get by with simpler "read committed" and "repeatable read" semantics. Instead of worrying about "read ahead" transaction logs on disk, all transactional state resides in distributed memory and uses a non-2PC commit algorithm optimized for small duration, non-overlapping transactions. The central theme is to avoid any single points of contentions like with a distribtued lock service. Our documentation has more details on how transactions work in SQLFire.

3) "Partition aware DB design": Almost every single high scale DB solution offers a way to linearly scale by hashing keys to a set of partitions. But, how do you make SQL queries and DML scale when they involve joins or complex conditions? Given that distributed joins inherently don't scale we promote the idea that the designer should think about common data access patterns and choose the partitioning strategy accordingly. To make things relatively simple for the designer, we extended the DDL (Data definition language in SQL) so the designer can specify how related data should be colocated ( for instance 'create table Orders (…) colocate with Customer' tells us that the order records for a customer should always be colocated onto the same partition). The colocation now makes join processing and query optimization a local partition problem (avoids large transfers of intermediate data sets). The design assumes classic OLTP workload patterns where vast majority of individual requests can be pruned to a few nodes and that the concurrent workload from all users is spread across the entire data set (and, hence across all the partitions). Look here for some details.

4) Shared nothing logs on disk: Disk stores are merely "append only" logs and designed so that application writes are never exposed to the disk seek latencies. Writes are synchronously streamed to disk on all replicas. A lot of the disk store design looks similar to other NoSQL systems – rolling logs, background/offline compression, memory tables pointing to disk offsets, etc. But, the one aspect that represents core IP is all around managing consistent copies on disk in the face of failures. Given that distributed members can come and go, how do we make sure that the disk state a member is working with is the one I should be working with? I cover our "shared nothing disk architecture" in lot more detail in this blog post on GemFire 6.5.

5) Parallelize data access and application behavior: We extend the classic stored procedure model by allowing applications to parallelize the procedure across the cluster or just a subset of nodes by hinting the data the procedure is dependent on. This applicaton hinting is done by supplying a "where clause" that is used to determine where to route and parallelize the execution. Unlike traditional databases, procedures can be arbitrary application Java code (you can infact embed the cluster members in your Spring container) and run collocated with the data. Yes, literally in the same process space where the data is stored. Controversial, yes, but, now your application code can do a scan as efficiently as the database engine.

6) Dynamic rebalancing of data and behavior: This is the act of figuring out what data buckets should be migrated when new capacity (cluster size grows) is allocated (or removed) and how to do this without causing consistency issues or introducing contention points for concurrent readers and writes. Here is the patent that describes some aspects of the design.

Embedded or a client-server topology

SQLFire supports switching from the classic client-server (your DB runs in its own processes) topology to embedded mode where the DB cluster and the application cluster is one and the same (for Java apps). 

We believe the emdedded model will be very useful in scenarios where the data sets are relatively small. It simplifies deployment concerns and at the same time provides significant boost in performance when replicated tables are in use.

All you do is change the DB URL from 'jdbc:sqlfire://server_Host:port' to 'jdbc:sqlfire:;mcast-port=portNum' and now all your application processes that use the same DB URL will become part of a single distributed system. Essentially, the mcast-port port identifies a broadcast channel for membership gossiping. New servers will automatically join the cluster once authenticated. Any replicated tables will automatically get hosted in the new process and partitioned tables could get rebalanced and share some of the data with the new process. All this is abstracted away from the developer.

As far as the application is concerned, you just create connections and execute SQL like with any other DB.

Topology1-whitebg

Topology2-whitebg

 

How well does it perform and scale?

Here are the results of a simple benchmark done internally using commodity (2 CPU) machines showcasing linear scaling with concurrent user load. I will soon augment this with more interesting workload characterization. We have more details on the SQLFire community.

SQLFire Linearly Scaling Queries

 

SQLFire Linear Scale Low Latency

 

Comparing SQLFire and GemFire

Here is a high level view into how the two products compare. I hope to add a blog post that provides specific details on the differences and use cases where one might apply better than the other.

GemFire versus SQLFire

SQLFire benefits from the years of commercially deployed production code found in GemFire. SQLFire adds a rich SQL engine with the idea that now folks can manage operational data primarily in memory, partitioned across any number of nodes and with a disk architecture that avoids disk seeks. Note the two offerings, SQLFire and GemFire, are completely unique products and deployed separately

As always, I would love to get your candid feedback (link to our forum). I assure you that trying it out is very simple – just like using Apache Derby or H2.

Get to the download, docs and quickstart all from here. The developer license is perpetual and works on upto 3 server nodes.

SQLFire at VMworld.

Vmworld2011
VMworld is less than a week away. If you're going to be at the show and want to learn more about making data extremely fast and very scalable you've got several ways to do it, through a session, through a lab, and also by stopping by the Cloud Application Platform section of the VMware booth.

#CAP1942 "Managing High Performance Data with SQLFire": See SQLFire's Chief Architect, Jags Ramnarayan talk about the design decisions that make SQLFire a highly scalable, distributed NewSQL database. Session is Wednesday August 30 at 1:00 p.m. Don't forget to pre-register.

#HOL12 "Optimizing Data Access for Your Cloud Infrastructure": You'd never guess by looking at it but this lab actually features SQLFire as well as other VMware database technologies. I caught a quick preview of this lab last week and one of the cool things you'll see in this lab include how SQLFire can effortlessly replicate data across slow internet connections so you can have ultra-fast database access in multiple geographic locations. Not only that but you'll see just some of the advantages of being in-memory when it comes to ultra-fast reads and writes. What more could you ask for in under the space of one hour?

This year we've assigned hashtags to all our sessions and labs so if yuo've got comments, feedback or just want to meet up with like-minded people, be sure to add the relevant hashtag to your tweet. See you at the show!

SQLFire and Grails

Chris Harris has written a series of blog posts showing how to use SQLFire from within Grails. In the process he introduces a couple of very interesting features of SQLFire that I wanted to talk more about, specifically how your app should take advantage of SQLFire's elasticity and the possibility of using SQLFire as a cache. Grails is a web development framework based on the Groovy programming language, and is designed to make writing web applications quick and painless. Using SQLFire from Grails is quite simple, mainly because SQLFire supports a JDBC interface, so connecting Grails apps to SQLFire only requires changing two lines.

Getting Started With SQLFire and Grails

Chris's first post shows the basics of creating an application and connecting it to SQLFire. All total there are 10 lines of code, excluding whitespace, which is really good considering Chris introduces constraints around maximum and minimum valid name lengths. The "grails create-controller" command handles the messy details of actually doing these constraint checks so the developer can focus on more important things.

Taking Advantage of SQLFire's Elasticity

Next, Chris introduces the concept of the SQLFire Locator. SQLFire is elastic by design, meaning that you can add and remove nodes whenever you want. When you do that, data is automatically rebalanced across all SQLFire nodes. You can see how this could be a problem, your app needs to be sure it's talking to something that's not going to go away some time in the future. SQLFire solves this using things called Locators. Locators keep track of what nodes exist in the SQLFire system and where data resides within the system. They are also persistent so your app can rely on the Locator to always be in the same place.

Let's look at an example. In this example we have a SQLFire system with two nodes and a locator.  Two Node SQLFire System With Locator

Now let's say that either the size of the database needs to grow or we need to add more processing power to it. We can just add a node. Again, when we do that the data is automatically shared with the new node and balanced across the whole system. Since the app is talking to the locator it doesn't notice any changes.

Three Node SQLFire System With Locator
The reverse works as well, if I need to get rid of a node I can just pull it out and my app will keep working fine. Chris's second blog post shows how this works in Grails. There's not much to it really, in Grails just make sure your JDBC URL points to the Locator.

SQLFire as a cache

Chris's third post shows how you can use SQLFire as an in-memory frontend to a MySQL database. Let's be clear, SQLFire is a real database, you can store your application's real data inside it, back it up, restore it, do all the things you would do with databases you're used to. There's no need to pair SQLFire with another database, but there are times when you may want to do it. For example you may have a data warehouse with huge amounts of data you wouldn't want to try to fit in memory.

Another common thing these days is people who hit a performance wall with their database, so they look at putting an in-memory cache on top of the database, such as memcache. This has worked very well but introduces a lot of complexity, for one thing you've got a new set of software you have to care and feed, as well as a new and incompatible set of APIs to access in-memory data versus on-disk data. Adding this sort of in-memory layer introduces a lot of stack complexity both for developers and operations.

When we look at relational databases, we believe that ultimately SQLFire is a better choice than using a combination of relational database plus any sort of in-memory caching. After all, why care for 2 databases when you only need 1? However it takes time to get there and until it happens SQLFire as a cache in front of an RDBMs may be the right thing for many people. It's less disruptive than a switch directly to an in-memory database, and is less complex than introducing a non-relational cache on top of a relational database.

Whatever the case may be Chris shows that it's pretty easy to make SQLFire into an in-memory cache on top of a MySQL database. The magic is in a couple of CALL commands made within the sqlf utility. Note that Chris does not need to write code that maps the SQLFire tables to the MySQL tables, this is the advantage of our cache being a real SQL database, we don't have to go through this error-prone process like we would if we were using something like memcache.

Thanks Chris for your hard work in writing these blog posts and I hope we can continue to see more from you.

As always to learn more or ask questions be sure to join our community to connect with the experts.

SQLFire 1.0 beta released! Structured data now with performance and scale.

Today we’re very happy to announce that our beta of vFabric SQLFire has been released!

There’s tremendous change underway today in data management. These days people are looking for databases that are faster, more scalable, more reliable, and can effortlessly serve users around the globe. We believe SQLFire does a great job addressing these concerns and more.

Since we’re all pretty busy and evaluating a product can be hard, I’m going to kick things with this little video tour through our quick start guide, covering install and running a few basic commands. Some of the important points it covers are:

  1. The video shows how easy it is to add nodes to the database, or “distributed system” in our jargon. So it’s very easy to horizontally scale SQLFire. (Removing nodes is just as easy though the video doesn’t cover that.)
  2. If you’ve scaled a system out you have to plan very explicitly for failure. If a member fails about once a year, and you’ve got 12 of them, you can expect one failure per month. 24 members, you can expect 2 per month. You get the idea. The video shows that if a distributed system member fails for some reason, SQLFire clients will automatically connect to some other member in the system.

In our formal marketing-speak we describe SQLFire as a memory-oriented, shared-nothing distributed data management system that uses SQL as its interface.

Memory-oriented: SQLFire is memory-oriented in the sense that regular data accesses are all done purely in memory. SQLFire can also write data to disk, which you might do to protect yourself from a crash, but data is not written to disk in a way that is used for random data retrievals. Traditional databases work very hard to optimize around disk access, and use a lot of tricks to try to minimize disk seeks, structure data sequentially, etc. With SQLFire, queries and data retrieval are done purely in memory for maximum speed. Anybody who watches the prices and configuration of servers knows that memory volumes are getting huge, a server with 1 terabyte of RAM costs well under $50,000 these days, in a few years we’ll look back and laugh at servers that “only” have 1TB of RAM. Quite a lot of databases will fit very happily inside 1TB and SQLFire is taking advantage of this industry shift.

Shared-nothing and distributed: Another big shift that’s been underway for a while is the shift away from big, monolithic systems toward scale-out systems built from commodity hardware. The big web giants really pioneered this shift but a lot of people still aren’t benefiting from it. SQLFire embraces scale-out by making it trivial to add and remove capacity at any time.

There’s a lot more in SQLFire I didn’t cover here, so be sure to download SQLFire and try it yourself. Visit the SQLFire Community to get everything you need, and be sure to check in on the Discussions tab and let us know what you think!