PBMS version 0.5.09 has been released.

A new release of the PrimeBase Media Streaming engine is now available for download at
http://www.blobstreaming.org .

The main focus of this release was to provide the features required to make PBMS production ready and with the addition of transaction support and engine level backup I think it is almost there. The engine does not yet provide engine level replication but it will in a future release.

What's new:

• PBMS now provides two methods to backup the BLOB repository which are documented here.
  
• PBMS now supports transactions independently of the storage engine.
• There is now a PHP extension for PBMS. Check it out!
  
• The interface between PBMS and storage engines has been greatly simplified so that PBMS support can be added directly to MySQL or other engines such as InnoDB by applying a simple patch to the handler as talked about in an earlier blog here.

What's Next:

The next step is to take this version and do extensive testing to make sure it is stable. There are a few known bugs listed in the To-Do list that I need to fix and as I add more test cases and do more stress testing I am sure I will find more.

The S3 Cloud support, which was mentioned here, has been pulled out of this release and I want to get it back in as soon as possible.

I also plan to make some patches available that can be applied to MySQL or InnoDB to make them PBMS enabled.

And of course I need to get PBMS into Drizzle. That isn't really that much work, I have already had it running with Drizzle but I have been waiting to get a stable full feature version first. I think what I have now is close enough so I will be working on this shortly.

What's after that:

Once I get the current version running stably with no major bugs or limitations and have taken care of the above mentioned issues, I plan on working on the following .

• Remote repositories: the entire repository is stored somewhere other than in the MySQL data directory, most likely on a different machine entirely. This is different from cloud storage in that the entire repository is stored remotely instead of just the BLOB data. The remote repository server would be a standalone PBMS HTTP server providing clients with access to the BLOB data stored in the repository. The standalone PBMS HTTP server could in turn be using cloud storage so that the BLOB data itself was stored in a cloud. A solution like this should be able to work with the NDBCLUSTER storage engine.
• Engine level replication: This would be done similar to the remote repository but the data would be stored in the local repository as well as being forwarded to the slave servers.
• Incremental backup. This allows you to basically sync the current state of the BLOB repository with that of a backup. To do this all new BLOBs and BLOB references in the current database would be added to the backup and BLOBs and BLOB references in the backup that no longer exist in the current database would be removed. In this way only the new data is copied to the backup location.

As usual if you have any questions about PBMS and BLOB storage please send them to me and I will do my best to answer them.

Barry

New simplified engine interface for PBMS

By making PBMS transactional I have been able to greatly simplify the engine interface making it much easier for engine builders to build in support for PBMS. How much simpler is it? From the time I decided to make InnoDB PBMS enabled to when I started the rebuild of MySQL was less than half an hour!

The same way that I added PBMS support to InnoDB it can be added directly to the MySQL server so that the PBMS engine will be used for BLOB storage for all engines regardless of if they have been enabled or not. PBMS support for drizzle will be provided via a data filter plug-in which I have yet to write but will soon.

To add PBMS support all you need to do is add the file pbms_enabled.cc to your source code and add the following to your handler code. I will use the InnoDB handler code as an example:


File ha_innodb.cc:

#ifdef USE_PRAGMA_IMPLEMENTATION
#pragma implementation    // gcc: Class implementation
#endif
:
:
:
/* Include necessary InnoDB headers */
extern "C" {
#include "../storage/innobase/include/univ.i"
:
:
:
#include "../storage/innobase/include/ha_prototypes.h"
}

#define PBMS_ENABLED

#ifdef PBMS_ENABLED
#include "pbms_enabled.h"
#endif

