you're reading...

Founders BLOG

MySQL BLOB meets Amazon S3: Weblobs explained

Cloud-powered BLOB type provides ACID guarantees and fast direct access to blobs via Web URLs.

Storing unstructured data

Typically unstructured data (such as pictures, media files, documents)

a) Is either stored on the file system, unlike the related with it relational data which is stored in the database. This is well known, “convenient” practice that allows fast access to files but offers no transactional story and no unified data management (for db and filesystem)

b) Or is stored in BLOBs. This ensures transactional consistency and reduces management complexities, but is really bad for performance and scalability.

We took advantage of the cloud, and came up with an upgrade to the BLOB – a solution that combines the benefits of the two.

Weblob data type

Weblob is a new data type that is supported by the Cloud Storage Engine for MySQL (ClouSE).

Like BLOB

To a database developer, a WEBLOB behaves (almost) like a regular BLOB.  In particular, it can be used within a transaction to be atomically updated together with other data.  Which is handy: for example, a picture can be atomically updated together with its metadata and thumbnails.  Weblobs are stored in Amazon S3, just like the rest of the database. This reduces management complexities, and ensures that all the data is stored extremely durably on the cloud storage.

Like File (actually, better!)

A client application (such as a web browser) accesses weblobs like files stored as Amazon S3 objects through HTTP URLs.  These URLs then can be used by a Web browser to download the BLOB content (e.g. the picture) directly from Amazon S3.  Thus the Web server leverages Amazon S3 to deliver pictures (and other media) to the Web browser directly, which makes serving the media highly scalable.

Weblobs provide the following advantages over using files on the file system:

  • Weblobs are stored under the same level of protection as the relational data
  • Weblobs can leverage the cloud power to scale out content delivery because weblob content is served by the cloud storage utility provider which takes load off the application server
  • Weblob access is ACID-compliant so weblobs can be updated in a transaction along with the associated relational data and this transaction can commit or roll back as a whole

Using Weblobs

Creating a table with Weblob column

In MySQL, a Weblob is expressed via a pair of BLOB fields that have a special naming convention: field_name$wblob and field_name$wblob_info. The following example shows how to create the pictures table that is keyed by an id and has a picture Weblob:

mysql> CREATE TABLE pictures (id BIGINT KEY, picture$wblob LONGBLOB, picture$wblob_info BLOB)
       ENGINE=CLOUSE;

The picture$wblob field behaves just like a regular BLOB field.  It can be used to store the data of the actual picture.

The picture$wblob_info field is what provides the Weblob functionality.  It can be used to retrieve the direct Amazon S3 URL for the BLOB content.

Accessing Weblob data using SQL

In MySQL, Weblobs can be modified using the standard SQL INSERT, UPDATE and DELETE statements.  For example:

mysql> INSERT INTO pictures VALUES (42, 'some-content', NULL);

The statement inserts a record with id = 42 and some content.  The special picture$wblob_info field is set to NULL.  So far there is really not much difference between BLOBs and Weblobs (and that’s good – it means that what you know about BLOBs mostly applies to Weblobs).  The advantages become clear when we retrieve the data:

mysql> SELECT picture$wblob_info FROM pictures;
+----------------------------------------------------------------------------+
| picture$wblob_info                                                         |
+------------------------------------------------ ---------------------------+
|  https://s3.amazonaws.com/oblaksoft-yapixx/db0/wblob/174083C686EAD1/4/4C/  |
|  lajV2lT10sQi4JXlylx9UbmdIO5pNHGdgoh-T_A2Ekg/wb                            |
+----------------------------------------------------------------------------+

Accessing Weblob data using URL

The retrieved value of the picture$wblob_info field is now set to the URL that can be used to retrieve the content from Amazon S3 directly.  Let’s paste it into a Web browser:

The browser downloaded the content that we inserted, as expected.  We just scaled out content delivery to Amazon S3 without leaving the relational database paradigm!

Weblob use cases

Weblobs provide a simple and powerful model to leverage the cloud to scale out content delivery.  For photo / video / music -heavy web sites this could lead to a significant workload scale-out.  Other classes of applications may benefit as well.

For a real-world example, take a look at Yapixx: ready-to-run WordPress that stores all its data in Amazon S3.  In addition to storing the Website’s data (pages and uploads) extremely durably, Yapixx leverages Amazon S3 to deliver pictures (and other media) to the Web browser directly, which makes serving the media highly scalable.

Yapixx uses the WP2Cloud WordPress plugin to upload pictures (and other media) to Amazon S3.  Under the covers, WP2Cloud uses Weblobs.  Without WP2Cloud, WordPress stores pictures (and other media) as files on the local file system.  This is a typical approach in many applications as MySQL doesn’t provide a good solution for large content management.

In conclusion

ClouSE is the database designed from ground up for the cloud and with the cloud in mind. Weblob is an example of innovation tuned to bring the cloud scale and reliability to simplify data management. While ClouSE allows migrating MySQL-based applications to the cloud without changing a line of code, Weblob is a feature that you’ll actually want to change your code for.

See also

MySQL BLOB meets Amazon S3: advanced Weblob features

WordPress on S3: run a beautiful website on Amazon cloud storage.

WordPress on S3: how it works.

WordPress on S3: run it anywhere.

WordPress on S3: no more backups.

Discussion

Trackbacks/Pingbacks

  1. [...] http://www.oblaksoft.com/mysql-blob-meets-amazon-s3-blob-weblobs-explained/ [...]

    The Pythian Blog - June 14, 2012

Post a Comment

Categories

Twitting ...