static const long AUTOINC_OLD_STYLE_LOCKING = 0;
static const long AUTOINC_NEW_STYLE_LOCKING = 1;
:
:
:
innobase_init(
/*==========*/
           /* out: 0 on success, error code on failure */
void    *p) /* in: InnoDB handlerton */
{
:
:
:
err = innobase_start_or_create_for_mysql();

if (err != DB_SUCCESS) {
   my_free(internal_innobase_data_file_path,
                    MYF(MY_ALLOW_ZERO_PTR));
   goto error;
}

#ifdef PBMS_ENABLED
   PBMSResultRec result;
   if (!pbms_initialize("InnoDB", &result)) {
       sql_print_error("pbms_initialize() Error: %s", result.mr_message);
       goto error;
   }
#endif
(void) hash_init(&innobase_open_tables,system_charset_info, 32, 0, 0,
               (hash_get_key) innobase_get_key, 0, 0);
:
:
:
error:
DBUG_RETURN(TRUE);
}
:
:
:
innobase_end(handlerton *hton, ha_panic_function type)
/*==============*/
           /* out: TRUE if error */
{
int err= 0;

DBUG_ENTER("innobase_end");

#ifdef __NETWARE    /* some special cleanup for NetWare */
if (nw_panic) {
   set_panic_flag_for_netware();
}
#endif
if (innodb_inited) {

:
:
:

#ifdef PBMS_ENABLED
       pbms_finalize();
#endif
}

DBUG_RETURN(err);
}

:
:
:
int
ha_innobase::write_row(
/*===================*/
       /* out: error code */
uchar* record) /* in: a row in MySQL format */
{
int      error = 0;
ibool    auto_inc_used= FALSE;
ulint    sql_command;
trx_t*   trx = thd_to_trx(user_thd);

DBUG_ENTER("ha_innobase::write_row");

#ifdef PBMS_ENABLED
   PBMSResultRec result;
   error = pbms_write_row_blobs(table, record, &result);
   if (error) {
       sql_print_error( "pbms_write_row_blobs() Error: %s", result.mr_message);
       DBUG_RETURN(error);
   }
#endif

:
:
:
#ifdef PBMS_ENABLED
   pbms_completed(table, (error == 0));
#endif
DBUG_RETURN(error);
}
:
:
:
ha_innobase::update_row(
/*====================*/
      /* out: error number or 0 */
const uchar*  old_row,  /* in: old row in MySQL format */
uchar*        new_row)  /* in: new row in MySQL format */
{
upd_t* uvect;
int    error = 0;
trx_t* trx = thd_to_trx(user_thd);

DBUG_ENTER("ha_innobase::update_row");

#ifdef PBMS_ENABLED
   PBMSResultRec result;

   error = pbms_delete_row_blobs(table, old_row, &result);
   if (error) {
       sql_print_error( "update_row:pbms_delete_row_blobs() Error: %s",
           result.mr_message);
       DBUG_RETURN(error);
   }
   error = pbms_write_row_blobs(table, new_row, &result);
   if (error) {
       sql_print_error( "update_row:pbms_write_row_blobs() Error: %s",
           result.mr_message);
       goto pbms_done;
   }
#endif

:
:
:
#ifdef PBMS_ENABLED
   pbms_done:
   pbms_completed(table, (error == 0));
#endif
DBUG_RETURN(error);
}
:
:
:
int
ha_innobase::delete_row(
/*====================*/
                       /* out: error number or 0 */
const uchar*    record) /* in: a row in MySQL format */
{
int    error = 0;
trx_t* trx = thd_to_trx(user_thd);

DBUG_ENTER("ha_innobase::delete_row");

#ifdef PBMS_ENABLED
   PBMSResultRec result;

   error = pbms_delete_row_blobs(table, record, &result);
   if (error) {
       sql_print_error( "pbms_delete_row_blobs() Error: %s", result.mr_message);
       DBUG_RETURN(error);
   }
#endif

:
:
:
#ifdef PBMS_ENABLED
   pbms_completed(table, (error == 0));
#endif
DBUG_RETURN(error);
}
:
:
:
int
ha_innobase::delete_table(
/*======================*/
                   /* out: error number */
const char* name)   /* in: table name */
{
:
:
:
#ifdef PBMS_ENABLED
   /* Call pbms_delete_table_with_blobs() last because it cannot be undone. */
   if (!error) {
       PBMSResultRec result;

       if (pbms_delete_table_with_blobs(name, &result)) {
           sql_print_error( "pbms_delete_table_with_blobs() Error: %s",
               result.mr_message);
       }
  
       pbms_completed(NULL, true);
   }
#endif
DBUG_RETURN(error);
}

:
:
:
int
ha_innobase::rename_table(
/*======================*/
                  /* out: 0 or error code */
const char* from,  /* in: old name of the table */
const char* to)    /* in: new name of the table */
{
ulint   name_len1;
ulint   name_len2;
int     error;
trx_t*  parent_trx;
trx_t*  trx;
char    norm_from[1000];
char    norm_to[1000];
THD*    thd = ha_thd();

DBUG_ENTER("ha_innobase::rename_table");

#ifdef PBMS_ENABLED
   PBMSResultRec result;

   error = pbms_rename_table_with_blobs(from, to, &result);
   if (error) {
       sql_print_error( "pbms_rename_table_with_blobs() Error: %s", result.mr_message);
       DBUG_RETURN(error);
   }
#endif
:
:
:
#ifdef PBMS_ENABLED
   pbms_completed(NULL, (error == 0));
#endif
DBUG_RETURN(error);
}

Now you are probably wondering how I spent half an hour just adding that.

PBMS is transactional!

The PBMS engine now has built in support for transaction so that if you reference or dereference BLOBs during a transaction the changes you made will be committed or rolled back with the transaction. Up until now PBMS had depended on the host engine to handle transactions and inform it what needed to be done in the event of a rollback.

I have implemented transaction support by adding a circular transaction log and transaction cache. The circular transaction log is dynamic and can grow and shrink as required. The transaction records are all small (30 byte) fixed length records so the log doesn’t need to be that large to be able to handle a lot of transactions. A head and tail pointer are maintained to indicate where the head and tail of the circular log is. If the circular log becomes full then the records just overflow and are written to the end of the log. Once the transaction reader has caught up after an overflow has occurred it simply resizes the circular log to include the overflow and everything continues as normal with a larger circular log. The circular log will be reduced to its normal size once free space becomes available at the end of the log.

If anyone has any use for something like this them selves it has been written as a standalone module so it would not be to difficult to pull it out and build it into something else.

Backing up BLOBs

The PBMS BLOB repositories can now be backed up with mysqldump.

A new system table ‘pbms_dump’ contains 1 row for each repository record. Each row consists of 1 column which is a BLOB. The content of this table has no purpose other than for the use in repository backup. By inserting the data back into this table the repository is recovered. Before doing the backup the pbms_dump system table needs to be discovered, to do this execute the command “select * from pbms_dump where false”. After doing this then mysqldump will include it in backups. Be sure to use the “—hex-blob” option. For example:

Mysqldump –u root –hex-blob mydatabase > mydatabase.sql

Before recovering the database you need to notify PBMS that a recovery operation is in progress so that the recovery of the tables containing the BLOB references do not increment the BLOB reference count. This notification is done by setting a bool flag in a new system table (I like system tables ☺) pbms_variable. To restore a database perform the following operations:

----------------------------
create database recovtest;
use recovtest;

update pbms_variable set Value = "true" where Name = "Restoring-Dump";
source mydatabase.sql;
update pbms_variable set Value = "false" where Name = "Restoring-Dump";
-------------------------------

I am also planning on implementing the engine level backup and recovery supported in MySQL 6.o as well as an internal backup method that will produce a backup that you can recover just by dragging and dropping it into the ‘pbms’ folder where your database folders are located.

The internal backup will run asynchronously in the background and will not block normal operations of the database including adding and deleting BLOBs. The way that it would work is that the administrator would lock the tables containing the BLOB references and back them up. Sometime during the backup, before releasing the locks, the asynchronously PBMS backup of the database would be started. Progress of the backup could be monitored via a pbms_status table.

The version supporting mysqldump is in the PBMS branch "~pbms-core/pbms/cloud" on launchpad. Be warned though that this is a development branch with everything that implies.

BLOB storage in the cloud with PBMS

I am pleased to announce a cloud storage version of the PBMS engine.

What I have created is a version of PBMS that stores the BLOB data in a cloud and when a request for the data comes in the client is sent a redirect to get the BLOB directly from the cloud. The BLOB reference tracking and metadata is handled the same as before in that they are stored in the BLOB record in the repository but the actual BLOB is stored somewhere else.

This has several advantages over storing the BLOB in the repository record:

1. It reduces the disk storage requirement of the database server’s machine.
2. It reduces the bandwidth requirement of the database server’s machine.

The beauty of it is that the client application doesn’t need to know anything about the cloud because it is all handled by the PBMS engine and PBMS lib.

Here is a diagram showing how an insert works:

• Step 1: The BLOB is sent to the PBMS engine. Ideally this is done by using the PBMS lib to send it to the PBMS engine’s HTTP server and then inserting the returned BLOB reference into the table in place of the BLOB. Optionally the BLOB can be insert directly with an ‘insert’ statement and the PBMS engine will replace it with a BLOB reference internally. The first method is preferable though since it impacts server performance less and reduces the server’s memory usage because the BLOB is streamed into the repository file and is never stored in memory.
• Step 2: A repository record is created containing the BLOB data.

(Steps 1 and 2 are the same for both cloud and none-cloud versions of PBMS.)

• Step 3: The BLOB is scheduled for upload to the cloud. A separate thread, called the ‘cloud’ thread, which operates in the background, performs the upload.
• Step 4: The local BLOB data is scheduled for deletion. This action can be delayed for a time to ensure that the data is actually accessible by the clients before the local copy of the BLOB is deleted. The repository record remains but the space used by the actual BLOB is added to the repository’s garbage count and the space will be reclaimed later by the PBMS compactor thread.

Here is how the BLOB is accessed. Keep in mind that all the client application does is provide the PBMS lib with a PBMS BLOB reference as it currently does and receives the BLOB in return. It knows nothing about the cloud.

• Step 1: A BLOB request containing a PBMS BLOB reference is sent to the PBMS engine’s HTTP server.
• Step 2: The BLOB’s repository record is read from local storage.
• Step 3: An HTTP redirect reply is sent back to the client redirecting the request to the BLOB stored in the cloud. The metadata associated with the BLOB is returned to the client in the reply’s HTTP headers. The redirect URL is an authenticated query string that gives the client time limited access to the BLOB data. Use of an authenticated query string allows the data in the cloud to have access protection without requiring the client applications to know the private key normally required to get access.
• Step 4: The redirect is followed to the cloud and the BLOB data is retrieved.

Note the absence of lines passing through the MySQL server. All of this is done outside of the database server, freeing the database server to process database queries rather than serve up BLOB data.

The current version I am working on uses Amazon S3 storage but the cloud access is done via a fairly simple class interface, which should be easy to implement for any Amazon S3 - like interface.


If you want to find out more be sure to attend my talk at the MySQL conference “BLOB Streaming: Efficient Reliable BLOB Handling for all Storage Engines “.

The PrimeBase BLOB Streaming (PBMS) engine alpha version 5.08 is ready

Alpha version 5.08 of the BLOB streaming engine for MySQL has been released. You can download the source code from www.blobstreaming.org/download. The documentation has also been updated.

What's new in 5.08:

• All PBMS data is stored under a 'pbms' directory in the MySQL server's data directory rather than in the database directories them selves.
• This version now builds with Drizzle and can be loaded as a 'Blobcontainer' plug-in.
• Added the possibility of storing BLOB metadata along with the BLOB in the repository.
• Added the possibility of assigning an alias to a BLOB, which can then be used to retrieve the BLOB instead of using the engine generated URL.
• Added an updateable system table 'pbms_metadata_header' to control which HTTP headers are stored as metadata.
• Added an updateable system table 'pbms_metadata' that contains all the metadata associated with the BLOBs.
• New PBMS API functions have been added to set and get BLOB metadata.
• A new PBMS API function has been added to allow applications to get the BLOB metadata with out getting the actual BLOB.
• Added some new fields to the 'pbms_repository' system table.
• Removed the raw BLOB data from the 'pbms_repository' system table and placed it in its own table, 'pbms_blob'.
• Dropping a database containing PBMS BLOBS referenced from non-PBXT tables no longer requires special handling.
• System tables can now be selected with an 'order by' clause.

As you can see a lot of work has been done on it and a lot of things have changed. I have already talked about most of the major changes in my previous 2 BLOG postings but I recommend having a look at the documentation for more details.

A few things to watch for in the new version:

• The location and format of the BLOB repository files has changed. This means that if you are using an older version you will need to import the data from the server using the older version of PBMS into tables on a server running the new version. Feel free to contact me if you have any questions about this. I will try to maintain backward compatibility with older versions of PBMS but until the code is no longer alpha I can not guarantee this.
• A patch was added to the PBXT engine to prevent a crash when shutting down MySQL. The PBXT version '1.0.07m-rc' contains this patch and is available for download with the the PBMS engine.
• There remains an unsolved problem that can lead to PBXT hanging when a PBXT table containing longblobs is dropped and the PBMS engine is being used for BLOB storage.
  

As usual if you have any questions about PBMS and BLOB storage please send them to me and I will do my best to answer them.

Barry

PBMS supports BLOB metadata and aliases

The PrimeBase PBMS engine now supports user defined metadata.

When the PBMS engine receives a BLOB the HTTP header tag value pairs are stored with the BLOB as metadata. To restrict which headers are stored as metadata an updatable system table 'pbms_http_fields' is provided in which users can add the names of the headers that are to be treated as metadata. When the BLOB is retrieved from the engine the metadata is sent back along with the BLOB as HTTP headers in the same way that it was received.

The metadata can be accessed from within the database via the 'pbms_matadata' system table or altered by performing inserts, updates, or deletes on the table.

A BLOB alias is a special metadata field that allows you to associate a database wide unique name with the BLOB which can then be used later to retrieve it. If you are familiar with Amazon S3 storage it works in a similar manner where you can think of the database as the S3 bucket and the BLOB alias as the S3 key.  To fetch the BLOB back using the alias you use a URL with the format <database>/<alias>. The following is an example using 'curl' to send a BLOB with alias 'MyBLOB' to the PBMS engine and then fetch it back again:

curl -H "PBMS_BLOB_ALIAS:MyBLOB" -d "A BLOB with alias" "http://localhost:8080/test"

Returning:

~*test/_1-632-4934f86e-0*17

curl -D - "http://localhost:8080/test/MyBLOB"

Returning:

HTTP/1.1 200 OK

PBMS_CHECKSUM: A3762FF16159FAB246EBA2BE50F98CF4

PBMS_BLOB_SIZE: 17

PBMS_LAST_ACCESS: 1236200654

PBMS_ACCESS_COUNT: 0

PBMS_CREATION_TIME: 1236200654

PBMS_BLOB_ALIAS: MyBLOB

Content-Length: 17

A BLOB with alias

In this example the BLOB has been uploaded to the PBMS engine but not yet referenced so it will be automatically deleted after a preset time. 

As you can see several custom headers have been added to the reply:

• PBMS_CHECKSUM is the MD5 checksum of the BLOB data.
  
• PBMS_BLOB_SIZE is the size of the BLOB data in bytes.
  
• PBMS_LAST_ACCESS is the last access time of the BLOB in seconds since Jan. 1 1970.
  
• PBMS_ACCESS_COUNT is the number of times the BLOB has been downloaded.
  
• PBMS_CREATION_TIME is the creation time of the BLOB in seconds since Jan. 1 1970.

It is also possible to fetch back just the header info with out the BLOB data, example:

curl -H "PBMS_RETURN_INFO_ONLY:yes" -D - "http://localhost:8080/test/MyBLOB"

Returning:

HTTP/1.1 200 OK

PBMS_CHECKSUM: A3762FF16159FAB246EBA2BE50F98CF4

PBMS_BLOB_SIZE: 17

PBMS_LAST_ACCESS: 1236200654

PBMS_ACCESS_COUNT: 0

PBMS_CREATION_TIME: 1236200654

PBMS_BLOB_ALIAS: MyBLOB

Content-Length: 0

The PBMS BLOB metadat enables users to store BLOB specific data with the BLOB and have it returned to them with out having to create a separate database table to store it and then execute separate SQL command to update it and retrieve it when ever they upload or download a BLOB. 

The BLOB alias allows users to generate their own names for BLOBs which they can then use to access the BLOB without having to make a call to the database to get the PBMS generated URL.